[gdal-dev] Setting roles in PostGIS connection

[Pekka Sarkola]

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/20210705/d358f012/attachment.html>