PostgreSQL and Apache: authentication and authorization

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.
>From pgsql-general-owner(at)postgresql(dot)org Tue Jun 13 13:26:20 2006
X-Original-To: pgsql-general-postgresql(dot)org(at)postgresql(dot)org
Received: from localhost (mx1.hub.org [200.46.208.251])
by postgresql.org (Postfix) with ESMTP id C52E29FA13E
for <pgsql-general-postgresql(dot)org(at)postgresql(dot)org>; Tue, 13 Jun 2006 13:26:19 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
with ESMTP id 97524-06 for <pgsql-general-postgresql(dot)org(at)postgresql(dot)org>;
Tue, 13 Jun 2006 13:26:15 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from fetter.org (dsl092-188-065.sfo1.dsl.speakeasy.net [66.92.188.65])
by postgresql.org (Postfix) with ESMTP id 3A3F09F9316
for <pgsql-general(at)postgresql(dot)org>; Tue, 13 Jun 2006 13:26:15 -0300 (ADT)
Received: by fetter.org (Postfix, from userid 500)
id AE05BC62B8; Tue, 13 Jun 2006 09:26:11 -0700 (PDT)
Date: Tue, 13 Jun 2006 09:26:11 -0700
From: David Fetter <david(at)fetter(dot)org>
To: Scott Ribe <scott_ribe(at)killerbytes(dot)com>
Cc: Aaron Bingham <bingham(at)cenix-bioscience(dot)com>, dananrg(at)yahoo(dot)com,
pgsql-general(at)postgresql(dot)org
Subject: Re: Fabian Pascal and RDBMS deficiencies in fully implementing
Message-ID: <20060613162611(dot)GB17534(at)fetter(dot)org>
References: <448D2F27(dot)4080000(at)cenix-bioscience(dot)com> <C0B43359(dot)4CBBB%scott_ribe(at)killerbytes(dot)com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <C0B43359(dot)4CBBB%scott_ribe(at)killerbytes(dot)com>
User-Agent: Mutt/1.4.2.1i
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/520
X-Sequence-Number: 96892

On Tue, Jun 13, 2006 at 09:18:17AM -0600, Scott Ribe wrote:
> > What say we just stop right there and call Date's Relational Model
> > what it is: a silly edifice built atop wrong premises.
>
> SQL was a quick and dirty hack (Systems R and R* needed some way to
> interface with data) with multiple deficiencies recognized and
> documented right within the very first paper by its own authors.

Perfection isn't a human attribute. There isn't a whole lot of
convincing evidence that it's a divine attribute. Did you have a
point to make?

> To hold it up as any kind of paradigm is really misinformed.

SQL had something that relational algebra/relational calculus did not
have, which is that somebody without a math degree can stare at it a
short while and *do* something with it right away. That it also has
other properties that are extremely useful and powerful (the ability
to specify states of ignorance using NULL, do arithmetic, use
aggregates, etc.) is what has made it such a smashing success.

Now, there's another thing that makes it amazingly hard to displace:
imagining what would be better *enough* to justify the many millions
of people-years and even more billions of dollars needed to move away
from it. Despite Date's many whines over the decades, his
still-vaporware Relational Model doesn't even vaguely approximate that
criterion.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!