How to create a read-only user in PostgreSQL

Steps Overview

Connect to PostgreSQL: Start by connecting to the PostgreSQL database with superuser privileges.

Create a Role for Read-Only Access: We will create a group role that has only read privileges on all tables in the target database, ensuring future tables will also inherit these permissions.

Grant Necessary Privileges: We'll grant the SELECT privilege on all existing tables, views, and sequences in the schema. Additionally, default privileges will be set for any future tables.

Create a Login Role: A login role will be created and assigned to the read-only group role, ensuring it inherits the appropriate read-only permissions.

Restrict Permissions: Finally, we’ll ensure the user cannot create, modify, or delete data, and revoke any unnecessary permissions.

Connect to PostgreSQL

psql -U postgres -d database

Create a new Role Group

CREATE ROLE role_group NOINHERIT;

Assign privileges to role_group

GRANT CONNECT ON DATABASE database TO role_group;
GRANT USAGE ON SCHEMA public TO role_group;

SELECT 'GRANT SELECT ON '||schemaname||'."'||tablename||'" TO role_group;' FROM pg_tables WHERE schemaname IN ('public') ORDER BY schemaname, tablename;

You will see a list of GRANT statements.
Copy the GRANT statements generated by the query and then paste them into your terminal window. 
To restrict access to a subset of tables, only run the GRANT statements for the corresponding tables.

Assign privileges SELECT to future tables

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

Create user with password

CREATE ROLE user_read_only LOGIN PASSWORD 'password' NOSUPERUSER NOCREATEDB NOCREATEROLE;

Assign role group role_group to user user_read_only

GRANT role_group TO user_read_only;

Verify that the role group role_group not privileges of the postgres or public

REVOKE ALL ON SCHEMA public FROM public;
REVOKE ALL ON DATABASE database FROM public;

Test access

psql -U user_read_only -d database

Performs create table operations, update data in any table, delete items. You should receive a permission denied message.

Regards,