Specifying Password for PSQL Non-Interactively

Using the -c Option:

• You can specify the password using the PGPASSWORD environment variable:

  PGPASSWORD=my_password psql -c "SELECT * FROM my_table;" -d my_database
  

  • Replace my_password with your actual password.
  • Replace my_database with the name of your database.

• You can use the PGPASSWORD environment variable to set the password for this user:

  PGPASSWORD=my_password psql -U my_user -d my_database
  

  • Replace my_user with your username.

Additional Notes:

• The PGPASSWORD environment variable is temporary and is not stored in your shell history.
• You can also use the -h option to specify the hostname of the PostgreSQL server.
• For more advanced password management, consider using tools like pgpass or ssh-agent.

Example Codes for Specifying a Password to psql Non-Interactively

Using the -c Option with PGPASSWORD Environment Variable

export PGPASSWORD=my_password
psql -c "SELECT * FROM my_table;" -d my_database

• export PGPASSWORD=my_password: Sets the PGPASSWORD environment variable to your desired password.
• psql -c "SELECT * FROM my_table;" -d my_database: Executes a SQL command (SELECT * FROM my_table;) on the specified database (my_database).

PGPASSWORD=my_password psql -U my_user -d my_database

• psql -U my_user -d my_database: Connects to the specified database (my_database) as the specified user (my_user).

PGPASSWORD=my_password psql -f my_script.sql -d my_database

• psql -f my_script.sql -d my_database: Executes the SQL script my_script.sql on the specified database (my_database).

Note: Replace my_password, my_user, my_database, and my_script.sql with your actual values.

Additional Considerations

• Security: Avoid storing passwords in plain text. Consider using password management tools or encrypting your password.
• Environment Variables: The PGPASSWORD environment variable is temporary and should be cleared after use to prevent unauthorized access.
• Error Handling: Implement error handling in your scripts to gracefully handle connection failures or other exceptions.
• Automation: If you need to automate these tasks frequently, consider using shell scripting or other automation tools.

Using the pgpass File

3. Specify credentials: In the pgpass file, add a line for each connection, following the format:

   hostname:port:database:username:password
   

   For example:

   localhost:5432:my_database:my_user:my_password
   

Using ssh-agent and ssh-add

1. Generate a key pair: Use ssh-keygen to create a public and private key pair.
2. Add the private key to ssh-agent: Use ssh-add to add your private key to ssh-agent.
3. Connect using SSH: Connect to the PostgreSQL server using SSH, specifying your username and the host.
4. Run psql: Once connected via SSH, you can run psql without providing a password, as it will use the authenticated SSH connection.

Using pg_service.conf

2. Specify credentials: Add a service definition to the pg_service.conf file, including the password:

   [my_service]
   host = localhost
   port = 5432
   dbname = my_database
   user = my_user
   password = my_password
   

Choosing the Right Method:

• pgpass: Suitable for simple setups or when you need to store multiple credentials.
• ssh-agent: Ideal for complex environments or when you need to use SSH tunnels.
• pg_service.conf: Useful for defining multiple services and simplifying connection strings.