When a user is granted USAGE on a foreign server, the psql command \deu+ will show them the username and password bound to the applicable user mapping.
To demonstrate (9.3+):
(as a superuser)
# create extension postgres_fdw ;
# create foreign server loopback_server
foreign data wrapper postgres_fdw
options(host '127.0.0.1', port '5432') ;
# create user mapping for public
server loopback_server
options(username 'foo', password 'bar') ;
(as a normal user)
> \deu+
List of user mappings
Server | User name | FDW Options
-----------------------+-----------+----------------
loopback_server | public |
(1 row)
So far so good?
> select * from dblink('loopback_server', 'select current_date') as x(column1 date) ;
ERROR: permission denied for foreign server loopback_server
OK, I can't do that now. Let's fix it:
# grant usage on foreign server loopback_server to public ;
> select * from dblink('loopback_server', 'select current_date') as x(column1 date) ;
column1
------------
2015-02-03
(1 row)
Sweet! But...
> \deu+
List of user mappings
Server | User name | FDW Options
-----------------------+-----------+--------------------------------
loopback_server | public | ("user" 'foo', password 'bar')
(1 row)
Crap.
(FWIW, it doesn't matter whether you grant to PUBLIC or to a specific user, the result is the same.)
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.
--
Noah Yetter
Data Architect/DBA @ Craftsy
