Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I'd expect if you'd try your queries with the rails user, tey would fail as well. myapp is the owner, so it has all privileges by default (see here). Depending on your needs, you could grant the necessary privileges to rails or add meltemi to myapp. –  dezso Nov 30 '12 at 7:23
add comment

1 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.

share|improve this answer
    
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?!? –  Meltemi Nov 30 '12 at 17:01
1  
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. –  Erwin Brandstetter Nov 30 '12 at 17:29
    
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?!? –  Meltemi Nov 30 '12 at 17:59
1  
@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 –  Erwin Brandstetter Dec 2 '12 at 4:02
    
@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. –  Craig Ringer Dec 2 '12 at 8:56
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.