Creating New users in postgresql and providing grants to the users

Steps to create read only user in postgre DB

1. Command to create the user

create user USER_NAME password 'PASWWORD';

2. Set read only to the user

alter user USER_NAME set default_transaction_read_only = on;

3. Provide DB previlages to user

GRANT select on DB_NAME to USER; // if only db name doesn't work. give list of tables in command, so that the user can have permission to all the tables

4. login to DB

 psaql -h HOSTNAME -UUSERNAME -d DBNAME

5. Once you logged into the DB. execute below commands

GRANT CONNECT ON DATABASE db_name to user_name;
GRANT USAGE ON SCHEMA public to user_name;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO user_name;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_name;