Allowing users to create objects in version controlled schema

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


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.



[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux