PostgreSQL Practical Guide: A Complete Solution from Connection to Authentication

Preface

When using PostgreSQL, we often encounter various connection and authentication issues. Based on practical troubleshooting experience, this article systematically introduces PostgreSQL user management, authentication configuration, password policies, and solutions to common problems.

• Preface
• Environment preparation
  • Enter the PostgreSQL command line
• User Management
  • Create user
  • Change user password
  • Verify after changing password
  • View user information
  • Delete user
• Authentication Configuration
  • Detailed Explanation of pg_hba.conf Configuration
  • Authentication Method Description
  • Configuration example
  • Configuration takes effect
• password policy
  • Supported password encryption methods
  • Test results for special characters in passwords
  • Password strength recommendations
• Practical example: a complete password setup and verification workflow
  • Step 1: Enter PostgreSQL
  • Step 2: Set password
  • Step 3: Exit and test
  • Step 4: Cleanup (optional)
• Connection test
  • Why is testing needed?
  • Command line test
  • Basic testing process
  • .pgpass file format
  • Connection test script
• Common troubleshooting
  • Problem 1: Password authentication failed
  • Problem 2: Issues caused by exclamation marks in bash
  • Problem 3: User duplicate name validation
• Go language connection example
  • Install driver
  • Basic connection example
  • Encapsulate the connection function
  • Connection pool configuration
• Best practice recommendations
  • 1. Security configuration
  • 2. Password Policy
  • 3. Monitoring and Logging
  • 4. Troubleshooting Checklist
• Summary
  • Key points
  • Quick reference
• Disclaimer
• Reference resources

Environment preparation

Enter the PostgreSQL command line

Before performing any operations, you first need to enter the PostgreSQL command-line environment.

# Method 1: Log in using the postgres system user (recommended)
sudo -u postgres psql

# Method 2: Switch to the postgres user and then enter
sudo su - postgres
psql

# Method 3: If a password has already been configured, connect directly
psql -h localhost -U username -d database

Notes:

• sudo -u postgres psql will directly enter as the postgres superuser

• The prompt you see will become postgres=#, indicating that you have successfully entered

• Input \q You can exit.

# What it looks like after successfully entering
postgres=#

User Management

Create user

-- Create a regular user
CREATE USER username WITH PASSWORD 'password';

-- Create superuser
CREATE USER admin WITH PASSWORD 'admin123' SUPERUSER;

-- Create a user with an expiration time
CREATE USER temp_user WITH PASSWORD 'temp123' VALID UNTIL '2024-12-31';

Change user password

-- Change password
ALTER USER username WITH PASSWORD 'new_password';

-- Force the user to change their password on next login
ALTER USER username WITH PASSWORD 'new_password' VALID UNTIL 'infinity';

Verify after changing password

After changing the password, be sure to perform a connection test to ensure the password takes effect:

# 1. Exit psql
\q

# 2. Test the connection using the new password
export PGPASSWORD='Cloudflare@2026@Secure&$'
psql -h 127.0.0.1 -U root -d postgres -c "SELECT 'Connection successful' as status;"

# Successful output example:
#        status
# -----------------------
#  Connection successful
# (1 row)

# 3. If the connection fails, check whether the password is correct.
# You can re-enter psql to view user information.
sudo -u postgres psql -c "SELECT usename, passwd IS NOT NULL FROM pg_shadow WHERE usename = 'root';"

Complete example:

# Enter PostgreSQL
sudo -u postgres psql

# Change password
ALTER USER root WITH PASSWORD 'Cloudflare@2026@Secure&$';

# Exit
\q

# Verify connection
export PGPASSWORD='Cloudflare@2026@Secure&$'
psql -h 127.0.0.1 -U root -d postgres -c "SELECT 1"

# Clean up environment variables (optional)
unset PGPASSWORD

View user information

-- View all users
\du

-- View detailed user permissions
SELECT usename,
       usesuper AS is_superuser,
       usecreatedb AS can_create_db,
       passwd IS NOT NULL AS has_password
FROM pg_shadow;

-- View user password encryption method
SELECT usename,
       CASE
           WHEN passwd LIKE 'SCRAM-SHA-256%' THEN 'scram-sha-256'
           WHEN passwd LIKE 'md5%' THEN 'md5'
           ELSE 'unknown'
       END AS encryption_type
FROM pg_shadow;

Delete user

-- Delete user (need to first transfer or delete the objects they own)
DROP USER username;

-- Force delete (will delete the user and all objects owned by the user)
DROP OWNED BY username CASCADE;
DROP USER username;

Authentication Configuration

Detailed Explanation of pg_hba.conf Configuration

pg_hba.conf The file is located in the PostgreSQL data directory and controls the client authentication method.

# View configuration file location
sudo -u postgres psql -c "SHOW hba_file;"

