Re: dblink connection security

Tom Lane wrote:
> 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.)

Well certainly dbi-link has the exact same issue. 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.

> 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 won't have time to work on this until the end of the coming week --
tomorrow is the last day of my current business trip which tends to be
busy. Tuesday I spend all day getting from Germany to San Diego. If it
can wait that long, I'll look into it starting on the 5th, unless
someone beats me to it.

Joe