[postgis-users] geometry column permission denied using shp2pgsql

[Paul Ramsey]

Permission problems and encoding problems are quite separate, so let's  
examine the first problem first. Your first error is the permission  
problem. What happens when you log in as yourself with psql and try to  
insert a column into geometry_columns? Hopefully it should fail, since  
that's all the shp2pgsql-generated script is doing.

Run \d and see who owns that table. Probably it's owned by postgres  
and you don't have rights to it. Log in as postgres and do some GRANTS  
to provide yourself permissions and run from there.

The encoding issue is separate, and you seem to be on the right track  
with the -W switch, assuming that ISO-8859-8  is in fact the original  
encoding.

P

On Jan 9, 2008, at 1:48 AM, David Epstein wrote:

>    Hello,
>
>        I am the owner of the database but am getting a permission
>        denied on the
>        geometry column when using shp2pgsql. There seems to be a
>        character
>        encoding issue also. The data was likely created on a computer
>        running
>        windows in Hebrew (reads right to left) and I am running ubuntu
>        in
>        English. Any help would be appreciated. I've pasted the output
>        below.
>
>        thank you in advance,
>        -david
>
>
>        --> shp2pgsql -s 2039 -W ISO-8859-8 URBANI.shp urban_areas |
>        psql
>        spatialtest
>
>        Shapefile type: Polygon
>        Postgis type: MULTIPOLYGON[2]
>        SET
>        BEGIN
>        NOTICE:  CREATE TABLE will create implicit sequence
>        "urban_areas_gid_seq" for serial column "urban_areas.gid"
>        NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
>        "urban_areas_pkey" for table "urban_areas"
>        CREATE TABLE
>        ERROR:  permission denied for relation geometry_columns
>        CONTEXT:  SQL statement "DELETE FROM geometry_columns WHERE
>                        f_table_catalog = '' AND f_table_schema =
>        'public' AND
>        f_table_name = 'urban_areas' AND f_geometry_column =  
> 'the_geom'"
>        PL/pgSQL function "addgeometrycolumn" line 94 at execute
>        statement
>        SQL statement "SELECT AddGeometryColumn('', $1 , $2 , $3 , $4 ,
>        $5 ,
>        $6 )"
>        PL/pgSQL function "addgeometrycolumn" line 4 at SQL statement
>        ERROR:  current transaction is aborted, commands ignored until
>        end of
>        transaction block
>        ERROR:  current transaction is aborted, commands ignored until
>        end of
>        transaction block
>        WARNING:  nonstandard use of \' in a string literal
>        LINE 1: ...VALUES ('8.88287388491e+003','1.23130983450e
>        +006','בוענה נז
>        \...
>                                                                     ^
>        HINT:  Use '' to write quotes in strings, or use the escape
>        string
>        syntax (E'...').
>        ERROR:  current transaction is aborted, commands ignored until
>        end of
>        transaction block
>        ERROR:  current transaction is aborted, commands ignored until
>        end of
>        transaction block
>        ERROR:  current transaction is aborted, commands ignored until
>        end of
>        transaction block
>
>
>        --> sudo tail -n 50 /var/log/postgresql/postgresql-8.2-main.log
>        2008-01-09 10:35:25 IST HINT:  Use the escape string syntax for
>        escapes,
>        e.g., E'\r\n'.
>        2008-01-09 10:35:25 IST WARNING:  nonstandard use of escape  
> in a
>        string
>        literal at character 84
>        2008-01-09 10:35:25 IST HINT:  Use the escape string syntax for
>        escapes,
>        e.g., E'\r\n'.
>        2008-01-09 10:49:05 IST NOTICE:  CREATE TABLE will create
>        implicit
>        sequence "urban_areas_gid_seq" for serial column
>        "urban_areas.gid"
>        2008-01-09 10:49:05 IST NOTICE:  CREATE TABLE / PRIMARY KEY  
> will
>        create
>        implicit index "urban_areas_pkey" for table "urban_areas"
>        2008-01-09 10:49:05 IST ERROR:  permission denied for relation
>        geometry_columns
>        2008-01-09 10:49:05 IST CONTEXT:  SQL statement "DELETE FROM
>        geometry_columns WHERE
>                                f_table_catalog = '' AND f_table_schema
>        =
>        'public' AND f_table_name = 'urban_areas' AND f_geometry_column
>        =
>        'the_geom'"
>                PL/pgSQL function "addgeometrycolumn" line 94 at  
> execute
>        statement
>                SQL statement "SELECT AddGeometryColumn('', $1 , $2 ,
>        $3 , $4 ,
>        $5 , $6 )"
>                PL/pgSQL function "addgeometrycolumn" line 4 at SQL
>        statement
>        2008-01-09 10:49:05 IST STATEMENT:  SELECT
>         
> AddGeometryColumn('','urban_areas','the_geom','2039','MULTIPOLYGON', 
> 2);
>        2008-01-09 10:49:05 IST ERROR:  current transaction is aborted,
>        commands
>        ignored until end of transaction block
>        2008-01-09 10:49:05 IST STATEMENT:  INSERT INTO
>         
> "urban_areas 
> " ("shape_leng 
> ","shape_area","שם_הישוב","הערות","הסבר",the_geom)  
> VALUES ('9.06802995385e+003','1.01826276165e 
> +006','עילבון','ישוב',NULL,'SRID=2039;01060000000 [text  
> removed here] 1008BE226418C406025410C0D41F2C4D21C8CE22641');
>        2008-01-09 10:49:05 IST ERROR:  current transaction is aborted,
>        commands
>        ignored until end of transaction block
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users