# View current configuration
sudo -u postgres psql -c "SELECT * FROM pg_hba_file_rules;"

Authentication Method Description

Method	Description	Use cases
trust	Unconditional trust, no password required	Local development environment only
reject	Unconditional refusal	Security control
md5	MD5 password encryption	Compatible with legacy clients
scram-sha-256	SCRAM-SHA-256 encryption	Recommended to use (PG 10+)
password	Plaintext password transmission	Not recommended
peer	Use operating system user authentication	Local Area Connection
ident	Use ident service authentication	Deprecated

Configuration example

# /etc/postgresql/14/main/pg_hba.conf

# Local connections use peer authentication.
local   all             postgres                                peer

# Unix domain socket connection
local   all             all                                     scram-sha-256

# IPv4 Local Connection
host    all             all             127.0.0.1/32            scram-sha-256

# IPv6 Local Connection
host    all             all             ::1/128                 scram-sha-256

# 内网连接
host    all             all             192.168.1.0/24          scram-sha-256

# Public network connection (using stronger authentication)
host    all             all             0.0.0.0/0               scram-sha-256

Configuration takes effect

# Reload configuration (without interrupting existing connections)
sudo systemctl reload postgresql

# or restart the service
sudo systemctl restart postgresql

password policy

Supported password encryption methods

-- Check the current encryption method
SHOW password_encryption;

-- Set the encryption method
SET password_encryption = 'scram-sha-256';  -- Recommended
SET password_encryption = 'md5';             -- Compatible with older versions

Test results for special characters in passwords

After actual testing, PostgreSQL's support for password characters:

Usable characters

@ # $ & _ - + = | : ? . , / ~ `

Characters that cannot be used

!           # An exclamation mark will cause authentication to fail.
空格         # will cause connection string parsing errors
\n \r \t    # Control characters

Password strength recommendations

-- Strong password example (without using exclamation marks)
-- Length: 16+ characters
-- Includes: uppercase and lowercase letters, numbers, special characters
ALTER USER root WITH PASSWORD 'Cloudflare@2026#Secure$Postgres';

Practical example: a complete password setup and verification workflow

The following is a complete, tested password setup process:

Step 1: Enter PostgreSQL

sudo -u postgres psql

Step 2: Set password

-- Set a strong password (without exclamation marks)
ALTER USER root WITH PASSWORD 'Cloudflare@2026@Secure&$';

-- Check user password status
SELECT usename,
       passwd IS NOT NULL AS has_password,
       CASE
           WHEN passwd LIKE 'SCRAM-SHA-256%' THEN 'scram-sha-256'
           WHEN passwd LIKE 'md5%' THEN 'md5'
           ELSE 'unknown'
       END AS encryption_type
FROM pg_shadow
WHERE usename = 'root';

-- Expected output:
-- usename | has_password | encryption_type
-- ---------+--------------+-----------------
-- root    | t            | scram-sha-256

Step 3: Exit and test

-- Exit psql
\q

-- Test connection
export PGPASSWORD='Cloudflare@2026@Secure&$'
psql -h 127.0.0.1 -U root -d postgres -c "SELECT 'Connection successful' as status;"

-- Expected output:
--        status
-- -----------------------
--  Connection successful
-- (1 row)

-- Test multiple password variants
export PGPASSWORD='Cloudflare@2026#Secure'
psql -h 127.0.0.1 -U root -d postgres -c "SELECT 1"

export PGPASSWORD='Cloudflare#2026@Secure'
psql -h 127.0.0.1 -U root -d postgres -c "SELECT 1"

export PGPASSWORD='Cloudflare@2026$superman$&'
psql -h 127.0.0.1 -U root -d postgres -c "SELECT 1"

Step 4: Cleanup (optional)

# Clear environment variables
unset PGPASSWORD

# If using .pgpass, you can update the password file.
echo "127.0.0.1:5432:postgres:root:Cloudflare@2026@Secure&$" > ~/.pgpass
chmod 600 ~/.pgpass

Connection test

Why is testing needed?

After each password or configuration change, a connection test must be performed to ensure:

• The new password has taken effect.

• Authentication configuration is correct

• Network connection is normal.

Command line test

# Method 1: Direct connection
psql -h 127.0.0.1 -U username -d database

# Method 2: Set environment variables
export PGPASSWORD='your_password'
psql -h 127.0.0.1 -U username -d database -c "SELECT 1"

# Method 3: Use a connection string
psql "postgresql://username:[email protected]:5432/database"

# Method 4: Using the .pgpass file
echo "127.0.0.1:5432:database:username:password" > ~/.pgpass
chmod 600 ~/.pgpass
psql -h 127.0.0.1 -U username -d database

Basic testing process

# 1. Set the password environment variable
export PGPASSWORD='your_password_here'

# 2. Execute a simple query verification
psql -h 127.0.0.1 -U username -d database -c "SELECT 1"

