Briefly, I create two tables, one having a column which references the
other and which implements cascade deletes and updates. I create a user
who has modify access on one table, but only select on the referenced
table. This user is not allowed to insert a record into the referencing
table - the error message refers to the referenced table.
I don't think referential integrity should work this way. Any thoughts?
Details:
create table foo (
foo char(10)
);
revoke all on foo from public on foo;
create table bar (
foo char(10) references foo (foo) on delete cascade on update cascade,
parm int
);
revoke all on bar from public on bar;
create user lim ;
grant select on foo to lim;
grant insert on bar to lim;
grant update on bar to lim;
grant delete on bar to lim;
grant select on bar to lim;
bash$ psql -U lim test
Password:
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test=> select * from foo ;
foo
------------
foo
bar
(2 rows)
test=> insert into bar values ('foo', 1);
ERROR: foo: Permission denied.
test=>
--
Mike Howard <mike@clove.com>
