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)
Change History (18)
comment:1 by , 12 years ago
Keywords: | gsoc added |
---|
comment:2 by , 12 years ago
Milestone: | PostGIS 2.1.0 → PostGIS 2.0.4 |
---|
comment:3 by , 12 years ago
Milestone: | PostGIS 2.0.4 → PostGIS 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 , 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 , 9 years ago
Milestone: | PostGIS 2.2.0 → PostGIS 2.3.0 |
---|---|
Owner: | changed from | to
Type: | defect → enhancement |
comment:6 by , 8 years ago
Milestone: | PostGIS 2.3.0 → PostGIS 2.4.0 |
---|
comment:7 by , 7 years ago
Owner: | changed from | to
---|
comment:8 by , 7 years ago
Milestone: | PostGIS 2.4.0 → PostGIS 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 , 6 years ago
Milestone: | PostGIS 2.5.0 → PostGIS next |
---|
comment:11 by , 5 years ago
Milestone: | PostGIS 3.0.0 → PostGIS 3.1.0 |
---|
comment:12 by , 4 years ago
Milestone: | PostGIS 3.1.0 → PostGIS Fund Me |
---|
comment:14 by , 3 years ago
Steps to reproduce
- Run
make test
once to have the neededpostgis.sql
andspatial_ref_sys.sql
present in the expected locations - 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
psql -U postgres -f minimal.sql
(see the minimal.sql file attached)- 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:
pgsql2shp -u <db_user> -P <db_pass> -h <db_host> -f /path/output <database> <table>
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
by , 3 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 , 3 years ago
Owner: | changed from | to
---|
comment:16 by , 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 , 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.
would make good goolge summer of code. Added to:
http://trac.osgeo.org/postgis/wiki/GoogleSummerCode2012