Row-Level Access Control via FK to pg_catalog.pg_authid - Mailing list pgsql-general

Folks,

I'm working on a way to do row-level access via VIEWs and ROLEs.  The
idea:

Given a table foo with pk foo_id, which is to be the subject of these
row-level permissions, I'd make another table, say can_read_foo, which
looks like:

CREATE TABLE can_read_foo (
    foo_id INTEGER NOT NULL REFERENCES foo(foo_id),
    rolname NAME NOT NULL REFERENCES pg_catalog.pg_authid(rolname) /* OOPS! */
);

Then a VIEW my_foo that uses CURRENT ROLE and JOINs foo, can_read_foo,
and some clever recursive role spidering in order to determine what
rows to present to a particular role on SELECT.

The problem is that that foreign key to pg_catalog.pg_authid is
generically disallowed.  This is because (thanks for explaining,
Andrew of Supernews) it's a shared catalog, so other DBs must be able
to modify it without looking inside the one I have this installed in.
Other than MySQLishly leaving an unenforced FK constraint to pg_authid
flapping in the breeze, is there any way to handle this?

Thanks in advance for any hints, tips or pointers :)

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!