Thread: Provide read-only access to system catalog tables
Hi Team,
Appreciate your time to look into this.
I have a requirement, where a user has to be provided DDL access on the schema (which is provided to the user) and as there is some development work in process the user has to be provided the read only access on system catalog tables (information_schema and pg_catalog)
I have surfed a lot of materials online, but did not get any solution for the same.
Request you to share some valuable input on this.
Thank You.
Regards,
Chirag Karkera
Chirag Karkera
On Tuesday, May 17, 2022, Chirag Karkera <chiragkrkr102@gmail.com> wrote:
the user has to be provided the read only access on system catalog tables (information_schema and pg_catalog)
All roles have this, no action required.
David J.
Thanks David for your reply!
But when i created a role i am not able to view objects under information_schema.*
I mean I am not able to view the data, I can see only the column names.
Thanks.
Regards,
Chirag Karkera
Chirag Karkera
On Tue, May 17, 2022 at 6:40 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, May 17, 2022, Chirag Karkera <chiragkrkr102@gmail.com> wrote:the user has to be provided the read only access on system catalog tables (information_schema and pg_catalog)All roles have this, no action required.David J.
On Tue, May 17, 2022 at 6:21 AM Chirag Karkera <chiragkrkr102@gmail.com> wrote:
Thanks David for your reply!But when i created a role i am not able to view objects under information_schema.*I mean I am not able to view the data, I can see only the column names.
Which goes to demonstrate you have permissions. But information_schema uses the permissions of the executing user to decide what to show - it is pre-filtered (and doesn't address PostgreSQL-only features). If you need less restrictive behavior your best bet is to just use the system catalogs. Those give you everything.
David J.
Thank you for the clarification.
Will use the system catalogs tables.
Thank You.
Regards,
Chirag Karkera
On Tue, May 17, 2022 at 6:59 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, May 17, 2022 at 6:21 AM Chirag Karkera <chiragkrkr102@gmail.com> wrote:Thanks David for your reply!But when i created a role i am not able to view objects under information_schema.*I mean I am not able to view the data, I can see only the column names.Which goes to demonstrate you have permissions. But information_schema uses the permissions of the executing user to decide what to show - it is pre-filtered (and doesn't address PostgreSQL-only features). If you need less restrictive behavior your best bet is to just use the system catalogs. Those give you everything.David J.