# Problem with PowerBI Desktop and permissions

**URL:** <https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722>\
**Category:** Integrations\
**Created:** [June 14, 2021, 2:09pm UTC](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722 "2021-06-14T14:09:01Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![iames](https://avatars.discourse-cdn.com/v4/letter/i/e99b99/32.png) [@iames](https://community.cratedb.com/u/iames)\
**Post date:** [June 14, 2021, 2:09pm UTC](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722/1 "2021-06-14T14:09:01Z")

</div>

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:

> [@](#):
>
> The field ‘table\_schema’ has a type currently unknown to Npgsql (OID 1043). You can retrieve it as a string by marking it as unknown, please see the FAQ.

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,

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [June 14, 2021, 2:25pm UTC](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722/2 "2021-06-14T14:25:51Z")

</div>

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](https://github.com/crate/crate/pull/11282)

---

<div class="post-metadata">

**Author:** ![iames](https://avatars.discourse-cdn.com/v4/letter/i/e99b99/32.png) [@iames](https://community.cratedb.com/u/iames)\
**Post date:** [June 14, 2021, 2:56pm UTC](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722/3 "2021-06-14T14:56:20Z")

</div>

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:

```auto
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:

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [June 15, 2021, 8:06am UTC](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722/4 "2021-06-15T08:06:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![iames](https://avatars.discourse-cdn.com/v4/letter/i/e99b99/32.png) [@iames](https://community.cratedb.com/u/iames)\
**Post date:** [June 15, 2021, 9:58am UTC](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722/5 "2021-06-15T09:58:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [June 15, 2021, 10:22am UTC](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722/6 "2021-06-15T10:22:34Z")

</div>

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](https://hub.docker.com/r/crate/crate/tags?page=1&ordering=last_updated)  
**Tarball** (Linux only): [Index of /downloads/releases/nightly/](https://cdn.crate.io/downloads/releases/nightly/)

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [June 15, 2021, 10:28am UTC](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722/7 "2021-06-15T10:28:00Z")

</div>

There was a workaround for earier versions using

> **[sgoley/DirectQuery-for-ODBC-in-PowerBI](https://github.com/sgoley/DirectQuery-for-ODBC-in-PowerBI/tree/master/ODBCPostgresDirect/bin/Debug)**
>
> master/ODBCPostgresDirect/bin/Debug

which needs to be put in  
`C:\Users\xxxx\Documents\Power BI Desktop\Custom Connectors`

and then allowed in the options

 ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/44c721372c679d6442874381526232b8f67173c3.jpeg)

 ![image](https://us1.discourse-cdn.com/flex020/uploads/crate/original/1X/c15af974881299876c6885d558d23ed69425f4d7.png)

* * *

Anyway this should be resolved with **4.6**  
Sorry for the inconvenience

---

<div class="post-metadata">

**Author:** ![iames](https://avatars.discourse-cdn.com/v4/letter/i/e99b99/32.png) [@iames](https://community.cratedb.com/u/iames)\
**Post date:** [June 15, 2021, 10:36am UTC](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722/8 "2021-06-15T10:36:53Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![proddata](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/proddata/32/1379_2.png) [@proddata](https://community.cratedb.com/u/proddata)\
**Post date:** [June 15, 2021, 10:57am UTC](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722/9 "2021-06-15T10:57:16Z")

</div>

granting DQL on all schemas, also mitigates this issue. i.e.:  
`GRANT DQL to <username>`

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [July 27, 2021, 12:56pm UTC](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722/10 "2021-07-27T12:56:46Z")

</div>

3 posts were merged into an existing topic: [Problem with PowerBI Desktop and Timestamps](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-timestamps/753/2)

---

<div class="post-metadata">

**Author:** ![iames](https://avatars.discourse-cdn.com/v4/letter/i/e99b99/32.png) [@iames](https://community.cratedb.com/u/iames)\
**Post date:** [July 27, 2021, 12:58pm UTC](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-permissions/722/11 "2021-07-27T12:58:40Z")

</div>

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](https://community.cratedb.com/t/problem-with-powerbi-desktop-and-timestamps/753/2).