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
> 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.
The Postgres Database Company
Sent via pgsql-hackers mailing list (firstname.lastname@example.org)
To make changes to your subscription: