Thread: Force SSL / username combination
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
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
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
"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
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
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