MIME-Version: 1.0
Date: Wed, 13 Sep 2023 17:54:13 +0000
From: ivanov17@riseup.net
To: Pgsql Novice <pgsql-novice@lists.postgresql.org>
Subject: Re: Grant CREATE privilege on all schemas
In-Reply-To: <72494a1f44ffee61199cabd104a278d4ff30c4e1.camel@cybertec.at>
References: <e7fdd3ef1f36f903aa3b13a628a88a61@riseup.net>
 <CAKFQuwZMV2njhUjugmmiy_i_=S0XXAYUojzA8oqRgbYnR7z+qw@mail.gmail.com>
 <42a7db124fa2f9685f01c0a80161549c@riseup.net>
 <72494a1f44ffee61199cabd104a278d4ff30c4e1.camel@cybertec.at>
Message-ID: <b95d2638fa6b035dd10a4fbcedb8ee1c@riseup.net>
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/b95d2638fa6b035dd10a4fbcedb8ee1c%40riseup.net>
Precedence: bulk

On 2023-09-12 17:59, Laurenz Albe wrote:

> The owner of the tables must be the user that creates them, that is your
> migration role.
> 
> If the database and the schemas are owned by a different user, that does
> not matter, as long as the migration user has CREATE on all schemas.
> 
> You could use ALTER DEFAULT PRIVILEGES to make sure that every schema
> created by the database owner has that required permission by default.
> 
> While that should work fine, I deem it more complicated than necessary.
> I would opt for the migration user being the same as the database owner.
> 
> Yours,
> Laurenz Albe

Thank you. I use different Ansible roles to configure CI and web
servers, so it's easier for me to create different database roles for
different purposes.

But you're right: this results in the need to assign many additional
privileges because objects in schemas are not owned by schema owners. It
works, but doesn't look very good.

I think I have found the final solution, and it even looks elegant.

    GRANT web1 TO migration;
    GRANT web2 TO migration;
    ALTER ROLE migration IN DATABASE web1 SET role TO web1;
    ALTER ROLE migration IN DATABASE web2 SET role TO web2;

Now when I connect to the database as the migration role, all operations
are performed by the database owner role, so all created objects in the
schema owned by the database owner. And no confusion with privileges.

-- 
With appreciation,
Ivanov