Read Only DB User

[Anonymous]

Hello,

we are currently trying to use a Read Only PostgreSql user which has only credentials to select from a certain schema.

When I connect to that schema with the Read Only user, Power BI cannot resolve the constraints / references between the tables in that schema and display them. It seems Power BI therefore uses the table_constraints to check the constraints, but a read only user that only has SELECT credentials doesn't occur there.

The PostgreSql documentation says the following to this table:

"The view table_constraints contains all constraints belonging to tables that the current user owns or has some privilege other than SELECT on."

So my question is, is there a possibility to use such a Read Only user in Power BI without giving this user any privilieges except SELECT on the tables? E.g. DBWeaver shows the constraints between the tables in the schema even for this Read Only user without any problems.

Thanks and kind regards,

Dirk

[Anonymous · Accepted solution]

For people searching for such a solution in the future: It's detached from Power BI and related to the database. We know extend the privileges by REFERENCES, so the user can do nothing except SELECT and REFERENCES and since he doesn't own a table or something else, a foreign key cannot be created. So he has the right to REFERENCE, but cannot use it at all.

[amitchandak]

@Anonymous , power bi to read the schema(Schema Tables) to get the table and column name, make sure it has access to that

[Anonymous]

@amitchandak Not sure, if I understood your answer. The read only user of course has read (SELECT) access to all the tables and columns in the schema. But SELECT only, no INSERT, UPDATE, DELETE, TRUNCATE. But for any reason it seems Power Bi forces the user to have not only SELECT access to the tables, at least when using Postgres. Otherwise, the relations / constraints between the tables are neither shown nor known when importing them into Power Bi.