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
