I am trying to automate database creation process with a shell script and one thing I've hit a road block with passing a password to psql. Here is a bit of code from the shell script:
psql -U $DB_USER -h localhost -c"$DB_RECREATE_SQL"
How do I pass a password to psql
in a non-interactive way?
This question is related to
bash
postgresql
command-line
On Windows:
Assign value to PGPASSWORD: C:\>set PGPASSWORD=pass
Run command: C:\>psql -d database -U user
Ready
Or in one line,
set PGPASSWORD=pass&& psql -d database -U user
Note the lack of space before the && !
Added content of pg_env.sh to my .bashrc:
cat /opt/PostgreSQL/10/pg_env.sh
#!/bin/sh
# The script sets environment variables helpful for PostgreSQL
export PATH=/opt/PostgreSQL/10/bin:$PATH
export PGDATA=/opt/PostgreSQL/10/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5433
export PGLOCALEDIR=/opt/PostgreSQL/10/share/locale
export MANPATH=$MANPATH:/opt/PostgreSQL/10/share/man
with addition of (as per user4653174 suggestion)
export PGPASSWORD='password'
in one line:
export PGPASSWORD='password'; psql -h 'server name' -U 'user name' -d 'base name' -c 'command'
with command a sql command such as "select * from schema.table"
or more readable:
export PGPASSWORD='password'
psql -h 'server name' -U 'user name' -d 'base name' \
-c 'command' (eg. "select * from schema.table")
An alternative to using the PGPASSWORD
environment variable is to use the conninfo
string according to the documentation:
An alternative way to specify connection parameters is in a conninfo string or a URI, which is used instead of a database name. This mechanism give you very wide control over the connection.
$ psql "host=<server> port=5432 dbname=<db> user=<user> password=<password>"
postgres=>
This can be done by creating a .pgpass
file in the home directory of the (Linux) User.
.pgpass
file format:
<databaseip>:<port>:<databasename>:<dbusername>:<password>
You can also use wild card *
in place of details.
Say I wanted to run tmp.sql
without prompting for a password.
With the following code you can in *.sh file
echo "192.168.1.1:*:*:postgres:postgrespwd" > $HOME/.pgpass
echo "` chmod 0600 $HOME/.pgpass `"
echo " ` psql -h 192.168.1.1 -p 5432 -U postgres postgres -f tmp.sql `
If its not too late to add most of the options in one answer:
There are a couple of options:
set an environment variable and get it from there:
export PGPASSWORD='password'
and then run your psql to login or even run the command from there:
psql -h clustername -U username -d testdb
On windows you will have to use "set" :
set PGPASSWORD=pass and then login to the psql bash.
Set the PGPASSWORD environment variable inside the script before calling psql
PGPASSWORD=pass1234 psql -U MyUsername myDatabaseName
For reference, see http://www.postgresql.org/docs/current/static/libpq-envars.html
Edit
Since Postgres 9.2 there is also the option to specify a connection string or URI that can contain the username and password.
Using that is a security risk because the password is visible in plain text when looking at the command line of a running process e.g. using ps
(Linux), ProcessExplorer (Windows) or similar tools, by other users.
See also this question on Database Administrators
Source: Stackoverflow.com