How to Specify a Password for psql Non-Interactively

Specifying a Password for psql Non-Interactively

When using the PostgreSQL command-line tool psql, you might want to specify a password non-interactively, which allows scripts or applications to connect to the database without requiring manual password entry. There are a few methods to securely provide the password to psql without user interaction.

Methods to Specify a Password for psql Non-Interactively

1. Using the .pgpass File:

The .pgpass file is a secure way to store passwords for PostgreSQL connections. By placing the password in this file, psql can authenticate automatically without prompting for a password.

2. Using Environment Variables:

The PGPASSWORD environment variable allows you to specify a password for a single command execution. This is suitable for quick, non-interactive logins but is less secure than the .pgpass method.

Method 1: Using the .pgpass File

The .pgpass file should be created in the user’s home directory with specific permissions. The format of .pgpass is as follows:

hostname:port:database:username:password

1. Create the .pgpass File:

• Create a file named .pgpass in your home directory (e.g., ~/.pgpass on Linux or %APPDATA%\postgresql\pgpass.conf on Windows).

2. Add Connection Details:

localhost:5432:mydatabase:myuser:mypassword

Replace localhost, 5432, mydatabase, myuser, and mypassword with your actual connection details.

3. Set Permissions:

Set permissions so that only the owner can read the file. In Linux:

chmod 600 ~/.pgpass

4. Connect with psql:

psql -U myuser -d mydatabase

Explanation:

• psql automatically checks .pgpass for the password and authenticates without prompting.

Method 2: Using the PGPASSWORD Environment Variable

The PGPASSWORD variable is set temporarily for a single command execution:

PGPASSWORD='mypassword' psql -U myuser -h localhost -p 5432 -d mydatabase

Explanation:

• PGPASSWORD='mypassword': Temporarily sets the password for this command.
• psql -U myuser -h localhost -p 5432 -d mydatabase: Connects using the specified credentials and password from PGPASSWORD.

Warning: Avoid using PGPASSWORD in scripts, as it can expose the password to other users on the system.

Example Code:

# Using the .pgpass file
# Step 1: Create the .pgpass file and add the line:
# localhost:5432:mydatabase:myuser:mypassword

# Step 2: Set permissions (Linux)
chmod 600 ~/.pgpass

# Step 3: Connect using psql without a password prompt
psql -U myuser -d mydatabase

# Using the PGPASSWORD environment variable for a one-time command
PGPASSWORD='mypassword' psql -U myuser -h localhost -p 5432 -d mydatabase

Important Notes:

• Security: The .pgpass file is more secure than using PGPASSWORD, as it keeps the password out of command history and other system logs.
• Permissions: Ensure .pgpass has restrictive permissions (600) to prevent unauthorized access.

All PostgreSQL Questions, Answers, and Code Snippets Collection.