Specify a Password in psql Non-Interactively

1. Overview

PostgreSQL is a leading open-source relational database management system (DBMS) renowned for its reliability and advanced features. In many scenarios, especially in automated environments, we might need to connect to a PostgreSQL database without human intervention.

Typically, when connecting to a PostgreSQL database from the command line, we use the psql tool. In turn, psql prompts users for a password by default. This interactive behavior can be inconvenient, especially when scripting database interactions or automating tasks. Fortunately, PostgreSQL provides several ways to supply passwords non-interactively, each method with its pros and cons.

In this tutorial, we’ll cover various methods to specify a password to psql without user interaction.

2. Understanding Non-Interactive Connections

Scripts and automation tools frequently interact with databases in development and production environments. During these automated processes, interactive password prompts can break scripts and pipelines.

Non-interactive password handling is essential for automated database backups, enabling scripts to run without manual intervention. It’s also crucial for scripted migrations, enabling seamless transitions between database versions.

Additionally, non-interactive password management supports continuous integration (CI) and continuous deployment (CD) pipelines, ensuring that applications can access the database during automated workflows. Lastly, such automation plays a significant role in monitoring and testing scripts, facilitating consistent and reliable database interactions without requiring user input.

3. Using the .pgpass File

Using the .pgpass file is one of the most common methods to handle PostgreSQL passwords non-interactively.

The .pgpass file is a fairly straightforward, secure method to store PostgreSQL credentials locally. It enables psql and other PostgreSQL tools to retrieve them without prompting for a password.

In particular, .pgpass is a plain text file in the user home directory:

• /home/<username>/.pgpass on Linux
• C:\Users\<username>\.pgpass on Windows

This file contains rows with colon-separated values with the necessary connection details for PostgreSQL databases such as the hostname, port, database name, username, and password:

hostname:port:database:username:password

To use the .pgpass file, we can start by creating it in the home directory:

$ touch ~/.pgpass

Then, we need to set the file permission to 600 (user read and write) to ensure it’s only accessible by the specific user:

$ chmod 600 ~/.pgpass

Finally, we can open the file and add an entry:

localhost:5432:database_name:username:password

With this entry in place, psql automatically fetches the password, bypassing the prompt.

Additionally, the .pgpass file allows wildcards (*) in certain fields, which is helpful when we have multiple databases or hosts with similar credentials.

For example, we can use a wildcard to match any host:

*:5432:database_name:username:password

Furthermore, the same credentials can be used across multiple databases on the same host:

localhost:5432:*:username:password

Of course, we should be cautious when using wildcards in the context of security, as they could expose more databases to unauthorized access if managed improperly.

The risks of the .pgpass file include unauthorized access due to inadequate permissions, exposure through backups, accidental sharing, vulnerability to malware, and lack of auditing capabilities.

4. Using the PGPASSWORD Environment Variable

One of the simplest methods for non-interactively specifying a password is through environment variables. Specifically, PostgreSQL recognizes the PGPASSWORD environment variable, which temporarily sets the password for the database connection in the current context.

In fact, we can set PGPASSWORD and then call psql on the same line:

$ export PGPASSWORD="password" psql -h localhost -U username -d database_name

With this method, psql uses the password stored in the PGPASSWORD variable and bypasses any password prompts.

The main downside of using PGPASSWORD is that environment variables may be visible to other processes on the same machine. In multi-user systems, this method could expose the password to other users with access to system process information.

We can mitigate the risks by using PGPASSWORD in secure, private sessions or controlled environments. Moreover, we should unset the environment variable after use and avoid using PGPASSWORD in long-running scripts or background jobs.

5. Using Connection URIs

Using connection URIs (Uniform Resource Identifiers) is a method for specifying database connection details in a single-string format, simplifying the connection to a PostgreSQL database. A connection URI can contain all necessary parameters, including the username, password, host, port, and database name. This is often useful in scripts or configuration files where such URIs are supported.

Let’s look at the general format for a PostgreSQL connection URI:

postgresql://username:password@hostname:port/dbname

We can use it with the psql command in a single line:

$ psql postgresql://username:password@localhost:5432/database_name

Connection URIs can be easily modified to change connection parameters without altering multiple settings. In addition, several libraries and frameworks support connection URIs, enabling straightforward application integration.

Embedding the password in a URI can be helpful in scripts, but it exposes the password within the command itself, which could be insecure if logged or shared.

6. Using the PGSERVICEFILE File

PostgreSQL supports connection service files, which provide a way to specify connection details in separate configuration files. This helps in managing complex connection setups and keeps passwords out of scripts.

In particular, PGSERVICEFILE is a configuration file where we can define multiple PostgreSQL connection services. Each service contains all connection parameters, including the host, port, database, user, and SSL settings. We can refer to the service name instead of providing these details whenever we connect.

By default, PostgreSQL searches for the file in /etc/pg_service.conf for system-wide configurations and the ~/.pg_service.conf for user-specific configurations.

Of course, we can also specify a custom location using the PGSERVICEFILE environment variable.

To use the PGSERVOCEFILE file, we can start by creating it in the home directory:

$ touch ~/.pg_service.conf

Then, let’s add database configurations to PGSERVOCEFILE:

[dbservicename]
host=localhost
port=5432
dbname=database_name
user=username
password=password

Next, we set the environment variable to point to the service file location:

$ export PGSERVICEFILE="/path/to/pg_service.conf"

After setting the environment variable, PostgreSQL tools should look for and within this file for connection information.

Finally, we can use the psql command to connect through the service name:

$ psql service=dbservicename

By referencing the service name, we avoid specifying the host, port, user, and other options whenever we connect.

The PGSERVICEFILE file enhances security by keeping sensitive information out of scripts and supports environment-specific configurations by dynamically setting the PGSERVICEFILE path without directly storing credentials within the environment.

7. Conclusion

In this article, we examined various ways to securely connect to PostgreSQL non-interactively by specifying the password preemptively. These included .pgpass files, environment variables, and the PGSERVICEFILE file. Each method offers unique advantages depending on the setup.

The .pgpass file is suitable for simple, persistent configuration. On the other hand, environment variables offer flexibility in scripts. Finally, PGSERVICEFILE offers centralized management of complex environments.

Selecting the right method and applying best practices ensures efficient, secure, and automated PostgreSQL connection for applications and scripts.