[postgresql] How do you create a read-only user in PostgreSQL?

I'd like to create a user in PostgreSQL that can only do SELECTs from a particular database. In MySQL the command would be:

GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';

What is the equivalent command or series of commands in PostgreSQL?

I tried...

postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy';
postgres=# GRANT SELECT ON DATABASE mydb TO xxx;

But it appears that the only things you can grant on a database are CREATE, CONNECT, TEMPORARY, and TEMP.

CREATE USER username SUPERUSER  password 'userpass';
ALTER USER username set default_transaction_read_only = on;

By default new users will have permission to create tables. If you are planning to create a read-only user, this is probably not what you want.

To create a true read-only user with PostgreSQL 9.0+, run the following steps:

# This will prevent default users from creating tables
REVOKE CREATE ON SCHEMA public FROM public;

# If you want to grant a write user permission to create tables
# note that superusers will always be able to create tables anyway
GRANT CREATE ON SCHEMA public to writeuser;

# Now create the read-only user
CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'strongpassword';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;

If your read-only user doesn't have permission to list tables (i.e. \d returns no results), it's probably because you don't have USAGE permissions for the schema. USAGE is a permission that allows users to actually use the permissions they have been assigned. What's the point of this? I'm not sure. To fix:

# You can either grant USAGE to everyone
GRANT USAGE ON SCHEMA public TO public;

# Or grant it just to your read only user
GRANT USAGE ON SCHEMA public TO readonlyuser;

I read trough all the possible solutions, which are all fine, if you remember to connect to the database before you grant the things ;) Thanks anyway to all other solutions!!!

user@server:~$ sudo su - postgres

create psql user:

postgres@server:~$ createuser --interactive 
Enter name of role to add: readonly
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

start psql cli and set a password for the created user:

postgres@server:~$ psql
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 9.5.14)
Type "help" for help.

postgres=# alter user readonly with password 'readonly';
ALTER ROLE

connect to the target database:

postgres=# \c target_database 
psql (10.6 (Ubuntu 10.6-0ubuntu0.18.04.1), server 9.5.14)
You are now connected to database "target_database" as user "postgres".

grant all the needed privileges:

target_database=# GRANT CONNECT ON DATABASE target_database TO readonly;
GRANT

target_database=# GRANT USAGE ON SCHEMA public TO readonly ;
GRANT

target_database=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly ;
GRANT

alter default privileges for targets db public shema:

target_database=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES

Do note that PostgreSQL 9.0 (today in beta testing) will have a simple way to do that:

test=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;

Taken from a link posted in response to despesz' link.

Postgres 9.x appears to have the capability to do what is requested. See the Grant On Database Objects paragraph of:

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

Where it says: "There is also an option to grant privileges on all objects of the same type within one or more schemas. This functionality is currently supported only for tables, sequences, and functions (but note that ALL TABLES is considered to include views and foreign tables)."

This page also discusses use of ROLEs and a PRIVILEGE called "ALL PRIVILEGES".

Also present is information about how GRANT functionalities compare to SQL standards.

If your database is in the public schema, it is easy (this assumes you have already created the readonlyuser)

db=> GRANT SELECT ON ALL TABLES IN SCHEMA public to readonlyuser;
GRANT
db=> GRANT CONNECT ON DATABASE mydatabase to readonlyuser;
GRANT
db=> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public to readonlyuser;
GRANT

If your database is using customschema, execute the above but add one more command:

db=> ALTER USER readonlyuser SET search_path=customschema, public;
ALTER ROLE

The not straightforward way of doing it would be granting select on each table of the database:

postgres=# grant select on db_name.table_name to read_only_user;

You could automate that by generating your grant statements from the database metadata.

Reference taken from this blog:

Script to Create Read-Only user:

CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234' 
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Assign permission to this read only user:

GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;

Assign permissions to read all newly tables created in the future

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

Here is the best way I've found to add read-only users (using PostgreSQL 9.0 or newer):

$ sudo -upostgres psql postgres
postgres=# CREATE ROLE readonly WITH LOGIN ENCRYPTED PASSWORD '<USE_A_NICE_STRONG_PASSWORD_PLEASE';
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

Then log in to all related machines (master + read-slave(s)/hot-standby(s), etc..) and run:

$ echo "hostssl <PUT_DBNAME_HERE> <PUT_READONLY_USERNAME_HERE> 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/9.2/main/pg_hba.conf
$ sudo service postgresql reload

I’ve created a convenient script for that; pg_grant_read_to_db.sh. This script grants read-only privileges to a specified role on all tables, views and sequences in a database schema and sets them as default.