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

Question

I want to create a user with only select privilege for all tables in all databases. I thought that I could get a list of databases and apply the following command for each database:

GRANT select ON DATABASE dbname to user1;

But I got the following error:

ERROR:  invalid privilege type SELECT for database

When I googled people advised to do the grant select operation for all tables. But new tables are being added always. So this is not an acceptable solution for me. Does anyone know any workarounds?

Answer 1

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

Answer 2

You cannot do this on database level, only on schema level.

Assuming you are only using the public schema in each database, you can do this:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user;

Answer 3

For Postgres versions lower than 9.0:

psql -d DBNAME -qAt -c "SELECT 'GRANT SELECT ON ' || tablename || ' TO USER;' 
FROM pg_tables WHERE schemaname = 'public'" | psql -d DBNAME

psql -d DBNAME -qAt -c "SELECT 'GRANT SELECT ON ' || viewname || ' TO USER;' 
FROM pg_views WHERE schemaname = 'public'" | psql -d DBNAME

psql -d DBNAME -qAt -c "SELECT 'GRANT SELECT ON ' || relname || ' TO USER;' 
FROM pg_statio_all_sequences WHERE schemaname = 'public'" | psql -d DBNAME

Answer 4

I do the next steps for create read-only user:

create your_user:

1. createuser --interactive --pwprompt

go to postgresql in your_databases:

2. psql your_database                      

define access privileges:

3. GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user; 

define default access privileges:

4. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO your_user;

Answer 5

I realize you've already said this isn't an acceptable answer, but it's the right answer anyway.

Specifying security (GRANT and REVOKE) is part of table design and testing.

Don't move tables to production before table definitions, security, tests, and test data are under version control.

Having said that, PostgreSQL doesn't have any SELECT permissions on databases. You can grant only CREATE, CONNECT, or TEMP permissions on databases.

You can grant SELECT on all tables in a given schema. I don't know how that affects tables created after running the GRANT statement, but it's fairly easy to test.

PostgreSQL Grant syntax

Answer 6

I'm aware that this is a very old question, but it still comes up when searching for this problem so I'm just posting this for an up-to-date answer.

As of Postgres 14 or newer, there are now predefined roles for that purpose:

CREATE USER your_readonly_user WITH PASSWORD 'changeme';
GRANT pg_read_all_data TO your_readonly_user;

According to the docs, this gives all the necessary privileges to:

"Read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all schemas, even without having it explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being used, an administrator may wish to set BYPASSRLS on roles which this role is GRANTed to."

See here for more info on the predefined roles.

Answer 7

Try:

CREATE USER readonly WITH ENCRYPTED PASSWORD 'yourpassword';
GRANT CONNECT ON DATABASE <database_name> to readonly;
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;