Thread: GRANT USAGE on FOREIGN SERVER exposes passwords

On Tue, Feb 3, 2015 at 6:26 PM, Noah Yetter <nyetter@gmail.com> wrote:
> The obvious objection is, "well you should just use foreign tables instead
> of dblink()".  I'll cut a long story short by saying that doesn't work for
> us.  We are using postgres_fdw to allow our analysts to run queries against
> AWS Redshift and blend those results with tables in our OLTP schema.  If you
> know anything about Redshift, or about analysts, you'll realize immediately
> why foreign tables are not a viable solution.  Surely there are many others
> in a similar position, where the flexibility offered by dblink() makes it
> preferable to fixed foreign tables.
>
> Soooo... what gives?  This seems like a really obvious security hole.  I've
> searched the mailing list archives repeatedly and found zero discussion of
> this issue.

Maybe this is an impertinent question, but why do you care if the user
has the password?  If she's got dblink access, she can run arbitrary
SQL queries on the remote server anyway, which is all the password
would let her do.  Also, she could use dblink to run ALTER ROLE foo
PASSWORD '...' on the remote server, and then she'll *definitely* know
the password.

I would suggest not relying on password authentication in this
situation.  Instead, use pg_hba.conf to restrict connections by IP and
SSL mode, and maybe consider SSL certificate authentication.

All that having been said, it wouldn't be crazy to try to invent a
system to lock this down, but it *would* be complicated.  An
individual FDW can call its authentication-related options anything it
likes; they do not need to be called 'password'.  So we'd need a way
to identify which options should be hidden from untrusted users, and
then a bunch of mechanism to do that.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> All that having been said, it wouldn't be crazy to try to invent a
> system to lock this down, but it *would* be complicated.  An
> individual FDW can call its authentication-related options anything it
> likes; they do not need to be called 'password'.  So we'd need a way
> to identify which options should be hidden from untrusted users, and
> then a bunch of mechanism to do that.

It's also debatable whether this wouldn't be a violation of the SQL
standard.  I see nothing in the SQL-MED spec authorizing filtering
of the information_schema.user_mapping_options view.

We actually are doing some filtering of values in user_mapping_options,
but it's all-or-nothing so far as the options for any one mapping go.
That's still not exactly supportable per spec but it's probably less of a
violation than option-by-option filtering would be.

It also looks like that filtering differs in corner cases from what the
regular pg_user_mappings view does, which is kinda silly.  In particular
I think we should try to get rid of the explicit provision for superuser
access.

I was hoping Peter would weigh in on what his design considerations
were for these views ...
        regards, tom lane

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Tue, Feb 3, 2015 at 6:26 PM, Noah Yetter <nyetter@gmail.com> wrote:
> > The obvious objection is, "well you should just use foreign tables instead
> > of dblink()".  I'll cut a long story short by saying that doesn't work for
> > us.  We are using postgres_fdw to allow our analysts to run queries against
> > AWS Redshift and blend those results with tables in our OLTP schema.  If you
> > know anything about Redshift, or about analysts, you'll realize immediately
> > why foreign tables are not a viable solution.  Surely there are many others
> > in a similar position, where the flexibility offered by dblink() makes it
> > preferable to fixed foreign tables.
> >
> > Soooo... what gives?  This seems like a really obvious security hole.  I've
> > searched the mailing list archives repeatedly and found zero discussion of
> > this issue.
>
> Maybe this is an impertinent question, but why do you care if the user
> has the password?

Eh.  Password-reuse risk, policies, regulations and auditing all come to
mind.

> If she's got dblink access, she can run arbitrary
> SQL queries on the remote server anyway, which is all the password
> would let her do.  Also, she could use dblink to run ALTER ROLE foo
> PASSWORD '...' on the remote server, and then she'll *definitely* know
> the password.

And I thought this was about FDW options and not about dblink, really..

> I would suggest not relying on password authentication in this
> situation.  Instead, use pg_hba.conf to restrict connections by IP and
> SSL mode, and maybe consider SSL certificate authentication.

