From: Laurenz Albe <laurenz.albe@cybertec.at>
To: kaido vaikla <kaido.vaikla@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: pg_dumpall and owner of the extension
Date: Wed, 24 Jan 2024 15:13:24 +0100
Message-ID: <3419e4a28e16f2155fd97fe666b1a931ba7067a9.camel@cybertec.at> (raw)
In-Reply-To: <CA+427g_gE_Np=MLtO+kJYfpfKsTWADOcRbo64DoKTAze8ZU24Q@mail.gmail.com>
References: <CA+427g-YecPcvrHLJjoP92gDQy199qvvKCj_OvXhR3qmeu0zRQ@mail.gmail.com>
<17144af8b80466cf6c807d531de8410f1783ed90.camel@cybertec.at>
<CA+427g_gE_Np=MLtO+kJYfpfKsTWADOcRbo64DoKTAze8ZU24Q@mail.gmail.com>
On Tue, 2024-01-23 at 18:01 +0200, kaido vaikla wrote:
> Yes, i have a real problem. To keep things clear in database, my design is:
> - every application has own schema
> - every schema has owner who is not a user postgres
> - only schema owner can do DDL's on schema
>
> So if some application needs some extensions, then i give temporary suppersuser privilege to schema owner,
> if "create extension" needs it, extensions are installed "with schema" And revoke suppersuser privilege after extension inatall.
> I'm not sure, is it my design against postgres concept or not :(.
>
> But in same time, i expect that pg_dumpall will not change ownership or at least gives some error or something.
I think your expectation is founded, and the current behavior is surprising.
At first, I thought that would be a security problem in combination with
trusted extensions, but then the objects from a trusted extension always
belong to the bootstrap superuser.
So, all extension objects always belong to a superuser, and a change in
ownership to a different after a restore won't be a privilege escalation.
In practice, it doesn't matter much to which superuser extension objects
belong.
Your usage pattern sort of breaks that. I think that your practice of
temporarily elevating the prospective extension owner to superuser is
a questionable one. You end up having extension objects owned by a
non-superuser. That probably won't make much difference in most cases,
but it could matter for SECURITY DEFINER functions or views that use
restricted objects. And if the extension object owner changes after a
restore, the behavior could change.
I don't really see what you are expecting to gain from your design.
Instead of temporarily elevating the schema owner's privileges, why
don't you create the extension as superuser right away?
(As an aside: you need a superuser to turn sumebody into a superuser.
Don't tell me that your application is connecting as superuser...)
So while I think that the current behavior is less than ideal, I don't
see it as a massive problem that needs to be fixed. What is it that
stops working for you after the restore?
Perhaps some documentation for the surprising behavior might be in place.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, kaido.vaikla@gmail.com
Subject: Re: pg_dumpall and owner of the extension
In-Reply-To: <3419e4a28e16f2155fd97fe666b1a931ba7067a9.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox