PostgreSQL: permission denied for relation

Question

I'm a bit confused about setting permissions in PostgreSQL.

I have these roles:

 List of roles
 Role name | Attributes | Member of 
-----------+------------------------------------------------+-----------
 admin | Superuser, Create role, Create DB, Replication | {}
 meltemi | Create role, Create DB | {rails}
 rails | Create DB, Cannot login | {}
 myapp | | {rails}

and databases:

 List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges 
---------------------+--------+----------+-------------+-------------+-------------------
 myapp_production | rails | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
 ...

user myapp has no problem querying the myapp_production database adding & deleting records. I'd like for meltemi to also be able to query the same database. So, I created a role rails which owns the database and made both meltemi and myapp members of rails. But I still get permission denied for relation errors. Meltemi can view the schema but can't query the DB.

I just noticed (with \dt command) that myapp is the owner of the tables:

 List of relations
 Schema | Name | Type | Owner 
--------+-------------------+-------+-------
 public | events | table | myapp
 public | schema_migrations | table | myapp
 ...
 public | users | table | myapp
 ...

The tables were created via an ORM (Rails' ActiveRecord migrations).

I know authorization is very different in PostgreSQL (as opposed to MySQL & others I've used). How should I be setting up my database so that different users can access it. Some should be able to CRUD but others may only be able to Read, etc...

Thanks for any help. Sorry, I know this is a very basic question but I haven't been able to find the answer myself.

Answer 1

I just wrote about this in my answer to Granting rights on postgresql database to another user on ServerFault.

Basically, the best solution when you have a single user and you want to grant other users the same rights is to turn that user into a group, create a new user with the same name as the original one that's a member of the group, and grant that group to other users too.

So in your case, rails gets renamed to say myapp_users, then you create a new login role (user) named rails and GRANT myapp_users TO rails. Now you an GRANT myapp_users TO meltemi. Both the new rails account and the meltemi user now have the rights of the old railsaccount.

For more fine-grained control I ususally advise that you avoid giving the day-to-day login users or their groups ownership of the tables. Give them access via a NOINHERIT group they must explicitly SET GROUP to, or better, use a completely different user for privileged operations like DDL and GRANTs. Unfortunately this doesn't work with Rails, because Rails likes to apply migrations whenever it feels like it and AFAIK doesn't give you the ability to specify a different, more-privileged user to run the migrations as.

• Meltemi: OK, read the post you linked to; very helpful! Now, if I'm understanding things right, I think you may have meant to use myapp instead of rails above? Because myapp owns the tables (I never specified that, the migration must have). Anyway, it would sorta make sense if I renamed myapp to myapp_group and then made a new user myapp which the rails app would use to connect to DB. Make myapp and the existing meltemi, both members of the myapp_group role. But what happens when I run the next migration. won't it be owned by myapp re-creating the problem all over again?!?
• Erwin Brandstetter: You must understand that PostgreSQL has only roles (since version 8.1). The terms user and group are kept around for historic reasons and compatibility. Basically a "group" is a role without the login privilege. You can grant myapp to meltemi even if myapp is just another "user". Start by reading the manual here.
• Meltemi: I DO understand the roles vs groups vs users separation in Postgres, at least I think I do. Sorry to use the wrong (and confusing) terminology above. But I still don't understand how to set up my database so a no-login role OWNS the database and two login roles myapp and meltemi can both have full access. One of those roles myapp will be running Rails migrations that will, inevitably?, create new tables that are, once again, owned by myapp, a login user. Should I just make meltemi a 'member' of myapp and be done with it? But that just seems kludgy...no?!?
• Erwin Brandstetter: @Meltemi: If you want to grant all privileges that myapp holds to meltemi, then that would be the right thing to do. If you want meltemi to get only a subset of privileges, it wouldn't. Then create a group role to hold the set of privileges and grant that to meltemi. You will most probably be interested in this related question on SO. I answered explaining DEFAULT PRIVILEGES
• Craig Ringer: @Meltemi Yes, as usual Rails migrations complicate the picture. Rails really should let you specify a different user account to run migrations as. You can possibly add a SET ROLE command to the start of your migrations and a RESET ROLE to the end, but I wouldn't trust Rails to run the whole thing neatly in order. Erwin's right; in this case the best workaround will be to GRANT the the user rails gives ownership to to the other user, using the 1st user as a group for the second.