Re: PostgreSQL and Apache: authentication and authorization

jqpx37 wrote:

>I can envision, broadly, two authentication schemes:
>(1) Users authenticate to Apache.
>(2) Users authenticate to PG.
>
>
>
This is a little too complicated.

The most accurate and precise security is obtained by having the user
log in with a real postgres account, and to grant table priveleges to
that account (though usually we make the account a member of a group
that has certain priveleges (except now we call them all roles to make
it more confusing)).

In our system we store the username's userid and password in the PHP
session and nothing more. On each round trip we make a connection and
run the request.

No Apache configuration is required.

Our package that does all of this is available as GPL, you are free to
trawl the code: http://docs.secdat.com.

Folks with a heavy coding background who distrust databases will usually
recommend connecting as superuser and enforcing security with
application code. This is bad because A) a mistake in code could be
catastropic, and B) it takes so much longer to code up security in the
app layer and C) anybody connecting directly to the database can subvert
it all.