subject:"\[gdal\-dev\] Setting roles in PostGIS connection"

Re: [gdal-dev] Setting roles in PostGIS connection

2021-07-06 Thread Pekka Sarkola

Hi,

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:

psql -t -c "SELECT
  'DROP TABLE IF EXISTS \"' || schemaname || '\". \"' || tablename || '\"
CASCADE;'
FROM
  pg_tables WHERE schemaname = 'my_schema';" | psql


Thanks for the advices,

Pekka

Pekka Sarkola
Gispo Oy
pekka.sark...@gispo.fi   - GSM +358 40 725 2042
www.gispo.fi – www.paikkatieto.com


ma 5. heinäk. 2021 klo 7.55 Pekka Sarkola (pe...@gispo.fi) kirjoitti:

> Hi Even,
>
> That's correct: I use ogr2ogr. At least I tested with "-overwrite", but I
> will check how this works with "-update"
>
> Rgs,
>
> Pekka
>
> Pekka Sarkola
> Gispo Oy
> pekka.sark...@gispo.fi   - GSM +358 40 725 2042
> www.gispo.fi – www.paikkatieto.com
>
>
> pe 2. heinäk. 2021 klo 17.26 Even Rouault (even.roua...@spatialys.com)
> kirjoitti:
>
>> Pekka,
>>
>> I suspect you use "ogr2ogr -f PostgreSQL PG:."
>>
>> 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)
>>
>> Even
>> Le 02/07/2021 à 08:52, Pekka Sarkola a écrit :
>>
>> 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
>>
>> Pekka Sarkola
>> Gispo Oy
>> pekka.sark...@gispo.fi   - GSM +358 40 725 2042
>> www.gispo.fi – www.paikkatieto.com
>>
>> ___
>> gdal-dev mailing 
>> listgdal-dev@lists.osgeo.orghttps://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>> -- http://www.spatialys.com
>> My software is free, but my time generally not.
>>
>> ___
>> gdal-dev mailing list
>> gdal-dev@lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] Setting roles in PostGIS connection

2021-07-06 Thread Pekka Sarkola

Hi!

Just for records: PGDump is valid solution for this:
- First I created SQL file with ogr2ogr and "-f PGDump"-option
- Second, I uploaded result SQL file with psql: psql -t -q -c "SET ROLE
admins;" -f ./result.sql

So this is work-around to manage group roles.

Rgs,

Pekka

Pekka Sarkola
Gispo Oy
pekka.sark...@gispo.fi   - GSM +358 40 725 2042
www.gispo.fi – www.paikkatieto.com


ma 5. heinäk. 2021 klo 7.53 Pekka Sarkola (pe...@gispo.fi) kirjoitti:

> 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.sark...@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.rahko...@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@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
>>
>
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] Setting roles in PostGIS connection

2021-07-04 Thread Pekka Sarkola

Hi Even,

That's correct: I use ogr2ogr. At least I tested with "-overwrite", but I
will check how this works with "-update"

Rgs,

Pekka

Pekka Sarkola
Gispo Oy
pekka.sark...@gispo.fi   - GSM +358 40 725 2042
www.gispo.fi – www.paikkatieto.com


pe 2. heinäk. 2021 klo 17.26 Even Rouault (even.roua...@spatialys.com)
kirjoitti:

> Pekka,
>
> I suspect you use "ogr2ogr -f PostgreSQL PG:."
>
> 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)
>
> Even
> Le 02/07/2021 à 08:52, Pekka Sarkola a écrit :
>
> 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
>
> Pekka Sarkola
> Gispo Oy
> pekka.sark...@gispo.fi   - GSM +358 40 725 2042
> www.gispo.fi – www.paikkatieto.com
>
> ___
> gdal-dev mailing 
> listgdal-dev@lists.osgeo.orghttps://lists.osgeo.org/mailman/listinfo/gdal-dev
>
> -- http://www.spatialys.com
> My software is free, but my time generally not.
>
> ___
> gdal-dev mailing list
> gdal-dev@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] Setting roles in PostGIS connection

2021-07-04 Thread 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.sark...@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.rahko...@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@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
>
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] Setting roles in PostGIS connection

2021-07-02 Thread Even Rouault


Pekka,

I suspect you use "ogr2ogr -f PostgreSQL PG:."

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)


Even

Le 02/07/2021 à 08:52, Pekka Sarkola a écrit :

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

Pekka Sarkola
Gispo Oy
pekka.sark...@gispo.fi - GSM +358 
40 725 2042
www.gispo.fi – www.paikkatieto.com 




___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev


--
http://www.spatialys.com
My software is free, but my time generally not.

___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] Setting roles in PostGIS connection

2021-07-02 Thread 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@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

Re: [gdal-dev] Setting roles in PostGIS connection

2021-07-02 Thread 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
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

[gdal-dev] Setting roles in PostGIS connection

2021-07-02 Thread Pekka Sarkola

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

Pekka Sarkola
Gispo Oy
pekka.sark...@gispo.fi   - GSM +358 40 725 2042
www.gispo.fi – www.paikkatieto.com
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev

Re: [gdal-dev] Setting roles in PostGIS connection

Re: [gdal-dev] Setting roles in PostGIS connection

Re: [gdal-dev] Setting roles in PostGIS connection

Re: [gdal-dev] Setting roles in PostGIS connection

Re: [gdal-dev] Setting roles in PostGIS connection

Re: [gdal-dev] Setting roles in PostGIS connection

Re: [gdal-dev] Setting roles in PostGIS connection

[gdal-dev] Setting roles in PostGIS connection

8 matches

Site Navigation

Mail list logo

Footer information