I found that
inserting into a relation (eg. ref) referencing an other table (key)
as a "granted user" (grant insert on ref to private)
needs that the user has SELECT and UPDATE permission on the referenced table.
I`d like to know if there is any solution that does not need the UPDATE permission
on key except the owner of these tables. (SELECT permission is not so important.)
(PostgreSQL "7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2" is used.)
Here comes the first session in which the key and ref tables have been created:
try=# \set PROMPT1 '%n%R%#' -- just for see the current_user in prompt
gerzson=#create table key(col integer primary key);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'key_pkey' for table 'key'
CREATE
gerzson=#create table ref(col_ref integer references t_key);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
gerzson=#grant insert on ref to private;
CHANGE
gerzson=#insert into key values (5);
Here comes an other session trying to insert into ref table an existing value:
private=>insert into ref values (5);
ERROR: key: Permission denied.
Papp Gyozo
s7461pap@hszk.bme.hu, gerzson17@freemail.hu
