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

Postgresql Problem Overview

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.

Postgresql Solutions

Solution 1 - Postgresql

Grant usage/select to a single table

If you only grant CONNECT to a database, the user can connect but has no other privileges. You have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually like so:

GRANT CONNECT ON DATABASE mydb TO xxx;
-- This assumes you're actually connected to mydb..
GRANT USAGE ON SCHEMA public TO xxx;
GRANT SELECT ON mytable TO xxx;

Multiple tables/views (PostgreSQL 9.0+)

In the latest versions of PostgreSQL, you can grant permissions on all tables/views/etc in the schema using a single command rather than having to type them one by one:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

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 xxx;

Note that by default this will only affect objects (tables) created by the user that issued this command: although it can also be set on any role that the issuing user is a member of. However, you don't pick up default privileges for all roles you're a member of when creating new objects... so there's still some faffing around. If you adopt the approach that a database has an owning role, and schema changes are performed as that owning role, then you should assign default privileges to that owning role. IMHO this is all a bit confusing and you may need to experiment to come up with a functional workflow.

Multiple tables/views (PostgreSQL versions before 9.0)

To avoid errors in lengthy, multi-table changes, it is recommended to use the following 'automatic' process to generate the required GRANT SELECT to each table/view:

SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;'
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');

This should output the relevant GRANT commands to GRANT SELECT on all tables, views, and sequences in public, for copy-n-paste love. Naturally, this will only be applied to tables that have already been created.

Solution 2 - Postgresql

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;

Solution 3 - Postgresql

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;

Solution 4 - Postgresql

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

Solution 5 - Postgresql

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;

Solution 6 - Postgresql

From PostgreSQL v14 on, you can do that simply by granting the predefined pg_read_all_data role:

GRANT pg_read_all_data TO xxx;

Solution 7 - Postgresql

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.

Solution 8 - Postgresql

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

Solution 9 - Postgresql

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

Solution 10 - Postgresql

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.

Solution 11 - Postgresql

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.

Solution 12 - Postgresql

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

Content Type	Original Author	Original Content on Stackoverflow
Question	Ethan	View Question on Stackoverflow
Solution 1 - Postgresql	araqnid	View Answer on Stackoverflow
Solution 2 - Postgresql	Anvesh	View Answer on Stackoverflow
Solution 3 - Postgresql	bortzmeyer	View Answer on Stackoverflow
Solution 4 - Postgresql	Jay Taylor	View Answer on Stackoverflow
Solution 5 - Postgresql	Adrian Macneil	View Answer on Stackoverflow
Solution 6 - Postgresql	Laurenz Albe	View Answer on Stackoverflow
Solution 7 - Postgresql	Jakub Jirutka	View Answer on Stackoverflow
Solution 8 - Postgresql	thomi_ch	View Answer on Stackoverflow
Solution 9 - Postgresql	josephmisiti	View Answer on Stackoverflow
Solution 10 - Postgresql	Pablo Santa Cruz	View Answer on Stackoverflow
Solution 11 - Postgresql	kbulgrien	View Answer on Stackoverflow
Solution 12 - Postgresql	Viktor Viktor	View Answer on Stackoverflow