Complete Guide to Executing PostgreSQL SQL Files via Command Line with Authentication Solutions

Problem Context and Challenges

In PostgreSQL database management practice, there is frequent need to execute SQL files containing numerous INSERT statements. When file sizes become too large for graphical interface tools, the command-line utility psql becomes the only viable solution. However, users attempting to execute the command psql -d HIGHWAYS -a -f CLUSTER_1000M.sql encounter password authentication failures, where the system doesn't allow password input and returns the error: "psql: FATAL: password authentication failed for user \"myUsername\"".

Root Causes of Authentication Failure

The core issue with password authentication failure lies in incomplete connection parameter configuration. The original command lacks username specification, preventing the system from establishing valid authentication connection. PostgreSQL's security mechanism requires complete connection credentials in password authentication mode.

Let's demonstrate the correct connection approach through a complete example:

# Basic connection format
psql -U username -d database_name -f file.sql

# Specific application example
psql -U dbadmin -d HIGHWAYS -f CLUSTER_1000M.sql

Four Password Supply Solutions

Environment Variable Method

By setting the PGPASSWORD environment variable, passwords can be provided without interactive input. This method is suitable for script automation scenarios.

# In Unix/Linux systems
export PGPASSWORD='your_password'
psql -U username -d database_name -f file.sql

# In Windows systems
set PGPASSWORD=your_password
psql -U username -d database_name -f file.sql

The advantage of environment variables is integration into automation scripts, but security risks must be considered as passwords may appear in process lists or log files.

Password File Solution

Creating a .pgpass file provides a more secure long-term solution. This file should be stored in the user's home directory with the format: hostname:port:database:username:password.

# Create or edit .pgpass file
echo "localhost:5432:HIGHWAYS:myUsername:myPassword" >> ~/.pgpass
chmod 600 ~/.pgpass

# Now connect without password prompt
psql -U myUsername -d HIGHWAYS -f CLUSTER_1000M.sql

The password file method offers the best balance of security and convenience, particularly suitable for environments requiring frequent connections.

Trust Authentication Configuration

For development or testing environments, trust authentication can be configured to completely bypass password checks. This requires modifying the pg_hba.conf file.

# Add trust rules in pg_hba.conf
# IPv4 local connections:
host    HIGHWAYS    myUsername    127.0.0.1/32    trust

# IPv6 local connections:
host    HIGHWAYS    myUsername    ::1/128         trust

After configuration modification, PostgreSQL service needs reloading:

# Reload configuration
pg_ctl reload
# Or use SQL command
SELECT pg_reload_conf();

While trust authentication is convenient, it's recommended only for secure internal network environments.

Connection String Method

PostgreSQL versions 9.1 and above support connection strings, allowing specification of all connection parameters in a single line.

# Using connection string
psql "postgresql://username:password@localhost:5432/HIGHWAYS" -f CLUSTER_1000M.sql

# Or using URI format
psql postgresql://myUsername:myPassword@localhost/HIGHWAYS -f CLUSTER_1000M.sql

Advanced Execution Options

Transaction Control

For SQL files containing multiple operations, using single transactions ensures data consistency.

# Execute all commands in single transaction
psql -U username -d HIGHWAYS -f CLUSTER_1000M.sql -1

# Equivalent explicit transaction control
psql -U username -d HIGHWAYS -c "BEGIN;" -f CLUSTER_1000M.sql -c "COMMIT;"

Output Control and Logging

Controlling execution output verbosity is crucial for debugging and auditing purposes.

# Display all executed SQL statements
psql -U username -d HIGHWAYS -f CLUSTER_1000M.sql -a

# Redirect output to log file
psql -U username -d HIGHWAYS -f CLUSTER_1000M.sql -a -L execution_log.txt

# Display only error messages
psql -U username -d HIGHWAYS -f CLUSTER_1000M.sql -q

Error Handling Strategies

Configuring appropriate error handling mechanisms prevents data inconsistency from partial failures.

# Stop execution immediately on error
psql -U username -d HIGHWAYS -f CLUSTER_1000M.sql --set ON_ERROR_STOP=1

# Set variable to control error behavior
\set ON_ERROR_STOP on
\i CLUSTER_1000M.sql

Performance Optimization Recommendations

Large File Processing Techniques

When processing large files containing thousands of INSERT statements, employ these optimization strategies:

# Disable autocommit for performance improvement
\set AUTOCOMMIT off
\i large_insert_file.sql
\commit

# Use COPY command instead of multiple INSERTs
COPY table_name FROM '/path/to/datafile.csv' WITH CSV;

Connection Parameter Optimization

Adjusting connection parameters can significantly improve performance for large file execution:

# Set connection timeout and keepalives
psql "host=localhost port=5432 dbname=HIGHWAYS user=myUsername connect_timeout=10 keepalives=1" -f CLUSTER_1000M.sql

Security Best Practices

Password Security Management

In production environments, strict security guidelines must be followed:

# Use .pgpass file instead of environment variables
# Ensure correct file permissions
chmod 600 ~/.pgpass

# Regularly rotate passwords and update .pgpass file
# Use encrypted connections
psql "host=localhost sslmode=require dbname=HIGHWAYS user=myUsername" -f CLUSTER_1000M.sql

Principle of Least Privilege

Create dedicated users for SQL execution with minimal necessary permissions:

-- Create dedicated user
CREATE USER script_runner WITH PASSWORD 'secure_password';
GRANT INSERT, UPDATE ON target_table TO script_runner;

Troubleshooting Guide

Common Errors and Solutions

Various issues that may arise during SQL file execution:

# Check connection parameters
psql -U username -d database_name -c "SELECT version();"

# Verify file path and permissions
ls -l CLUSTER_1000M.sql

# Test SQL file syntax
psql -U username -d database_name -f CLUSTER_1000M.sql --echo-errors

Diagnostic Tool Usage

Utilize PostgreSQL built-in tools for problem diagnosis:

# View current connection information
\conninfo

# Check database status
SELECT datname, datistemplate, datallowconn FROM pg_database;

# Monitor execution progress
\watch 5 "SELECT count(*) FROM target_table;"

Automated Deployment Integration

Scripted Execution Solutions

Integrate SQL file execution into automated deployment workflows:

#!/bin/bash
# Deployment script example
set -e

export PGPASSWORD=${DB_PASSWORD}
psql -h ${DB_HOST} -U ${DB_USER} -d ${DB_NAME} -f /path/to/setup.sql -v ON_ERROR_STOP=1

# Verify deployment results
psql -h ${DB_HOST} -U ${DB_USER} -d ${DB_NAME} -c "SELECT COUNT(*) FROM information_schema.tables;"

Continuous Integration Environment Configuration

Securely execute database scripts in CI/CD pipelines:

# GitHub Actions example
- name: Execute SQL Scripts
  run: |
    psql postgresql://${{ secrets.DB_USER }}:${{ secrets.DB_PASSWORD }}@${{ secrets.DB_HOST }}/${{ secrets.DB_NAME }} \
    -f database/migrations.sql \
    --set ON_ERROR_STOP=1

Conclusion and Recommendations

Through the four password supply methods and various advanced options introduced in this article, users can flexibly choose SQL file execution solutions suitable for their specific environments. For production environments, using .pgpass files combined with appropriate permission controls is recommended; for development and testing environments, trust authentication or connection string methods can be selected based on security requirements. Regardless of the chosen method, security best practices should be followed to ensure the safety and reliability of database access.

In practical applications, it's advised to select the most appropriate authentication and execution strategies based on specific scenarios, and establish comprehensive monitoring and rollback mechanisms to ensure the stability of database operations and consistency of data.