Re: Allowing users to create objects in version controlled schema

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(dot)victor(dot)thomsen(at)gmail(dot)com>
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(dot)oliver(at)gmail(dot)com> 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
> >
> >
>
>
>