Database script for creating readonly user in PostgreSQL

Bharat Dwarkani | May 10, 2020 | Database

Database Script to create read only user credentials


Login using admin account. Switch to default postgres database



CREATE ROLE readonlyuser WITH LOGIN PASSWORD 'strong_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity'; 
GRANT CONNECT ON DATABASE readonlyuser To actual_db_name;

 

Switch to particular db for which you want to create read only credentials

 

GRANT USAGE ON  SCHEMA public To readonlyuser ;
GRANT SELECT ON ALL TABLES IN SCHEMA public To readonlyuser;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public To readonlyuser;

 

If need to give execute permission execute this

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public To readonlyuser ;


Terms Used

readonlyuser - User name for read only user

actual_db_name  - Database Name

strong_password  - Password for readonly user


Now you have created a new DB user which can just read data

bookmarks
share
visibility9
visibility9 share bookmarks
add