I have table of documents
CREATE TABLE document ( DocumentType CHARACTER(1), .... )
DocumentType field determines the document type stored in record.
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
I created application which implements those restictions.
Unfortunately, users can run pgAdmin and bypass the access restrictions.
I'm thinking about two solutions:
1. Postgres should automatically modify WHERE clauses to apply restrictions
based on user name.
For example, if user A runs query from pgAdmin
SELECT * FROM document
Postgres should actually run the query
SELECT * FROM document WHERE DocumentType IN ('X','Y')
2. Postgres should allow access from my application only. Is it possible to
use authentication method which allows access from my application only ?
Users connect to 5432 port from internet.
I'm using Postgres 8 in Windows from Windows ODBC clients.
Any idea how to implement this ?
Andrus.
