Message-ID: <72494a1f44ffee61199cabd104a278d4ff30c4e1.camel@cybertec.at>
Subject: Re: Grant CREATE privilege on all schemas
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: ivanov17@riseup.net, Pgsql Novice <pgsql-novice@lists.postgresql.org>
Date: Tue, 12 Sep 2023 19:59:03 +0200
In-Reply-To: <42a7db124fa2f9685f01c0a80161549c@riseup.net>
References: <e7fdd3ef1f36f903aa3b13a628a88a61@riseup.net>
	 <CAKFQuwZMV2njhUjugmmiy_i_=S0XXAYUojzA8oqRgbYnR7z+qw@mail.gmail.com>
	 <42a7db124fa2f9685f01c0a80161549c@riseup.net>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/72494a1f44ffee61199cabd104a278d4ff30c4e1.camel%40cybertec.at>
Precedence: bulk

On Tue, 2023-09-12 at 17:38 +0000, ivanov17@riseup.net wrote:
> Thank you for your answer. Unfortunately, I'm not even a junior DBA, so
> I might be misunderstanding something. =D0=A1an I ask more questions?
>=20
> Typically, for each of my websites, I create one database, one database
> owner role, and its private schema. And I want to have a role that can
> to access each private schema to create tables and write data to provide
> database migrations.
>=20
> I can't know what schema I need to create tomorrow, so I think it's
> reasonable to grant privileges to the migration role in advance. But
> maybe I don't need predefined roles at all.
>=20
> Now I see that I might to have another problem because tables should be
> created by migration role, but after that they must be fully accessible
> to the database owner. I think I also need to grant full default
> privileges to the database/schema owners on all tables created by the
> migration role in their own schemas, right?
>=20
> Also, when creating databases/schemas, I need to give the migration role
> full default privileges to use private schemas, create tables in them,
> and write data. In this case, I no longer need the predefined roles.
>=20
> Does this sound like a good plan? Could you please correct me if there
> is a better way to do this?

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