David Fetter wrote:
> 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?
Maybe you can install a pg_shdepend entry instead of using a real FK?
The problem then is that if you do DROP ROLE CASCADE, your "foo" object
will go away ... or rather, an elog(ERROR) will be raised saying that
the "foo" object class is unknown.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
