Re: Hot to restrict access to subset of data

On Fri, Jul 01, 2005 at 01:56:41PM +0300, Andrus wrote:
>
> I want to restrict access to this table based on the user name, document
> type and access level. I have 3 levels: no access, view only, modify access.
>
> Example:
>
> User A can only view documents of type X and modify documents of type Y
> User B can only view documents of type Z

You could use a view: revoke all privileges from the table and grant
privileges to a view that selects from the table and restricts the
output based on CURRENT_USER or SESSION_USER (e.g., via a join with
a permissions table).  For updates you could create a rule on the
view; see "The Rule System" in the documentation for more information.

> 2. Postgres should allow access from my application only. Is it possible to
> use authentication method which allows access from my application only ?

You could have the application connect to the database as a particular
user and grant permissions on the table only to that user.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/