From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Allowing users to create objects in version controlled schema
Date: Mon, 7 Nov 2022 14:18:24 -0600
Message-ID: <4240dcf8-2226-15dc-3a0d-1fd76f8c38bc@gmail.com> (raw)
In-Reply-To: <903563259.260181.1667842135503@office.mailbox.org>
References: <903563259.260181.1667842135503@office.mailbox.org>
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.
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: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Allowing users to create objects in version controlled schema
In-Reply-To: <4240dcf8-2226-15dc-3a0d-1fd76f8c38bc@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox