Postgresql – script breaks the ability to run psql

pg-hba.confpostgresqlUbuntu

I've successfully installed postgres on my Ubuntu machine. I am running a script for a website I am working on, here is the relevant code:

apt-get -y install postgresql libpq-dev
su postgres <<EOF
    createuser app
    createdb ultimaterehab
EOF
PG_HBA=$(su postgres -c "psql -t -P format=unaligned -c 'SHOW hba_file'")

When executed this script gives the error:

createuser: could not connect to database postgres: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "postgres", SSL off
createdb: could not connect to database template1: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "template1", SSL off

and when I try running psql I get a similar error:

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "robin", database "robin", SSL off

I have tried editing the pg_hba.conf file to add the hosts but I keep getting the same error.

Best Answer

PG_HBA=$(su postgres -c "psql -t -P format=unaligned -c 'SHOW hba_file'")

That seems potentially problematic.

  • That line above shows createdb ultimaterehab. That line doesn't however connect to the ultimaterehab database. It connects to the default database.
  • Debian/Ubuntu already does the setting up for the right pg_hba. You don't have to do anything whatsoever. If you absolutely need the location of the pg_hba file, which you do not need, you'd be better of working with the system utility pg_conftool to get that location: pg_conftool show hba_file.

Moreover, ubuntu doesn't use su, it uses sudo.

sudo -u postgres createuser app
sudo -u postgres createdb ultimaterehab

And you're done.

Related Question