Re: Blocked inserts on tables with FK to tables for which UPDATE has been revoked

Hello Alban,

You are right on both count of me not using the "test" user. At first, it
was set-up to use the "test" user, but later on, I wondered if owner of a
database got treated differently. The behaviour is the same regardless of if
it's the owner or any other role.

In the end, I got mixed up and sent the wrong version of the test script to
the list. However, even if it is not the right user, the problem remains.

Samuel

On Saturday, July 24, 2010 06:08:23 Alban Hertroys wrote:
> On 23 Jul 2010, at 20:39, Samuel Gilbert wrote:
> > Hello,
> >
> > I have encountered a problem with inserts failing because of permissions
> >
> > issues when the table in which I try to insert has foreign key
> > constraints to tables for which UPDATE has been revoked.
> >
> > The script bellow show how to reproduce the problem with a bare-bones
> > test case. Reproducibility is 100% on PostgreSQL 8.2.5 (I know it's not
> > even the latest revision of the 8.2 line, but it's what I have to work
> > with :( ) I Googled the error message and a couple of meaningful
> > keywords, searched the documentation and the mailing list archives
> > without success.
>
> It's not a solution to your problem, but one observation I made in your test
script:
> > CREATE DATABASE test WITH OWNER afsugil ENCODING 'LATIN1';
> >
> > CREATE ROLE test WITH NOCREATEDB NOCREATEROLE NOCREATEUSER INHERIT
> > LOGIN;
> > \c test afsugil
>
> You create a new user, but you still connect with the user who created the
> database.
>
> > REVOKE UPDATE ON station FROM afsugil;
>
> And then you revoke rights from that user instead of from the test user.
> Effectively you're not using the 'test' user at all in your script. Was
> that intended?
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the ceiling.
>
>
> !DSPAM:737,4c4abba6286216566810360!