Re: client authentication towards postgresql in php?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Daniel Struck (wto 11. listopad 2003 14:54):
> The problem is, I don't have a password available in php.
> The users are authenticated with x509 certificats and a private key, not a
> password. The private key is stored on a smartcard and never leaves it, the
> smartcard itself handles the authentication with apache.
>
> Problem now is, I want to authenticate the user with postgresql, to be able
> to log what the user is doing in postgresql itself. But I don't have a
> password to authenticate the user.
> Thought about using a Kerberos ticket in postgresql, but don't know how to
> setup this.

If you want only the logging ability, you may try to handle authentication
inside the database. I mean:
- - connect to PG database as one user (apache)
- - make every PHP script create a temporary table with username
just after establishing the connection:
CREATE TEMPORARY TABLE logged_user (username varchar);
INSERT INTO logged_user VALUES ('username');
- - prepare triggers that log every modification to every table you're
interested in. The trigger procedure(s) should get the data from that
temporary table and use it to store who's doing the modifications.
If the table does not exists, fire an exception inside the trigger
procedure. This will ensure that only logged users will success with
modifications.

Why temporary tables?
- - They last only for the session. You don't have to remember to remove them at
the end of your PHP script.
- - They are visible only in the session that created them.

I'm using similiar scheme with passwords. I wasn't able to create many users
in the PG database and has to go with authentication inside the database.

To prevent users from, for example, disabling or removing triggers, you may
create tables as another user, and grant only necessary permissions to
'apache' user.

Another thing to remember is that in every procedure you write in postgresql
you have to remember that logged_user table is a temporary table, so
procedures in pgsql language have to acces it thru EXECUTE 'select username
from logged_user;' construction.

HTH

- --
[http://skoot.qi.pl for GPG keys]
"A computer programmer is someone who, when told to "Go to Hell", sees
the "Go to", rather than the destination, as harmful."
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE/sjv+vkWo15WV1rkRAhLQAJ47mkhPXGdXckaHRmmZOXTPEoEWhACcDYSK
K2JUokvC37aIT9FZUoSNWqM=
=jrc+
-----END PGP SIGNATURE-----