Postgresql Scripting psql execution with password

Question

How can I call psql so that it doesn t prompt for a password   This is what I have   psql -Umyuser  lt  myscript sql   However  I couldn t find the argument that passes the password  and so psql always prompts for it

User · Answer

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

User · Answer

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

User · Answer

You can add this command line at the begining of your script   set PGPASSWORD  your password

User · Answer

Use -w in the command  psql -h localhost -p 5432 -U user -w

User · Answer

I find  that psql show password prompt even you define PGPASSWORD variable  but you can specify -w option for psql to omit password prompt

User · Answer

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  gt  pgbench out 2 gt  amp 1  amp

User · Answer

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  lt  myscript sql

User · Answer

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  lt server gt  port 5432 dbname  lt db gt  user  lt user gt  password  lt password gt    postgres  gt

User · Answer

There are several ways to authenticate to PostgreSQL  You may wish to investigate alternatives to password authentication at https   www postgresql org docs current static client-authentication html   To answer your question  there are a few ways provide a password for password-based authentication   The obvious way is via the password prompt   Instead of that  you can provide the password in a pgpass file or through the PGPASSWORD environment variable   See these    https   www postgresql org docs 9 0 static libpq-pgpass html https   www postgresql org docs 9 0 interactive libpq-envars html   There is no option to provide the password as a command line argument because that information is often available to all users  and therefore insecure   However  in Linux Unix environments you can provide an environment variable for a single command like this   PGPASSWORD yourpass psql

User · Answer

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 amp      psql postgresql   username password localhost 5432 mydb

User · Answer

You have to create a password file  see http   www postgresql org docs 9 0 interactive libpq-pgpass html for more info

User · Answer

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   gt   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 -hmyserver  the -pmyport  or -Ujdoe 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

User · Answer

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  lt  echo myserver 5432 mydb jdoe password  psql -h myserver -U jdoe -p 5432 mydb   This uses the  lt    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

User · Answer

8 years later       On my mac  I had to put a line into the file    pgpass like    lt IP gt   lt PORT gt   lt dbname gt   lt user gt   lt password gt    Also see  https   www postgresql org docs current libpq-pgpass html https   wiki postgresql org wiki Pgpass

User · Answer

PGPASSWORD  your password  psql -Umyuser  lt  myscript sql

User · Answer

You may find this useful  Windows PSQL command line  is there a way to allow for passwordless login

[postgresql] Postgresql: Scripting psql execution with password

Examples related to postgresql

Examples related to psql