| 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 |
