Thread: client authentication towards postgresql in php?

Hello,


I am searching for a secure way to handle client authentication in php towards postgresql.

Let me explain what I like to setup:

Client will connect to the application via Apache.
The authentication of the clients will be done over ssl and the certificates of the clients will be stored on
smartcards.
(I have written a small documentation how to setup this at www.opensc.org).
Next I will read in the PHP application environment variables of apache to know if the user was correctly authenticated
byapache. 

In postgresql I would like to create for every client a user.
Purpose: automatically log every action like update/delete of users by rules without requiring scripts in php.

The problem I have is to authenticate the client to postgresql.
Of course I could save the passwords for the clients in a text file accessible by the php application, but I don't like
theidea of this file lying around on the hard drive. 

I thought about using Kerberos in this setup, sort of:

1.apache authenticates the client (two way ssl with smartcards)
2. client receives a ticket from kerberos
3. which php could forward to postgresql.

Is this possible?

Maybe someone knows another secure way of authenticating users to postgresql in a web application?


Best regards,
Daniel Struck


--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg

phone: +352-44116105
fax:   +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu

On Sun, 2003-11-09 at 10:52, Daniel Struck wrote:
> Hello,
>
>
> I am searching for a secure way to handle client authentication in php towards postgresql.
>
> Let me explain what I like to setup:
>
> Client will connect to the application via Apache.
> The authentication of the clients will be done over ssl and the certificates of the clients will be stored on
smartcards.
> (I have written a small documentation how to setup this at www.opensc.org).
> Next I will read in the PHP application environment variables of apache to know if the user was correctly
authenticatedby apache. 
>
> In postgresql I would like to create for every client a user.
> Purpose: automatically log every action like update/delete of users by rules without requiring scripts in php.
>
> The problem I have is to authenticate the client to postgresql.

If your actually creating a user inside the database for each user,
authentication is handled inside the database and passwords are held
inside the database.  When your php login script fires off, save the
user name/ password into a session and then use that info to build your
pg_connect strings.

Or maybe I've missed something because this sounds a lot easier to do
than your making it out to be.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

> If your actually creating a user inside the database for each user,
> authentication is handled inside the database and passwords are held
> inside the database.  When your php login script fires off, save the
> user name/ password into a session and then use that info to build your
> pg_connect strings.

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


Daniel

--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg

phone: +352-44116105
fax:   +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu

On Tue, Nov 11, 2003 at 14:54:25 +0100,
  Daniel Struck <struck.d@retrovirology.lu> wrote:
>
> 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
smartcardand 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
postgresqlitself. 
> 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 trust the host the php/web server runs on you may be able to use
trust authentication. If you don't trust all of the users on that host
then you can use ident authentication, though if the db server and php/web
server aren't the same host using identd may slow things down too much.

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

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

thanks for this nice workaround :-), I will use it if i can't integrate the apache module "mod_kct" in my setup.
(mod_kct, http://www.citi.umich.edu/projects/kerb_pki/)

Daniel


--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg

phone: +352-44116105
fax:   +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu

I think I found what I was looking for :-)

"KCT and mod_KCT

mod_KCT is an Apache web server module that acquires a Kerberos service ticket from the KCT on behalf of an SSL
authenticateduser. The web server can then act as a Kerberos client on the user's behalf. 

KCT runs on the same machine that runs the KDC. It accepts user certificates via SSL from mod_KCT and returns a
Kerberosservice ticket. It uses the OpenSSL toolkit." 
(source: http://www.citi.umich.edu/projects/kerb_pki/)

I have just to try it out ;-)

Daniel

--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg

phone: +352-44116105
fax:   +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu

On Tue, 11 Nov 2003 12:47:52 -0600
Bruno Wolff III <bruno@wolff.to> wrote:

> If you trust the host the php/web server runs on you may be able to use
> trust authentication. If you don't trust all of the users on that host
> then you can use ident authentication, though if the db server and php/web
> server aren't the same host using identd may slow things down too much.

The web application, which will make the connection to the database, is normally running under the user apache, so I
don'tthink I could use the ident method? 


I have found this interesting info:

"The goal of the Negotiateauth project is to create an plugin for the Mozilla browser supporting the HTTP Negotiate
authenticationmethod. Main motivation is to add support for the Kerberos mechanism and use Kerberos tickets for user's
authenticationinstead of their password. This way the user's Kerberos password will no longer be transfered to the web
server.More information on the use of Negotiate method in Mozilla and Apache can be found at
http://meta.cesnet.cz/software/heimdal/negotiate.en.html."

So maybe I could authenticate every user at the client machines with kerberos, and pass the kerberos ticket with this
methodto apache, who will pass it to php, which does use it to connect to postgresql. 

Would now be interesting to know if I can authenticate to a Kerberos server with a smartcard.


--
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barblé
L-1210 Luxembourg

phone: +352-44116105
fax:   +352-44116113
web: http://www.retrovirology.lu
e-mail: struck.d@retrovirology.lu

On Wed, Nov 12, 2003 at 11:26:03 +0100,
  Daniel Struck <struck.d@retrovirology.lu> wrote:
> On Tue, 11 Nov 2003 12:47:52 -0600
> Bruno Wolff III <bruno@wolff.to> wrote:
>
> > If you trust the host the php/web server runs on you may be able to use
> > trust authentication. If you don't trust all of the users on that host
> > then you can use ident authentication, though if the db server and php/web
> > server aren't the same host using identd may slow things down too much.
>
> The web application, which will make the connection to the database, is normally running under the user apache, so I
don'tthink I could use the ident method? 

Sure you can. If you trust the apache user, you can use ident authentication
to allow it to connect as any of the valid users. It may be in your case
that you can't trust the apache user, but we don't know enough about your
setup to be able to tell that.