Ok, regardless of the the use case, if an object is managed by a process, use the process for those objects. So, to your point, schema separation with no access to modify database or managed schemas would be the minimal abstraction I would consider (no superuser access). I would probably create another server and FDW after reading other replies.
In my opinion, it is impossible to speak to security, data integrity, or reliability of the system without sane permissions. At that point, you are one bad statement away from a headache.
Perhaps requesting the phone numbers for every superuser so you can add them to an oncall rotation / alerts would resolve the preference for access level.
Best regards,
Dan Smith
On Tue, Nov 8, 2022, 00:42 Bo Victor Thomsen <bo.victor.thomsen@xxxxxxxxx> wrote:
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
>
>
