Ruizs Place

I develop a product at work that (among many other things) allows users to easily create and manage databases to interact with other packages on their appliance. One feature that was requested and green-lighted is the ability to create a read-only user for existing databases- a user that can connect to a given database and access all tables and views in that DB, and nothing else.

Since PostgreSQL is our database of choice, I started researching the process, thinking it would be a couple mysql-esqe GRANT statements and that would be it. Turns out that it is a huge PITA in postgres – even my solution I’m documenting here has its shortcomings, but as far as I know is the best / only way to accomplish this task. I ran across quite a few sites that helped with pieces of this, but none that actually tied the process together for production usage. I am by no means a Postgres expert, but do have a good bit of experience mucking around with back-end settings and figuring how to script common tasks.

There are two main things to watch out for when trying to create a read-only user in PostgreSQL, especially if you come from a DB like MySQL:

PostgreSQL only sets permissions on objects, not on databases, so you need to grant read access to all your tables/views/etc, and if you add a table down the line, you need to remember to manully grant read access to it after creating the table.

I’m guessing 95% of postgres users just use the default “public” schema, and as such, you need to revoke create privileges from the PUBLIC group. Otherwise, your “read-only” user will still be allowed to create tables that it owns, even if you’ve only given it read only access to all other objects in your database.

For this example, we’ll use database name “mydb”, database user/owner “mydbuser”, and we’ll create a read-only user named “mydbuser_ro”. This assumes that you did not define a schema for your database and are using the default “public” schema.

Revoke default permissions from public group:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE USAGE ON SCHEMA public FROM PUBLIC;

Add back permissions for your database owner:
GRANT CREATE ON SCHEMA public TO mydbuser;
GRANT USAGE ON SCHEMA public TO mydbuser;

Create the new user via the command line, or pgadmin/etc:
psql -U postgres -t -c "create role mydbuser_ro password 'abc123' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

Grant usage permissions for your read-only user:
GRANT USAGE ON SCHEMA public TO mydbuser_ro;

Grant select permissions on all database tables from the command line:
psql -U postgres -qAt -c "select 'grant select on ' || tablename || ' to \"mydbuser_ro\";' from pg_tables where schemaname = 'public'" mydb | psql -U postgres mydb

Setup remote access for the read only user in pg_hba.conf as appropriate

Once complete, you can verify the settings with a quick sql query. You should see something like this, with the user=UC (for Usage/Create), and user_ro=U (for Usage):
mydb=> select * from pg_namespace where nspname='public';
nspname | nspowner | nspacl
---------+----------+------------------------------------------------------------------
public | 10 |
{postgres=UC/postgres,mydbuser=UC/postgres,mydbuser_ro=U/postgres}


Tags: database, db, linux, mysql, postgresql