pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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-----





view thread (9+ messages)  latest in thread

Message-ID: <200311121456.23177.skoot@qi.pl>
Permalink:  ../200311121456.23177.skoot@qi.pl/
Also on:    postgresql.org/message-id/200311121456.23177.skoot@qi.pl

 · 

reply

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