Create user with READONLY access on PostgresQL

Published on January 4, 2023

-- create role
CREATE ROLE read_only;

-- grant access to tables
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- grant access to feature tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;

-- create user and grant role
CREATE USER reader WITH PASSWORD 'secret';
GRANT read_only TO reader;

Create role #

$ psql -U postgres -d new_database

new_database=# CREATE ROLE read_only;

Connect to database as superuser using psql utility. Perform the request creating new role with read_only

Grant access to tables #

new_database=# GRANT USAGE ON SCHEMA public TO read_only;
new_database=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

Grant access to feature tables #

new_database=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;

All new tables in schema public will be accessible for role read_only.

Create user and grant role #

new_database=# CREATE USER reader WITH PASSWORD 'secret';
new_database=# GRANT read_only TO reader;