Hi,
I am using a single node Crate 4.5.1 installation and I am trying to connect using PowerBI Postgresql connector. If I use the crate user it works correctly, but if a create a user in crate and only allow DQL to a schema I get an Npgsql error:
when adding a new datasource to PowerBI. I think it may be a permission error, currently the user has DQL GRANT permissions on pg_catalog and the data schema. ¿Is there any other schema should I add?
Regards,
Could you maybe provide a list of queries from sys.jobs_log?
On a Mac currently and would need to start a Windows VM.
The pg_catalog permissions should be fixed with an upcoming version (probably 4.6)
see any user can access pg_catalog by default by jeeminso · Pull Request #11282 · crate/crate · GitHub
I didn’t knew sys.jobs_log before. I have looked for all the queries powerBI mades and executed them using crash, I paste the results:
cr> /*** Load all supported types ***/
SELECT ns.nspname, a.typname, a.oid, a.typrelid, a.typbasetype,
CASE WHEN pg_proc.proname='array_recv' THEN 'a' ELSE a.typtype END AS type,
CASE
WHEN pg_proc.proname='array_recv' THEN a.typelem
WHEN a.typtype='r' THEN rngsubtype
ELSE 0
END AS elemoid,
CASE
WHEN pg_proc.proname IN ('array_recv','oidvectorrecv') THEN 3 /* Arrays last */
WHEN a.typtype='r' THEN 2 /* Ranges before */
WHEN a.typtype='d' THEN 1 /* Domains before */
ELSE 0 /* Base types first */
END AS ord
FROM pg_type AS a
JOIN pg_namespace AS ns ON (ns.oid = a.typnamespace)
JOIN pg_proc ON pg_proc.oid = a.typreceive
LEFT OUTER JOIN pg_class AS cls ON (cls.oid = a.typrelid)
LEFT OUTER JOIN pg_type AS b ON (b.oid = a.typelem)
LEFT OUTER JOIN pg_class AS elemcls ON (elemcls.oid = b.typrelid)
LEFT OUTER JOIN pg_range ON (pg_range.rngtypid = a.oid)
WHERE
a.typtype IN ('b', 'r', 'e', 'd') OR /* Base, range, enum, domain */
(a.typtype = 'c' AND cls.relkind='c') OR /* User-defined free-standing composites (not table composites) by default */
(pg_proc.proname='array_recv' AND (
b.typtype IN ('b', 'r', 'e', 'd') OR /* Array of base, range, enum, domain */
(b.typtype = 'p' AND b.typname IN ('record', 'void')) OR /* Arrays of special supported pseudo-types */
(b.typtype = 'c' AND elemcls.relkind='c') /* Array of user-defined free-standing composites (not table composites) */
)) OR
(a.typtype = 'p' AND a.typname IN ('record', 'void')) /* Some special supported pseudo-types */
ORDER BY ord
;
+---------+---------+-----+----------+-------------+------+---------+-----+
| nspname | typname | oid | typrelid | typbasetype | type | elemoid | ord |
+---------+---------+-----+----------+-------------+------+---------+-----+
+---------+---------+-----+----------+-------------+------+---------+-----+
LOAD 0 rows in set (0.003 sec)
cr> /*** Load field definitions for (free-standing) composite types ***/
SELECT typ.oid, att.attname, att.atttypid
FROM pg_type AS typ
JOIN pg_namespace AS ns ON (ns.oid = typ.typnamespace)
JOIN pg_class AS cls ON (cls.oid = typ.typrelid)
JOIN pg_attribute AS att ON (att.attrelid = typ.typrelid)
WHERE
(typ.typtype = 'c' AND cls.relkind='c') AND
attnum > 0 AND /* Don't load system attributes */
NOT attisdropped
ORDER BY typ.oid, att.attnum;
+-----+---------+----------+
| oid | attname | atttypid |
+-----+---------+----------+
+-----+---------+----------+
LOAD 0 rows in set (0.004 sec)
cr> /*** Load enum fields ***/
SELECT pg_type.oid, enumlabel
FROM pg_enum
JOIN pg_type ON pg_type.oid=enumtypid
ORDER BY oid, enumsortorder;
+-----+-----------+
| oid | enumlabel |
+-----+-----------+
+-----+-----------+
LOAD 0 rows in set (0.001 sec)
cr> select character_set_name from INFORMATION_SCHEMA.character_sets;
+--------------------+
| character_set_name |
+--------------------+
| UTF8 |
+--------------------+
SELECT 1 row in set (0.000 sec)
cr> select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
from INFORMATION_SCHEMA.tables
where TABLE_SCHEMA not in ('information_schema', 'pg_catalog')
order by TABLE_SCHEMA, TABLE_NAME;
+--------------+-----------------+------------+
| table_schema | table_name | table_type |
+--------------+-----------------+------------+
| bemp_demo | meter_config | BASE TABLE |
| bemp_demo | prediction_data | BASE TABLE |
| bemp_demo | processed_data | BASE TABLE |
| bemp_demo | raw_data | BASE TABLE |
| bemp_demo | var_names | BASE TABLE |
+--------------+-----------------+------------+
SELECT 5 rows in set (0.001 sec)
cr> select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
from INFORMATION_SCHEMA.tables
where TABLE_SCHEMA not in ('information_schema', 'pg_catalog')
order by TABLE_SCHEMA, TABLE_NAME;
+--------------+-----------------+------------+
| table_schema | table_name | table_type |
+--------------+-----------------+------------+
| bemp_demo | meter_config | BASE TABLE |
| bemp_demo | prediction_data | BASE TABLE |
| bemp_demo | processed_data | BASE TABLE |
| bemp_demo | raw_data | BASE TABLE |
| bemp_demo | var_names | BASE TABLE |
+--------------+-----------------+------------+
SELECT 5 rows in set (0.001 sec)
I have tried the first query using crate user and the result is different, may be that the problem:
cr> /*** Load all supported types ***/
SELECT ns.nspname, a.typname, a.oid, a.typrelid, a.typbasetype,
CASE WHEN pg_proc.proname='array_recv' THEN 'a' ELSE a.typtype END AS type,
CASE
WHEN pg_proc.proname='array_recv' THEN a.typelem
WHEN a.typtype='r' THEN rngsubtype
ELSE 0
END AS elemoid,
CASE
WHEN pg_proc.proname IN ('array_recv','oidvectorrecv') THEN 3 /* Arrays last */
WHEN a.typtype='r' THEN 2 /* Ranges before */
WHEN a.typtype='d' THEN 1 /* Domains before */
ELSE 0 /* Base types first */
END AS ord
FROM pg_type AS a
JOIN pg_namespace AS ns ON (ns.oid = a.typnamespace)
JOIN pg_proc ON pg_proc.oid = a.typreceive
LEFT OUTER JOIN pg_class AS cls ON (cls.oid = a.typrelid)
LEFT OUTER JOIN pg_type AS b ON (b.oid = a.typelem)
LEFT OUTER JOIN pg_class AS elemcls ON (elemcls.oid = b.typrelid)
LEFT OUTER JOIN pg_range ON (pg_range.rngtypid = a.oid)
WHERE
a.typtype IN ('b', 'r', 'e', 'd') OR /* Base, range, enum, domain */
(a.typtype = 'c' AND cls.relkind='c') OR /* User-defined free-standing composites (not table composites) by default */
(pg_proc.proname='array_recv' AND (
b.typtype IN ('b', 'r', 'e', 'd') OR /* Array of base, range, enum, domain */
(b.typtype = 'p' AND b.typname IN ('record', 'void')) OR /* Arrays of special supported pseudo-types */
(b.typtype = 'c' AND elemcls.relkind='c') /* Array of user-defined free-standing composites (not table composites) */
)) OR
(a.typtype = 'p' AND a.typname IN ('record', 'void')) /* Some special supported pseudo-types */
ORDER BY ord;
+------------+------------------------------+------+----------+-------------+------+---------+-----+
| nspname | typname | oid | typrelid | typbasetype | type | elemoid | ord |
+------------+------------------------------+------+----------+-------------+------+---------+-----+
| pg_catalog | int8 | 20 | 0 | 0 | b | 0 | 0 |
| pg_catalog | varchar | 1043 | 0 | 0 | b | 0 | 0 |
| pg_catalog | regproc | 24 | 0 | 0 | b | 0 | 0 |
| pg_catalog | interval | 1186 | 0 | 0 | b | 0 | 0 |
| pg_catalog | int2 | 21 | 0 | 0 | b | 0 | 0 |
| pg_catalog | regclass | 2205 | 0 | 0 | b | 0 | 0 |
| pg_catalog | timetz | 1266 | 0 | 0 | b | 0 | 0 |
| pg_catalog | timestamptz | 1184 | 0 | 0 | b | 0 | 0 |
| pg_catalog | oid | 26 | 0 | 0 | b | 0 | 0 |
| pg_catalog | float8 | 701 | 0 | 0 | b | 0 | 0 |
| pg_catalog | bool | 16 | 0 | 0 | b | 0 | 0 |
| pg_catalog | float4 | 700 | 0 | 0 | b | 0 | 0 |
| pg_catalog | char | 18 | 0 | 0 | b | 0 | 0 |
| pg_catalog | name | 19 | 0 | 0 | b | 0 | 0 |
| pg_catalog | date | 1082 | 0 | 0 | b | 0 | 0 |
| pg_catalog | numeric | 1700 | 0 | 0 | b | 0 | 0 |
| pg_catalog | text | 25 | 0 | 0 | b | 0 | 0 |
| pg_catalog | json | 114 | 0 | 0 | b | 0 | 0 |
| pg_catalog | point | 600 | 0 | 0 | b | 0 | 0 |
| pg_catalog | record | 2249 | 0 | 0 | p | 0 | 0 |
| pg_catalog | timestamp without time zone | 1114 | 0 | 0 | b | 0 | 0 |
| pg_catalog | int4 | 23 | 0 | 0 | b | 0 | 0 |
| pg_catalog | oidvector | 30 | 0 | 0 | b | 0 | 3 |
| pg_catalog | _float4 | 1021 | 0 | 0 | a | 700 | 3 |
| pg_catalog | _timestamp without time zone | 1115 | 0 | 0 | a | 1114 | 3 |
| pg_catalog | _bool | 1000 | 0 | 0 | a | 16 | 3 |
| pg_catalog | _interval | 1187 | 0 | 0 | a | 1186 | 3 |
| pg_catalog | _char | 1002 | 0 | 0 | a | 18 | 3 |
| pg_catalog | _json | 199 | 0 | 0 | a | 114 | 3 |
| pg_catalog | _record | 2287 | 0 | 0 | a | 2249 | 3 |
| pg_catalog | _text | 1009 | 0 | 0 | a | 25 | 3 |
| pg_catalog | _regproc | 1008 | 0 | 0 | a | 24 | 3 |
| pg_catalog | _int8 | 1016 | 0 | 0 | a | 20 | 3 |
| pg_catalog | _point | 1017 | 0 | 0 | a | 600 | 3 |
| pg_catalog | _int2 | 1005 | 0 | 0 | a | 21 | 3 |
| pg_catalog | _regclass | 2210 | 0 | 0 | a | 2205 | 3 |
| pg_catalog | _int4 | 1007 | 0 | 0 | a | 23 | 3 |
| pg_catalog | _float8 | 1022 | 0 | 0 | a | 701 | 3 |
| pg_catalog | _numeric | 1231 | 0 | 0 | a | 1700 | 3 |
| pg_catalog | _timestamptz | 1185 | 0 | 0 | a | 1184 | 3 |
| pg_catalog | _date | 1182 | 0 | 0 | a | 1082 | 3 |
| pg_catalog | _varchar | 1015 | 0 | 0 | a | 1043 | 3 |
| pg_catalog | _timetz | 1270 | 0 | 0 | a | 1266 | 3 |
+------------+------------------------------+------+----------+-------------+------+---------+-----+
LOAD 43 rows in set (0.025 sec)
Hi @iames
Thanks for the queries. Really helped to identify the problem.
It seems like even with DQL on pg_catalog a SELECT on pg_proc only returns a subset of records. with the JOIN on pg_type this results in an empty set. This should be solved with CrateDB v4.6 (currently available as nightly build, with scheduled release for end of june/start of july) which grants rights to pg_catalog by default (as postgres does)
Are you using the builtin Postgres connector or the Postgres ODBC driver?
thx
I am using the builtin connector since I want to use DirectQuery. With ODBC connector seems to work well but it needs to import all data and sometimes it is not viable.
How did you install CrateDB? are you using docker or tarball?
the query on pg_proc works with 4.6
The nightly (4.6 build) is available here:
Docker: Docker Hub
Tarball (Linux only): Index of /downloads/releases/nightly/
There was a workaround for earier versions using
which needs to be put in
C:\Users\xxxx\Documents\Power BI Desktop\Custom Connectors
and then allowed in the options
Anyway this should be resolved with 4.6
Sorry for the inconvenience
Thank you for your response. I am using Crate installed from apt repository on Ubuntu 20.04. I am going to check all this information. Again, thank you!
granting DQL on all schemas, also mitigates this issue. i.e.:
GRANT DQL to <username>
Today I have updated the server to 4.6.1 version (from 4.5.3) and tested again PowerBI connection. Now it connects correctly using PostgreSQL datasource using DirectQuery with non privileged user.
But the problem now are the timestamps: All show as “Error”, autogenerated ones and inserted ones, see Problem with PowerBI Desktop and Timestamps - #2 by iames.
