4

I need to create role users of which can only read (select) data. I can do it for a particular schema. But I need it to work with all existing and future schemas (existing at least). How can I do it?

I've tried

GRANT select ON DATABASE my_db TO my_role;

But it says that I can't:

0LP01 invalid_grant_operation

p.s: I need to grant rights on select to all schemas in db, I'm just trying to do it with db in query as a option, but if you know how to do give role permission on select for the hole db, I'll be glad to see that too

1
  • I need user to be able to select data from all tables from all schemas in db. I tried to do it by granting him this permission on all db, but I can't do it...
    – Mzia
    Apr 21, 2021 at 9:49

2 Answers 2

6

You can wait for PostgsqlSQL v14 and its pg_read_all_data system role.

Other than that, you will have to grant USAGE on all schemas and SELECT on all tables individually.

GRANT SELECT ON ALL TABLES IN SCHEMA will make that job much easier.

My recommendation is to issue all these grants not to the end user role, but to an intermediate role that you then grant to the end user. That way it will be easy to drop qaread.

3
  • And what about me mixing up schemas and db? Is there a way to grant select role on the whole sb. so that all schemas would be available for it too?
    – Mzia
    Apr 21, 2021 at 10:03
  • No. There is no SELECT privilege on the database level, as you found out. I originally thought you mixed those two up, but it seems like you wanted to achieve what you want on the database level, which is not possible. Apr 21, 2021 at 10:21
  • Understood, thank you
    – Mzia
    Apr 21, 2021 at 10:38
1

You have to set the permissions up for each existing schema manually or with a script to speed things up. If there are not too many schemas, something like

GRANT USAGE ON SCHEMA schemaX to myrole; GRANT SELECT ON ALL TABLES IN SCHEMA schemaX to myrole;

is pretty efficient.

To handle future schemas & tables, use: ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS to [YOUR_ROLE or YOUR_USER];

ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES to [YOUR_ROLE or YOUR_USER];

Be sure to run that statement as the creating user for the schemas, or specify ALTER DEFAULT PRIVILEGES FOR ROLE creating_role. Full explanation of that is available at https://dba.stackexchange.com/a/265185/245068

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.