Re: ALTER OBJECT any_name SET SCHEMA name

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Are you proposing that an extension object is schema qualified?
>
> Dunno, I'm just asking the question. If it isn't, why not?

Because extension are much like languages for stored procedure, on the
utility side rather than on the query side. The only queries that uses
language directly are utility statements, yet functions will depend on
them and live in some schema.

That's how I see extensions too, a new utility. Nothing I expect to be
visible in queries. They don't need schema, they are database globals.
The objects that are depending on the extension may or may not live in a
schema. A single extension script could create more than one schema and
have objects spread there.

So either we restrict an extension's to live in a single schema and we
have to forbid changing the schema of the objects in there on their own
(DEPENDENCY_INTERNAL should help), or we add a very simple check at
ALTER EXTENSION ... SET SCHEMA time to error out when the extension
depends on more than one schema. I'd do the later, obviously.

> Here's another question: if an extension's objects live (mostly or
> entirely) in schema X, what happens if the possibly-unprivileged owner
> of schema X decides to drop it? If the extension itself is considered
> to live within the schema, then "the whole extension goes away" seems
> like a natural answer. If not, you've got some problems.

Currently, creating an extension is superuser only. So the owner of
those objects is a superuser. My understanding is that the drop schema
will then fail without any more code.

>> Would we lower creating extension privileges to database owners, too,
>> rather than only superusers?
>
> That seems like an orthogonal question. I can see people wanting both
> behaviors though. Maybe an extension's config file should specify the
> privs needed to install it?

Orthogonal indeed, but it popped in my mind reading the previous mail,
and reading your previous question I guess you see why :)

I'm not for offering extension's author to control this behavior. As the
extension will more often than not come with a shared object lib, and as
the goal is to install SQL objects that will NOT be part of pg_dump
output, my feeling is that superuser only makes most sense.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support