From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Erik Wienhold <ewie@ewie.name>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Allowing users to create objects in version controlled schema
Date: Mon, 7 Nov 2022 14:42:29 -0500
Message-ID: <a4317435-63d9-e1a3-02fa-99f35924531f@sqlexec.com> (raw)
In-Reply-To: <903563259.260181.1667842135503@office.mailbox.org>
References: <903563259.260181.1667842135503@office.mailbox.org>
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 <mailto:michaelvitale@sqlexec.com>
703-600-9343
Attachments:
[image/jpeg] pgadvanced3.jpg (20.6K, ../a4317435-63d9-e1a3-02fa-99f35924531f@sqlexec.com/3-pgadvanced3.jpg)
download | view image
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: MichaelDBA@sqlexec.com, ewie@ewie.name, pgsql-admin@lists.postgresql.org
Subject: Re: Allowing users to create objects in version controlled schema
In-Reply-To: <a4317435-63d9-e1a3-02fa-99f35924531f@sqlexec.com>
* 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