How to test postgresql credentials from bash?

Question

I have a Django application that runs from a virtual environment. I am writing a bash script to test the virtual environment. The postgresql username and password are environment variables in the virtual environment, that Django pulls into settings.py.

In order to test the connection to the database, I have written the following:

echo -n 'Testing database connection  ...'

export COMMAND="from psycopg2 import connect; import sys; c=connect(dbname='myappdb',\ 
user='$POSTGRESQL_USER', password='$POSTGRESQL_PWD', host='127.0.0.1', port=5432); \
sys.exit(c.closed)"

test `python -c "$COMMAND" 2>/dev/null && echo true `

Where test is a function:

function test {
  [ $1 ] && echo "[OK]" || echo "[FAIL]"
}

It seems that even with the wrong credentials, no exception is raised and c.closed is always zero. Is there a way I can test if the credentials are right in the virtual environment?

Answer 1

It's bash, not python, so not sure if it fits you needs:

vao@vao-X102BA:~$ psql -d "postgresql://p:goodpassword@localhost/t" -c "select now()"
              now
-------------------------------
 2017-07-14 11:42:40.712981+03
(1 row)

vao@vao-X102BA:~$ echo $?
0
vao@vao-X102BA:~$ psql -d "postgresql://p:badpassword@localhost/t" -c "select now()"
psql: FATAL:  password authentication failed for user "p"
FATAL:  password authentication failed for user "p"
vao@vao-X102BA:~$ echo $?
2

If you want to suppres output, instead of select now(), you can just quit with -c "\q"

Answer 2

Do you not have psql on your machine? It'd be more simple to use a command app than python to do what you want.

if PGHOST=127.0.0.1 PGPORT=5432 PGUSER=$POSTGRESQL_USER PGPASSWORD=$POSTGRESQL_PWD \
        PGDATABASE=myappdb psql -c \q 2>/dev/null
then
    echo "[OK]"
else
    echo "[FAIL]"
fi

PGHOST=127.0.0.1 ... is temporarily exporting variables to psql. The \q just tells the client to quit immediately if the connection was successful.