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

📝 Blog Post: How to Specify a Password to 'psql' Non-Interactively

Are you tired of manually entering your password every time you run a script with 'psql'? Want to automate your database creation process without the hassle of interactive password prompts? Look no further! In this guide, we'll explore how to specify a password to 'psql' in a non-interactive way.

The Problem

Suppose you're working on a shell script to automate your database creation process. Everything seems to be going smoothly until you hit a roadblock – passing a password to 'psql'. You don't want to be prompted for your password every time the script runs, but you also don't want to compromise security by hardcoding it into your script. So, what's the solution?

The Solution

Thankfully, there's a simple and secure way to specify a password to 'psql' non-interactively. We'll walk you through the process step-by-step.

1. Utilizing the .pgpass File

The '.pgpass' file is a convenient way to store your database credentials securely. Here's how you can use it:

1. Open your Terminal or command prompt.

2. Create a file named '.pgpass' in your home directory if it doesn't already exist.

3. Add the following line to the '.pgpass' file:

   hostname:port:database:username:password

   Replace 'hostname', 'port', 'database', 'username', and 'password' with your own database credentials. For example:

   localhost:5432:mydatabase:myuser:mypassword

   Make sure to set appropriate permissions on the '.pgpass' file to keep it secure. Run the following command:

   chmod 0600 ~/.pgpass

2. Modifying Your Shell Script

Now that we have the '.pgpass' file set up, we can modify our shell script to utilize it:

#!/bin/bash

export PGPASSFILE=~/.pgpass

psql -U $DB_USER -h localhost -c "$DB_RECREATE_SQL"

By setting the 'PGPASSFILE' environment variable to the location of our '.pgpass' file, 'psql' will automatically find and use it to retrieve the password.

That's it! Now, every time your shell script runs the 'psql' command, it will authenticate using the password specified in the '.pgpass' file.

Wrapping Up

Automating your database creation process has never been easier! By leveraging the '.pgpass' file and modifying your shell script accordingly, you can seamlessly specify a password to 'psql' non-interactively. Say goodbye to tedious manual password prompts and streamline your workflow today.

Give it a try and let us know how it works for you! If you have any questions or encounter any issues along the way, leave a comment below, and we'll be happy to assist you.

✨ Start automating your 'psql' commands with ease today! ✨