PostgreSQL Roles – How to Configure Per Database Roles

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.

It sounds like what you probably want is to:

• Create a role to own all the common tables and schema, or just use your own if you really will always be the only one with full control of the main tables.

• Create another role you intend to give only read-only access to the shared tables and schemas. GRANT that role rights using GRANT SELECT ON ALL TABLES IN SCHEMA [x] for each shared schema. You may also want to ALTER DEFAULT PRIVILEGES to make sure this role has read rights on any new tables created in these schemas too.

• Now GRANT each user membership of the read-only access role with INHERIT.

• For the private schemas, create a schema the same as the user's username with CREATE SCHEMA [username] AUTHORIZATION [username] or the older style where you create the schema then ALTER SCHEMA ... OWNER TO.

See the postgresql manual for the detailed syntax of all of the above commands. Start with user management, part of the broader database administration topic that includes grant management etc. The PostgreSQL manual is detailed, comprehensive and readable: reading it is strongly recommended.