Re: dblink connection security

Joe Conway <mail(at)joeconway(dot)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.)

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);

This returns true if the server had demanded a password during the
authentication phase. Aside from solving the immediate problem, this
can be useful for regular clients such as psql: it could be applied to a
failed connection object to decide whether to prompt for a password
(replacing the current egregious hack of strcmp'ing the error message).

2. Make dblink close the connection and throw error if called by a
non-superuser and PQconnectionUsedPassword returns false.

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.)

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.

regards, tom lane