public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: [email protected]
To: Pgsql Novice <[email protected]>
Subject: Re: Grant CREATE privilege on all schemas
Date: Tue, 12 Sep 2023 19:59:03 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<CAKFQuwZMV2njhUjugmmiy_i_=S0XXAYUojzA8oqRgbYnR7z+qw@mail.gmail.com>
	<[email protected]>

On Tue, 2023-09-12 at 17:38 +0000, [email protected] wrote:
> Thank you for your answer. Unfortunately, I'm not even a junior DBA, so
> I might be misunderstanding something. Сan I ask more questions?
> 
> 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.
> 
> 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.
> 
> 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?
> 
> 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.
> 
> 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

view thread (7+ messages)  latest in thread

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], [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