Thread: PostgreSQL and Apache: authentication and authorization

Thanks to everyone who replied to my previous post, "PostgreSQL and Apache," where I asked about references describing
howto get PostgreSQL and Apache working together. 

Here, I'll ask the specific questions which motivated that post.

The setup:  I'm working on a database project.  We chose PostgreSQL (PG) (right now 8.1.3 is installed, though we will
soonswitch to the latest release, 8.1.4 I believe).  For a user interface, I decided to have users access the DB via a
webserver, and chose Apache 2 (relatively recent version) for that.  The dynamic HTML and DB access will be handled by
PHP(tentatively I want to use PHP5). 

The web server is not being used to put up a public webpage.  Rather, it's just a choice for a user interface.  Both
thedatabase and the web server will reside on the same Linux box.  That box will sit on an intranet, behind a firewall;
andall users will connect from within the firewall from web browsers.  Users will be able to both read and write data.
Securityis paramount; only authorized users will be able to connect.  Each user will have access to only parts of the
data(authorization). 

My question concerns authentication and access.

I can envision, broadly, two authentication schemes:
(1) Users authenticate to Apache.
(2) Users authenticate to PG.

(For security reasons, I'm not considering any scheme where users authenticate/log into the Linux server; or, where
authenticationto PG or Apache relies on an OS-level password file listing OS-level users.) 

Regarding scheme (1), once a user authenticates to Apache, it's not clear to me how to pass on authorization (access)
rightsto PG.  One method would be to have a single user, say, www_user (aside from the PG superuser), and pass access
rightsinformation along in some kind of flag.  But then I wouldn't be able to rely on PG's built-in authorization model
(ROLEs). (Instead either access rights would be handled in PHP, where I don't think it belongs, or in some extra DB
logic,which would be messy.) 

Another possibility would be to have the PG recognize the same users that Apache does, and use one of the weaker
authenticationmethods for PG itself.  That is, the "real" authentication would lie with Apache; once authenticated
there,a user would be passed on to PG by the weaker method.  I'm not sure, however, if this method is secure.  (The
hopeis it would be secure because Apache was secured, the OS/box is otherwise secure, and PG would accept local
connectionsonly.) 

Re (2), it appears that there is at least one Apache module that allows Apache to handle authentication based on a PG
authenticationfile.  (So while there would still be Apache-level authentication, the data for that would reside within
PG.) This avoids problems with ROLEs, since the users would be PG users, but I'm not sure how it would fit in with
otherApache-level authentication I want to do (e.g., client certificates, etc).  Nor am I sure how secure it is. 

My question is the pros and cons of the possible methods.  I also need pointers to information on how to do these
things. (There are lots of Apache books, and a few PG books, out there, but I haven't found lengthy material on the
connectionsbetween the two that I'm wondering about.) 

Thanks, and apologies for any conceptual confusion evidence here.

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.