Can I GRANT SELECT on all schemas in a database?

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

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.

StackExchange Database Administrators Q#290186, answer score: 6