Postgres readonly access

Having used MySQL quite a bit in the past. I have been used to being able to setup a read only user for a database. In PostgreSQL things work a bit differently. In order to allow read only access you need to assign SELECT access on each table to a role.

The following seems to work.

Login to database

psql database

Create readonly role

CREATE ROLE readonly;


This will grant select access on every table.


tables=$(psql database -A -t -c "SELECT table_name FROM information_schema.tables \ 
WHERE table_schema = 'public';")

for table in $tables
echo "Granting select to readonly on $table"
psql database -c "GRANT SELECT ON $table to readonly;"

Grant readonly role to existing user

GRANT readonly TO existinguser;

Once this is done you will be able to give users read only access to the database.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: