Power BI Postgres: Read Only User-CodePudding

I already posted a similar question to the Power Bi forum, but probably it's too short-sighted to search the solution on Power Bi side.

We have a postgres schema and a postgres user that has the following rights: granted connect to db and schema, revoked create on schema, granted select privilege on all tables in schema. Nothing more. The user works in the db client, can only read from the tables in the schema, cannot create, insert, read, update, delete anything.

When we import the schema in Power Bi with our Read Only User, the relations between the tables is not displayed. The postgres documentation writes the following:

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

This would mean, our Read Only User must have granted insert, delete or update permissions to make Power Bi able to display the table relations?! Our postgres db shall work as a datawarehouse and give customers the possibility to connect from their local Power Bi Desktop or their companies Power Bi instance, so we don't have too much influence on Power Bi side at all.

Is there a permission we can give the Read Only User that keeps him read only but allows to resolve the table relations in Power Bi?

Thanks and kind regards!

We tried giving the user a insert privilege on all tables, that made Power Bi resolve the table relations but is conflicting with our Read Only behave. We read documentation and tried to find out, if there are other possibilities than the above.

CodePudding user response：

That is really unfortunate, but the SQL standard explicitly requires that (e.g. in ISO/IEC 9075-11:2003, 5.58):

5.58 TABLE_CONSTRAINTS view

Function

Identify the table constraints defined on tables in this catalog that are accessible to a given user or role.

Definition

CREATE VIEW TABLE_CONSTRAINTS AS
    SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME,
           TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
           CONSTRAINT_TYPE, IS_DEFERRABLE, INITIALLY_DEFERRED
    FROM DEFINITION_SCHEMA.TABLE_CONSTRAINTS
    WHERE ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ) IN
            ( SELECT TP.TABLE_CATALOG, TP.TABLE_SCHEMA, TP.TABLE_NAME
              FROM DEFINITION_SCHEMA.TABLE_PRIVILEGES AS TP
              WHERE TP.PRIVILEGE_TYPE <> 'SELECT'
[...]

So really this is a shortcoming of Power BI, if it is supposed to work with a read-only user. On the other hand, if they want to be database agnostic, they have little choice but to use the information_schema.

But interesting as that may be, you are looking for a solution or a workaround. If you look at the view definition of information_schema.table_constraints, you will see that visibility is determined by this condition:

pg_has_role(r.relowner, 'USAGE'::text) OR
has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR
has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES'::text)

I'd say that the most promising privilege here is REFERENCES, which allows a user to create a foreign key pointing to the table. If the user doesn't have the CREATE privilege on any schema, they cannot create a table that references the table, so the privilege is effectively useless. Still, if you grant the user REFERENCES on all tables, the constraints will become visible. It is hacky, but it should be good enough.