Re: tracking owner of extension-managed objects

On 12/21/2015 12:46 PM, Tom Lane wrote:

> BTW, any such ownership relationship really needs to be reflected into
> pg_shdepend, else someone might drop a role that still owns objects.
> (I guess there are problems with extensions trying to do such things at
> all, since we don't provide a way for extensions to hook into the DROP
> mechanisms. Perhaps that should be fixed.)

That is literally *the very next* e-mail I was going to compose.

I was looking at pg_(sh)?depend, and it seems they both only allow
recording dependencies *of* things in system catalogs *on* things
in system catalogs. It doesn't seem to offer a way to record that
some row in my added, non-system table, does in fact depend on
some system object. I can probably cobble around this with some
combination of triggers on my own table ('cause that works) and
event triggers to grovel through the parse trees of commands that
could affect the system object, but I get tired just thinking about
it.

> But tell me: why do you need to record ownership?

Some fraction of the maybe unusually demanding things PL/Java tries
to do might just be chalked up to its being one of the few PLs
for which there's an existing standard. ISO 9075-13 says jars got
owners. So they got owners. (It also says they got ACLs, USAGE
anyway, which PL/Java's jars ain't got yet, but yeah, that's
another thing.)

Noah and I have had a side conversation about what 9075-13 says
about jar paths, and how that is and isn't similar to what
Thomas implemented in PL/Java; in the standard when you load
a jar you also get to say what other jars it depends on, which
requires you to own the dependent one and have USAGE on the
dependencies.

> Anything involving filesystem
> references really ought to be superuser-only, I'd think, and the
> ability
> to load arbitrary jarfiles even more so.

It's kind of subtle ... if you have a PL and you assume it exercises
enough control over code it executes to qualify as a trusted one,
then you want non-supers to be able to declare functions, and
somehow they have to be able to supply the code their functions
will run. It happens that for most PLs they supply it by stuffing
the code itself between the quote marks after AS. In PL/Java
what you put there instead is a reference to a jar previously
loaded and given an internal name by install_jar(url, intname, ...)
(and that is straight outta the standard).

So your ability to call install_jar with some url is nothing more
than the PL/Java way of supplying the code for your functions,
and if non-superusers are allowed to supply their own code for
other PLs, this isn't a completely different game.

Now, where it gets different is that one possible scheme for a url
is file:, and currently in PL/Java if you call install_jar with a
file: url, you are telling it to read from the server's filesystem.
If the file exists and is a jar, you can then call code in it;
otherwise from the error you can deduce something about the file,
that it doesn't exist, isn't readable by postgres, isn't a jar....

The standard does leave an implementation complete freedom to say
what urls work for install_jar, whether to forbid certain
urls or schemes entirely, or even to allow special schemes that
have no meaning outside the implementation.

So it would be perfectly standard-conformant to say only a superuser
gets to use a file: url with install_jar, or, non-superusers can
only use file: urls within file:/tmp/placetoputmyjars/. If the
user puts his jar up on his web server and calls install_jar with
an http: url, that should be no more of a security concern than
any other PL allowing the user to say whatever he wants between
the quote marks after AS. And if the implementation wanted to
define a special urlscheme pqcopy: where pqcopy:/path/to/file
refers to a jar on the client machine, all of that falls within
what the standard allows. (I haven't really looked at how \copy
works enough to know whether a scheme like pqcopy: can really be
implemented, initiated from the server side; just brainstorming.)

Btw, the standard is silent on what install_jar actually does
with the jar, beyond that it gets a short name and an owner,
and no longer depends on the original url being accessible.
It could be stored in a table or tables (as PL/Java currently
does), a blob (in PG versions where blobs got owners, that could
simplify the owner dependency problem), or even some internally
managed filesystem area by the PL implementation itself; that
doesn't count as filesystem access by user code, any more than
it would if a trusted function requests a sort for which PG creates
a temp file behind the scenes. The JVM itself also creates and
manages temp files transparently for various internal purposes,
just as, for all I know, Python or R might.

-Chap