Re: GRANT USAGE on FOREIGN SERVER exposes passwords

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