[gdal-dev] Setting roles in PostGIS connection

[Pekka Sarkola]

Hi,

For records: "-doo "PRELUDE_STATEMENTS=SET ROLE admins" works with ogr2ogr
-append function. In my case, I need to update/overwrite several tables, so
I add small psql-query to remove existing tables from schema:

psql -t -c "SELECT
  'DROP TABLE IF EXISTS \"' || schemaname || '\". \"' || tablename || '\"
CASCADE;'
FROM
  pg_tables WHERE schemaname = 'my_schema';" | psql


Thanks for the advices,

Pekka

Pekka Sarkola
Gispo Oy
pekka.sarkola at gispo.fi   - GSM +358 40 725 2042
www.gispo.fi – www.paikkatieto.com


ma 5. heinäk. 2021 klo 7.55 Pekka Sarkola (pekka at gispo.fi) kirjoitti:

> Hi Even,
>
> That's correct: I use ogr2ogr. At least I tested with "-overwrite", but I
> will check how this works with "-update"
>
> Rgs,
>
> Pekka
>
> Pekka Sarkola
> Gispo Oy
> pekka.sarkola at gispo.fi   - GSM +358 40 725 2042
> www.gispo.fi – www.paikkatieto.com
>
>
> pe 2. heinäk. 2021 klo 17.26 Even Rouault (even.rouault at spatialys.com)
> kirjoitti:
>
>> Pekka,
>>
>> I suspect you use "ogr2ogr -f PostgreSQL PG:....."
>>
>> If you use rather "ogr2ogr -update PG:...." you should be able to use
>> -doo (not sure the SET ROLE will work properly in that context though)
>>
>> Even
>> Le 02/07/2021 à 08:52, Pekka Sarkola a écrit :
>>
>> Hi!
>>
>> We have a PostGIS database with login roles and group roles (like
>> "admins", "editors" and "viewers"). We have defined that only "admins" can
>> create new schemas and tables (among other privileges). My problem is that
>> I'd like to use ogr2ogr to bulk load some data to a PostGIS database using
>> ogr2ogr with a certain login role with "admins" role.
>>
>> It seems that it is not possible to define roles in PostgreSQL connection
>> parameters (my first try) or in PostgreSQL driver options.
>>
>> I tried to use PREDUDE_STATEMENTS like: "-doo "PRELUDE_STATEMENTS=SET
>> ROLE admins", but got warning "Warning 1: -doo ignored when creating the
>> output datasource."
>>
>> Any solutions or suggestions?
>>
>> There is also similar case in QGIS: Supporting "set role" when connecting
>> to a postgres database - https://github.com/qgis/QGIS/issues/42763
>>
>> Versions: GDAL 3.0.4, released 2020/01/28, PostgreSQL 12.7, PostGIS 3.1
>>
>> Rgs,
>>
>> Pekka
>>
>> Pekka Sarkola
>> Gispo Oy
>> pekka.sarkola at gispo.fi   - GSM +358 40 725 2042
>> www.gispo.fi – www.paikkatieto.com
>>
>> _______________________________________________
>> gdal-dev mailing listgdal-dev at lists.osgeo.orghttps://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>> -- http://www.spatialys.com
>> My software is free, but my time generally not.
>>
>> _______________________________________________
>> gdal-dev mailing list
>> gdal-dev at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20210706/e45c2107/attachment-0001.html>