Re: GRANT USAGE on FOREIGN SERVER exposes passwords

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Tue, Feb 3, 2015 at 6:26 PM, Noah Yetter <nyetter(at)gmail(dot)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