Re: dblink connection security

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>> What about using the attached for 8.3, as well as earlier?
>
>> It simply does not allow the local database user to become someone else
>> on the libpq remote connection unless they are a superuser.
>
> This assumes that usernames on the remote site are equivalent to those
> locally. Which is helpful for the sort of local-loop scenarios we've
> been thinking about, but is hardly watertight even then (consider
> multiple postmasters on one machine). For remote connections it seems
> counterproductive; you might as well say "you must be superuser" and
> keep it simple.

I see your point. OK, I'm back to implementing your proposal...

One question: should we provide the SECURITY DEFINER functions with
revoked privileges or just mention that in the docs? I was thinking
something along the lines of the following even for the backpatched version:

CREATE OR REPLACE FUNCTION dblink_connect_u (text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_connect'
LANGUAGE C STRICT SECURITY DEFINER;

CREATE OR REPLACE FUNCTION dblink_connect_u (text, text)
RETURNS text
AS 'MODULE_PATHNAME','dblink_connect'
LANGUAGE C STRICT SECURITY DEFINER;

REVOKE execute ON FUNCTION dblink_connect_u (text) FROM public;
REVOKE execute ON FUNCTION dblink_connect_u (text, text) FROM public;

Joe