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@lists.osgeo.org' <gdal-dev@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@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev