Re: Allowing users to create objects in version controlled schema

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Eric -

I am both (PostgreSQL) database and QGIS developer and occasionally a DB admin.  From that point of view:

There is a ton of use cases, where having a private schema for each QGIS user makes sense. For example, GIS analysis will often create datasets with limited lifetime, i.e being part of a process. These datasets has to be stored somewhere. If it's not in a private schema, you'll have them splattered out over your entire filesystems in different file-based GIS formats, including some on the users local hard drive.

Further, having even temporary data stored in your database gives the user opportunity to off-load some heavy-duty GIS analysis to the database using the PostGIS extension. Think of your database as a "file system", a "scratch pad".

This kind of work is fundamentally different from a traditional 3-tier setup where your normal user probably is not even aware of the underlying  data structure. And structure changes can be "hidden" in the business logic layer.

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,

This is a good idea. When some kind of analysis workflow is worked out / stabilised , the resulting tables/views etc. can be quality-checked and transferred to the "proper" database.

You could establish a separate database (including -server ?) and let QGIS users access the central database using Foreign Data Wrappers.

Med venlig hilsen / Best regards

Bo Victor Thomsen


Den 08-11-2022 kl. 03:29 skrev Erik Wienhold:
On 08/11/2022 02:19 CET Wells Oliver <wells.oliver@xxxxxxxxx> wrote:

Feels like more of a process question and less of a technical postgres
question.
You're right.  But I'm wondering what's possible on the technical front.

In our world, each DB user has their own schema they have full access to, and
can grant privileges as required.
Right, the standard approach with search_path = "$user".

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,
That's what I meant with version controlled migrations.  And I actually don't
want users to bypass that process to create and reference their own database
structures when those structures should rather be part of the common application
schema.

and people are cognizant of writing optimized SQL.
That's where I have my doubts when it comes to inexperienced "devs" and no
guiding code reviews.

--
Erik







[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux