Re: Allowing users to create objects in version controlled schema

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


> On 08/11/2022 05:42 CET Bo Victor Thomsen <bo.victor.thomsen@xxxxxxxxx> wrote:
> 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.

Thanks a lot.

I don't have my team mate's requirement yet so I can't say if private schemas
are an option.  If those tables are used as layers, I assume they are meant to
be shared with other users.  I think in that case it is also better to have them
manage privileges manually instead of automating that part as I've outlined in
my original post.

It still gives me headaches when I think about users who may create objects with
dependencies on the versioned part of the schema.  Those dependencies may prevent
migrations that alter dependent objects.  But this can indeed be isolated via
FDW with some overhead to keep the schemas in sync.


[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