Обсуждение: tracking owner of extension-managed objects

I'm looking for best-practice advice.

PL/Java is an extension that manages some objects (jar files, which
users can tell PL/Java to load, drop, or replace). The objects have
owners (have had since PL/Java 1.1.0 anyway).

When the owner tracking was added for 1.1.0 it recorded the owner oid.

In 2006, before 1.3.0, it was changed to keep the owner name instead
of the oid, in response to a bug report 1506 that involved the wrong
owner name being shown after dump/restore into another db where the
user oids were different.

Neither approach seems perfect to me (in fact, they both strike me
as having complementary sides of the same weakness, which dump/restore
just happens to expose). I am also wondering whether PL/Java ought to
create references, or a trigger, on pg_authid to clean up if the user
goes away; it currently doesn't.

Before I spend too many cycles on this, is there a most favored
design pattern that has already emerged for this kind of thing?

-Chap

Chapman Flack <chap@anastigmatix.net> writes:
> PL/Java is an extension that manages some objects (jar files, which
> users can tell PL/Java to load, drop, or replace). The objects have
> owners (have had since PL/Java 1.1.0 anyway).

> When the owner tracking was added for 1.1.0 it recorded the owner oid.

> In 2006, before 1.3.0, it was changed to keep the owner name instead
> of the oid, in response to a bug report 1506 that involved the wrong
> owner name being shown after dump/restore into another db where the
> user oids were different.

Surely that is wrong.  What happens after ALTER USER RENAME?

You should *store* user identities as OIDs in the catalogs, but textual
dumps should present them as names.  The recently added datatype regrole
might help with doing this in extension-defined tables.

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.)

> I am also wondering whether PL/Java ought to
> create references, or a trigger, on pg_authid to clean up if the user
> goes away; it currently doesn't.

A trigger would be useless, since we do not support triggers on system
catalogs, and are unlikely to start doing so, and even if we did it could
not fix ownerships appearing in other databases.  But see pg_shdepend.

On the whole I'm afraid PL/Java may have gotten out in front of the
available extension infrastructure by trying to do this.  But tell me:
why do you need to record ownership?  Anything involving filesystem
references really ought to be superuser-only, I'd think, and the ability
to load arbitrary jarfiles even more so.  If so, you really don't need to
remember which superuser created the reference; all superusers are
equivalent from a security standpoint.
        regards, tom lane

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

On 12/21/2015 02:30 PM, Chapman Flack wrote:
> On 12/21/2015 12:46 PM, Tom Lane wrote:

>> 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, ...
> 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,

right, I can't event-trigger on role commands either, can I?

What's the lightest-weight object I can create that has an owner,
and whose disappearance I can be notified of?

-Chap

On Mon, Dec 21, 2015 at 3:09 PM, Chapman Flack <chap@anastigmatix.net> wrote:
> On 12/21/2015 02:30 PM, Chapman Flack wrote:
>> On 12/21/2015 12:46 PM, Tom Lane wrote:
>
>>> 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, ...
>> 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,
>
> right, I can't event-trigger on role commands either, can I?

No, and it wouldn't do what you want anyway, because roles are global
objects spanning all databases.  You can't guarantee that your event
trigger is installed in all of them, and even if you could, there's
nothing particularly useful you can do if you are in database A and
get told that a role is going away, and the stuff you care about
fixing up is in database B.

> What's the lightest-weight object I can create that has an owner,
> and whose disappearance I can be notified of?

Schema?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 12/23/15 12:26 PM, Robert Haas wrote:
>> What's the lightest-weight object I can create that has an owner,
>> >and whose disappearance I can be notified of?
> Schema?

I was thinking view or function, since then you can hide all of them in 
an internal-only schema.

BTW, I've been pondering a very similar problem to this. I'm working on 
a metacoding framework, and it's inevitable that at some point it will 
want to know what objects it's created. I can use reg* for a lot of 
that, but that still doesn't let me do a foreign key. And not all 
objects have reg* casts last I looked.

I was planning on just making a best possible attempt and solving this 
in an extension via a combination of event triggers, reg* and other 
voodoo, but being able to insert things directly into pg_(sh)depend or 
equivalent tables would be a lot more robust.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 12/23/15 15:02, Jim Nasby wrote:
> BTW, I've been pondering a very similar problem to this. I'm working on a
> metacoding framework, and it's inevitable that at some point it will want to
> know what objects it's created....
> 
> I was planning on just making a best possible attempt and solving this in an
> extension via a combination of event triggers, reg* and other voodoo,

