Re: Force SSL / username combination

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