client authentication towards postgresql in php?

Hello,


I am searching for a secure way to handle client authentication in php towa=
rds 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 certificate=
s 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 by apache.

In postgresql I would like to create for every client a user.
Purpose: automatically log every action like update/delete of users by rule=
s 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 accessi=
ble by the php application, but I don't like the idea of this file lying ar=
ound 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 postgresq=
l in a web application?


Best regards,
Daniel Struck


--=20
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barbl=E9
L-1210 Luxembourg

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: client authentication towards postgresql in php?

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: client authentication towards postgresql in php?

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

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.


Daniel

--=20
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barbl=E9
L-1210 Luxembourg

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: client authentication towards postgresql in php?

On Tue, Nov 11, 2003 at 14:54:25 +0100,
Daniel Struck 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 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 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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: client authentication towards postgresql in php?

On Tue, 11 Nov 2003 12:47:52 -0600
Bruno Wolff III 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 nor=
mally running under the user apache, so I don't think 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 Mozil=
la browser supporting the HTTP Negotiate authentication method. Main motiva=
tion is to add support for the Kerberos mechanism and use Kerberos tickets =
for user's authentication instead of their password. This way the user's Ke=
rberos password will no longer be transfered to the web server. More inform=
ation 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 kerber=
os, and pass the kerberos ticket with this method to 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.


--=20
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barbl=E9
L-1210 Luxembourg

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: client authentication towards postgresql in php?

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 tic=
ket from the KCT on behalf of an SSL authenticated user. 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 certificate=
s via SSL from mod_KCT and returns a Kerberos service ticket. It uses the O=
penSSL toolkit."
(source: http://www.citi.umich.edu/projects/kerb_pki/)

I have just to try it out ;-)

Daniel

--=20
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barbl=E9
L-1210 Luxembourg

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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, t=
he
> smartcard itself handles the authentication with apache.
>
> Problem now is, I want to authenticate the user with postgresql, to be ab=
le
> 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=
=20
inside the database. I mean:=20
- - connect to PG database as one user (apache)
- - make every PHP script create a temporary table with username
just after establishing the connection:=20
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.=20
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?=20
- - They last only for the session. You don't have to remember to remove th=
em at=20
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 user=
s=20
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=
=20
create tables as another user, and grant only necessary permissions to=20
'apache' user.

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

HTH

- --=20
[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+vkWo15WV1rkRAhLQAJ47mkhPXGdXckaHRmmZOXTPEoEWhACc DYSK
K2JUokvC37aIT9FZUoSNWqM=3D
=3Djrc+
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: client authentication towards postgresql in php?

> If you want only the logging ability, you may try to handle authenticatio=
n=20
> inside the database. I mean:=20
> - - connect to PG database as one user (apache)
> - - make every PHP script create a temporary table with username
> just after establishing the connection:=20
> 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.=20
> 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


--=20
Retrovirology Laboratory Luxembourg
Centre Hospitalier de Luxembourg
4, rue E. Barbl=E9
L-1210 Luxembourg

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: client authentication towards postgresql in php?

On Wed, Nov 12, 2003 at 11:26:03 +0100,
Daniel Struck wrote:
> On Tue, 11 Nov 2003 12:47:52 -0600
> Bruno Wolff III 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't think 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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster