pgsql2shp should not require temp table creation privs when dumping a query

[strk]

If you can't create a temporary table, then you can't get a shapefile out of a query using pgsql2shp. You can by using ogr2ogr.

[robe]

would make good goolge summer of code. Added to:

http://trac.osgeo.org/postgis/wiki/GoogleSummerCode2012

[strk]

I don't think this belongs to a micro release. It isn't a bug to use temporary tables.

[dfuhry]

A related ticket is #2644 which points out pgsql2shp from a streaming replica fails because CREATE TEMP TABLE statement fails for the read-only db.

[robe]

feel free to push this back to 2.4 if you get to it this week. but I'm moving it out of the way for now.

[robe]

Milestone renamed

[stefanpetrea]

Hi,

Please review this branch for this ticket.

The branch linked to below adds a new flag called -Z . When used, this flag will prevent the generation of ANALYZE statements. Without the -Z flag (default behaviour), the ANALYZE statements will be generated.

​https://github.com/wsdookadr/postgis/tree/2045-analyze-z-flag

The pull request is here

​https://github.com/postgis/postgis/pull/610

Best regards, Stefan

[stefanpetrea]

Steps to reproduce

1. Run make test once to have the needed postgis.sql and spatial_ref_sys.sql present in the expected locations
2. Have the following line present in pg_hba.conf to allow connections from the user test_2045

host all test_2045 127.0.0.1/32 md5

3. psql -U postgres -f minimal.sql (see the minimal.sql file attached)
4. Running this command

../loader/pgsql2shp -u test_2045 -P test -h localhost -f /tmp/t22 test_2045 "select * from c;"

will generate the following error:

  Initializing...
  Error executing user query: ERROR:  permission denied to create temporary tables in database "test_2045"

Note: This command runs fine because the last argument is a table name and not a query.

../loader/pgsql2shp -u test_2045 -P test -h localhost -f /tmp/t22 test_2045 c

Code-related notes

pgsql2shp has two modes of operation:

1. pgsql2shp -u <db_user> -P <db_pass> -h <db_host> -f /path/output <database> <table>
2. pgsql2shp -u <db_user> -P <db_pass> -h <db_host> -f /path/output <database> "<select_query>"

The behaviour mentioned in this ticket only happens for the 2nd case in the list above. This ​line of code is reached and from there on a statement like:

CREATE TEMPORARY TABLE <tmp_table> AS (<select_query>)

is generated, and that's why TEMP privileges are required for the db_user PostgreSQL user.

Questions

One way to solve this ticket would be to remove the TEMP table and replace it with the actual SQL query, and from there just use the regular ​libpq API to retrieve the results. Would this be acceptable ?

What other options are there for this ticket, and be the prefered approach?

Best regards, Stefan

[stefanpetrea]

minimal reproducible example (sql script to generate a test db, test user, and set the right kinds of privileges)

[strk]

The limit of privileges for creating temporary tables was resolved, on upgrade, by using temporary function instead, see #4975 -- could that work for this case as well ?

[robe]

I'm not sure anyone would be able to create functions that would be required to load data. Extensions is a special case since those are higher privileged even in DbaaS.