Обсуждение: bug?: permission denied for schema on "on delete set null"
I get a Query failed: ERROR: permission denied for schema user_test CONTEXT: SQL statement "UPDATE ONLY "user_test"."shop_commerce_baskets" SET "sid" = NULL WHERE $1::pg_catalog.text OPERATOR(pg_catalog.=) "sid"::pg_catalog.text This query is run when I do a DELETE FROM user_test.sessions WHERE timestamp < 1236672815; as a result of an on delete set null but when I directly do a update user_test.shop_commerce_baskets set sid=null; I get no error. create table user_test.sessions( sid int primary key, ); create table user_test.shop_commerce_baskets ( sid int references sessions (sid) on delete set null, ... ); I'm on PostgreSQL 8.3.6 (Debian Lenny). thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo schrieb: > I get a > > Query failed: ERROR: permission denied for schema user_test CONTEXT: > SQL statement "UPDATE ONLY "user_test"."shop_commerce_baskets" SET > "sid" = NULL WHERE $1::pg_catalog.text OPERATOR(pg_catalog.=) > "sid"::pg_catalog.text > > This query is run when I do a > DELETE FROM user_test.sessions WHERE timestamp < 1236672815; > as a result of an > > on delete set null > > but when I directly do a > > update user_test.shop_commerce_baskets set sid=null; > > I get no error. > > create table user_test.sessions( > sid int primary key, > ); > > create table user_test.shop_commerce_baskets ( > sid int references sessions (sid) on delete set null, > ... > ); > > I'm on PostgreSQL 8.3.6 (Debian Lenny). > > thanks > which rights does the actual user have for the schema user_test? > Query failed: ERROR: permission denied for schema user_test CONTEXT: I think the user does not have the rights for the schema ... Cheers Andy -- St.Pauli - Hamburg - Germany Andreas Wenk
On Tue, 10 Mar 2009 14:31:56 +0100 Andreas Wenk <a.wenk@netzmeister-st-pauli.de> wrote: > which rights does the actual user have for the schema user_test? > > > Query failed: ERROR: permission denied for schema user_test > > CONTEXT: > > I think the user does not have the rights for the schema ... I just run http://pgedit.com/tip/postgresql/access_control_functions on public and user_test schema + grant all on schema user_test to user_test; It was related to something similar to this: http://archives.postgresql.org//pgsql-general/2007-06/msg01365.php I still have to sort it out since I did some random changes to ownership of schema and tables. It seems that the schemas have to be owned by the "group" and not by the single users. To sum it up: a role as a group owning everything several roles as "users" member of the previous role everything owned by the "group" permissions assigned to the single users -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo schrieb: > On Tue, 10 Mar 2009 14:31:56 +0100 > Andreas Wenk <a.wenk@netzmeister-st-pauli.de> wrote: > >> which rights does the actual user have for the schema user_test? >> >> > Query failed: ERROR: permission denied for schema user_test >> > CONTEXT: >> >> I think the user does not have the rights for the schema ... > > I just run > http://pgedit.com/tip/postgresql/access_control_functions > on public and user_test schema + > grant all on schema user_test to user_test; > > It was related to something similar to this: > http://archives.postgresql.org//pgsql-general/2007-06/msg01365.php > > I still have to sort it out since I did some random changes to > ownership of schema and tables. > > It seems that the schemas have to be owned by the "group" and not by > the single users. > > To sum it up: > > a role as a group owning everything > several roles as "users" member of the previous role > everything owned by the "group" > permissions assigned to the single users In general you will put a user into a group role. And if the group role does not have the permissions for the schema you will run into this issue. So give the permissions to the group role and it will work ... Check this out: http://archives.postgresql.org//pgsql-admin/2009-02/msg00268.php up to http://archives.postgresql.org//pgsql-admin/2009-02/msg00274.php maybe you follow that discussion for some minutes ... Cheers Andy
Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> I get a
> Query failed: ERROR: permission denied for schema user_test CONTEXT:
> SQL statement "UPDATE ONLY "user_test"."shop_commerce_baskets" SET
> "sid" = NULL WHERE $1::pg_catalog.text OPERATOR(pg_catalog.=)
> "sid"::pg_catalog.text
> This query is run when I do a
> DELETE FROM user_test.sessions WHERE timestamp < 1236672815;
> as a result of an
> on delete set null
Queries for RI constraints are run with the permissions of the owner of
the other table. It looks to me like the owner of user_test.sessions
doesn't have usage permission on schema user_test ...
regards, tom lane
Tom Lane schrieb: > Ivan Sergio Borgonovo <mail@webthatworks.it> writes: >> I get a >> Query failed: ERROR: permission denied for schema user_test CONTEXT: >> SQL statement "UPDATE ONLY "user_test"."shop_commerce_baskets" SET >> "sid" = NULL WHERE $1::pg_catalog.text OPERATOR(pg_catalog.=) >> "sid"::pg_catalog.text > >> This query is run when I do a >> DELETE FROM user_test.sessions WHERE timestamp < 1236672815; >> as a result of an > >> on delete set null > > Queries for RI constraints are run with the permissions of the owner of > the other table. It looks to me like the owner of user_test.sessions > doesn't have usage permission on schema user_test ... > > regards, tom lane > that's what I tried to say ;-) therfore the examples in my other posts ... Cheers Andy
On Tue, 10 Mar 2009 13:12:03 -0400 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > > I get a > > Query failed: ERROR: permission denied for schema user_test > > CONTEXT: SQL statement "UPDATE ONLY > > "user_test"."shop_commerce_baskets" SET "sid" = NULL WHERE > > $1::pg_catalog.text OPERATOR(pg_catalog.=) "sid"::pg_catalog.text > > > This query is run when I do a > > DELETE FROM user_test.sessions WHERE timestamp < 1236672815; > > as a result of an > > > on delete set null > Queries for RI constraints are run with the permissions of the > owner of the other table. It looks to me like the owner of > user_test.sessions doesn't have usage permission on schema > user_test ... It looks a bit more complicated... or at least unexpected to me. It surely is a problem of ownership... but when I set the ownership of the schema to the "user" it didn't work. It started to work when the ownership of the schema was set to the user "group" (that is the owner of the DB too). This is a bit sub-optimal since it would be nice to have stuff in the public schema, stuff in a shared schema owned by the "group" and stuff in a schema just owned by the user. I'll try to tighten access later. At this moment it is not really an issue since users are just a "trick" to have a simple way to have the search path set as I want without directly modifying it. -- Ivan Sergio Borgonovo http://www.webthatworks.it