PostgreSQL read only user

Question

We have a PostgreSQL database 'db1', which is having around 500 schemas. I am trying to create a read-only user for this particular PostgreSQL database.

I could find the following command for creating a user and granting permission schema wise

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO readonly_user;

but

1) We have around 500 schemas, so granting permission to each schema is difficult.

2) These schemas will be dynamically created or dropped on a daily basis, so giving permission at each time a schema is created is also a difficult task.

Is there any way to give the read-only permission for a whole database instead of schema?

In MySQL, I can do it using the following command

grant select on *.* to 'user_name'@'IP';

I am looking for a similar command in PostgreSQL

We are using PostgreSQL 10.

Answer 1

We have around 500 schemas, so granting permission to each schema is difficult.

No. It's tedious to do by hand, but it's also a prime candidate for automation, through scripting.

... dynamically created or dropped on a daily basis, so giving permission at each time a schema is created is also a difficult task.

I disagree. You're doing all the "difficult" stuff every day - dropping, recreating and repopulating all these schemas. I would suggest that adding this single statement to the end of that process is relatively trivial!

Is there any way to give the read-only permission for a whole database instead of schema?

Even if there is, you should not do so.

It sounds to me like these schemas are discrete entities (otherwise why not combine them into one?). Granting access across all of them strikes me as a Bad Idea.