<div dir="ltr">Hi,<div><br></div><div>For records: "-doo "PRELUDE_STATEMENTS=SET ROLE admins" works with ogr2ogr -append function. In my case, I need to update/overwrite several tables, so I add small psql-query to remove existing tables from schema:</div><div><br></div><blockquote style="margin:0 0 0 40px;border:none;padding:0px"><div>psql -t -c "SELECT </div><div> 'DROP TABLE IF EXISTS \"' || schemaname || '\". \"' || tablename || '\" CASCADE;' </div><div>FROM</div><div> pg_tables WHERE schemaname = 'my_schema';" | psql </div></blockquote><div><br></div><div>Thanks for the advices,</div><div><br></div><div>Pekka</div><div><div><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div dir="ltr"><div dir="ltr"><p><span style="font-size:12.8000001907349px">Pekka Sarkola<br></span><span style="font-size:12.8000001907349px">Gispo Oy<br></span><a href="mailto:pekka.sarkola@gispo.fi" style="font-size:12.8000001907349px" target="_blank">pekka.sarkola@gispo.fi</a><span style="font-size:12.8000001907349px"> -
GSM +358 40 725 2042<br></span><a href="http://www.gispo.fi/" style="font-size:12.8000001907349px" target="_blank">www.gispo.fi</a><span style="font-size:12.8000001907349px"> – </span><a href="http://www.paikkatieto.com/" style="font-size:12.8000001907349px" target="_blank">www.paikkatieto.com</a></p></div></div></div></div></div></div></div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">ma 5. heinäk. 2021 klo 7.55 Pekka Sarkola (<a href="mailto:pekka@gispo.fi">pekka@gispo.fi</a>) kirjoitti:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi Even,<div><br></div><div>That's correct: I use ogr2ogr. At least I tested with "-overwrite", but I will check how this works with "-update"</div><div><br></div><div>Rgs,</div><div><br></div><div>Pekka</div><div><div><div dir="ltr"><div dir="ltr"><div><div dir="ltr"><div dir="ltr"><div dir="ltr"><p><span style="font-size:12.8px">Pekka Sarkola<br></span><span style="font-size:12.8px">Gispo Oy<br></span><a href="mailto:pekka.sarkola@gispo.fi" style="font-size:12.8px" target="_blank">pekka.sarkola@gispo.fi</a><span style="font-size:12.8px"> -
GSM +358 40 725 2042<br></span><a href="http://www.gispo.fi/" style="font-size:12.8px" target="_blank">www.gispo.fi</a><span style="font-size:12.8px"> – </span><a href="http://www.paikkatieto.com/" style="font-size:12.8px" target="_blank">www.paikkatieto.com</a></p></div></div></div></div></div></div></div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">pe 2. heinäk. 2021 klo 17.26 Even Rouault (<a href="mailto:even.rouault@spatialys.com" target="_blank">even.rouault@spatialys.com</a>) kirjoitti:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<p>Pekka,</p>
<p>I suspect you use "ogr2ogr -f PostgreSQL PG:....."</p>
<p>If you use rather "ogr2ogr -update PG:...." you should be able to
use -doo (not sure the SET ROLE will work properly in that context
though)<br>
</p>
<p>Even<br>
</p>
<div>Le 02/07/2021 à 08:52, Pekka Sarkola a
écrit :<br>
</div>
<blockquote type="cite">
<div dir="ltr">Hi!
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>It seems that it is not possible to define roles in
PostgreSQL connection parameters (my first try) or in
PostgreSQL driver options.</div>
<div> </div>
<div>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."</div>
<div><br>
</div>
<div>Any solutions or suggestions?</div>
<div><br>
There is also similar case in QGIS: Supporting "set role" when
connecting to a postgres database - <a href="https://github.com/qgis/QGIS/issues/42763" target="_blank">https://github.com/qgis/QGIS/issues/42763</a></div>
<div><br>
</div>
<div>Versions: GDAL 3.0.4, released 2020/01/28, PostgreSQL 12.7,
PostGIS 3.1</div>
<div><br>
</div>
<div>Rgs,</div>
<div><br>
</div>
<div>Pekka</div>
<div>
<div>
<div dir="ltr">
<div dir="ltr">
<div>
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<p><span>Pekka Sarkola<br>
</span><span>Gispo Oy<br>
</span><a href="mailto:pekka.sarkola@gispo.fi" target="_blank">pekka.sarkola@gispo.fi</a><span>
-
GSM +358 40 725 2042<br>
</span><a href="http://www.gispo.fi/" target="_blank">www.gispo.fi</a><span>
– </span><a href="http://www.paikkatieto.com/" target="_blank">www.paikkatieto.com</a></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
<fieldset></fieldset>
<pre>_______________________________________________
gdal-dev mailing list
<a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a>
<a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a>
</pre>
</blockquote>
<pre cols="72">--
<a href="http://www.spatialys.com" target="_blank">http://www.spatialys.com</a>
My software is free, but my time generally not.</pre>
</div>
_______________________________________________<br>
gdal-dev mailing list<br>
<a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
</blockquote></div>
</blockquote></div>