PostgreSQL Role Configuration

permissionspostgresqlrole

I am using PostgreSQL 9.3. I am working with a friend of mine on a project on which both of us would need access to the same database and all of the permissions on every table.

So I created a Role (Group) assigning all of the permissions over said database.

Then I created two roles (users) for me and my friend and added them to the Group Role and Inherited all of the Group Role's permissions. So no problem whatsoever.

The problem comes when I create a new table, because the ownership of the table is set to me instead of the group role. Which causes that my friend can't edit the tables I create, and I can't edit the ones he creates.

I did some research and found options like removing the INHERIT flag from my user roles and using a SET ROLE GROUP_ROLE before executing any query. This works but I am looking for a more automated process. We work on Navicat, so we create all our tables visually, fetching the sql, pasting it into the query builder, and adding the SET ROLE line on each query so that ownership is set to GROUP_ROLE makes the process a bit sluggish.

With further research I found out about DEFAULT PRIVILEGES, which I tried but had no success. I didn't understood this option quite well.

EDIT

What I tried was the following commands: 

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO usergroup; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO user1; 
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO user2;

But still, when user1 creates a new table, user2 doesn't have enough privileges to edit it.

Is there any way that we (me and my friend) can share the same database with ALL the privileges and that if my friend creates a new table I automatically have the permissions to edit it and vice versa?

Is there an instruction so that PostgreSQL sets the ownership automatically to the GROUP_ROLE instead of the user executing the query?

Or maybe changing the default privileges so that when my_user creates a new table, all of the users under my_group share the same permissions?

PD. I'm running Ubuntu 14. And when I was trying to do the DEFAULT PRIVILEGES configuration I was using the default user I guess.

I just log in to my server via SSH and do the following,

  • sudo su postgres
  • psql
  • /c my_database
  • ALTER DEFAULT PRIVILEGES …. … … ..

Thanks,

Best Answer

The PostgreSQL 9.3 Documentation has outlined how to alter default permissions. Follow this link to learn more!

Here is an excerpt that demonstrates how one would change the default permissions for a GROUP:

ALTER DEFAULT PRIVILEGES [ FOR { ROLE | USER } target_role [, ...] ] [ IN SCHEMA schema_name [, ...] ] abbreviated_grant_or_revoke

where abbreviated_grant_or_revoke is:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON TABLES TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

You may also benefit from learning more about ROLES (follow link to documentation here). That would make it easier if you ever needed to re-assign permissions to other people to.

It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In PostgreSQL this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.

A good place to do some practice/tutorial work on permissions, groups, roles, etc. is a site called Tutorials Point. They've got examples that will help you work through setting up permissions.

Related Question