From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Row-Level Access Control via FK to pg_catalog.pg_authid |
Date: | 2007-02-21 22:41:38 |
Message-ID: | 20070221224137.GB23083@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
From | Date | Subject | |
---|---|---|---|
Next Message | Erick Papadakis | 2007-02-22 00:09:13 | Re: postgresql vs mysql |
Previous Message | Filipe Fernandes | 2007-02-21 20:59:39 | (no subject) |