From: Erik Wienhold <ewie@ewie.name>
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Allowing users to create objects in version controlled schema
Date: Mon, 7 Nov 2022 18:28:55 +0100 (CET)
Message-ID: <903563259.260181.1667842135503@office.mailbox.org> (raw)
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
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: ewie@ewie.name, pgsql-admin@lists.postgresql.org
Subject: Re: Allowing users to create objects in version controlled schema
In-Reply-To: <903563259.260181.1667842135503@office.mailbox.org>
* 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