role to access all information_schema.*?
| От | Ruben Laguna |
|---|---|
| Тема | role to access all information_schema.*? |
| Дата | |
| Msg-id | CAFOAOWJNJKLr8-YS7Q5wYnUHTjjaTbU6=rmTF_jW5W8fNfFamA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: role to access all information_schema.*?
|
| Список | pgsql-admin |
From what I see a user can only see in `select * from information_schema.tables` the tables that the user has been granted SELECT privilege.
From the https://www.postgresql.org/docs/current/predefined-roles.html I don't see any role that could be used to grant a user a "metadata viewer" privilege, right?
I can see that from `\dt+ information_schema.tables` that the view has a condition
```
WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char", 'p'::"char"])) AND NOT pg_is_other_temp_schema(nc.oid) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text));
```
where the rows will be filtered out if the current_user is not the owner or has SELECT privileges.
So, my question is: Is there is some other way to get a user to be a "metadata viewer" without been a user that also has access to the data in those tables?
Do you know if there is any plan to add such a role? Has it been discuss before and deemed a bad idea?
My use case is to have OpenMetadata to read the information_schema.* and publish the table name, column names, etc in the OM user interface. I would prefer keeping the privileges of the OM user to a minimum but it seems that right now the minimum would be `pg_read_all_data`
Best regards/Rubén
where the rows will be filtered out if the current_user is not the owner or has SELECT privileges.
So, my question is: Is there is some other way to get a user to be a "metadata viewer" without been a user that also has access to the data in those tables?
Do you know if there is any plan to add such a role? Has it been discuss before and deemed a bad idea?
My use case is to have OpenMetadata to read the information_schema.* and publish the table name, column names, etc in the OM user interface. I would prefer keeping the privileges of the OM user to a minimum but it seems that right now the minimum would be `pg_read_all_data`
Best regards/Rubén
/Rubén
В списке pgsql-admin по дате отправления: