Re: [PATCH] unalias of ACL_SELECT_FOR_UPDATE

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> IMHO the only sane change would be to introduce a new
> ACL_SELECT_FOR_SHARE permission for SELECT FOR SHARE.

This might be worth doing ...

> That way you could
> grant SELECT_FOR_SHARE permission on a table to let people insert rows
> into other tables that have a foreign key reference to it, without
> having to grant UPDATE permission.

... but this argument for it is utter nonsense. FKs are not a
permissions problem, because the triggers run as the table's owner.
The only permission you need is REFERENCES:

regression=# create user m;
CREATE ROLE
regression=# create user s;
CREATE ROLE
regression=# \c - m
psql (8.4beta1)
You are now connected to database "regression" as user "m".
regression=> create table m(f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "m_pkey" for table "m"
CREATE TABLE
regression=> insert into m values(1);
INSERT 0 1
regression=> \c - s
psql (8.4beta1)
You are now connected to database "regression" as user "s".
regression=> create table s (f1 int references m);
ERROR: permission denied for relation m
regression=> \c - m
psql (8.4beta1)
You are now connected to database "regression" as user "m".
regression=> grant references on m to s;
GRANT
regression=> \c - s
psql (8.4beta1)
You are now connected to database "regression" as user "s".
regression=> create table s (f1 int references m);
CREATE TABLE
regression=> insert into s values(1);
INSERT 0 1
regression=> insert into s values(2);
ERROR: insert or update on table "s" violates foreign key constraint "s_f1_fkey"
DETAIL: Key (f1)=(2) is not present in table "m".
regression=>

regards, tom lane