Thread: Force SSL / username combination

Force SSL / username combination

From
Koen Vermeer
Date:
Hi,

I want to set up a database server that is connected to the Internet.
Therefore, I want my users to use SSL/TLS certificates. I provide these
certificates to the users, so I can control the information that is in
there. Now, I know that I can set up PostgreSQL to use SSL/TLS, but I am
unable to find a way to map a SSL/TLS certificate to a specific user.

Suppose that user A and user B both have a different, but valid
certificate. They can both use their certificate to connect to the
server, but then use the username of the other user to log in. I want to
make sure that each user is connected with his/her specific certificate.

Is there a way to set this up? If so, how?

Thanks for any pointers!

Koen


Re: Force SSL / username combination

From
Koen Vermeer
Date:
On Mon, 2007-07-09 at 09:05 +0200, Koen Vermeer wrote:
> I want to set up a database server that is connected to the Internet.
> Therefore, I want my users to use SSL/TLS certificates. I provide these
> certificates to the users, so I can control the information that is in
> there. Now, I know that I can set up PostgreSQL to use SSL/TLS, but I am
> unable to find a way to map a SSL/TLS certificate to a specific user.
> Is there a way to set this up? If so, how?
> Thanks for any pointers!

Despite the somewhat less than overwhelming number of replies, I think I
found a way around this. Just in case someone else may be looking for
this:

In contrib, there is something called 'sslinfo', which provides details
of the X509 certificate that was used by the client for the session.
Based on that, I can validate whether a username indeed matches the
certificate, or make sure a user can only get data matching some field
in the table to a field of the certificate, which probably is all I
need.

Koen


Re: Force SSL / username combination

From
Robert Treat
Date:
On Thursday 12 July 2007 10:44, Koen Vermeer wrote:
> On Mon, 2007-07-09 at 09:05 +0200, Koen Vermeer wrote:
> > I want to set up a database server that is connected to the Internet.
> > Therefore, I want my users to use SSL/TLS certificates. I provide these
> > certificates to the users, so I can control the information that is in
> > there. Now, I know that I can set up PostgreSQL to use SSL/TLS, but I am
> > unable to find a way to map a SSL/TLS certificate to a specific user.
> > Is there a way to set this up? If so, how?
> > Thanks for any pointers!
>
> Despite the somewhat less than overwhelming number of replies, I think I
> found a way around this. Just in case someone else may be looking for
> this:
>
> In contrib, there is something called 'sslinfo', which provides details
> of the X509 certificate that was used by the client for the session.
> Based on that, I can validate whether a username indeed matches the
> certificate, or make sure a user can only get data matching some field
> in the table to a field of the certificate, which probably is all I
> need.
>

I'm guessing the lack of response is due to a lack of knowledge on the topic.
Personally I've never quite understood how you'd make use of the sslinfo
functions to manage connections without something like on commit triggers, so
I hope you'll consider submitting some documentation once you figure it out.

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

Re: Force SSL / username combination

From
Gregory Stark
Date:
"Robert Treat" <xzilla@users.sourceforge.net> writes:

> I'm guessing the lack of response is due to a lack of knowledge on the topic.
> Personally I've never quite understood how you'd make use of the sslinfo
> functions to manage connections without something like on commit triggers, so
> I hope you'll consider submitting some documentation once you figure it out.

Well if you do the popular technique of doing everything through stored
procedures (in our case plpgsql functions) then you can have those functions
check. I don't like that approach myself though.

You could also have a column with a default value which uses the sslinfo to
retrieve the common name. Or you could have a trigger which throws an error if
that function doesn't return valid value. Either way you would be doing a lot
more work than necessary since it would be checking every row, not once per
session. And it wouldn't stop selects.

I think what you really want is a ON CONNECT trigger for this.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com


Re: Force SSL / username combination

From
Robert Treat
Date:
On Friday 13 July 2007 05:21, Gregory Stark wrote:
> "Robert Treat" <xzilla@users.sourceforge.net> writes:
> > I'm guessing the lack of response is due to a lack of knowledge on the
> > topic. Personally I've never quite understood how you'd make use of the
> > sslinfo functions to manage connections without something like on commit
> > triggers, so I hope you'll consider submitting some documentation once
> > you figure it out.
>
> Well if you do the popular technique of doing everything through stored
> procedures (in our case plpgsql functions) then you can have those
> functions check. I don't like that approach myself though.
>

Right. This approach always seemed "too late" to me, since the user was
already connected at that point.

> You could also have a column with a default value which uses the sslinfo to
> retrieve the common name. Or you could have a trigger which throws an error
> if that function doesn't return valid value. Either way you would be doing
> a lot more work than necessary since it would be checking every row, not
> once per session. And it wouldn't stop selects.
>
> I think what you really want is a ON CONNECT trigger for this.

lol! I surely meant ON CONNECT triggers above! I'm pretty sure PostgreSQL can
do "on commit" triggers right now. :-D

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

Re: Force SSL / username combination

From
Koen Vermeer
Date:
On Fri, 2007-07-13 at 06:53 -0400, Robert Treat wrote:
> > Well if you do the popular technique of doing everything through stored
> > procedures (in our case plpgsql functions) then you can have those
> > functions check. I don't like that approach myself though.
> Right. This approach always seemed "too late" to me, since the user was
> already connected at that point.

I agree with both of you: I would rather have the client to use its
certificate to login to PostgreSQL, like some other database is capable
of. However, given that this is not (currently) possible (as far as I
know), I have to consider workarounds. And one of those is to have the
client use a certificate for the underlying connection, restrict access
to the database to stored procedures, and have those stored procedures
use sslinfo to obtain the certificate data.

On my system, I want different people to access the same database, but I
have to make sure they cannot access other peoples data. If I just let
them use certificates for the underlying connection, without further
checks at the database level, I do not think I can make sure user A
doesn't get user B's data, if he/she wants to.

My initial design of the system included a front-end on the server to
which all clients connect, which would translate/check the queries. I
then thought it would be easier if this would all be done by the
database server, using stored procedures. Basically, that means I'm
changing my front-end from a separate application to a set of stored
procedures. Note that, for my case, the set of different queries a
client can do is pretty limited, so I can easily write a stored
procedure for all queries in the set.

If there is some alternative that is easier to implement, please let me
know!

Koen