On Mon, Oct 17, 2016 at 12:03 AM, Jeff Janes <jeff.ja...@gmail.com> wrote: > postgres_fdw has some checks to enforce that non-superusers must connect to > the foreign server with a password-based method. The reason for this is to > prevent the authentication to the foreign server from happening on the basis > of the OS user who is running the non-foreign server. > > But I think these super user checks should be run against the userid of the > USER MAPPING being used for the connection, not the userid of currently > logged on user. > > That is, I think the last line in this script should succeed: ('jjanes' is > both a superuser, and a database): > > > CREATE EXTENSION IF NOT EXISTS postgres_fdw WITH SCHEMA public; > CREATE SERVER foo FOREIGN DATA WRAPPER postgres_fdw; > CREATE USER MAPPING FOR jjanes SERVER foo; > CREATE TABLE foobar1 ( x integer); > CREATE FOREIGN TABLE foobar2 ( x integer) SERVER foo OPTIONS ( table_name > 'foobar1'); > CREATE VIEW foobar3 AS SELECT foobar2.x FROM foobar2; > CREATE USER test; > GRANT SELECT ON TABLE foobar3 TO test; > \c jjanes test > select * from foobar3; > > It connects back to itself, simply for demonstration purposes. > > The attached patch implements this change in auth checking. >
I agree with your analysis, that any passwordless foreign server access with super user's user mapping should be allowed. If it's safe to access a foreign server without password for a superuser, then it should be safe to do so when corresponding user mapping is used even when login user is non-superuser. But there's one problem with the patch. login as some useruser and run following commands. create extension postgres_fdw; create server foo foreign data wrapper postgres_fdw options (dbname 'postgres'); create user test; grant USAGE ON FOREIGN server foo to test; set role test; create user mapping for test server foo; create foreign table fpg_class (oid oid) server foo options (table_name 'pg_class', schema_name 'pg_catalog'); create view fview as select * from fpg_class; set role <some superuser>; select * from fview limit 0; With your patch it gives error ERROR: password is required DETAIL: Non-superuser cannot connect if the server does not request a password. HINT: Target server's authentication method must be changed. Without the patch it does not give any error. Is that intentional? I guess, this is because of asymmetry in check_conn_params() and connect_pg_server(). The first one does not check any params if the logged in user is a superuser but the later checks if only the user in the mapping is superuser. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers