Re: WITH CHECK and Column-Level Privileges

* Stephen Frost (sfrost(at)snowman(dot)net) wrote:
> > Is there similar problems with unique or exclusion constraints?
>
> That's certainly an excellent question.. I'll have to go look.

Looks like there is an issue here with CHECK constraints and NOT NULL
constraints, yes. The uniqueness check complains about the key already
existing and returns the key, but I don't think that's actually a
problem- to get that to happen you have to specify the new key and
that's what is returned.

Looks like this goes all the way back to column-level privileges and was
just copied into WithCheckOptions from ExecConstraints. :(

Here's the test case I used:

create table passwd (username text primary key, password text);
grant select (username) on passwd to public;
grant update on passwd to public;
insert into passwd values ('abc','hidden');
insert into passwd values ('def','hidden2');

set role alice;
update passwd set username = 'def';
update passwd set username = null;

Results in:

postgres=> update passwd set username = 'def';
ERROR: duplicate key value violates unique constraint "passwd_pkey"
DETAIL: Key (username)=(def) already exists.
postgres=> update passwd set username = null;
ERROR: null value in column "username" violates not-null constraint
DETAIL: Failing row contains (null, hidden).

Thoughts?

Thanks,

Stephen