Message-ID: <4240dcf8-2226-15dc-3a0d-1fd76f8c38bc@gmail.com>
Date: Mon, 7 Nov 2022 14:18:24 -0600
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.2.2
Subject: Re: Allowing users to create objects in version controlled schema
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: <903563259.260181.1667842135503@office.mailbox.org>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: <903563259.260181.1667842135503@office.mailbox.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/4240dcf8-2226-15dc-3a0d-1fd76f8c38bc%40gmail.com>
Precedence: bulk

Why does a developer(?) need to create his own tables in a production(?) 
database?

On 11/7/22 11:28, Erik Wienhold wrote:
> I want to get some feedback on the idea of allowing users to create objects in
> a database whose schema is already managed with version controlled migrations.
>
> One of my team mates is displeased with our setup because he cannot create his
> own tables without bypassing version control.  This is a bad idea IMO even if
> it's technically possible to give users a reserved shared namespace that is
> never touched by versioned migrations.
>
> The following issues come to my mind.  Issues 1 and 2 are solvable while issues
> 3 and 4 are just sources of unnecessary frustration on my part.
>
> 1. There are multiple users each with a dedicated database user.  Consequently,
>     table owners will vary but those tables should be accessible to any user.
>     The only solution I can find (besides giving superuser privileges, yikes!)
>     is to use an event trigger on ddl_command_end to handle CREATE TABLE and
>     change ownership to a group.
>
>     Is there something like CREATE SCHEMA AUTHORIZATION which applies to objects
>     created afterwards with separate DDL?  Or just leave the owner as it is and
>     resort to DEFAULT PRIVILEGES instead?
>
> 2. Users must not create views and procedures that depend on objects managed by
>     versioned migrations.  Otherwise migrations may fail or break procedures
>     unexpectedly due to untracked dependencies.  Event triggers can prevent that
>     as documented for table rewrites[1].
>
> 3. Reinventing the wheel and data duplication if anyone can haphazardly create
>     new objects.  It is also difficult to enforce best practices in that case.
>
> 4. Harder to reproduce bugs if the complete database schema cannot be recreated
>     from version control.
>
> My background is in web applications where you have a nice separation of
> database layer and application layer.  In our case, users work directly with
> a PostGIS-enabled database via QGIS and pgAdmin, hence the "need" (or wish) to
> create own tables because it looks like a quick and easy solution to them.
>
> I still have to accustom to this setup where people directly access the database
> instead of having a layer of abstraction in top.
>
> Has anybody experience with such a setup?  More arguments against it are
> appreciated.  Solutions are also welcome.
>
> [1] https://www.postgresql.org/docs/15/event-trigger-table-rewrite-example.html
>
> --
> Erik
>
>

-- 
Angular momentum makes the world go 'round.