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@xxxxxxxxx> wrote:
> On 07/11/2022 21:18 CET Ron <ronljohnsonjr@xxxxxxxxx> 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.oliver@xxxxxxxxx
wells.oliver@xxxxxxxxx
