Category: Postgres

Read-only user in postgres

  1. Connect as admin
  2. create user:
    CREATE USER 'uuu' PASSWORD 'ppp'
  3. switch to necessary db (if not already):
    \connect ddd
  4. add rights:
    GRANT CONNECT ON DATABASE ddd TO uuu;
    GRANT USAGE ON SCHEMA public TO uuu;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO uuu;

This only affects tables that have already been created. More powerfully, you can automatically have default roles assigned to new objects in future:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO uuu;