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 successfully assigned read-only permission to a particular schema using the following command

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 by 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

You can create grant sql queries which fetches the grants and execute them.

select distinct 'GRANT SELECT on ALL TABLES IN SCHEMA '||schemaname||' to user;' from pg_tables where schemaname not in ('pg_catalog','information_schema');

Answer 2

One DO statement can do the trick:

DO
$$DECLARE
   v_schema name;
BEGIN
   FOR v_schema IN
      SELECT nspname FROM pg_namespace
      WHERE nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
        AND nspname NOT LIKE 'pg_temp%'
        AND nspname NOT LIKE 'pg_toast_temp%'
   LOOP
      EXECUTE format(
                 'GRANT SELECT ON ALL TABLES IN SCHEMA %I TO readonly_user',
                 v_schema
              );
   END LOOP;
END;$$;