How to set Postres Read Only Role for a Read only user script

connect by command console to the database:
1) /usr/pgsql-10/bin/psql -U postgres
select the current db for which you want to create the read only user:
2) \c <database>
3) create the role
4) grant the permission after the connection to the right db

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;

Leave a Reply