ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user

ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user

Asked on January 9, 2019 in PostgreSQL.
Add Comment


  • 1 Answer(s)

    This  complete answer for PostgreSQL 9+, updated recently.

    CREATE USER readonly WITH ENCRYPTED PASSWORD 'readonly';
    GRANT USAGE ON SCHEMA public to readonly;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
    -- repeat code below for each database:
    GRANT CONNECT ON DATABASE foo to readonly;
    \c foo
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO readonly; --- this grants privileges on new tables generated in new database "foo"
    GRANT USAGE ON SCHEMA public to readonly;
    GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
    

    If everyone find small function , and preferably one that is able to perform this for all existing databases, extra kudos.

    Answered on January 9, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.