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.
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
- /c my_database
- ALTER DEFAULT PRIVILEGES .... ... ... ..