In the voodoo department, I guess PL/Java could accompany each loading of
a jar 'foo' with a 'CREATE TYPE sqlj.voodoo_foo AS ()' which is about as
lightweight as it gets, and an extra regtype column in the jar_repository
table could carry the Oid of that type. The type would be in pg_shdepend for
the owner, and its ACL could even be used to implement the SQL/JRT
requirement for jars to have an ACL (and be automatically represented
in pg_shdepend for any roles mentioned in the ACL). The type would also
end up in pg_depend for the owning extension, if the jar was installed
by an extension script. And (in 9.3+ anyway), I could have an sql_drop
event trigger to detect when the type goes away for any reason, and
remove the corresponding jar_repository entry.

How well will pg_dump/restore handle that voodoo? I suppose they'll
reliably recreate the types before loading the table with a regtype
column, and by typing the column as regtype, the dump will refer to
the type by name, and therefore work even if pg_dump is not given
the --oids option?

But that's all voodoo. What if we wanted to not need voodoo?

On 12/21/15 12:46, Tom Lane wrote:
> (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.)


Ok, how numerous would be the problems with this:


- The classid and refclassid columns (in both pg_shdepend and pg_depend) are currently Oid columns referencing
pg_class.oid. The catalog definition would not preclude putting the oid of a non-system table there.  The
*documentation*says it has to be the oid of a system catalog, and surely there is code that currently would be
surprisedby an entry that wasn't (for example, default: elog(ERROR, "unrecognized object class..." in getObjectClass).
Buteven now, looking at recordDependencyOn or shdepAddDependency, I don't see that the code would prevent such an entry
beingadded.
 

- It still would make no sense as a refclassid in pg_shdepend. All three other cases (classid in pg_shdepend, classid
orrefclassid in pg_depend) can make sense in a world of extension-managed objects.
 

- So, what would be needed to make those 3 cases supportable?  For starters, how about a strict rule for *when* a
non-system-catalogclassid or refclassid can be allowed into either of those tables:
 
 IF an ObjectAddress.classId IS NOT a system catalog   (currently detectable by getObjectClass(classId) throwing an
error),THEN: - it MUST be the Oid of an existing (non-system) relation - that relation MUST be WITH OIDS (so the
ObjectAddress.objectIdcan   identify a row in it)   ... alternatively, must have an int unique key, and the objSubId
can      be what identifies the row - that relation MUST have a DELETE FOR EACH ROW trigger that calls
pg_extension_check_depend,a system-provided trigger function to enforce   reference integrity for any
pg_depend/pg_shdependmentions of the row - that relation MAY have another DELETE trigger that was allowed to be
createdby the restrictions on triggers below.
 

- The pg_extension_check_depend trigger function has two duties: a. to ereport(ERROR) and prevent deletion in some
circumstances   (for example, when the row to be deleted is mentioned on the    classid side of an 'e' dependency, and
theextension isn't being    dropped, or on the refclassid side of a normal dependency, and    the dependent object
isn'tbeing dropped), b. in all other cases, to allow the deletion, while also removing    associated
pg_depend/pg_shdependentries. That's why no non-system table is allowed to be mentioned in pg_depend or pg_shdepend
unlessit has a trigger that calls this function.
 

- CREATE TRIGGER refuses to create a trigger that calls pg_extension_check_depend UNLESS: - creating_extension is true,
AND- the trigger is being created on a table belonging to the current   extension, AND - no other DELETE trigger exists
onthe table, unless the next rule   would allow it.
 

- CREATE TRIGGER refuses to create any other DELETE trigger on a table that has a pg_extension_check_depend DELETE
trigger,UNLESS: - creating_extension is true, AND - the table and the trigger function both belong to the current
extension.


With that set of rules, extensions (and only extensions) are able to
invent and manage new kinds of dependency-managed objects, representing
them as rows in a table with appropriate triggers. When doDeletion, for
example, encounters a pg_depend record with a non-system classid, it is
simply treated as a deletion of the row oid=objectId from that relation,
invoking the delete trigger(s) normally.

DROP EXTENSION will have to rid pg_shdepend and pg_depend of all records
referring to tables in the extension (by silently removing the record of
the dependency, or by cascading deletion, as each case entails).

One remaining piece: the pg_depend and pg_shdepend logic both make use
of getObjectDescription, so they can produce useful messages like
"can't drop that because operator class baz depends on it".

Somehow, getObjectDescription needs a hook mechanism, so an extension that
adds a new kind of managed thing can return a description string for it
("can't drop that user because _PL/Java jar file foo_ depends on it" /
"deletion would cascade to _Jim Nasby metaprogramming artifact quux_").

So perhaps one final condition should be checked when adding any depend/
shdepend entry mentioning a non-system table row: getObjectDescription
must return a value for it, confirming it's been properly hooked.

Perhaps my last piece of brainstorming of 2015....

-Chap

On 12/31/15 3:49 PM, Chapman Flack wrote:
> On 12/23/15 15:02, Jim Nasby wrote:
>> >BTW, I've been pondering a very similar problem to this. I'm working on a
>> >metacoding framework, and it's inevitable that at some point it will want to
>> >know what objects it's created....
>> >
>> >I was planning on just making a best possible attempt and solving this in an
>> >extension via a combination of event triggers, reg* and other voodoo,
> In the voodoo department, I guess PL/Java could accompany each loading of
> a jar 'foo' with a 'CREATE TYPE sqlj.voodoo_foo AS ()' which is about as
> lightweight as it gets, and an extra regtype column in the jar_repository
> table could carry the Oid of that type.

Hmm, interesting idea.

> How well will pg_dump/restore handle that voodoo? I suppose they'll
> reliably recreate the types before loading the table with a regtype
> column, and by typing the column as regtype, the dump will refer to
> the type by name, and therefore work even if pg_dump is not given
> the --oids option?

Yes, assuming the type hasn't been dropped. Otherwise regtype spits out 
the OID that used to be assigned, which AFAICT is completely useless for 
a non-system object.

I've thought about creating a datatype that worked the same as the reg* 
types, but also implemented a "foreign key" back to the relevant catalog 
so things couldn't just vanish on you. (Since you obviously can't do an 
FK to a catalog this would have to be simulated with event triggers).

