[gdal-dev] Setting roles in PostGIS connection

[Pekka Sarkola]

Hi!

Just for records: PGDump is valid solution for this:
- First I created SQL file with ogr2ogr and "-f PGDump"-option
- Second, I uploaded result SQL file with psql: psql -t -q -c "SET ROLE
admins;" -f ./result.sql

So this is work-around to manage group roles.

Rgs,

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.53 Pekka Sarkola (pekka at gispo.fi) kirjoitti:

> Hi!
>
> PGDump: that might be the solution, I need to check. Thank you!
>
> Yes, libpg-connection doesn't have role parameters. Maybe it should? I
> don't know how, but you can define role settings in pgAdmin connections
>
> 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 15.51 Rahkonen Jukka (MML) (
> jukka.rahkonen at maanmittauslaitos.fi) kirjoitti:
>
>> Hi again,
>>
>> GDAL is using the standand libpg-connect
>> https://www.postgresql.org/docs/12/libpq-connect.html and that does not
>> have support for defining roles during connect.
>>
>> Do I understand right that your user "Joe" does not have direct CREATEDB
>> privileges but gets them through the admins role? And because CREATEDB has
>> a special handling
>> https://www.postgresql.org/docs/current/role-membership.html there is no
>> other way to let Joe to create db that through SET ROLE ADMINS.
>>
>> Making GDAL to support
>> "-doo "PRELUDE_STATEMENTS=SET ROLE admins"
>> feels like a good idea but I have no idea about how difficult it would be
>> to implement.
>>
>> -Jukka Rahkonen-
>>
>>
>>
>> -----Alkuperäinen viesti-----
>> Lähettäjä: Rahkonen Jukka (MML)
>> Lähetetty: perjantai 2. heinäkuuta 2021 14.40
>> Vastaanottaja: 'gdal-dev at lists.osgeo.org' <gdal-dev at lists.osgeo.org>
>> Aihe: Re: Setting roles in PostGIS connection
>>
>> Hi,
>>
>> I wonder if writing the output into pgdump
>> https://gdal.org/drivers/vector/pgdump.html and editing the SQL a bit
>> could be used as a workaround.
>>
>> -Jukka Rahkonen-
>>
>> Pekka Sarkola wrote:
>>
>> > 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
>> _______________________________________________
>> 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/a5a9df93/attachment.html>