How to create read-only user in PostgreSQL

This tutorial explains the steps to create a user in PostgreSQL with read privileges only.

Step1: Create md5 password for the user. We can use the following Linux command to create the same.

#echo -n "md5"; echo -n "PASSWORDUSERNAME" | md5sum | awk '{print $1}'

Here the USERNAME and PASSWORD need to be replaced by the required values.

Step2: Login to PostgreSQL shell.

Step3: Change the database.

>\c DB;

Here DB need to be replaced by the database name.

Step4: Create the user.

>CREATE user USERNAME WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN CONNECTION LIMIT 5 PASSWORD 'MD5PASSWORD';

Here the USERNAME and MD5PASSWORD need to be replaced by the corresponding values from Step1.

Step5: Change privileges over public schema.

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

Here the USERNAME need to be replaced by the corresponding value from Step1.

Step6: Assign read permission over public schema.

>grant USAGE ON ALL tables in schema public TO USERNAME;
>grant SELECT ON ALL tables in schema public to USERNAME;

Here the USERNAME need to be replaced by the corresponding value from Step1.

Step7: List out all schema in the current database.

>\dn;

Step8: Assign read permission over all required schemas.

>grant USAGE ON schema SCHEMANAME TO USERNAME;
>grant SELECT ON ALL tables in schema SCHEMANAME to USERNAME;

Here USERNAME and SCHEMANAME need to be replaced by the corresponding values.

That's all…