Thanks to everyone who replied to my previous post, "PostgreSQL and Apache," where I asked about references describing how to 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 soon switch to the latest release, 8.1.4 I believe). For a user interface, I decided to have users access the DB via a web server, 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 the database and the web server will reside on the same Linux box. That box will sit on an intranet, behind a firewall; and all users will connect from within the firewall from web browsers. Users will be able to both read and write data. Security is 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 authentication to 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) rights to PG. One method would be to have a single user, say, www_user (aside from the PG superuser), and pass access rights information 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 authentication methods 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 hope is it would be secure because Apache was secured, the OS/box is otherwise secure, and PG would accept local connections only.) Re (2), it appears that there is at least one Apache module that allows Apache to handle authentication based on a PG authentication file. (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 other Apache-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 connections between the two that I'm wondering about.) Thanks, and apologies for any conceptual confusion evidence here.
