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@xxxxxxxxxx> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
