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 <sfr...@snowman.net> wrote: > * Robert Haas (robertmh...@gmail.com) wrote: > > On Tue, Feb 3, 2015 at 6:26 PM, Noah Yetter <nyet...@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 >