# 3. If successful, test more complex queries
psql -h 127.0.0.1 -U username -d database -c "SELECT version();"

# 4. Clear the environment variables after testing is complete (optional)
unset PGPASSWORD

.pgpass file format

# Format:hostname:port:database:username:password
# Example
localhost:5432:*:postgres:admin123
127.0.0.1:5432:postgres:root:Cloudflare@2026#Secure
*.example.com:5432:*:appuser:apppass

Connection test script

#!/bin/bash
# test_pg_connection.sh

HOST="127.0.0.1"
PORT="5432"
USER="root"
DATABASE="postgres"
PASSWORD="Cloudflare@2026#Secure"

# Test connection
export PGPASSWORD="$PASSWORD"
if psql -h $HOST -p $PORT -U $USER -d $DATABASE -c "SELECT 1" > /dev/null 2>&1; then
    echo "✓ Connection successful"
else
    echo "✗ Connection failed"
fi
unset PGPASSWORD

Common troubleshooting

Problem 1: Password authentication failed

Error message:

FATAL: password authentication failed for user "root"

Troubleshooting steps:

# 1. Check whether the password is set correctly
sudo -u postgres psql -c "ALTER USER root WITH PASSWORD 'correct_password'"

# 2. Check the encryption method
sudo -u postgres psql -c "SHOW password_encryption"

# 3. Check the pg_hba.conf configuration
sudo grep "127.0.0.1" /etc/postgresql/14/main/pg_hba.conf

# 4. View detailed logs
sudo tail -50 /var/log/postgresql/postgresql-14-main.log

Problem 2: Issues caused by exclamation marks in bash

Error message:

-bash: !': event not found

Solution:

# Option 1: Disable history expansion
set +H
export PGPASSWORD='Cloudflare@2026#Secure!'
psql -h 127.0.0.1 -U root -d postgres -c "SELECT 1"
set -H

# Option 2: Use a .pgpass file (recommended)
echo "127.0.0.1:5432:postgres:root:Cloudflare@2026#Secure!" > ~/.pgpass
chmod 600 ~/.pgpass

# Option 3: Use interactive input
psql -h 127.0.0.1 -U root -d postgres -W

Problem 3: User duplicate name validation

PostgreSQL by default allows users with different names to connect, but some applications require users with the same name:

-- Create a database user with the same name as the system user
CREATE USER root WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE postgres TO root;

Go language connection example

Install driver

go get github.com/lib/pq
go get github.com/jackc/pgx/v5

Basic connection example

package main

import (
    "database/sql"
    "fmt"
    "log"
    "net/url"

    _ "github.com/lib/pq"
)

func main() {
    // Configuration parameters
    config := struct {
        Host     string
        Port     int
        User     string
        Password string
        Database string
    }{
        Host:     "127.0.0.1",
        Port:     5432,
        User:     "root",
        Password: "Cloudflare@2026#Secure", // Note: do not use exclamation marks
        Database: "postgres",
    }

    // Method 1: Direct concatenation (recommended)
    dsn := fmt.Sprintf(
        "host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
        config.Host, config.Port, config.User, config.Password, config.Database,
    )

    // Method 2: URL format
    encodedPassword := url.QueryEscape(config.Password)
    dsnURL := fmt.Sprintf(
        "postgres://%s:%s@%s:%d/%s?sslmode=disable",
        config.User, encodedPassword, config.Host, config.Port, config.Database,
    )

    // Connect to the database
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        log.Fatal("Failed to open connection:", err)
    }
    defer db.Close()

    // Test connection
    if err := db.Ping(); err != nil {
        log.Fatal("Failed to ping database:", err)
    }

    fmt.Println("✓ Successfully connected to PostgreSQL!")
}

Encapsulate the connection function

package postgres

import (
    "fmt"
    "net/url"
    "strings"
)

type Config struct {
    Host     string
    Port     int
    Username string
    Password string
    Database string
    SSLMode  bool
    TimeZone string
}

func NewDSN(cfg *Config) string {
    // URL-encode the username and password
    encodedUser := url.QueryEscape(cfg.Username)
    encodedPass := url.QueryEscape(cfg.Password)

    // Build connection address
    address := fmt.Sprintf("%s:%d", cfg.Host, cfg.Port)
    if strings.Contains(cfg.Host, ":") {
        address = fmt.Sprintf("[%s]:%d", cfg.Host, cfg.Port)
    }

    // Build query parameters
    params := url.Values{}
    if cfg.SSLMode {
        params.Set("sslmode", "enable")
    } else {
        params.Set("sslmode", "disable")
    }
    if cfg.TimeZone != "" {
        params.Set("TimeZone", cfg.TimeZone)
    }

    // Generate DSN
    if cfg.Database == "" {
        return fmt.Sprintf("postgres://%s:%s@%s/?%s",
            encodedUser, encodedPass, address, params.Encode())
    }

    return fmt.Sprintf("postgres://%s:%s@%s/%s?%s",
        encodedUser, encodedPass, address, cfg.Database, params.Encode())
}

