Re: client authentication towards postgresql in php?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



-----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-----




[Index of Archives]     [Postgresql General]     [Postgresql Admin]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Backpacking]     [Postgresql Jobs]

  Powered by Linux