* Murray Cumming (murrayc@murrayc.com) wrote:
> I do have the option of creating a different set of user/password logins
> for the web UI and then either
> - Using one username/password for all web users' databases, with no
> PostgreSQL-level separation. But this would have to be in a config file
> at least. I guess this is what most web systems do, though they
> generally deal with only one database.
Actually, you could provide PG-level separation through the use of
roles. Create a role in the database which is 'noinherit' but which is
granted all the user roles. Then create a role for the web server to
log in as and then grant the 'noinherit' role to the web server role.
When the web server logs in and does whatever user verification it
needs, it can issue a 'set role userX;'.
Through that mechanism you could support client-side SSL certificates
(have Apache validate the user's client-side cert and then you can have
a mapping from DN to PG role for that user). With GSSAPI, as mentioned
up-thread, you could support Kerberos credentials and, with
mod_auth_kerb, could actually proxy those credentials to allow access to
the database as the user with GSSAPI. That requires setting up a
Kerberos environment, joining the client system to the Kerberos realm,
etc.
With the common web-server role method above, you could also implement
your own poor-man's GSSAPI using simple session ids, like a ton of web
sites out there do, and then have a mapping server-side between the
session ID and user role, along with first-issued time, last-used time,
etc, etc. If no session ID is present, go through the normal
authentication process and then have a security definer function be
used, upon successful login of that user, to set up the session.
Thanks,
Stephen