How to Specify a Password to 'Psql' Non-Interactively

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

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.

Postgresql: Scripting psql execution with password

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:

1. Via the password prompt. Example:

   psql -h uta.biocommons.org -U foo
   Password for user foo: 
   

2. In a pgpass file. See libpq-pgpass. Format:

   <host>:<port>:<database>:<user>:<password>
   

3. With the PGPASSWORD environment variable. See libpq-envars. Example:

   export PGPASSWORD=yourpass
   psql ...
   
   # Or in one line for this invocation only:
   PGPASSWORD=yourpass psql ...
   

4. 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
   

non-interactive password with createdb command

I just used this instead:

PGPASSWORD="$admin_user_pwd" createdb -U admin_user -h "$db_host" -p 5432 --no-password -e "$db_name"

and it worked. No password file, just the command line.

Psql Command Line Password and Query

Ok guys, found the solution. After a better read of the --help, i figured that

psql [OPTIONS]... [NOM_BASE [NOM_UTILISATEUR]]

Connexion options are to be used after the query so instead of psql [CONNEXION] -c "blabla"
Use this

psql -c "select * from table" "dbname=dbname user=user password=password host=host"

or

psql -c "select * from table" postgresql://user:password@host/dbname

Take care about your double quotes too.

Bye,

How to provide a password for PostgreSQL's createdb non-interactively?

createdb will use the PGPASSWORD environment variable if it's set, that's one simple way of providing a password in non-interactive runs.

Another option would be to set up a .pgpass file in the home directory of the unix user launching the createdb.

How to pass in password to pg_dump?

Create a .pgpass file in the home directory of the account that pg_dump will run as.

The format is:

hostname:port:database:username:password

Then, set the file's mode to 0600. Otherwise, it will be ignored.

chmod 600 ~/.pgpass

See the Postgresql documentation libpq-pgpass for more details.

Can I encrypt posgresql column after I created it?

Based on the information you provided in the question, this should help you for now:

UPDATE 
  table_name 
SET 
  password = crypt('password',gen_salt('bf'))

---

Related Topics