Thread: forcing table ownership
Hello, I'm pretty much brand new to using postgres and the privilege structure is taking some getting used to, especially when coming from mysql. What I'm trying to accomplish is to have multiple users/roles connect to a database and have ALL privileges to do whatever they want. The problem I'm running into is that is user1 creates table1 nobody else has permissions to it since they are not the table owner. How can I accomplish this? What I want to do is create a database group with ALL privs on a database, assign X number of users to it, and allow them to have their way with the database. I've done this but the behavior is the same as mentioned above, nobody other than the table owner can access the tables. Postgres 8.4.7 on OpenSUSE 11.3 - Users are authenticating against LDAP - User are being created with "createuser -S -D -R user#" - Group creation "CREATE GROUP grp1;" - Added users to group with "ALTER GROUP grp1 ADD USER user1, user2:" - Grant DB priv's "GRANT ALL ON database1 TO GROUP grp1;" Thanks! -- Darin Perusich Email: Darin.Perusich@ctg.com Office: 716-888-3690 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient of this message, please contact the sender and delete this material from this computer.
On 9/15/2011 3:59 PM, Darin Perusich wrote: > Hello, > > I'm pretty much brand new to using postgres and the privilege structure > is taking some getting used to, especially when coming from mysql. What > I'm trying to accomplish is to have multiple users/roles connect to a > database and have ALL privileges to do whatever they want. The problem > I'm running into is that is user1 creates table1 nobody else has > permissions to it since they are not the table owner. How can I > accomplish this? > > What I want to do is create a database group with ALL privs on a > database, assign X number of users to it, and allow them to have their > way with the database. I've done this but the behavior is the same as > mentioned above, nobody other than the table owner can access the > tables. > > Postgres 8.4.7 on OpenSUSE 11.3 > - Users are authenticating against LDAP > - User are being created with "createuser -S -D -R user#" > - Group creation "CREATE GROUP grp1;" > - Added users to group with "ALTER GROUP grp1 ADD USER user1, user2:" > - Grant DB priv's "GRANT ALL ON database1 TO GROUP grp1;" > > Thanks! > -- > Darin Perusich > Email: Darin.Perusich@ctg.com > Office: 716-888-3690 Any way you can update to PG 9? http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html -Andy
Hi Andy, > > Any way you can update to PG 9? > > http://www.postgresql.org/docs/9.0/static/sql- > alterdefaultprivileges.html That's not really an option I'd like to consider. I really don't want to deploy anything outside of what's distributed with the OS, since I have a TON of opensuse servers to keep track of and there will be a bunch running postgres. Thanks! -- Darin Perusich Email: Darin.Perusich@ctg.com Office: 716-888-3690 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient of this message, please contact the sender and delete this material from this computer.
On Thu, Sep 15, 2011 at 1:59 PM, Darin Perusich <Darin.Perusich@ctg.com> wrote: > I'm pretty much brand new to using postgres and the privilege structure > is taking some getting used to, especially when coming from mysql. What > I'm trying to accomplish is to have multiple users/roles connect to a > database and have ALL privileges to do whatever they want. The problem > I'm running into is that is user1 creates table1 nobody else has > permissions to it since they are not the table owner. How can I > accomplish this? It looks like you have two choices from what I can find in the manual after 5 minutes of reading. 1) alter the tables/schema/... to be owned by a role that all of these users belong to: http://www.postgresql.org/docs/8.4/interactive/sql-altertable.html 2) grant the table/schema to the role: http://www.postgresql.org/docs/8.4/interactive/privileges.html -- Regards, Richard Broersma Jr.
On 09/15/2011 04:31 PM, Darin Perusich wrote: > Hi Andy, > >> >> Any way you can update to PG 9? >> >> http://www.postgresql.org/docs/9.0/static/sql- >> alterdefaultprivileges.html > > That's not really an option I'd like to consider. I really don't want to > deploy anything outside of what's distributed with the OS, since I have > a TON of opensuse servers to keep track of and there will be a bunch > running postgres. > > Thanks! How about "set role ..." then? http://www.postgresql.org/docs/8.4/static/sql-set-role.html -Andy
> -----Original Message----- > From: Andy Colson [mailto:andy@squeakycode.net] > Sent: Thursday, September 15, 2011 7:51 PM > To: Darin Perusich > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] forcing table ownership > > On 09/15/2011 04:31 PM, Darin Perusich wrote: > > Hi Andy, > > > >> > >> Any way you can update to PG 9? > >> > >> http://www.postgresql.org/docs/9.0/static/sql- > >> alterdefaultprivileges.html > > > > That's not really an option I'd like to consider. I really don't want > to > > deploy anything outside of what's distributed with the OS, since I > have > > a TON of opensuse servers to keep track of and there will be a bunch > > running postgres. > > > > Thanks! > > How about "set role ..." then? > > http://www.postgresql.org/docs/8.4/static/sql-set-role.html > This works but it seems rather cumbersome to have to set the role every time someone connects to the database. There has to be another way. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient of this message, please contact the sender and delete this material from this computer.
Hi Richard, > > I'm trying to accomplish is to have multiple users/roles connect to a > > database and have ALL privileges to do whatever they want. The > problem > > I'm running into is that is user1 creates table1 nobody else has > > permissions to it since they are not the table owner. How can I > > accomplish this? > > It looks like you have two choices from what I can find in the manual > after 5 minutes of reading. > 1) alter the tables/schema/... to be owned by a role that all of these > users belong to: > http://www.postgresql.org/docs/8.4/interactive/sql-altertable.html Altering the table owner by setting it to the group role effectively denies permission to all users of the group. Unless they explicitly "SET role grp1" that is. > 2) grant the table/schema to the role: > http://www.postgresql.org/docs/8.4/interactive/privileges.html "GRANT ALL ON table TO grp1;" has the effect as mentioned above. I'm finding it hard to believe this is so difficult... -- Darin Perusich Email: Darin.Perusich@ctg.com Office: 716-888-3690 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient of this message, please contact the sender and delete this material from this computer.
On 16/09/11 14:13, Darin Perusich wrote: > > Altering the table owner by setting it to the group role effectively > denies permission to all users of the group. Unless they explicitly "SET > role grp1" that is. I've already got a user "richardh" As a superuser: CREATE GROUP mygroup INHERIT; GRANT mygroup TO richardh; CREATE TABLE shared_table (i int); As richardh: ALTER TABLE shared_table ADD COLUMN t text; ERROR: must be owner of relation shared_table As superuser: ALTER TABLE shared_table OWNER TO mygroup; As richardh: ALTER TABLE shared_table ADD COLUMN t text; ALTER TABLE I think the key bit you're missing is the "INHERIT" on the group. Also note that the CREATE USER/CREATE GROUP commands actually just run CREATE ROLE under the hood. -- Richard Huxton Archonet Ltd
Hi Richard, > -----Original Message----- > From: Richard Huxton [mailto:dev@archonet.com] > Sent: Friday, September 16, 2011 9:54 AM > To: Darin Perusich > Cc: Richard Broersma; pgsql-general@postgresql.org > Subject: Re: [GENERAL] forcing table ownership > > On 16/09/11 14:13, Darin Perusich wrote: > > > > Altering the table owner by setting it to the group role effectively > > denies permission to all users of the group. Unless they explicitly > "SET > > role grp1" that is. > > I've already got a user "richardh" > > As a superuser: > CREATE GROUP mygroup INHERIT; > GRANT mygroup TO richardh; > CREATE TABLE shared_table (i int); > As richardh: > ALTER TABLE shared_table ADD COLUMN t text; > ERROR: must be owner of relation shared_table > As superuser: > ALTER TABLE shared_table OWNER TO mygroup; > As richardh: > ALTER TABLE shared_table ADD COLUMN t text; > ALTER TABLE > > I think the key bit you're missing is the "INHERIT" on the group. Also > note that the CREATE USER/CREATE GROUP commands actually just run > CREATE > ROLE under the hood. > This works but I found that I also needed to set the database owner to the group as well. It would be nice to be able to set the default group but it appears thats only available in PG9. Thanks. -- Darin Perusich Email: Darin.Perusich@ctg.com Office: 716-888-3690 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient of this message, please contact the sender and delete this material from this computer.