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,
insert
,update
anddelete
? Or do you meanALTER TABLE
, i.e. change its structure, add columns, etc?ALTER DEFAULT PRIVILEGES
lets you set up grants for insert/update/delete, but you can't grant the right to alter the table, only the owner can do that. (Perhaps show some commands you're having trouble with?)