2

Context

I wish to add some data that are for the moment in ESRI shapefiles into a PostgreSQL database.

To do so, I am using the shp2pgsql (RELEASE: 2.4.3 (r16312)) tool from an Ubuntu server (18.04 bionic).

The command I usually use is: 

sudo -u postgres shp2pgsql -s 4326 my_shapefile.shp \ 
| psql -h <hostname> -d <databasename> -U <username>

as described here for example: How to import using shp2pgsql?
here: How to specify a password with shp2pgsql using CentOS?
or here: https://docs.qgis.org/3.4/en/docs/training_manual/spatial_databases/import_export.html?highlight=shp2pgsql
(but none of them answer the current question)

and where <username> is the default postgres, which is "derived" from the operating system postgres user created when installing PostgreSQL. So the connection mode in the pg_hba.conf is set to peer and I'd like to keep it so for the moment. That's also why, I don't want to define a password for the user postgres in PostgreSQL.

But by using the previous shp2pgsql command, I am always asked for the postgres password, which naturally doesn't exist.

Question:

How to avoid using a password prompt for the default user postgres while using shp2pgsql, hence keeping the advantage of the peer connection?

Updates

2020.02.03:

@Ian Turton's answer:

My first try is to switch directly to the user postgres as suggested: 

$ sudo su postgres

and then, from the postgres@hostmachine terminal: 

$ shp2pgsql -s 4326 my_shapefile.shp \
  | sudo -u postgres psql -h <hostname> -d <databasename> -U <username>

which in return gave me: 

postgres is not in the sudoers file.  This incident will be reported.

2nd try; I thought the sudo -u flag was doing the job of running the following command for the given user, and was, let's way somehow better than switching to that user explicitely or less naughty.
So I also tried to add (... ) | sudo -u postgres psql (...) after the pipe but the prompt keeps asking me for a password. And when I give one, whatever it is, it ends on; sql: FATAL: password authentication failed for user "postgres". I wonder why the difference with the first try?

3rd try, same as previous, but while having logged me as root -> I am still asked for a password for the postgres PostgreSQL user.

Improve this question
1
  • GDAL with ogr2ogr works perfectly too and not limited to shapefile format : ogr2ogr -f "PostgreSQL" PG:"host=$HOST user=$USER dbname=$DBNAME" ./my_shapefile.shp -nlt geometry -lco GEOMETRY_NAME=geom -lco FID=id -nln public.my_postgis_table -progress. Commented Feb 3, 2020 at 13:58

1 Answer 1

Reset to default
1

You need to be unix user postgres for peer connections to work so do su postgres and then run the shp2psql command in that shell. Or alternatively you could do:

shp2pgsql -s 4326 my_shapefile.shp \ 
 | sudo -u postgres psql -h <hostname> -d <databasename> -U <username>

But better practice would be to set $PGPASS to the password so you don't need to type it in.

Improve this answer
3
  • See edit of main post for an update. The postgres user of psql benefits the user login as sudo -u postgres to enter the psql terminal because of the peer mode. Actually I can access the psql terminal without any problem. So I do not have any password for that user. Should I set one to use $PGPASS then? Commented Feb 3, 2020 at 14:55
  • If you are postgres then you don't need the sudo Commented Feb 3, 2020 at 15:24
  • yes you would need a password to be set (good practice in itself) for the PGPASS option Commented Feb 3, 2020 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.