Quick and easy way to create read-only databases

Postgresql doesn’t have a one-step command to grant permissions on a batch of tables like you can in MySQL.  However, it is much simpler in 9+ than it was previously.  You still have to go schema by schema in a database, but at least you don’t have to go table by table, view by view.   Here are the steps to create a read-only user:

First, create the user:

CREATE USER readonlyusername WITH PASSWORD 'password';

Now for each schema in a database you want the read only user to have access to you need the next two statements:

GRANT USAGE ON SCHEMA schema TO readonlyusername;

GRANT SELECT ON ALL TABLES IN SCHEMA schema TO readonlyusername;

If you’ll be adding new tables or views and you want them to be accessable by your read only user you need the following:

ALTER DEFAULT PRIVILEGES IN SCHEMA schema GRANT SELECT ON TABLES TO readonlyuser;

If you know of a quicker and/or easier way please let me know!

Advertisements
  1. No trackbacks yet.

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

%d bloggers like this: