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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s


Get every new post delivered to your Inbox.

%d bloggers like this: