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.
Best Answer
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 saymyapp_users
, then you create a new login role (user) namedrails
andGRANT myapp_users TO rails
. Now you anGRANT myapp_users TO meltemi
. Both the newrails
account and themeltemi
user now have the rights of the oldrails
account.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 explicitlySET GROUP
to, or better, use a completely different user for privileged operations like DDL andGRANT
s. 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.