[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bug?: permission denied for schema on on delete set null
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bug?: permission denied for schema on on delete set null
On Tue, 10 Mar 2009 14:31:56 +0100 Andreas Wenk a.w...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bug?: permission denied for schema on on delete set null
Ivan Sergio Borgonovo schrieb: On Tue, 10 Mar 2009 14:31:56 +0100 Andreas Wenk a.w...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bug?: permission denied for schema on on delete set null
Ivan Sergio Borgonovo m...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bug?: permission denied for schema on on delete set null
Tom Lane schrieb: Ivan Sergio Borgonovo m...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] bug?: permission denied for schema on on delete set null
On Tue, 10 Mar 2009 13:12:03 -0400 Tom Lane t...@sss.pgh.pa.us wrote: Ivan Sergio Borgonovo m...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
