Re: dblink connection security

Joe Conway wrote:
> Robert Treat wrote:
>>>> Joe Conway <mail(at)joeconway(dot)com> writes:
>>> Well certainly dbi-link has the exact same issue.
>> dbi-link only works in plperlu, so you've already decided your superuser only.
>
> How so -- it is fundamentally no different than dblink, which is C
> language (also untrusted).
>
> I think the issue is that once the superuser creates said functions,
> usage of the functions is automatically granted to PUBLIC, no? Being an
> untrusted language just means that it takes a superuser to create the
> functions using that language, not to use the functions themselves.

In fact, this misconception can prove dangerous in other ways. From the
docs:

CREATE FUNCTION badfunc() RETURNS integer AS $$
my $tmpfile = "/tmp/badfile";
open my $fh, '>', $tmpfile
or elog(ERROR, qq{could not open the file "$tmpfile": $!});
print $fh "Testing writing to a file\n";
close $fh or elog(ERROR, qq{could not close the file "$tmpfile": $!});
return 1;
$$ LANGUAGE plperlu;

select usename, usesuper from pg_shadow;
usename | usesuper
----------+----------
postgres | t
foo | f
(2 rows)

contrib_regression=# \c - foo
You are now connected to database "contrib_regression" as user "foo".
contrib_regression=> select badfunc();
badfunc
---------
1
(1 row)

So anyone thinking that just because a language is untrusted means that
they don't need to be careful, is mistaken.

Joe