Re: dblink vs SQL/MED - security and implementation details - Mailing list pgsql-hackers

Joe Conway wrote:
> I don't see anything documented under GRANT which controls privileges on 
> a mapping, and the USAGE on a server only controls what a user can see 
> by query. I assume that if the superuser creates a mapping from user foo 
> to server bar, foo can still use bar via the mapping, even if they don't 
> have USAGE granted on the server. It isn't clear from the docs what is 
> intended, so I could have that wrong.

I think you are misunderstanding some details.  A user mapping does not 
map from a user to a server.  It maps, in the original sense of the 
meaning, a local user to a remote user within the context of a server. 
More generally, it maps a local user to a set of options that are 
necessary to reach the remote server, which would typically include 
remote user name and possibly password.

Regarding the scenario you describe above, a typical use case in a full 
implementation would be:

CREATE SERVER superdb FOREIGN DATA WRAPPER postgresql OPTIONS (host 
'localhost', port '5432', dbname 'mydb');

CREATE USER MAPPING FOR CURRENT_USER SERVER superdb OPTIONS (user 
'guest', password 'sekret');

CREATE FOREIGN TABLE foo SERVER superdb;

Then, when you access table "foo", the foreign-data wrapper would 
(depending on the implementation) connect to a PostgreSQL database using 
the union of the server and the user mapping options for the current 
user.  You could also put the user mapping options "user" and "password" 
into the server definition, if you only want to use one user identity. 
Or put the hostname into each user mapping.  It is just a mechanism to 
separate global and per-user connection options.

To get back to your point, if a superuser created a user mapping for 
user foo.  The permission check in the above example is that CREATE 
FOREIGN TABLE checks whether the current user has USAGE on the server. 
So the user mapping would possibly exist but not be usable.  So there is 
no problem.

dblink more or less takes the place of CREATE FOREIGN TABLE here, so the 
permission check should be more or less the same.

> But even if foo is granted USAGE on bar, I think you miss the point. If 
> you:
> 
> 1. grant a non-superuser (foo) access to a server (bar)
> 2. create a mapping for foo to bar which includes no password
> 3. configure bar to not require authentication (trust)
> 
> you will get the privilege escalation as shown (e.g. foo becomes 
> postgres on bar).

Don't do that then.

You could also:

1. write a function in an untrusted PL that calls out to a different 
database server (bar) (think PL/sh or PL/Perl + psql: it's one line)

2. grant non-superuser (foo) EXECUTE on that function

3. configure bar to not require authentication (trust)

Don't do that either. :-)

But dblink already has its own mechanisms for handling this case, and no 
one is asking you do give this up.  If we actually implement 
foreign-data wrappers, we will have to revisit this, but I don't see any 
need for change now.

Basically, both of the above scenarios are equivalent to granting 
EXECUTE on dblink_connect_u(), which is possible but not recommended in 
normal circumstances.  You just have to be careful about what you grant.