How to create a user with readonly privileges for all databases in Postgresql?

Question

You need to do 2 things: firstly, allow access to existing objects; and secondly, set the default access for new objects created from now on.

Note that granting access to “TABLES” includes views, but does not include sequences (such as the auto-increment function for “SERIAL” columns), so you’ll probably want to grant access to those as well.

The below assumes you want to do everything in the public schema. The ALTER DEFAULT PRIVILEGES statement can act on the entire database by omitting the IN SCHEMA ... clause; the GRANT has to be run once for each schema.

-- Grant access to current tables and views
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;
-- Now make sure that's also available on new tables and views by default
ALTER DEFAULT PRIVILEGES
    IN SCHEMA public -- omit this line to make a default across all schemas
    GRANT SELECT
ON TABLES 
TO user1;

-- Now do the same for sequences
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO user1;
ALTER DEFAULT PRIVILEGES
    IN SCHEMA public -- omit this line to make a default across all schemas
    GRANT SELECT, USAGE
ON SEQUENCES 
TO user1;

PostgreSQL manual

http://www.postgresql.org/docs/current/interactive/sql-grant.html
http://www.postgresql.org/docs/current/interactive/sql-alterdefaultprivileges.html

Leave a Comment Cancel reply