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.

• Laurenz Albe: Please don't write the same question on several stackexchange sites at once.

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.

• jithin giri: Hi Phill, thank you for the response. Our requirement is, some internal users need read-only access to all these schemas, also we can't combine these schemas to a single schema for some reason. We have a similar structure in MySQL and I could fulfil my team's requirement using a single command that I mentioned above, but I failed to find such command in PostgreSQL.
• jithin giri: I already thought of automation through scripting and adding the above single statement in the code while dropping and recreating schema as you mentioned, but all these things will be a waste if I could find a single command, that's why I need help to find out this command if it is there.
• András Váczi: @jithingiri there is no command that would do what you need. The best you can do is what Phill suggests.