dblink connection security

Robert Treat wrote:

Patch based on recent -hackers discussions, it removes usage from public, and
adds a note to the documentation about why this is neccessary.

I agree with the fix as the simplest and most sensible approach, and in
general with the doc change, but I'm not inclined to reference the
security paper. Maybe something like:

As a security precaution, dblink revokes access from PUBLIC role
usage for the dblink_connect functions. It is not safe to allow
remote users to execute dblink from a database in a PostgreSQL
installation that allows local account access using the "trust"
authentication method. In that case, remote users could gain
access to other accounts via dblink. If "trust" authentication
is disabled, this is no longer an issue.

I suppose this ought to be applied back through the 7.3 branch?

Joe

"Joe Conway" <mail@joeconway.com> writes:

Robert Treat wrote:

Patch based on recent -hackers discussions, it removes usage from public, and
adds a note to the documentation about why this is neccessary.

I agree with the fix as the simplest and most sensible approach, and in general
with the doc change, but I'm not inclined to reference the security paper.
Maybe something like:

As a security precaution, dblink revokes access from PUBLIC role
usage for the dblink_connect functions. It is not safe to allow
remote users to execute dblink from a database in a PostgreSQL
installation that allows local account access using the "trust"
authentication method. In that case, remote users could gain
access to other accounts via dblink. If "trust" authentication
is disabled, this is no longer an issue.

I think putting the emphasis on Postgresql trust authentication is missing the
broader point. I would suggest two paragraphs such as:

dblink allows any connected user to attempt to connect to TCP/IP or Unix
sockets from the database server as the user the database system is running.
This could allow users to circumvent access control policies based on the
connecting user or the connecting host.

In particular Postgres's "trust" authentication is one such system. It
authenticates connecting users based on the unix userid of the process
forming the connection. In typical configurations any user who is granted
execute access to dblink can form connections as the "postgres" user which is
the database super-user. If "trust" authentication is disabled this is no
longer an issue.

Therefore dblink requires you to explicitly grant execute privileges to users
or roles you wish to allow to form connections. It does not grant these
privileges to the PUBLIC role by default as other packages typically do.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

On Sunday 01 July 2007 13:15, Gregory Stark wrote:

"Joe Conway" <mail@joeconway.com> writes:

Robert Treat wrote:

Patch based on recent -hackers discussions, it removes usage from
public, and adds a note to the documentation about why this is
neccessary.

I agree with the fix as the simplest and most sensible approach, and in
general with the doc change, but I'm not inclined to reference the
security paper. Maybe something like:

As a security precaution, dblink revokes access from PUBLIC role
usage for the dblink_connect functions. It is not safe to allow
remote users to execute dblink from a database in a PostgreSQL
installation that allows local account access using the "trust"
authentication method. In that case, remote users could gain
access to other accounts via dblink. If "trust" authentication
is disabled, this is no longer an issue.

I think putting the emphasis on Postgresql trust authentication is missing
the broader point. I would suggest two paragraphs such as:

dblink allows any connected user to attempt to connect to TCP/IP or Unix
sockets from the database server as the user the database system is
running. This could allow users to circumvent access control policies based
on the connecting user or the connecting host.

In particular Postgres's "trust" authentication is one such system. It
authenticates connecting users based on the unix userid of the process
forming the connection. In typical configurations any user who is granted
execute access to dblink can form connections as the "postgres" user which
is the database super-user. If "trust" authentication is disabled this is
no longer an issue.

Did you mean s/trust/ident/g, otherwise I don't think I understand the
above... granted the combination of trust for localhost does open a door
for remote users if they have access to dblink, but I don't think that's what
you were trying to say.

Therefore dblink requires you to explicitly grant execute privileges to
users or roles you wish to allow to form connections. It does not grant
these privileges to the PUBLIC role by default as other packages typically
do.

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

Robert Treat <xzilla@users.sourceforge.net> writes:

Did you mean s/trust/ident/g, otherwise I don't think I understand the
above...

Both trust and ident local auth are sources of risk for this, although
ident is particularly nasty since the DBA probably thinks he's being
secure.

For that matter, I'm not sure that *any* auth method except password
offers much security against the problem; don't LDAP and Kerberos
likewise rely mostly on process-level identity? And possibly PAM
depending on which PAM plugin you're using?

I'm not sure whether this is something to back-patch, though, since
a back-patch will accomplish zero for existing installations.

