PostgreSQL: execute query from script

Question

I'm installing PostgreSQL + POSTGIS on a CentOS 7 virtual machine using Vagrant and Virtual Box.

My Vagtantfile is the follow ...

Vagrant.configure("2") do |config|
  config.vm.box = "centos/7"
  config.vm.network "private_network", ip: "192.168.56.2"

  config.vm.provider "virtualbox" do |vb|
     vb.memory = "4096"
     vb.name = "Test"
   end

  config.vm.provision "shell", path: "./scripts/InstallPostgresqlPostgis.sh"
end

In ./scripts/InstallPostgresqlPostgis.sh there are all the commands to install PostgreSQL and, when run, PostgreSQL is installed and works.

To add POSTGIS at my PostgreSQL installation, in interactive way, I use this procedure

su postgres 
  ----->>>>>>> HERE I'VE TO PUT THE USER PASSWORD <<<<<<<-------
  psql
    -- Enable PostGIS (includes raster)
    CREATE EXTENSION postgis;
    -- Enable Topology
    CREATE EXTENSION postgis_topology;
    -- Enable PostGIS Advanced 3D
    -- and other geoprocessing algorithms
    -- sfcgal not available with all distributions
    CREATE EXTENSION postgis_sfcgal;
    -- fuzzy matching needed for Tiger
    CREATE EXTENSION fuzzystrmatch;
    -- rule based standardizer
    CREATE EXTENSION address_standardizer;
    -- example rule data set
    CREATE EXTENSION address_standardizer_data_us;
    -- Enable US Tiger Geocoder
    CREATE EXTENSION postgis_tiger_geocoder;
  \q

and all works.

I've to "translate" this procedure in my InstallPostgresqlPostgis.sh that I refer in my Vagrantfile and I've tried this

sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION postgis"
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION postgis_topology"
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION postgis_sfcgal"
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION fuzzystrmatch"
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION address_standardizer"
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION address_standardizer_data_us"
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION postgis_tiger_geocoder"

but the result is ...

default: could not change directory to "/home/vagrant": Permission denied
default: CREATE EXTENSION
default: could not change directory to "/home/vagrant": Permission denied
default: CREATE EXTENSION
default: could not change directory to "/home/vagrant": Permission denied
default: CREATE EXTENSION
default: could not change directory to "/home/vagrant": Permission denied
default: CREATE EXTENSION
default: could not change directory to "/home/vagrant": Permission denied
default: CREATE EXTENSION
default: could not change directory to "/home/vagrant": Permission denied
default: CREATE EXTENSION
default: could not change directory to "/home/vagrant": Permission denied
default: CREATE EXTENSION

Where am I doing wrong?

This sounds like the commands (sudo...) are executed from a current working directory (/home/vagrant) that is not accessible to postgres user. You might want to check... — rpy
– rpy, Commented Feb 24, 2019 at 20:05
Could be .... when I execute my shell script I'm in /home/vagrantdirectory that is the home directory for the user vagrant .... I've seen that the home directory for the user postgres is /var/lib/pgsql .... Any idea how may I change, in my shell script, to change directory or change user (also modifying my approach ...)? — Cesare
– Cesare, Commented Feb 24, 2019 at 20:20

rpy · Accepted Answer · 2019-02-25 07:16:48Z

1

Your problem is that you are executing the commands with a working directory that is not accessible to postgres user. In fact it is the home directory of the user executing the commands (vagrant).

There are three approaches for fixing this issue:

use --login (or -i for short) option to sudo
This will cause sudo to execute the commands with settings similar to a login shell.
Especially this will (try) changing to the target user's home directory as a working directory.
change the working directory within your script using cd ~postgres
This will result in all sudo commands will being executed there.
Allow user postgres access to the home directory of user vagrant
THIS IS DANGEROUS AND ABSOLUTELY NOT RECOMMENDED!!!
I just mention it for completeness. It might be an option iff you need such access regularly
and you have some fine grain access control at hand (e.g. ACL)
that allows ensuring postgres really is the only user being granted access. Even then you should think thrice! In most cases alternatives 1. or 2. are to be preferred.

answered Feb 25, 2019 at 7:16

rpy

4,0432 gold badges22 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hildy Over a year ago

The -i option worked great for me in my script. One such line, for example, that worked well was where I'm giving the postgres user a password when I provision a local dev vm like so sudo -u postgres -i psql -U postgres -d postgres -c "alter user postgres with password '<password>';"

Cesare · Accepted Answer · 2019-03-10 19:47:58Z

0

I've solved in this way ...

sudo su postgres
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION postgis"
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION postgis_topology"
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION postgis_sfcgal"
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION fuzzystrmatch"
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION address_standardizer"
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION address_standardizer_data_us"
sudo -u postgres -H -- psql -d postgres -c "CREATE EXTENSION postgis_tiger_geocoder"

answered Mar 10, 2019 at 19:47

Cesare

1,75710 gold badges36 silver badges77 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: execute query from script

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related