Connection pool configuration

import (
    "context"
    "database/sql"
    "time"
)

func InitDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        return nil, err
    }

    // Configure the connection pool
    db.SetMaxOpenConns(25)                      // Maximum number of open connections
    db.SetMaxIdleConns(25)                      // Maximum idle connections
    db.SetConnMaxLifetime(5 * time.Minute)      // Maximum connection lifetime
    db.SetConnMaxIdleTime(3 * time.Minute)      // Maximum idle connection lifetime

    // Test connection
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()

    if err := db.PingContext(ctx); err != nil {
        return nil, err
    }

    return db, nil
}

Best practice recommendations

1. Security configuration

# Production environment configuration
# postgresql.conf
listen_addresses = '*'                    # Listen on all interfaces
password_encryption = scram-sha-256       # Use strong encryption
ssl = on                                   # Enable SSL
ssl_cert_file = 'server.crt'              # SSL certificate
ssl_key_file = 'server.key'               # SSL private key

# pg_hba.conf
# Disable remote root login
host    all             all             0.0.0.0/0               reject
# The application uses a dedicated user.
host    appdb           appuser         192.168.1.0/24          scram-sha-256

2. Password Policy

-- Create password policy function
CREATE OR REPLACE FUNCTION check_password_strength()
RETURNS trigger AS $$
BEGIN
    -- Password length at least 12 characters
    IF LENGTH(NEW.passwd) < 12 THEN
        RAISE EXCEPTION 'Password must be at least 12 characters';
    END IF;

    -- Password cannot contain exclamation marks
    IF NEW.passwd LIKE '%!%' THEN
        RAISE EXCEPTION 'Password cannot contain exclamation mark';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply password policy
CREATE CONSTRAINT TRIGGER check_password
AFTER UPDATE ON pg_authid
FOR EACH ROW
EXECUTE FUNCTION check_password_strength();

3. Monitoring and Logging

-- Enable connection logging
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_line_prefix = '%t [%p] %u@%d ';

-- Reload configuration
SELECT pg_reload_conf();

-- View current connections
SELECT pid, usename, application_name, client_addr, state
FROM pg_stat_activity
WHERE usename NOT IN ('postgres', 'replicator');

4. Troubleshooting Checklist

# 1. Check service status
sudo systemctl status postgresql

# 2. Check listening ports
sudo netstat -tlnp | grep 5432

# 3. Check the configuration file
sudo -u postgres psql -c "SHOW config_file"
sudo -u postgres psql -c "SHOW hba_file"

# 4. Test local connection
sudo -u postgres psql -c "SELECT 1"

# 5. Test the remote connection
psql -h your-host -U username -d database -c "SELECT 1"

# 6. View the error log
sudo tail -100 /var/log/postgresql/postgresql-*.log

Summary

Key points

• Enter the environment:sudo -u postgres psql It is the first step

• Password character limit: PostgreSQL does not allow exclamation marks in passwords !

• Authentication Configuration: recommended to use scram-sha-256 encryption method

• Connection test: Use .pgpass File avoids shell escaping issues

• Modification verification: The connection must be tested after every password change.

• Security practices: Create a dedicated database user, following the principle of least privilege.

• Troubleshooting Make good use of log files and troubleshoot configuration issues step by step.

Quick reference

# Quickly enter the command line
sudo -u postgres psql

# Exit
\q

# View user
\du

# Change password
ALTER USER username WITH PASSWORD 'newpass';

# Test connection
export PGPASSWORD='newpass'
psql -h 127.0.0.1 -U username -d database -c "SELECT 1"

# Quick Connect
psql "postgresql://user:pass@host:5432/db"

# Reset password
sudo -u postgres psql -c "ALTER USER username WITH PASSWORD 'newpass'"

# View configuration
sudo -u postgres psql -c "SHOW all" | grep -E "listen|port|ssl"

# Reload
sudo systemctl reload postgresql

Disclaimer

⚠️Disclaimer

The password examples in this article (such as Cloudflare@2026#Secure) are only used to demonstrate PostgreSQL connection and authentication mechanisms. These passwords are not sufficiently secure,Do not use in production environments or any actual systems..

Always follow password security best practices: use a random password of at least 16 characters that includes uppercase and lowercase letters, numbers, and special characters, and change it regularly.

Reference resources

• PostgreSQL Official Documentation

• pg_hba.conf documentation

• lib/pq driver documentation

• pgx driver documentation

📌This article is based on PostgreSQL 14.23 and practical troubleshooting experience, and applies to most PostgreSQL versions.