Date: Tue, 8 Nov 2022 03:29:51 +0100 (CET)
From: Erik Wienhold <ewie@ewie.name>
To: Wells Oliver <wells.oliver@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Message-ID: <1103549459.284197.1667874591587@office.mailbox.org>
In-Reply-To: 
 <CAOC+FBXaqH=8MwLpbBMk3A27QvQm4JY1tX35E+xm_+P-LyMjkg@mail.gmail.com>
References: <903563259.260181.1667842135503@office.mailbox.org>
 <4240dcf8-2226-15dc-3a0d-1fd76f8c38bc@gmail.com>
 <1581955070.282646.1667869831483@office.mailbox.org>
 <CAOC+FBXaqH=8MwLpbBMk3A27QvQm4JY1tX35E+xm_+P-LyMjkg@mail.gmail.com>
Subject: Re: Allowing users to create objects in version controlled schema
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Importance: Normal
Archived-At: 
 <https://www.postgresql.org/message-id/1103549459.284197.1667874591587%40office.mailbox.org>
Precedence: bulk

> On 08/11/2022 02:19 CET Wells Oliver <wells.oliver@gmail.com> wrote:
>
> Feels like more of a process question and less of a technical postgres
> question.

You're right.  But I'm wondering what's possible on the technical front.

> In our world, each DB user has their own schema they have full access to, and
> can grant privileges as required.

Right, the standard approach with search_path = "$user".

> All schema definitions tables/views/etc are versioned using git in a structured
> repository of flat sql files. It works well. Table defs etc get code reviewed
> and deployed by more DB-minded engineers,

That's what I meant with version controlled migrations.  And I actually don't
want users to bypass that process to create and reference their own database
structures when those structures should rather be part of the common application
schema.

> and people are cognizant of writing optimized SQL.

That's where I have my doubts when it comes to inexperienced "devs" and no
guiding code reviews.

--
Erik