Re: Allowing users to create objects in version controlled schema

Feels like more of a process question and less of a technical postgres
question. In our world, each DB user has their own schema they have full
access to, and can grant privileges as required. All schema
definitions tables/views/etc are versioned using git in a structured
repository of flat sql files. It works well. Table defs etc get code
reviewed and deployed by more DB-minded engineers, and people are cognizant
of writing optimized SQL. Maybe I misunderstand your situation, though.
postgres itself offers no "version control"

On Mon, Nov 7, 2022 at 5:10 PM Erik Wienhold <ewie(at)ewie(dot)name> wrote:

> > On 07/11/2022 21:18 CET Ron <ronljohnsonjr(at)gmail(dot)com> wrote:
> >
> > On 11/7/22 11:28, Erik Wienhold wrote:
> > > 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.
> >
> > Why does a developer(?) need to create his own tables in a production(?)
> > database?
>
> Not developers as in "software developer". They work in QGIS to modify
> data,
> create maps/documents. Also pgAdmin or psql to export CSV. For that
> purpose
> they may also write queries. On the production database of course.
>
> There's also a web application on top where I have more control but I
> still have
> to deal with the database schema.
>
> As far as I can tell, own tables are "necessary" to prepare geometric data
> for
> layouts. Beats me why those tables have to be created that way and where
> the
> data is coming from. Or why it has to be a table and not a view.
>
> Maybe it's the mindset and habit. The team started before I joined.
> Nobody had
> any experience in software development much less database development. It
> all
> started with databases where everybody was superuser. A dumping ground
> for CSV
> imports as I like to call it. I still see a lot of mistakes[1] being made.
> That's why I am pushing for version control and proper role management.
>
> [1] https://wiki.postgresql.org/wiki/Don%27t_Do_This
>
> --
> Erik
>
>
>

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>