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)
<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.
How to avoid using a password prompt for the default user
postgres while using
shp2pgsql, hence keeping the advantage of the
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
$ 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.