regards, tom lane

Tom Lane wrote:

Robert Treat <xzilla@users.sourceforge.net> writes:

Did you mean s/trust/ident/g, otherwise I don't think I understand the
above...

Both trust and ident local auth are sources of risk for this, although
ident is particularly nasty since the DBA probably thinks he's being
secure.

For that matter, I'm not sure that *any* auth method except password
offers much security against the problem; don't LDAP and Kerberos
likewise rely mostly on process-level identity? And possibly PAM
depending on which PAM plugin you're using?

LDAP is not affected - it requires the user to enter a password. Same
would be for any PAM plugins that actually require the user to enter a
password, I think.

Kerberos is not affected either, because the server does not get a copy
of the ticket. In theory it could be affected if the server requested a
delegation enabled ticket, and exported it so it could be used, but none
of these are done.

//Magnus

"Robert Treat" <xzilla@users.sourceforge.net> writes:

In particular Postgres's "trust" authentication is one such system. It
authenticates connecting users based on the unix userid of the process
forming the connection. In typical configurations any user who is granted
execute access to dblink can form connections as the "postgres" user which
is the database super-user. If "trust" authentication is disabled this is
no longer an issue.

Did you mean s/trust/ident/g, otherwise I don't think I understand the
above... granted the combination of trust for localhost does open a door
for remote users if they have access to dblink, but I don't think that's what
you were trying to say.

Er quite right. Moreover it's not even true that ``"if "ident" authentication
is disabled this is no longer an issue''. It's still possible to have other
restrictions in pg_hba which dblink would allow you to circumvent. That
sentence is too generous of a promise.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

* Magnus Hagander (magnus@hagander.net) wrote:

LDAP is not affected - it requires the user to enter a password. Same
would be for any PAM plugins that actually require the user to enter a
password, I think.

Agreed.

Kerberos is not affected either, because the server does not get a copy
of the ticket. In theory it could be affected if the server requested a
delegation enabled ticket, and exported it so it could be used, but none
of these are done.

That's quite a stretch even there, imv anyway... It'd have to be put
somewhere a backend connecting would think to look for it, given that
the user can't change the environment variables and whatnot (I don't
think) of the backend process... Regardless, strong wording against
allowing users to issue arbitrary connect's from a backend process is
appropriate, regardless of what's affected exactly (as that could
possibly change over time anyway too...).

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

* Magnus Hagander (magnus@hagander.net) wrote:

Kerberos is not affected either, because the server does not get a copy
of the ticket. In theory it could be affected if the server requested a
delegation enabled ticket, and exported it so it could be used, but none
of these are done.

That's quite a stretch even there, imv anyway... It'd have to be put
somewhere a backend connecting would think to look for it, given that
the user can't change the environment variables and whatnot (I don't
think) of the backend process...

Hmm. I think what you are both saying is that if the remote end wants
Kerberos auth then you would expect a dblink connection to always fail.
If so, then we still seem to be down to the conclusion that there
are only three kinds of dblink connection:
* those that require a password;
* those that don't work;
* those that are insecure.

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?

regards, tom lane

Tom Lane wrote:

Stephen Frost <sfrost@snowman.net> writes:

* Magnus Hagander (magnus@hagander.net) wrote:

Kerberos is not affected either, because the server does not get a copy
of the ticket. In theory it could be affected if the server requested a
delegation enabled ticket, and exported it so it could be used, but none
of these are done.

That's quite a stretch even there, imv anyway... It'd have to be put
somewhere a backend connecting would think to look for it, given that
the user can't change the environment variables and whatnot (I don't
think) of the backend process...

Hmm. I think what you are both saying is that if the remote end wants
Kerberos auth then you would expect a dblink connection to always fail.
If so, then we still seem to be down to the conclusion that there
are only three kinds of dblink connection:
* those that require a password;
* those that don't work;
* those that are insecure.

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?

Not sure. That would break any attempts of implementing delegation in
Kerberos for dblink, but I don't know if we're interested in doing that
anyway.

BTW, what I wrote about delegation before is wrong, of course. If
delegation worked, in that pg requested a delegation enabled ticket and
exported it through the dblink connection, it would authenticate as the
user that authenticated to the original database, not as the superuser
or anything like that. So delegation would actually be perfectly secure.
If implemented properly of course.

//Magnus

Tom Lane wrote:

