Re: User permissions

"Lars Preben S. Arnesen" <l(dot)p(dot)arnesen(at)usit(dot)uio(dot)no> writes:

> I'm writing a web application to access a Postgres database. I want
> the application to use a user with limited permissions - it should
> only be able to execute predefined functions in the database.
>
> I have been searching for the Postgres-way of doing this, but I din't
> find any solution.
>
> I have designed functions with pl/pgsql which do alter, insert, select
> and delete in the database, but I don't want the database user to be
> able to perform these actions without using the predefined functions.

We had a nice little flamewar about this a few weeks ago. ;)

The "Postgres" way to do it is to lock the unprivileged user out of
the "real" tables, and create views for that user to access. The
views can include only the fields that you want them to see, and you'd
create ON INSERT/DELETE/UPDATE rules to validate input and write to
the actual tables.

This is kind of a different way of thinking about it than the "proxy
functions" concept but you should be able to do everything you want to
do.

-Doug
--
Doug McNaught Wireboard Industries http://www.wireboard.com/

Custom software development, systems and network consulting.
Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...