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

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