Re: Hot to restrict access to subset of data

"Michael Fuhr" <mike(at)fuhr(dot)org> wrote in message
news:20050701144604(dot)GA14542(at)winnie(dot)fuhr(dot)org(dot)(dot)(dot)
> 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.

Thank you. I'm thinking about following approach:

My application connects to Postgres always as superuser, using user name
postgres.
Postgres server as only one user.
Actual users names of users who can access data are stored in special table.
Since only my application knows the super-user password, the users can only
access data
throught my application. My application implements desired level of security
by allowing only pre-defined queries to be run by particular user.

Is this approach secure and better ?

Andrus.