Обсуждение: Database security granularity

Hello everyone,

I'm working with an application, and I'm realizing that perhaps the
model for security that I have used in the past won't work all that well
with the application that I'm working on.  I am certain that this
particular model is how web applications traditionally work, but I am
wondering if I can do something a little more then this in PostgreSQL.

Traditionally, I have a table that contains the user names and hashed
passwords for each of the users that are defined to the application.
When they login, it checks against this table, and so forth.

However, the application that I'm currently working on is something that
I want to have a portable set of front-ends to work with.  For example,
I'd like a front-end in PHP for web-based access, but also I am working
on creating a front-end that will be written in a more client/server
fashion.  I'd like to know if I can constrict database and data access
on a row-level with PgSQL by using some sort of trickery in the database
configuration itself.

If not, that's okay, I suppose -- it'll just mean that I'll need to come
up with my own client/server portions of the program, with the server
side of it regulating the access.  That will make it significantly more
of a challenge, but it is the only other thing that I can come up with.

Ideas?

    Thanks,
    Mike

Michael Trausch <michael.trausch@comcast.nope.net> writes:
> I'd like to know if I can constrict database and data access
> on a row-level with PgSQL by using some sort of trickery in the database
> configuration itself.

You could do this with views, on the order of

    create view secure_view as
    select * from base_table where access_allowed(current_user, ...);

where you need to write an access_allowed function that implements your
security policy (probably based on fields in the base table that are not
reflected in the view, so it's not really gonna be "select *").  Then
you grant access to the view but not the base table to the users.

Also, take a look at
    http://pgfoundry.org/projects/veil/
and search the archives for past discussions of row-level security.

            regards, tom lane