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.

Lohit Gupta · Accepted Answer · 2019-09-03 09:28:34Z

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');

answered Sep 3, 2019 at 9:28

Lohit Gupta

1,0816 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jithin giri Over a year ago

Thank you for the response Lohit, this will solve my first problem. Is there any way we can solve the second issue. " 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. "

Lohit Gupta Over a year ago

You can create a cron job or a database job to schedule the grant execution daily.

Laurenz Albe · Accepted Answer · 2019-09-03 15:24:26Z

1

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

answered Sep 3, 2019 at 15:24

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: read only user

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related