[gdal-dev] Setting roles in PostGIS connection

[Rahkonen Jukka (MML)]

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