[postgis-tickets] [PostGIS] #2045: pgsql2shp should not require temp table creation privs when dumping a query

[PostGIS]

#2045: pgsql2shp should not require temp table creation privs when dumping a query
----------------------------+-----------------------------
  Reporter:  strk           |      Owner:  pramsey
      Type:  enhancement    |     Status:  new
  Priority:  medium         |  Milestone:  PostGIS Fund Me
 Component:  loader/dumper  |    Version:  master
Resolution:                 |   Keywords:  gsoc
----------------------------+-----------------------------

Comment (by 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             all    0.0.0.0/0  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:

 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
 [https://github.com/postgis/postgis/blob/2a439cb5fad50bb50c62ed9b418656a8712dde29/loader
 /pgsql2shp-core.c#L1363 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
 [https://www.postgresql.org/docs/11/libpq-exec.html libpq API] to retrieve
 the results.

 What other options are there for this ticket, and what would be a good
 solution here?


 Best regards,
 Stefan

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/2045#comment:14>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.