Robert Treat <xzilla@users.sourceforge.net> writes:

Did you mean s/trust/ident/g, otherwise I don't think I understand the
above...

Both trust and ident local auth are sources of risk for this, although
ident is particularly nasty since the DBA probably thinks he's being
secure.

For that matter, I'm not sure that *any* auth method except password
offers much security against the problem; don't LDAP and Kerberos
likewise rely mostly on process-level identity? And possibly PAM
depending on which PAM plugin you're using?

OK, so following that line of thought, how about:

As a security precaution, dblink revokes access from PUBLIC role
usage for the dblink_connect functions. It is not safe to allow
ordinary users to execute dblink from a database in a PostgreSQL
installation that allows account access using any authentication
method which does not require a password. In that case, ordinary
users could gain access to other accounts via dblink as if they
had the privileges of the database superuser.

If the allowed authentication methods require a password, this is no
longer an issue.

I'm not sure whether this is something to back-patch, though, since
a back-patch will accomplish zero for existing installations.

OK. But it might still be worth doing, along with something in the
release notes.

Joe

Tom Lane wrote:

Stephen Frost <sfrost@snowman.net> writes:

* Magnus Hagander (magnus@hagander.net) wrote:

Kerberos is not affected either, because the server does not get a copy
of the ticket. In theory it could be affected if the server requested a
delegation enabled ticket, and exported it so it could be used, but none
of these are done.

That's quite a stretch even there, imv anyway... It'd have to be put
somewhere a backend connecting would think to look for it, given that
the user can't change the environment variables and whatnot (I don't
think) of the backend process...

Hmm. I think what you are both saying is that if the remote end wants
Kerberos auth then you would expect a dblink connection to always fail.
If so, then we still seem to be down to the conclusion that there
are only three kinds of dblink connection:
* those that require a password;
* those that don't work;
* those that are insecure.

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?

Joe

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

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

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

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

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.

Agreed.

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

Hmm. It would be better if it never actually completed an authentication
in the first place, but I don't see how we can do that given how the
protocol works.
We could add a connection string parameter that disables it, but that
doesn't really help since the backend moves into authenticated mode
before you can abort anyway.
And it's probably not a big issue anyway.

//Magnus

Joe Conway <mail@joeconway.com> writes:

Tom Lane wrote:

bool PQconnectionUsedPassword(const PGconn *conn);

Maybe PQconnectionUsedAuthToken() to mean "data supplied by the client",
including other potential future mechanisms?

Well, that'd not solve the pre-existing problem of how to tell whether
to request a password. If we had a fairly clear idea of what other
sorts of auth tokens might be involved, I'd be willing to go that way,
but I distrust our ability to design it today.

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.

Why not just require the connect string to contain a password for
non-superusers?

Doesn't fix the problem, because you don't know whether libpq actually
used the password or not.

I won't have time to work on this until the end of the coming week --

No hurry, I don't think there are any short-term plans for a release.

regards, tom lane

Magnus Hagander <magnus@hagander.net> writes:

Hmm. It would be better if it never actually completed an authentication
in the first place, but I don't see how we can do that given how the
protocol works.
We could add a connection string parameter that disables it, but that
doesn't really help since the backend moves into authenticated mode
before you can abort anyway.

Yeah. Since this is really a question of client-side code protecting
itself from misuse of its credentials, I don't think it's a very severe
problem --- it can certainly make the check before allowing any use of
the new PGconn object.

regards, tom lane

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

Robert Treat <xzilla@users.sourceforge.net> writes:

Tom Lane wrote:

I like this approach better than removing public execute privileges
on the functions, for two reasons:

I think this will break backwards compatability though.

Well, revoking public execute will break backwards compatibility too.

If you have a situation where you think it's safe to allow a
non-superuser to get at passwordless connections, you could wrap the
dblink_connect function in a postgres-owned SECURITY DEFINER function.
So either change can be worked around to get the old behavior if necessary.

regards, tom lane

Robert Treat wrote:

Joe Conway <mail@joeconway.com> writes:

Well certainly dbi-link has the exact same issue.

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

How so -- it is fundamentally no different than dblink, which is C
language (also untrusted).

I think the issue is that once the superuser creates said functions,
usage of the functions is automatically granted to PUBLIC, no? Being an
untrusted language just means that it takes a superuser to create the
functions using that language, not to use the functions themselves.

Joe