Thread: Row-Level Access Control via FK to pg_catalog.pg_authid

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!

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

David Fetter <david@fetter.org> writes:
> I'm working on a way to do row-level access via VIEWs and ROLEs.

You sure you're not re-inventing the wheel?
http://pgfoundry.org/projects/veil/

            regards, tom lane