Re: dblink connection security - Mailing list pgsql-patches

On Sunday 01 July 2007 16:03, Joe Conway wrote:
> Tom Lane wrote:
> > Joe Conway <mail@joeconway.com> writes:
> >> Tom Lane wrote:
> >>> Would it be sensible to change dblink so that unless invoked by a
> >>> superuser, it fails any connection attempt in which no password is
> >>> demanded?  I am not sure that this is possible without changes to
> >>> libpq; but ignoring implementation difficulties, is this a sane idea
> >>> from the standpoint of security and usability?
> >>
> >> Possibly so. Remember that dblink is simply a libpq client. Doesn't that
> >> mean that similar (although likely less severe) issues affect other
> >> libpq clients executing locally, such as php or perl-dbi clients?
> >
> > Yeah, in principle this issue applies to any process performing a
> > Postgres connection on behalf of someone else.  (Whether there are any
> > programs doing that, other than dblink, is debatable; but someday there
> > may be.)
>
> Well certainly dbi-link has the exact same issue.

dbi-link only works in plperlu, so you've already decided your superuser only.

> And a local php-apache
> instance connecting to Postgres would allow Postgres connections as the
> apache user, no? Not that it is likely to be a problem, but if for some
> reason there was an apache user in Postgres, and even worse, if that
> user was given superuser status, you would have the exact same problem.
>

That doesnt seem like the same problem to me, since those connections are
still from an external source.  The issue with dblink is that because you are
making a connection from a postgres process, you are connecting as the
postgres user when you weren't that user before.

> > The point about Kerberos delegation is interesting, but given that it
> > doesn't work anyway, I'm not sure we need a solution for it right now.
> > Possibly, when and if we get around to implementing it, we can somehow
> > treat use of a delegated ticket as equivalent to use of a password.
> > The general point is that we'd like to know whether the connection was
> > authorized by means of some data supplied by the client, or on the basis
> > of our own process identity (the latter being the case we wish to
> > reject).  Right now the only kind of "data supplied by the client" here
> > is a password.
> >
> > Here's a straw-man proposal that we could perhaps do for 8.3:
> >
> > 1. Invent a libpq connection-status function
> >
> >     bool PQconnectionUsedPassword(const PGconn *conn);
>
> Maybe PQconnectionUsedAuthToken() to mean "data supplied by the client",
> including other potential future mechanisms?
>
> > 2. Make dblink close the connection and throw error if called by a
> > non-superuser and PQconnectionUsedPassword returns false.
>
> Sounds good to me.
>

> > This idea isn't usable as a back-patch, however, because adding
> > functions to existing libpq versions is too chancy.  What we could
> > possibly do in back versions is, if dblink_connect is called by a
> > non-superuser, first issue the connection attempt without any password
> > and reject if that doesn't fail.  (This'd involve parsing the connect
> > string well enough to remove the password, which is tedious, but
> > certainly doable.)
>
> Why not just require the connect string to contain a password for
> non-superusers?
>
> > I like this approach better than removing public execute privileges
> > on the functions, for two reasons:
> >
> > * A routine minor version update would install the security fix into
> > existing installations, without need for any DBA intervention.
> >
> > * It does not take away functionality that has perfectly legitimate uses.
>
> Agreed.
>

I think this will break backwards compatability though.  ie. non-superusers
may be calling functions that are relying on dblink to connect sans password,
which would be broken by the above changes. I'm pretty sure it can be worked
around (though the work around is ugly, you cant hardcode passwords in the
functions or else people can look them up in pgproc) but people may need to
make changes to thier code to live with this.

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