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;
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.