[gdal-dev] Setting roles in PostGIS connection

[Rahkonen Jukka (MML)]

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