public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: Pgsql Novice <[email protected]>
Subject: Re: Grant CREATE privilege on all schemas
Date: Wed, 13 Sep 2023 17:54:13 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<CAKFQuwZMV2njhUjugmmiy_i_=S0XXAYUojzA8oqRgbYnR7z+qw@mail.gmail.com>
	<[email protected]>
	<[email protected]>

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

view thread (7+ messages)

reply

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: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Grant CREATE privilege on all schemas
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox