Re: Best practice? Web application: single PostgreSQL

Keith Murphy wrote:
> At some point, I may try rolling my own PAM module (as Tom Lane
> suggested) that uses the user's browser-authenticated username and
> password to map to a PostgreSQL username that constitutes a "role"
> (assuming that's possible).

One option is to add an extra layer of indirection: the web server
interacts with a "transaction server" through eg: XML-RPC or CORBA.

The list of transactions ("interactions") you can perform is controlled,
each transaction can be logged, and each transaction handler can have
its own access rights to the postgresql database.

The transaction server can be hardened by only allowing access from the
web server. This does mean that anyone breaking into your web server can
potentially alter data by interacting with the transaction server - but
only to the extent allowed by the existing transactions. They can't make
wholesale changes to your database such as "select * from
credit_card_details; truncate invoices; drop table accounts_receivable;"
etc.

Regards
Alex Satrapa