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;

Create readonly_for_database.sh

This will grant select access on every table.


#!/bin/sh

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

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

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.

About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: