Re: Smaller access privilege changes

Peter Eisentraut <peter_e@gmx.net> writes:
> * Make DELETE distinct from UPDATE privilege

Okay.

> * rename the internal representation: s = select, i = insert, u = update,
>   d = delete, R = rules

Since the internal representation is visible to users, I fear that a
wholesale renaming will break existing applications.  Can we make this
part of the change less intrusive?

> * COPY FROM will require INSERT privilege.  It used to require
>   UPDATE/DELETE, it think that is not correct..
> * INSERT (the command) will require INSERT privilege.  UPDATE/DELETE won't
>   do any longer.  (Why was this there?)

Both of these are basically there because the underlying model is "read
and write", with "append" as a limited form of "write"; so "write"
allows everything that "append" does.  But if we are switching to a full
"insert/update/delete" model then this behavior should go away.

> * Implement SQL REFERENCES privilege:  grant references on A to B will
>   allow user B to create a foreign key referencing table A as primary key.

Which privilege will SELECT FOR UPDATE require, and how do you plan to
get the system to distinguish users' SELECT FOR UPDATE from the commands
issued by the foreign key triggers?

> I'd also like to create a regression test.  That will require creating
> some global users and groups in the installation where the test runs.  I
> think as long as we name them "regressuser1", "regressgroup2", etc. this
> won't harm anyone.

Seems reasonable, but be careful to cope with the case where these
objects already exist from a prior regression run.
        regards, tom lane