Re: bug?: permission denied for schema on "on delete set null"

On Tue, 10 Mar 2009 13:12:03 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ivan Sergio Borgonovo <mail(at)webthatworks(dot)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