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

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!