How-to "PowerBI Desktop with CrateDB": a simpler way to connect (without pgODBC)

[g-metan]

Hi there,
In current version of PowerBI Desktop, there is dedicated PostgreSQL datasource in category "Databases", so no need to install PostgreSQL ODBC driver and configure DSN for CrateDB.

[amotl]

Dear Gennady,

thank you for writing in and notifying us about this improvement to PowerBI. Does that mean we should update the setup & configuration walkthrough for PowerBI & CrateDB [1] accordingly?

With kind regards,
Andreas.

[1] https://github.com/crate/crate-howtos/blob/master/docs/integrations/powerbi-desktop.rst

/cc @hammerhead, @proddata

[g-metan]

Hi, Andeas.
It would be nice, as there is no need to install pgODBC, which requires admin rights in Windows. Or you can mention both ways of connecting. Most corporate (domain) user accounts don't have admin rights. So, if you provide (describe) a simpler way, we appreciate it.
However, while testing Postgres datasource, I was able to connect only under crate user, not a limited rights user, which I created with GRANT DQL. Which I am unable to explain.

CREATE USER PowerBI_user WITH (password = 'pa$w0rd');
GRANT DQL ON SCHEMA doc TO PowerBI_user

[proddata]

Hi @g-metan

However, while testing Postgres datasource, I was able to connect only under crate user, not a limited rights user, which I created with GRANT DQL. Which I am unable to explain.

Which CrateDB version are you using for testing?

With version 4.5 and older, you would also have to grant permissions on the pg_catalog schema, which is often needed for native Postgres clients. However this has been changed with 4.6 onwards.

from release notes v4.6
Users can now read tables within the pg_catalog schema without explicit DQL permission. They will only see records the user has privileges on.

However there also was a bug with versions 4.5 and lower with limited privileged user together with the default postgres driver, which should be resolved with 4.6 (https://community.crate.io/t/problem-with-powerbi-desktop-and-permissions/722/11)

[g-metan]

I am using CrateDB version 4.6.1
I found that I should enter username in lowercase in Power BI Desktop in order to connect to CrateDB.
And CrateDB stores user privileges table with username in lowercase (e.g. "powerbi_user"), despite I created it as "PowerBI_user"

[proddata]

@g-metan

This sounds strange. I can't really replicate that 😟

cr> CREATE USER "tEsT" WITH ( password = 'te$t');                                                                               
CREATE OK, 1 row affected  (0.318 sec)

cr> SELECT * FROM sys.users;                                                                                                    
+-------+----------+-----------+
| name  | password | superuser |
+-------+----------+-----------+
| crate | NULL     | TRUE      |
| tEsT  | ******** | FALSE     |
+-------+----------+-----------+
SELECT 2 rows in set (0.003 sec)

cr> GRANT DQL TO "tEsT";                                                                                                        
GRANT OK, 1 row affected  (0.075 sec)

cr> SELECT * FROM sys.PRIVILEGES;                                                                                               
+---------+---------+---------+-------+-------+------+
| class   | grantee | grantor | ident | state | type |
+---------+---------+---------+-------+-------+------+
| CLUSTER | tEsT    | crate   |  NULL | GRANT | DQL  |
+---------+---------+---------+-------+-------+------+
SELECT 1 row in set (0.002 sec)

[g-metan]

At least, this is how I see it in CrateDB Admin WebUI:

[proddata]

CrateDB as Postgres lowercases all identifiers, that aren't specifically but in doubles quotes ". PowerBI as client probably puts the identifier in double quotes

i.e.

CREATE USER PowerBI_user WITH (password = 'pa$w0rd');

is equivalent to

CREATE USER powerbi_user WITH (password = 'pa$w0rd');

---

but different to

CREATE USER "PowerBI_user" WITH (password = 'pa$w0rd');

also see https://crate.io/docs/crate/reference/en/4.6/sql/general/lexical-structure.html#key-words-and-identifiers

[g-metan]

I followed SQL syntax in https://crate.io/docs/crate/reference/en/4.6/admin/privileges.html
and there is no mention of doublequotes and how they affect case sensitivity.
I am not familiar with postgres SQL syntax.
I run SQL queries for user creation in CrateDB Admin WebUI.

[g-metan]

Update: PowerBI Desktop uses npgsql v. 4.0.10 to connect to Postgres datasource.