How do I specify a password to psql non-interactively

Question

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

User · Answer

On Windows    Assign value to PGPASSWORD  C   gt set PGPASSWORD pass Run command  C   gt psql -d database -U user   Ready  Or in one line   set PGPASSWORD pass amp  amp  psql -d database -U user   Note the lack of space before the  amp  amp

User · Answer

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

User · Answer

I tend to prefer passing a URL to psql   psql  postgresql    DB USER  DB PWD  DB SERVER  DB NAME    This gives me the freedom to name my environment variables as I wish and avoids creating unnecessary files   This requires libpq  The documentation can be found here

User · Answer

This can be done by creating a  pgpass file in the home directory of the  Linux  User   pgpass  file format    lt databaseip gt   lt port gt   lt databasename gt   lt dbusername gt   lt password gt    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   gt   HOME  pgpass echo    chmod 0600  HOME  pgpass     echo     psql -h 192 168 1 1 -p 5432  -U postgres  postgres  -f tmp sql

User · Answer

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

User · Answer

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

User · Answer

If its not too late to add most of the options in one answer  There are a couple of options   set it in the pgpass file  link   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  quot set quot    set PGPASSWORD pass and then login to the psql bash

User · Answer

From the official documentation      It is also convenient to have a    pgpass file to avoid regularly having to type in passwords  See Section 30 13 for more information            This file should contain lines of the following format    hostname port database username password      The password field from the first line that matches the current connection parameters will be used

User · Answer

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

[bash] How do I specify a password to 'psql' non-interactively?

Examples related to bash

Examples related to postgresql

Examples related to command-line