Re: GRANT USAGE on FOREIGN SERVER exposes passwords

It is far from central to this conversation, but I can confirm that
RedShift currently only supports user+pass combinations.

It's likely that each node has a pg_hba.conf, but the customer is not given
credentials to ssh to the individual nodes.

On Thu, Feb 5, 2015 at 10:48 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

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