Обсуждение: How to GRANT rights to modify a table ???
Hello, A very annoying problem with Postgres is the following one : As postgres user, I give all rights to all objects of a database or schema to a colleague : GRANT ALL ON mytable TO mycolleague; But when he tries to modify something, even something really small like adding a column to a table : ALTER TABLE mytable ADD COLUMN field integer; ERROR: must be the owner of the mytable relation Therefore, how to give right to some user on tables structure ??? Thanks a lot for any help, this problem is always giving us lots of difficulties... Denis
On 12/09/2009 16:44, Denis BUCHER wrote: > Hello, > > A very annoying problem with Postgres is the following one : > > As postgres user, I give all rights to all objects of a database or > schema to a colleague : > GRANT ALL ON mytable TO mycolleague; > > But when he tries to modify something, even something really small like > adding a column to a table : > ALTER TABLE mytable ADD COLUMN field integer; > ERROR: must be the owner of the mytable relation The clue is in the error message :-) .... GRANTing ALL doesn't make them the owner of the object as well. For that, you have to do something like ALTER TABLE mytable OWNER TO mycolleague; - I forget the exact syntax, but it'll be in the docs. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
"Raymond O'Donnell" <rod@iol.ie> writes:
> On 12/09/2009 16:44, Denis BUCHER wrote:
>> ERROR: must be the owner of the mytable relation
> The clue is in the error message :-) .... GRANTing ALL doesn't make them
> the owner of the object as well.
> For that, you have to do something like
> ALTER TABLE mytable OWNER TO mycolleague;
What might be more practical is to create a group role that actually
owns the table, and make everyone who needs to do this sort of thing
a member of that group.
CREATE ROLE mytable_owner;
ALTER TABLE mytable OWNER TO mytable_owner;
GRANT mytable_owner TO myself, mycolleague, ... ;
I highly recommend reading the GRANT command reference page.
regards, tom lane
Tom Lane a écrit : >>> ERROR: must be the owner of the mytable relation > >> The clue is in the error message :-) .... GRANTing ALL doesn't make them >> the owner of the object as well. > >> For that, you have to do something like >> ALTER TABLE mytable OWNER TO mycolleague; > > What might be more practical is to create a group role that actually > owns the table, and make everyone who needs to do this sort of thing > a member of that group. > > CREATE ROLE mytable_owner; > ALTER TABLE mytable OWNER TO mytable_owner; > GRANT mytable_owner TO myself, mycolleague, ... ; > > I highly recommend reading the GRANT command reference page. > > regards, tom lane That's great, in fact this is the right solution, I didn't think about it... Thanks a lot... Denis
Hello,
Tom Lane a écrit :
> "Raymond O'Donnell" <rod@iol.ie> writes:
>> On 12/09/2009 16:44, Denis BUCHER wrote:
>>> ERROR: must be the owner of the mytable relation
>
>> The clue is in the error message :-) .... GRANTing ALL doesn't make them
>> the owner of the object as well.
>
>> For that, you have to do something like
>> ALTER TABLE mytable OWNER TO mycolleague;
>
> What might be more practical is to create a group role that actually
> owns the table, and make everyone who needs to do this sort of thing
> a member of that group.
>
> CREATE ROLE mytable_owner;
> ALTER TABLE mytable OWNER TO mytable_owner;
> GRANT mytable_owner TO myself, mycolleague, ... ;
>
> I highly recommend reading the GRANT command reference page.
>
> regards, tom lane
It's strange but after having tried it, it doesn't work ?
pg_dump says :
CREATE ROLE tableowner;
ALTER ROLE tableowner WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB
NOLOGIN;
GRANT tableowner TO mycolleague GRANTED BY postgres;
GRANT tableowner TO postgres GRANTED BY postgres;
My table seems to be correct :
bw_rma=# \dt abc.testtable
Liste des relations
Schéma | Nom | Type | Propriétaire
--------+------------+-------+--------------
abc | testtable | table | tableowner
(1 ligne)
When I login as "mycolleague" :
ALTER TABLE abc.testtable ADD COLUMN field integer;
What is wrong in what I do ?
Thanks a lot for any help...
Denis
Denis BUCHER a écrit : > Tom Lane a écrit : >> "Raymond O'Donnell" <rod@iol.ie> writes: >>> On 12/09/2009 16:44, Denis BUCHER wrote: >>>> ERROR: must be the owner of the mytable relation >>> The clue is in the error message :-) .... GRANTing ALL doesn't make them >>> the owner of the object as well. >>> For that, you have to do something like >>> ALTER TABLE mytable OWNER TO mycolleague; >> What might be more practical is to create a group role that actually >> owns the table, and make everyone who needs to do this sort of thing >> a member of that group. >> >> CREATE ROLE mytable_owner; >> ALTER TABLE mytable OWNER TO mytable_owner; >> GRANT mytable_owner TO myself, mycolleague, ... ; >> >> I highly recommend reading the GRANT command reference page. >> >> regards, tom lane > > It's strange but after having tried it, it doesn't work ? > > pg_dump says : > > CREATE ROLE tableowner; > ALTER ROLE tableowner WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB > NOLOGIN; > GRANT tableowner TO mycolleague GRANTED BY postgres; > GRANT tableowner TO postgres GRANTED BY postgres; > > My table seems to be correct : > > bw_rma=# \dt abc.testtable > Liste des relations > Schéma | Nom | Type | Propriétaire > --------+------------+-------+-------------- > abc | testtable | table | tableowner > (1 ligne) > > When I login as "mycolleague" : > ALTER TABLE abc.testtable ADD COLUMN field integer; > > What is wrong in what I do ? Ok I found the solution myself ;-)) ALTER ROLE mycolleague INHERIT; That's all ;-)) Denis
Denis BUCHER <dbucherml@hsolutions.ch> writes:
> Tom Lane a �crit :
>> What might be more practical is to create a group role that actually
>> owns the table, and make everyone who needs to do this sort of thing
>> a member of that group.
> It's strange but after having tried it, it doesn't work ?
Hmm, worksforme:
regression=# create table t1 (f1 int);
CREATE TABLE
regression=# create role t1o ;
CREATE ROLE
regression=# alter table t1 owner to t1o;
ALTER TABLE
regression=# create user joe;
CREATE ROLE
regression=# grant t1o to joe;
GRANT ROLE
regression=# \c - joe
psql (8.5devel)
You are now connected to database "regression" as user "joe".
regression=> alter table t1 add column f2 int;
ALTER TABLE
regression=>
One possibility is that your "mycolleague" user is set up without the
INHERIT property, in which case he'd have to do "SET ROLE tableowner"
to acquire the ownership privilege. (tableowner's INHERIT setting
is not the relevant thing here; mycolleague's is.)
regards, tom lane