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?

Vao Tsun · Accepted Answer · 2017-07-14 08:43:29Z

27

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"

answered Jul 14, 2017 at 8:43

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

2 Comments

Jacques Gaudin Over a year ago

The pg_hba.conf was showing trust everywhere. Works like a charm now. Thanks.

djvg Feb 20 at 8:26

also see psql docs

Dunes · Accepted Answer · 2017-07-14 08:46:14Z

4

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.

answered Jul 14, 2017 at 8:46

Dunes

42.1k7 gold badges86 silver badges107 bronze badges

1 Comment

Jacques Gaudin Over a year ago

Found out that pg_hba.conf was configured with trust. Thanks for your help.

Jacques Gaudin · Accepted Answer · 2017-07-14 21:50:49Z

0

It turns out that the pg_hba.conf file that was in use was:

local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

I changed it to:

local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

and both answers above work.

answered Jul 14, 2017 at 21:50

Jacques Gaudin

17.1k11 gold badges59 silver badges80 bronze badges

Collectives™ on Stack Overflow

How to test postgresql credentials from bash?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related