Metabase v0.48.4: Problem connecting CrateDB

[amotl]

Problem

On the comments to the video Metabase and CrateDB: Data Visualization, a few users, possibly @salamander101 and @JoeHan007, reported that:

It does not work with Metabase opensource 0.50.32.1 and CrateDB opensource 5.9.2.

[amotl]

Hi there,

thanks for your report. Can you provide more information around the problem you are running into, in order to support us to investigate and resolve this issue optimally? ¹

• Maybe it stopped working with Metabase v0.50.32.1, released two weeks ago, while it worked before?
• Maybe it was a short-term hiccup with Metabase, and has been resolved already?
  Do you mind to try using Metabase v0.51.4.2, released today?
• Did it stop working after upgrading to CrateDB 5.9.2 in the end, while it worked before?
• What exactly means "It does not work"? Can you provide more details on the error(s) you are observing, and how to reproduce them?

With kind regards,
Andreas.

Footnotes

1. Thanks for providing version numbers from the very beginning. However, we need a better picture about the genesis, so we are humbly asking to read our questions, and/or report more extensively about what you think could be important for us to know, in order to resolve the problem. ↩

[michaelkremmel]

@amotl Thanks for creating this ticket. I tried this with the latest Metabase image from Docker, running locally and connecting it to a CRFREE Cluster in CrateDB Cloud. The issue I'm facing is that once I've created the database connection, it starts syncing tables, but it never stops. I've tried it with two different data samples, the weather_data first (9.3 MB), and since it took forever, I tried it with a smaller one, the marketing_data sample (217 KB), but the same here. I can click on "Dismiss sync spinner manually," and the spinner goes away, but the data is still not synced after ~15 minutes.

[amotl]

Hi again. We have been able to validate the integration with Metabase worked well until v0.48.3, and started failing with v0.48.4.

• Metabase: Add test harness for validating Metabase against CrateDB cratedb-examples#750

It might be related to this SQL statement that reflects table privileges, which might have been updated, and trips CrateDB.

with table_privileges as (
 select
   NULL as role,
   t.schemaname as schema,
   t.objectname as table,
   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'UPDATE') as update,
   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'SELECT') as select,
   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'INSERT') as insert,
   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'DELETE') as delete
 from (
   select schemaname, tablename as objectname from pg_catalog.pg_tables
   union
   select schemaname, viewname as objectname from pg_catalog.pg_views
   union
   select schemaname, matviewname as objectname from pg_catalog.pg_matviews
 ) t
 where t.schemaname !~ '^pg_'
   and t.schemaname <> 'information_schema'
   and pg_catalog.has_schema_privilege(current_user, t.schemaname, 'USAGE')
)
select t.*
from table_privileges t;

SQLParseException[line 5:17: no viable alternative at input 'select\nNULL as role,\nt.schemaname as schema,\nt.objectname as table']

metabase  | 2024-11-23 17:06:13,684 WARN sync.util :: Error in sync step Sync postgres Database 2 'cratedb-testdrive'
metabase  | 2024-11-23 17:07:00,031 ERROR core.JobRunShell :: Job DEFAULT.metabase.task.sync-and-analyze.job threw an unhandled Exception:
metabase  | org.postgresql.util.PSQLException: ERROR: line 5:20: no viable alternative at input 'select\n   NULL as role,\n   t.schemaname as schema,\n   t.objectname as table'

With version v0.48.3, this was apparently just a warning, not failing the sync-and-analyze job.

metabase  | 2024-11-23 18:06:59,370 WARN sync.util :: Error running step 'sync-table-privileges' for postgres Database 2 'cratedb-testdrive'
metabase  | org.postgresql.util.PSQLException: ERROR: line 5:18: no viable alternative at input 'select\n  NULL as role,\n  t.schemaname as schema,\n  t.tablename as table'

[amotl]

[...] which might have been updated, and trips CrateDB.

In metabase/driver/postgres.clj, the difference between v0.48.3 and v0.48.4 is not significant, and the previous query raised the same error. What might be different now is that the outcome of the function driver/current-user-table-privileges is evaluated stronger, so the behavior is no longer graceful if that fails.

Maybe there are some hints in the Driver Interface Changelog for Metabase 0.49.0?

[amotl]

The reason why this SQL statement trips, by the way, is because it uses identifiers role, schema, and table, which are reserved in CrateDB.

When quoting them properly, this particular query works well.

with table_privileges as (
 select
   NULL as "role",
   t.schemaname as "schema",
   t.objectname as "table",
   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'UPDATE') as "update",
   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'SELECT') as "select",
   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'INSERT') as "insert",
   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'DELETE') as "delete"
 from (
   select schemaname, tablename as objectname from pg_catalog.pg_tables
   union
   select schemaname, viewname as objectname from pg_catalog.pg_views
   union
   select schemaname, matviewname as objectname from pg_catalog.pg_matviews
 ) t
 where t.schemaname !~ '^pg_'
   and t.schemaname <> 'information_schema'
   and pg_catalog.has_schema_privilege(current_user, t.schemaname, 'USAGE')
)
select t.*
from table_privileges t;

[amotl]

Proposals

1. For a start, we may try to improve the PostgreSQL driver on corresponding spots that would improve compatibility with CrateDB, and probe if upstream would accept relevant patches. First, we may want to try if improving this particular query yields some improvements when connecting CrateDB at all.

   diff --git a/application/metabase/backlog.md b/application/metabase/backlog.md
   index a1a5011..e00de4c 100644
   --- a/application/metabase/backlog.md
   +++ b/application/metabase/backlog.md
   @@ -10,13 +10,13 @@ metabase  | org.postgresql.util.PSQLException: ERROR: line 5:20: no viable alter
    ```sql
    with table_privileges as (
     select
   -   NULL as role,
   -   t.schemaname as schema,
   -   t.objectname as table,
   -   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'UPDATE') as update,
   -   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'SELECT') as select,
   -   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'INSERT') as insert,
   -   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'DELETE') as delete
   +   NULL as "role",
   +   t.schemaname as "schema",
   +   t.objectname as "table",
   +   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'UPDATE') as "update",
   +   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'SELECT') as "select",
   +   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'INSERT') as "insert",
   +   pg_catalog.has_table_privilege(current_user, '"' || t.schemaname || '"' || '.' || '"' || t.objectname || '"',  'DELETE') as "delete"
     from (
       select schemaname, tablename as objectname from pg_catalog.pg_tables
       union

2. If not, and maybe for the mid-term, my advise is to provide a dedicated CrateDB adapter to Metabase, deriving most of its functionality from the PostgreSQL adapter ¹, and only overriding specifics where needed. This would also give CrateDB a dedicated placement in Metabase's list of supported databases.

Footnotes

1. Possibly similar like the PostgreSQL <-> Redshift relationship and friends. ↩