Re: tracking owner of extension-managed objects

Chapman Flack <chap(at)anastigmatix(dot)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