Re: postgres_fdw super user checks

On Mon, Oct 17, 2016 at 12:03 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)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