Postgresql: Scripting psql execution with password
PostgresqlPsqlPostgresql Problem Overview
How can I call psql so that it doesn't prompt for a password?
This is what I have:
psql -Umyuser < myscript.sql
However, I couldn't find the argument that passes the password, and so psql always prompts for it.
Postgresql Solutions
Solution 1 - Postgresql
You may wish to read a summary of the ways to authenticate to PostgreSQL.
To answer your question, there are several ways provide a password for password-based authentication:
-
Via the password prompt. Example:
psql -h uta.biocommons.org -U foo Password for user foo:
-
In a pgpass file. See libpq-pgpass. Format:
<host>:<port>:<database>:<user>:<password>
-
With the
PGPASSWORD
environment variable. See libpq-envars. Example:export PGPASSWORD=yourpass psql ... # Or in one line for this invocation only: PGPASSWORD=yourpass psql ...
-
In the connection string The password and other options may be specified in the connection string/URI. See app-psql. Example:
psql postgresql://username:password@dbmaster:5433/mydb?sslmode=require
Solution 2 - Postgresql
PGPASSWORD=[your password] psql -Umyuser < myscript.sql
Solution 3 - Postgresql
You can add this command line at the begining of your script:
set PGPASSWORD=[your password]
Solution 4 - Postgresql
If you intend on having multiple hosts/database connections, the ~/.pgpass file is the way to go.
Steps:
- Create the file using
vim ~/.pgpass
or similar. Input your information in the following format:hostname:port:database:username:password
Do not add string quotes around your field values. You can also use * as a wildcard for your port/database fields. - You must
chmod 0600 ~/.pgpass
in order for it to not be silently ignored by psql. - Create an alias in your bash profile that runs your psql command for you. For example:
alias postygresy='psql --host hostname database_name -U username'
The values should match those that you inputted to the ~/.pgpass file. - Source your bash profile with
. ~/.bashrc
or similar. - Type your alias from the command line.
Note that if you have an export PGPASSWORD='' variable set, it will take precedence over the file.
Solution 5 - Postgresql
This might be an old question, but there's an alternate method you can use that no one has mentioned. It's possible to specify the password directly in the connection URI. The documentation can be found here, alternatively here.
You can provide your username and password directly in the connection URI provided to psql
:
# postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
psql postgresql://username:password@localhost:5432/mydb
Solution 6 - Postgresql
An alternative to using PGPASSWORD
environment variable is to use 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=>
Solution 7 - Postgresql
If you're having problems on windows like me (I'm using Windows 7 64-bit) and set PGPASSWORD=[Password]
did not work.
Then, as Kavaklioglu said in one of the comments,
export PGPASSWORD=[password]
You will need to save this at the top of the file, or before any usage so its set before being called.
Certainly does work on windows :)
Solution 8 - Postgresql
You have to create a password file: see http://www.postgresql.org/docs/9.0/interactive/libpq-pgpass.html for more info.
Solution 9 - Postgresql
Given the security concerns about using the PGPASSWORD environment variable, I think the best overall solution is as follows:
- Write your own temporary pgpass file with the password you want to use.
- Use the PGPASSFILE environment variable to tell psql to use that file.
- Remove the temporary pgpass file
There are a couple points of note here. Step 1 is there to avoid mucking with the user's ~/.pgpass file that might exist. You also must make sure that the file has permissions 0600 or less.
Some have suggested leveraging bash to shortcut this as follows:
PGPASSFILE=<(echo myserver:5432:mydb:jdoe:password) psql -h myserver -U jdoe -p 5432 mydb
This uses the <() syntax to avoid needing to write the data to an actual file. But it doesn't work because psql checks what file is being used and will throw an error like this:
WARNING: password file "/dev/fd/63" is not a plain file
Solution 10 - Postgresql
It can be done simply using PGPASSWORD. I am using psql 9.5.10. In your case the solution would be
PGPASSWORD=password psql -U myuser < myscript.sql
Solution 11 - Postgresql
Building on mightybyte's answer for those who aren't comfortable with *nix shell scripting, here's a working script:
#!/bin/sh
PGPASSFILE=/tmp/pgpasswd$$
touch $PGPASSFILE
chmod 600 $PGPASSFILE
echo "myserver:5432:mydb:jdoe:password" > $PGPASSFILE
export PGPASSFILE
psql mydb
rm $PGPASSFILE
The double dollar sign ($$
) in /tmp/pgpasswd$$
at line 2 appends the process ID number to the file name, so that this script can be run more than once, even simultaneously, without side effects.
Note the use of the chmod
command at line 4 - just like the "not a plain file" error that mightybyte described, there's also a "permissions" error if this is not done.
At line 7, you won't have to use the -h
myserver, the -p
myport, or -U
jdoe flag if you use the defaults (localhost : 5432) and only have one database user. For multiple users, (but the default connection) change that line to
psql mydb jdoe
Don't forget to make the script executable with
> chmod +x runpsql
(or whatever you called the script file)
UPDATE:
I took RichVel's advice and made the file unreadable before putting the password into it. That closes a slight security hole. Thanks!
Solution 12 - Postgresql
You may find this useful: https://stackoverflow.com/questions/6216432/windows-psql-command-line-is-there-a-way-to-allow-for-passwordless-login
Solution 13 - Postgresql
8 years later...
On my mac, I had to put a line into the file
~/.pgpass
like:
<IP>:<PORT>:<dbname>:<user>:<password>
Also see:
https://www.postgresql.org/docs/current/libpq-pgpass.html
https://wiki.postgresql.org/wiki/Pgpass
Solution 14 - Postgresql
This also works for other postgresql clis for example you can run pgbench in non-interactive mode.
export PGPASSWORD=yourpassword
/usr/pgsql-9.5/bin/pgbench -h $REMOTE_PG_HOST -p 5432 -U postgres -c 12 -j 4 -t 10000 example > pgbench.out 2>&1 &
Solution 15 - Postgresql
I find, that psql show password prompt even you define PGPASSWORD variable, but you can specify -w option for psql to omit password prompt.
Solution 16 - Postgresql
Use -w in the command: psql -h localhost -p 5432 -U user -w