From: Tom Lane <tgl@sss.pgh.pa.us>
To: kaido vaikla <kaido.vaikla@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-admin@postgresql.org
Subject: Re: pg_dumpall and owner of the extension
Date: Tue, 23 Jan 2024 11:30:37 -0500
Message-ID: <4048426.1706027437@sss.pgh.pa.us> (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>
kaido vaikla <kaido.vaikla@gmail.com> writes:
> 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 :(.
Well, it's certainly creating a problem for pg_dump: the alleged owner
of the extension doesn't have enough privilege to install it. The
easiest way to make pg_dump support this would be to have it issue
something like
SET ROLE extension_owner;
CREATE EXTENSION foo;
RESET ROLE;
but that would fail for you.
regards, tom lane
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: tgl@sss.pgh.pa.us, kaido.vaikla@gmail.com, laurenz.albe@cybertec.at
Subject: Re: pg_dumpall and owner of the extension
In-Reply-To: <4048426.1706027437@sss.pgh.pa.us>
* 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