> On 12/21/15 12:46, Tom Lane wrote:
>> >(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.)
>
> Ok, how numerous would be the problems with this:
>
>
> - The classid and refclassid columns (in both pg_shdepend and pg_depend)
>    are currently Oid columns referencing pg_class.oid.  The catalog
>    definition would not preclude putting the oid of a non-system table
>    there.  The*documentation*  says it has to be the oid of a system
>    catalog, and surely there is code that currently would be surprised by
>    an entry that wasn't (for example, default: elog(ERROR, "unrecognized
>    object class..." in getObjectClass).
>    But even now, looking at recordDependencyOn or shdepAddDependency,
>    I don't see that the code would prevent such an entry being added.

Either I'm not understanding what you're saying here, or you're confused 
about those two columns.

Those columns are there to tell you what *kind* of objects are involved 
in the dependency. For example, a function will have two entries. 
classid will be 1255 (pg_proc) for both of them. refclassid will be 2612 
(pg_language) for one and 2615 (pg_namespace) for the other.

Oh, were you thinking of having a new classid that represented files on 
disk?
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

On 12/31/15 19:06, Jim Nasby wrote:
> Those columns are there to tell you what *kind* of objects are involved in
> the dependency. For example, a function will have two entries. classid will
> be 1255 (pg_proc) for both of them. refclassid will be 2612 (pg_language)
> for one and 2615 (pg_namespace) for the other.

Right, well, the classid for a function is 1255 because 1255 is the Oid
of a row in pg_class ... and that row happens to describe a relation
named pg_proc, which is the relation in which you find functions.

Now, if you go to that relation, and look for a row whose Oid is the
objid part of the address, that row is the specific function you're
looking for.

So, if I give you the object address (1255, 1397, 0), these are the two
steps you will use to learn what I'm talking about:


SELECT relname FROM pg_class WHERE oid = 1255;
---------pg_proc


SELECT * FROM pg_proc WHERE oid = 1397;
-----+-...-+---------+-...abs | ... | int4abs | ...



Ok, now what if I give you the object address (17001, 17270, 0) ?
Of course, these oids aren't predefined so they won't be the same
from one database to the next. But in the test database I'm logged
in to right now, you can follow the very same two steps:


SELECT relname FROM pg_class WHERE oid = 17001;  relname
----------------jar_repository


SELECT * FROM jar_repository WHERE oid = 17270;jarid | jarname  | jarowner |    jarmanifest    | ...
-------+----------+----------+-------------------+-...    2 | examples | chap     | Manifest-Versio...| ...


Nothing new has happened here. The algorithm is unchanged.
The object address (17001, 17270, 0) means "the PL/Java examples jar",
in exactly the same way that (1255, 1397, 0) means "the int4abs
function".

(I had to cheat a little and ALTER TABLE jar_repository SET WITH OIDS
because in stock PL/Java it doesn't have them, but that was easy enough.)

The only thing that stops me at present from passing
an ObjectAddress like (17001, 17270, 0) to recordDependencyOn(...)
is that the *documentation* says the classid can't be the Oid
of just *any* old row in pg_class, is has to be the oid of a row
in pg_class *that describes a system catalog*.

As far as the actual code, if I tried that right now I don't actually
think anything would stop me from recording the dependency.
Things would break when I tried to drop something though, because
getObjectClass() would be called on my dependency, not recognize that
Oid among the ones it knows about, and throw an error.

How about having a default case there, saying "hmm, jar_repository,
not a table I recognize, it must belong to some extension. Well, I'll
just go and delete its row with oid = 17270 anyway, and let the extension
that owns it handle the DELETE trigger if any, and we're good to go." ?

To a first approximation, it's as easy as that.  All the rest of my
earlier message was about proposing rules to enforce reasonable
restrictions so the dependency jazz isn't opened up to every old user
table with triggers calling arbitrary code to get all tangled up in
PostgreSQL's DROP internals, but it is opened up in a controlled way
to extensions that create new classes of things to be managed.

-Chap

On 12/31/15 16:49, Chapman Flack wrote:
> Ok, how numerous would be the problems with this:
> 
> - The classid and refclassid columns (in both pg_shdepend and pg_depend)
>   are currently Oid columns referencing pg_class.oid.  The catalog
>   definition would not preclude putting the oid of a non-system table
>   there.  The *documentation* says it has to be the oid of a system
>   catalog, and surely there is code that currently would be surprised by
>   an entry that wasn't (for example, default: elog(ERROR, "unrecognized
>   object class..." in getObjectClass).
>   But even now, looking at recordDependencyOn or shdepAddDependency,
>   I don't see that the code would prevent such an entry being added.
> 
> - It still would make no sense as a refclassid in pg_shdepend. All three
>   other cases (classid in pg_shdepend, classid or refclassid in pg_depend)
>   can make sense in a world of extension-managed objects.
> 
> - So, what would be needed to make those 3 cases supportable?  For starters,
>   how about a strict rule for *when* a non-system-catalog classid or
>   refclassid can be allowed into either of those tables:
> 
>   IF an ObjectAddress.classId IS NOT a system catalog
>     (currently detectable by getObjectClass(classId) throwing an error),
>   THEN:
>   - it MUST be the Oid of an existing (non-system) relation
>   - that relation MUST be WITH OIDS (so the ObjectAddress.objectId can
>     identify a row in it)
>     ... alternatively, must have an int unique key, and the objSubId can
>         be what identifies the row
>   - that relation MUST have a DELETE FOR EACH ROW trigger that calls
>     pg_extension_check_depend, a system-provided trigger function to enforce
>     reference integrity for any pg_depend/pg_shdepend mentions of the row
>   - that relation MAY have another DELETE trigger that was allowed to be
>     created by the restrictions on triggers below.
> 
> - The pg_extension_check_depend trigger function has two duties:
>   a. to ereport(ERROR) and prevent deletion in some circumstances
>      (for example, when the row to be deleted is mentioned on the
>      classid side of an 'e' dependency, and the extension isn't being
>      dropped, or on the refclassid side of a normal dependency, and
>      the dependent object isn't being dropped),
>   b. in all other cases, to allow the deletion, while also removing
>      associated pg_depend/pg_shdepend entries.
>   That's why no non-system table is allowed to be mentioned in pg_depend
>   or pg_shdepend unless it has a trigger that calls this function.
> 
> - CREATE TRIGGER refuses to create a trigger that calls
>   pg_extension_check_depend UNLESS:
>   - creating_extension is true, AND
>   - the trigger is being created on a table belonging to the current
>     extension, AND
>   - no other DELETE trigger exists on the table, unless the next rule
>     would allow it.
> 
> - CREATE TRIGGER refuses to create any other DELETE trigger on a table
>   that has a pg_extension_check_depend DELETE trigger, UNLESS:
>   - creating_extension is true, AND
>   - the table and the trigger function both belong to the current extension.
> 
> 
> With that set of rules, extensions (and only extensions) are able to
> invent and manage new kinds of dependency-managed objects, representing
> them as rows in a table with appropriate triggers. When doDeletion, for
> example, encounters a pg_depend record with a non-system classid, it is
> simply treated as a deletion of the row oid=objectId from that relation,
> invoking the delete trigger(s) normally.
> 
> DROP EXTENSION will have to rid pg_shdepend and pg_depend of all records
> referring to tables in the extension (by silently removing the record of
> the dependency, or by cascading deletion, as each case entails).
> 
> One remaining piece: the pg_depend and pg_shdepend logic both make use
> of getObjectDescription, so they can produce useful messages like
> "can't drop that because operator class baz depends on it".
> 
> Somehow, getObjectDescription needs a hook mechanism, so an extension that
> adds a new kind of managed thing can return a description string for it
> ("can't drop that user because _PL/Java jar file foo_ depends on it" /
> "deletion would cascade to _Jim Nasby metaprogramming artifact quux_").
> 
> So perhaps one final condition should be checked when adding any depend/
> shdepend entry mentioning a non-system table row: getObjectDescription
> must return a value for it, confirming it's been properly hooked.
> 
> Perhaps my last piece of brainstorming of 2015....

I have done no work on this in the ensuing three years, but also no one
has pointed out flaws in the idea.

This little thread bump records the possibility that "Remove WITH OIDS
support, change oid catalog column visibility" [1] might require (or not)
some adjustment to the above design.

-Chap


[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=578b229