From: Mariusz Pekala <skoot@qi.pl>
To: pgsql-php@postgresql.org
Cc: Daniel Struck <struck.d@retrovirology.lu>
Subject: Re: client authentication towards postgresql in php?
Date: Wed, 12 Nov 2003 14:55:47 +0100
Message-ID: <200311121456.23177.skoot@qi.pl> (raw)
In-Reply-To: <20031111145425.716dbf1a.struck.d@retrovirology.lu>
References: <20031109165228.45503144.struck.d@retrovirology.lu>
<1068557456.10942.14684.camel@camel>
<20031111145425.716dbf1a.struck.d@retrovirology.lu>
-----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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: skoot@qi.pl, struck.d@retrovirology.lu
Subject: Re: client authentication towards postgresql in php?
In-Reply-To: <200311121456.23177.skoot@qi.pl>
References: <20031109165228.45503144.struck.d@retrovirology.lu> <1068557456.10942.14684.camel@camel> <20031111145425.716dbf1a.struck.d@retrovirology.lu> <200311121456.23177.skoot@qi.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox