Read Only User In Postgresql

Read only users in databases servers are frequently required, and generally  the method to create such user in different Database servers are very well known, such like in Mysql, Oracle, MSSQL etc...But making a read only user in Postgresql is bit different, so here is a short and smart way.

DB name is "mydb" (i am taking a DB name as mydb)

So just login with Postgres user and DB name on which you want to set read-only permission.

# psql -U postgres mydb

#####Revoke default permissions from public group############

mydb=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;

mydb=# REVOKE USAGE ON SCHEMA public FROM PUBLIC;

#####  Add back permissions for your database owner  ############

mydb=# GRANT CREATE ON SCHEMA public TO readwriteuser;

mydb=# GRANT USAGE ON SCHEMA public TO readwriteuser;

mydb=# \q

##### Now create a unprivileged User   ######

psql -U postgres -t -c "create role readonlyuser password 'abc123' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
mydb=# GRANT USAGE ON SCHEMA public TO readonlyuser;

mydb=# \q

# psql -U postgres -qAt -c "select 'grant select on ' || tablename || ' to \"readonlyuser\";' from pg_tables where schemaname = 'public'" mydb| psql -U postgres mydb

mydb=# select * from pg_namespace where nspname='public';
(Result below:)
nspname | nspowner | nspacl

---------+----------+------------------------------------------------------------------

public     | 10        | {postgres=UC/postgres,mydbuser=UC/postgres,mydbuser_ro=U/postgres}

Now test the "readonlyuser"  by creating a table in database mydb

# psql -U readonly mydb

mydb=#  CREATE TABLE films (
code        char(5) CONSTRAINT firstkey PRIMARY KEY,
title       varchar(40) NOT NULL,
did         integer NOT NULL,
date_prod   date,
kind        varchar(10),
len         interval hour to minute
);

ERROR:  permission denied for schema public

Above you can see that now "readonlyuser" is now not able to create a table even....  It can only perform "SELECT" statements.

Note:  These above is just for a specified schema, if you have created a different schema by other name, then change the settings accordingly.

Now Restrict the readonlyuser from pg_hba.conf

add the below line in pg_hba.conf

host    mydb    readonlyuser      192.168.23.72/32          trust

By this, a extra level of security you can force.