Saturday 27 October 2012

Creating a read only user in Postgres

If you’re looking to just get this done and don’t want to read my post about how I solved it, skip to the TL;DR

Background

It all starts with

 Error message from server: ERROR:  permission denied for relation auth_group_id_seq 

This is when I start realising that my limited DBA skills that I’ve learned from MySQL are not going to cut the mustard in the world of Postrgres. However, I’ve committed myself to it and used it as the database in my latest venture Obscure Metaphor.

In MySQL I’d create a user called backups with SELECT and LOCK TABLE privileges on the relevant databaes and be done with it; things are not so simply in Postgres.

Create the user

I created the user with the command line tool

 createuser backup_user 

And then altered the password inside of the psql shell

 alter user  backup_user with encrypted password 'yourpasswordinhere'; 

I now realise I could have done those two commands in one.

Let me in little piggy

I connected to the database I wanted to backup and I issued this command

 GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user; 

At this point I thought I’d be good to go. Alas not.

 pg_dump -O -Fc  --format=t productiondb -U backup_user -h localhost pg_dump: Error message from server: ERROR:  permission denied for relation auth_group_id_seq 

Norks. I could see the backup_user had permission to select on the tables but when doing a \l in the shell I couldn’t see my backup_user as having access privileges on the database. This felt like having the keys to every internal door in a house. but not being able to get into the house.

So I then ran the following command granting the backup_user connect access to my database

 GRANT CONNECT ON DATABASE productiondb to backup_user; 

Now when I did a \l in the shell could see that the backup user had connect privileges. I got exited so I tried dumping the database again.

 pg_dump -O -Fc  --format=t productiondb -U backup_user -h localhost pg_dump: Error message from server: ERROR:  permission denied for relation auth_group_id_seq 

Boh - no joy again. I knew I was going to have dig deeper so I put my tough mudder headband on.

What then proceeded was the usual half hour of head thumping whilst I tried to figure out why I was getting permission denied errors when I had access to connect and access to connect.

I then tried \z after reading a post from a German forum and that brought back a list of permissions for the tables and sequences. Guess what? That’s right, the backup user didn’t have permission on the sequences - it had nothing to do with relations. So I issued the following command.

 GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_user; 

Then I tried my dump again

 pg_dump -O -Fc  --format=t productiondb -U backup_user -h localhost 

And joy of joys — it worked!

TL;DR

Assuming that —

  • production database is called ‘production’
  • your backup user is called ‘backup_user’ with ‘password’ as the password
 CREATE USER backup_user  WITH ENCRYPTED PASSWORD 'password'; GRANT CONNECT ON DATABASE production to backup_user; \c production GRANT USAGE ON SCHEMA public to backup_user; /*thanks Dominic!*/ GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user; 

Now you can use your backup_user to dump databases with pg_dump. Authenticating the user automatically is a subject for another day.