Re: dblink vs SQL/MED - security and implementation details

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.