That's not actually an option here though, is it?  dblink_connect
requires a password-based authentication, unless you're a superuser
(which I'm pretty sure Noah Y would prefer these folks not be..).

Further, I don't think you get to control whatever the pg_hba.conf is on
the RedShift side..  I agree with the general sentiment that it'd be
better to use other authentication methods (SSL certificates or Kerberos
credentials), but we'd need to provide a way for those to work for
non-superusers.  Kerberos credential-forwarding comes to mind but I
don't know of anyone who is currently working on that and I doubt it'd
work with Redshift anyway.

> All that having been said, it wouldn't be crazy to try to invent a
> system to lock this down, but it *would* be complicated.  An
> individual FDW can call its authentication-related options anything it
> likes; they do not need to be called 'password'.  So we'd need a way
> to identify which options should be hidden from untrusted users, and
> then a bunch of mechanism to do that.

Agreed, we'd need to provide a way for FDWs to specify which options
should be hidden and which shouldn't be.  For my 2c, I do think that'd
be worthwhile to do.  We let users change their own passwords with ALTER
USER too, but they don't get to view it (or even the hash of it) in
pg_authid.
Thanks,
    Stephen

On Thu, Feb 5, 2015 at 10:48 AM, Stephen Frost <sfrost@snowman.net> wrote:
>> If she's got dblink access, she can run arbitrary
>> SQL queries on the remote server anyway, which is all the password
>> would let her do.  Also, she could use dblink to run ALTER ROLE foo
>> PASSWORD '...' on the remote server, and then she'll *definitely* know
>> the password.
>
> And I thought this was about FDW options and not about dblink, really..

The OP is pretty clearly asking about dblink.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Thu, Feb 5, 2015 at 10:48 AM, Stephen Frost <sfrost@snowman.net> wrote:
> >> If she's got dblink access, she can run arbitrary
> >> SQL queries on the remote server anyway, which is all the password
> >> would let her do.  Also, she could use dblink to run ALTER ROLE foo
> >> PASSWORD '...' on the remote server, and then she'll *definitely* know
> >> the password.
> >
> > And I thought this was about FDW options and not about dblink, really..
>
> The OP is pretty clearly asking about dblink.

I was just pointing out that it was an issue that all FDWs suffer from,
since we don't have any way for an FDW to say "don't show this option",
as discussed.
Thanks,
    Stephen

Stephen Frost <sfrost@snowman.net> writes:
> * Robert Haas (robertmhaas@gmail.com) wrote:
>> On Thu, Feb 5, 2015 at 10:48 AM, Stephen Frost <sfrost@snowman.net> wrote:
>>> And I thought this was about FDW options and not about dblink, really..

>> The OP is pretty clearly asking about dblink.

> I was just pointing out that it was an issue that all FDWs suffer from,
> since we don't have any way for an FDW to say "don't show this option",
> as discussed.

The dblink example is entirely uncompelling, given that as you said
somebody with access to a dblink connection could execute ALTER USER on
the far end.  It's much more credible to imagine that someone might be
given a mapping for a postgres_fdw, oracle_fdw, etc foreign server with
just a few foreign tables, with the expectation that that couldn't be
parlayed into unfettered access to the remote server.

Whether this is a realistic expectation given the wording of the SQL-MED
standard is unclear.

I'm also concerned that if we take this on board as being a security
concern, it will mean that any time we make an effort to push some
construct we didn't before over to the remote end, we have to worry about
whether it would be a security breach to allow the local user to cause
that code to execute on the remote end.  It's tough enough worrying about
semantic-equivalence issues without mixing hostile-user scenarios in.

So I would rather say that the baseline security expectation is that
granting a user mapping should be presumed to be tantamount to granting
direct access to the remote server with that login info.  In that context,
being able to see the password should not be considered to be any big deal.
        regards, tom lane

Tom Lane-2 wrote
> Stephen Frost <

> sfrost@

> > writes:
>> * Robert Haas (

> robertmhaas@

> ) wrote:
>>> On Thu, Feb 5, 2015 at 10:48 AM, Stephen Frost <

> sfrost@

> > wrote:
>>>> And I thought this was about FDW options and not about dblink, really..
> 
>>> The OP is pretty clearly asking about dblink.
> 
>> I was just pointing out that it was an issue that all FDWs suffer from,
>> since we don't have any way for an FDW to say "don't show this option",
>> as discussed.
> 
> The dblink example is entirely uncompelling, given that as you said
> somebody with access to a dblink connection could execute ALTER USER on
> the far end.  

So lets fix that loop-hole as well...


> So I would rather say that the baseline security expectation is that
> granting a user mapping should be presumed to be tantamount to granting
> direct access to the remote server with that login info.  In that context,
> being able to see the password should not be considered to be any big
> deal.

Is there any provision whereby "USAGE" would restrict the person so granted
from viewing any particulars even though they can call/name the item being
granted; and then require "SELECT" privileges to actual view any of the
associated settings?

Regardless, the OP described behavior of suppressing user options normally
but then showing them upon being granted USAGE on the server seems strange.

David J.




--
View this message in context:
http://postgresql.nabble.com/GRANT-USAGE-on-FOREIGN-SERVER-exposes-passwords-tp5836652p5836826.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Whether this is a realistic expectation given the wording of the SQL-MED
> standard is unclear.

I've only got a draft as of 2011-12 handy, but I'm not seeing anything
in the standard that cares one bit about the value of the options
specified for the FDW.  All that's said is "Both the option name and the
permissible ranges of option values of a generic option are defined by
the foreign-data wrappers."

In my view, that means that if we give FDWs the ability to control
what's displayed for their options, we're well within the requirements
of the spec.

> I'm also concerned that if we take this on board as being a security
> concern, it will mean that any time we make an effort to push some
> construct we didn't before over to the remote end, we have to worry about
> whether it would be a security breach to allow the local user to cause
> that code to execute on the remote end.  It's tough enough worrying about
> semantic-equivalence issues without mixing hostile-user scenarios in.

I agree that we don't want to go there but I don't see this as leading
us in that direction.

> So I would rather say that the baseline security expectation is that
> granting a user mapping should be presumed to be tantamount to granting
> direct access to the remote server with that login info.  In that context,
> being able to see the password should not be considered to be any big deal.

I don't agree with this, however.  Being able to execute arbitrary SQL
on the remote side through a specific interface does not equate to
having the password and direct access to the remote server.  Even if it
did, there are good reasons to not expose passwords, even to the user
whose password it is.  We take steps to avoid exposing user's passwords
and password hashes in core and I do not see this case as any different.
Thanks,
    Stephen

On 2/5/15 10:13 AM, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> All that having been said, it wouldn't be crazy to try to invent a
>> system to lock this down, but it *would* be complicated.  An
>> individual FDW can call its authentication-related options anything it
>> likes; they do not need to be called 'password'.  So we'd need a way
>> to identify which options should be hidden from untrusted users, and
>> then a bunch of mechanism to do that.
> 
> It's also debatable whether this wouldn't be a violation of the SQL
> standard.  I see nothing in the SQL-MED spec authorizing filtering
> of the information_schema.user_mapping_options view.
> 
> We actually are doing some filtering of values in user_mapping_options,
> but it's all-or-nothing so far as the options for any one mapping go.
> That's still not exactly supportable per spec but it's probably less of a
> violation than option-by-option filtering would be.
> 
> It also looks like that filtering differs in corner cases from what the
> regular pg_user_mappings view does, which is kinda silly.  In particular
> I think we should try to get rid of the explicit provision for superuser
> access.
> 
> I was hoping Peter would weigh in on what his design considerations
> were for these views ...

I recall that we had extensive discussions about this back in the day.
The SQL standard doesn't provide for any filtering of options, and the
current behavior is just a bare minimum to not appear completely stupid.

Since we don't know which options are security-sensitive, the only
choices are hide everything or hide nothing.  Note that if you opt for
hide everything, you will also need to hide everything from servers and
wrappers, since they could also contains passwords in their options.

We could ask the FDW which options are security sensitive, but that
seems quite complicated to implement.  We could require the user to
specify which option values they want hidden (SENSITIVE OPTION '...' or
whatever).

I would welcome improvements in this area.  I'm not worried about SQL
standard requirements here.

On 2/5/15 10:48 AM, Tom Lane wrote:
> Stephen Frost<sfrost@snowman.net>  writes:
>> >* Robert Haas (robertmhaas@gmail.com) wrote:
>>> >>On Thu, Feb 5, 2015 at 10:48 AM, Stephen Frost<sfrost@snowman.net>  wrote:
>>>> >>>And I thought this was about FDW options and not about dblink, really..
>>> >>The OP is pretty clearly asking about dblink.
>> >I was just pointing out that it was an issue that all FDWs suffer from,
>> >since we don't have any way for an FDW to say "don't show this option",
>> >as discussed.
> The dblink example is entirely uncompelling, given that as you said
> somebody with access to a dblink connection could execute ALTER USER on
> the far end.

Actually, you can eliminate that by not granting direct access to dblink 
functions. Instead you create a SECURITY DEFINER function that sanity 
checks the SQL you're trying to run and rejects things like ALTER USER. 
While you're doing that, you can also lock away the connection 
information. A former coworker actually built a system that does this, 
at least to a limited degree.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> On 2/5/15 10:48 AM, Tom Lane wrote:
>> The dblink example is entirely uncompelling, given that as you said
>> somebody with access to a dblink connection could execute ALTER USER on
>> the far end.

> Actually, you can eliminate that by not granting direct access to dblink 
> functions. Instead you create a SECURITY DEFINER function that sanity 
> checks the SQL you're trying to run and rejects things like ALTER USER. 
> While you're doing that, you can also lock away the connection 
> information. A former coworker actually built a system that does this, 
> at least to a limited degree.

... but if you aren't giving the untrusted user direct access to the
connection, then he also doesn't get to see its options in the view.
So this still isn't compelling, so far as dblink goes.
        regards, tom lane