Postgresql - query from bash script as database user 'postgres'

sql

bash-scripting

dynamic-sql

error-handling

byAlex Kataev·Aug 3, 2024

To execute a PostgreSQL query as the 'postgres' user, utilize the below command:

sudo -u postgres psql -d dbname -c "SELECT * FROM table;"

In the above command, make sure to replace dbname with your database's name, table with the table you want to execute the query on, and "SELECT * FROM table;" with the SQL query you want to run. This command's magic is the seamless blend of sudo for user switching, psql for PostgreSQL command-line interface, and -c to process a single-command query.

When handling sensitive data, try not to include credentials directly in your scripts. You might consider using environment variables or configuration files with proper permissions.

To streamline your output, use -t for a clean tuples-only output and -X to prevent psql from reading the .psqlrc file.

Ensure that the sudo user running the script has the necessary permissions to impersonate 'postgres'.

Step-by-step guide

Running dynamic queries

To run dynamic SQL queries:

table="employees"
column="salary"
value="10000"

echo "Let's get this bread! 🍞" # Reddit-style comment 😄

sql_query="UPDATE \${table} SET \${column} = '\${value}' WHERE id = 1;"

sudo -u postgres psql -d dbname -c "\$sql_query"

Capturing query results

To capture the result of a psql query and use it further in your bash script:

output=$(sudo -u postgres psql -d dbname -t -c "SELECT column FROM table;")
echo "Query result: $output"

Error handling

For better error management, apply the set -e and set -u options:

set -eu
sudo -u postgres psql -d dbname -c "SELECT * FROM non_existent_table;"
# Above line - A clear example of asking for trouble, isn't it? 😄

The script will immediately terminate when an error occurs (set -e) and treat unset variables as an error (set -u), saving you from nasty surprises.

Running complex queries

#!/bin/bash
export PGPASSWORD='your_password'
output=$(psql -U postgres -h localhost -d dbname -t -c "SELECT column FROM table;")
unset PGPASSWORD
echo "Query output: $output"

Ensuring secure access

Avoid exposing sensitive details in logs or scripts. 😎
Use password-less authentication methods for automated scripts.

Multi-line and complex queries

For multi-line or complex SQL queries, you can structure scripts for readability use 'EOF':

sudo -u postgres psql -d dbname <<EOF
SELECT * FROM information_schema.tables
EOF
# 🚀 Blastoff! You've just queried info on all tables in the schema! 🚀

Maintaining transaction integrity

To ensure transaction integrity:

sudo -u postgres psql -d dbname <<EOF
BEGIN;
INSERT INTO table (column) VALUES ('data');
UPDATE other_table SET column = 'value' WHERE condition;
ROLLBACK;
EOF
# 👀 Nothing to see here, folks! Just made some irreversible changes! 💥