Re: Allowing users to create objects in version controlled schema

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.