Opened 12 years ago

Last modified 3 years ago

#2045 new enhancement

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

Reported by: strk Owned by: stefanpetrea
Priority: medium Milestone: PostGIS Fund Me
Component: utils/loader-dumper Version: master
Keywords: gsoc Cc:

Description

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.

Attachments (1)

minimal.sql (775 bytes ) - added by stefanpetrea 4 years ago.
minimal reproducible example (sql script to generate a test db, test user, and set the right kinds of privileges)

Download all attachments as: .zip

Change History (18)

comment:1 by robe, 12 years ago

Keywords: gsoc added

would make good goolge summer of code. Added to:

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

comment:2 by robe, 12 years ago

Milestone: PostGIS 2.1.0PostGIS 2.0.4

comment:3 by strk, 12 years ago

Milestone: PostGIS 2.0.4PostGIS 2.2.0

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

comment:4 by dfuhry, 11 years ago

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.

comment:5 by robe, 9 years ago

Milestone: PostGIS 2.2.0PostGIS 2.3.0
Owner: changed from mcayland to robe
Type: defectenhancement

comment:6 by robe, 8 years ago

Milestone: PostGIS 2.3.0PostGIS 2.4.0

comment:7 by pramsey, 7 years ago

Owner: changed from robe to pramsey

comment:8 by robe, 7 years ago

Milestone: PostGIS 2.4.0PostGIS 2.5.0

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.

comment:9 by robe, 6 years ago

Milestone: PostGIS 2.5.0PostGIS next

comment:10 by robe, 6 years ago

Milestone: PostGIS nextPostGIS 3.0.0

Milestone renamed

comment:11 by pramsey, 5 years ago

Milestone: PostGIS 3.0.0PostGIS 3.1.0

comment:12 by pramsey, 4 years ago

Milestone: PostGIS 3.1.0PostGIS Fund Me

comment:13 by stefanpetrea, 4 years ago

Last edited 4 years ago by stefanpetrea (previous) (diff)

comment:14 by stefanpetrea, 4 years ago

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

  1. psql -U postgres -f minimal.sql (see the minimal.sql file attached)
  2. 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

Last edited 4 years ago by stefanpetrea (previous) (diff)

by stefanpetrea, 4 years ago

Attachment: minimal.sql added

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

comment:15 by stefanpetrea, 4 years ago

Owner: changed from pramsey to stefanpetrea

comment:16 by strk, 3 years ago

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 ?

comment:17 by robe, 3 years ago

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.

Note: See TracTickets for help on using tickets.