Re: Allowing users to create objects in version controlled schema

There is a CREATE ON SCHEMA myschema TO whomever privilege.
But ALTERS/DROPS require the owner role to be granted to such user.



Erik Wienhold wrote on 11/7/2022 12:28 PM:

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

Regards,

Michael Vitale

Michaeldba@sqlexec.com

703-600-9343