PostgreSQL 实战指南：从连接到认证的完整解决方案

前言

在使用 PostgreSQL 的过程中，我们经常会遇到各种连接和认证问题。本文将基于实际排错经验，系统地介绍 PostgreSQL 的用户管理、认证配置、密码策略以及常见问题的解决方案。

环境准备

进入 PostgreSQL 命令行

在进行任何操作之前，首先需要进入 PostgreSQL 的命令行环境。

bash

# 方法1：使用 postgres 系统用户进入（推荐）
sudo -u postgres psql

# 方法2：切换到 postgres 用户后进入
sudo su - postgres
psql

# 方法3：如果已经配置了密码，直接连接
psql -h localhost -U username -d database

注意事项：

sudo -u postgres psql 会直接以 postgres 超级用户身份进入
看到的提示符会变成 postgres=#，表示你已经成功进入
输入 \q 可以退出

bash

# 成功进入后的样子
postgres=#

用户管理

创建用户

sql

-- 创建普通用户
CREATE USER username WITH PASSWORD 'password';

-- 创建超级用户
CREATE USER admin WITH PASSWORD 'admin123' SUPERUSER;

-- 创建带过期时间的用户
CREATE USER temp_user WITH PASSWORD 'temp123' VALID UNTIL '2024-12-31';

修改用户密码

sql

-- 修改密码
ALTER USER username WITH PASSWORD 'new_password';

-- 强制用户下次登录时修改密码
ALTER USER username WITH PASSWORD 'new_password' VALID UNTIL 'infinity';

修改密码后验证

修改密码后，务必进行连接测试，确保密码生效：

bash

# 1. 退出 psql
\q

# 2. 使用新密码测试连接
export PGPASSWORD='Cloudflare@2026@Secure&$'
psql -h 127.0.0.1 -U root -d postgres -c "SELECT 'Connection successful' as status;"

# 成功输出示例：
#        status
# -----------------------
#  Connection successful
# (1 row)

# 3. 如果连接失败，检查密码是否正确
# 可以重新进入 psql 查看用户信息
sudo -u postgres psql -c "SELECT usename, passwd IS NOT NULL FROM pg_shadow WHERE usename = 'root';"

完整示例：

bash

# 进入 PostgreSQL
sudo -u postgres psql

# 修改密码
ALTER USER root WITH PASSWORD 'Cloudflare@2026@Secure&$';

# 退出
\q

# 验证连接
export PGPASSWORD='Cloudflare@2026@Secure&$'
psql -h 127.0.0.1 -U root -d postgres -c "SELECT 1"

# 清理环境变量（可选）
unset PGPASSWORD

查看用户信息

sql

-- 查看所有用户
\du

-- 查看用户详细权限
SELECT usename,
       usesuper AS is_superuser,
       usecreatedb AS can_create_db,
       passwd IS NOT NULL AS has_password
FROM pg_shadow;

-- 查看用户密码加密方式
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;

删除用户

sql

-- 删除用户（需要先转移或删除其拥有的对象）
DROP USER username;

-- 强制删除（会删除用户及其拥有的所有对象）
DROP OWNED BY username CASCADE;
DROP USER username;

认证配置

pg_hba.conf 配置详解

pg_hba.conf 文件位于 PostgreSQL 数据目录下，控制客户端认证方式。

bash

# 查看配置文件位置
sudo -u postgres psql -c "SHOW hba_file;"

# 查看当前配置
sudo -u postgres psql -c "SELECT * FROM pg_hba_file_rules;"

认证方法说明

方法	说明	使用场景
`trust`	无条件信任，无需密码	仅限本地开发环境
`reject`	无条件拒绝	安全控制
`md5`	MD5 密码加密	兼容旧客户端
`scram-sha-256`	SCRAM-SHA-256 加密	推荐使用（PG 10+）
`password`	明文密码传输	不推荐
`peer`	使用操作系统用户认证	本地连接
`ident`	使用 ident 服务认证	已废弃

配置示例

conf

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

# 本地连接使用 peer 认证
local   all             postgres                                peer

# Unix domain socket 连接
local   all             all                                     scram-sha-256

# IPv4 本地连接
host    all             all             127.0.0.1/32            scram-sha-256

# IPv6 本地连接
host    all             all             ::1/128                 scram-sha-256

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

# 公网连接（使用更强的认证）
host    all             all             0.0.0.0/0               scram-sha-256

配置生效

bash

# 重新加载配置（不中断现有连接）
sudo systemctl reload postgresql

# 或重启服务
sudo systemctl restart postgresql

密码策略

支持的密码加密方式

sql

-- 查看当前加密方式
SHOW password_encryption;

-- 设置加密方式
SET password_encryption = 'scram-sha-256';  -- 推荐
SET password_encryption = 'md5';             -- 兼容旧版

密码中的特殊字符测试结果

经过实际测试，PostgreSQL 对密码字符的支持情况：

可以使用的字符

bash

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

不能使用的字符

bash

!           # 感叹号会导致认证失败
空格         # 会导致连接字符串解析错误
\n \r \t    # 控制字符

密码强度建议

sql

-- 强密码示例（不使用感叹号）
-- 长度：16+ 字符
-- 包含：大小写字母、数字、特殊字符
ALTER USER root WITH PASSWORD 'Cloudflare@2026#Secure$Postgres';

实战示例：完整的密码设置与验证流程

以下是一个完整的、经过测试的密码设置流程：

步骤1：进入 PostgreSQL

bash

sudo -u postgres psql

步骤2：设置密码

sql

-- 设置一个强密码（不含感叹号）
ALTER USER root WITH PASSWORD 'Cloudflare@2026@Secure&$';

-- 查看用户密码状态
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';

-- 预期输出：
-- usename | has_password | encryption_type
-- ---------+--------------+-----------------
-- root    | t            | scram-sha-256

步骤3：退出并测试

bash

-- 退出 psql
\q

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

-- 预期输出：
--        status
-- -----------------------
--  Connection successful
-- (1 row)

-- 测试多个密码变体
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"

步骤4：清理（可选）

bash

# 清除环境变量
unset PGPASSWORD

# 如果使用 .pgpass，可以更新密码文件
echo "127.0.0.1:5432:postgres:root:Cloudflare@2026@Secure&$" > ~/.pgpass
chmod 600 ~/.pgpass

连接测试

为什么需要测试？

每次修改密码或配置后，都必须进行连接测试，以确保：

新密码已生效
认证配置正确
网络连接正常

命令行测试

bash

# 方法1：直接连接
psql -h 127.0.0.1 -U username -d database

# 方法2：设置环境变量
export PGPASSWORD='your_password'
psql -h 127.0.0.1 -U username -d database -c "SELECT 1"

# 方法3：使用连接字符串
psql "postgresql://username:password@127.0.0.1:5432/database"

# 方法4：使用 .pgpass 文件
echo "127.0.0.1:5432:database:username:password" > ~/.pgpass
chmod 600 ~/.pgpass
psql -h 127.0.0.1 -U username -d database

基础测试流程

bash

# 1. 设置密码环境变量
export PGPASSWORD='your_password_here'

# 2. 执行简单查询验证
psql -h 127.0.0.1 -U username -d database -c "SELECT 1"

# 3. 如果成功，测试更复杂的查询
psql -h 127.0.0.1 -U username -d database -c "SELECT version();"

# 4. 测试完成后清除环境变量（可选）
unset PGPASSWORD

.pgpass 文件格式

bash

# 格式：hostname:port:database:username:password
# 示例
localhost:5432:*:postgres:admin123
127.0.0.1:5432:postgres:root:Cloudflare@2026#Secure
*.example.com:5432:*:appuser:apppass

连接测试脚本

bash

#!/bin/bash
# test_pg_connection.sh

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

# 测试连接
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

常见问题排查

问题1：密码认证失败

错误信息：

FATAL: password authentication failed for user "root"

排查步骤：

bash

# 1. 检查密码是否正确设置
sudo -u postgres psql -c "ALTER USER root WITH PASSWORD 'correct_password'"

# 2. 检查加密方式
sudo -u postgres psql -c "SHOW password_encryption"

# 3. 检查 pg_hba.conf 配置
sudo grep "127.0.0.1" /etc/postgresql/14/main/pg_hba.conf

# 4. 查看详细日志
sudo tail -50 /var/log/postgresql/postgresql-14-main.log

问题2：bash 中感叹号导致的问题

错误信息：

-bash: !': event not found

解决方案：

bash

# 方案1：禁用历史扩展
set +H
export PGPASSWORD='Cloudflare@2026#Secure!'
psql -h 127.0.0.1 -U root -d postgres -c "SELECT 1"
set -H

# 方案2：使用 .pgpass 文件（推荐）
echo "127.0.0.1:5432:postgres:root:Cloudflare@2026#Secure!" > ~/.pgpass
chmod 600 ~/.pgpass

# 方案3：使用交互式输入
psql -h 127.0.0.1 -U root -d postgres -W

问题3：用户同名校验

PostgreSQL 默认允许不同名的用户连接，但某些应用需要同名用户：

sql

-- 创建与系统用户同名的数据库用户
CREATE USER root WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE postgres TO root;

Go语言连接示例

安装驱动

bash

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

基础连接示例

package main

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

    _ "github.com/lib/pq"
)

func main() {
    // 配置参数
    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", // 注意：不要使用感叹号
        Database: "postgres",
    }

    // 方法1：直接拼接（推荐）
    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,
    )

    // 方法2：URL 格式
    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,
    )

    // 连接数据库
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        log.Fatal("Failed to open connection:", err)
    }
    defer db.Close()

    // 测试连接
    if err := db.Ping(); err != nil {
        log.Fatal("Failed to ping database:", err)
    }

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

封装连接函数

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 编码用户名和密码
    encodedUser := url.QueryEscape(cfg.Username)
    encodedPass := url.QueryEscape(cfg.Password)

    // 构建连接地址
    address := fmt.Sprintf("%s:%d", cfg.Host, cfg.Port)
    if strings.Contains(cfg.Host, ":") {
        address = fmt.Sprintf("[%s]:%d", cfg.Host, cfg.Port)
    }

    // 构建查询参数
    params := url.Values{}
    if cfg.SSLMode {
        params.Set("sslmode", "enable")
    } else {
        params.Set("sslmode", "disable")
    }
    if cfg.TimeZone != "" {
        params.Set("TimeZone", cfg.TimeZone)
    }

    // 生成 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())
}

连接池配置

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

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

    // 配置连接池
    db.SetMaxOpenConns(25)                      // 最大打开连接数
    db.SetMaxIdleConns(25)                      // 最大空闲连接数
    db.SetConnMaxLifetime(5 * time.Minute)      // 连接最大生命周期
    db.SetConnMaxIdleTime(3 * time.Minute)      // 空闲连接最大存活时间

    // 测试连接
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()

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

    return db, nil
}

最佳实践建议

1. 安全配置

bash

# 生产环境配置
# postgresql.conf
listen_addresses = '*'                    # 监听所有接口
password_encryption = scram-sha-256       # 使用强加密
ssl = on                                   # 启用 SSL
ssl_cert_file = 'server.crt'              # SSL 证书
ssl_key_file = 'server.key'               # SSL 私钥

# pg_hba.conf
# 禁止远程 root 登录
host    all             all             0.0.0.0/0               reject
# 应用使用专用用户
host    appdb           appuser         192.168.1.0/24          scram-sha-256

2. 密码策略

sql

-- 创建密码策略函数
CREATE OR REPLACE FUNCTION check_password_strength()
RETURNS trigger AS $$
BEGIN
    -- 密码长度至少 12 位
    IF LENGTH(NEW.passwd) < 12 THEN
        RAISE EXCEPTION 'Password must be at least 12 characters';
    END IF;

    -- 密码不能包含感叹号
    IF NEW.passwd LIKE '%!%' THEN
        RAISE EXCEPTION 'Password cannot contain exclamation mark';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 应用密码策略
CREATE CONSTRAINT TRIGGER check_password
AFTER UPDATE ON pg_authid
FOR EACH ROW
EXECUTE FUNCTION check_password_strength();

3. 监控和日志

sql

-- 启用连接日志
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_line_prefix = '%t [%p] %u@%d ';

-- 重新加载配置
SELECT pg_reload_conf();

-- 查看当前连接
SELECT pid, usename, application_name, client_addr, state
FROM pg_stat_activity
WHERE usename NOT IN ('postgres', 'replicator');

4. 故障排查清单

bash

# 1. 检查服务状态
sudo systemctl status postgresql

# 2. 检查监听端口
sudo netstat -tlnp | grep 5432

# 3. 检查配置文件
sudo -u postgres psql -c "SHOW config_file"
sudo -u postgres psql -c "SHOW hba_file"

# 4. 测试本地连接
sudo -u postgres psql -c "SELECT 1"

# 5. 测试远程连接
psql -h your-host -U username -d database -c "SELECT 1"

# 6. 查看错误日志
sudo tail -100 /var/log/postgresql/postgresql-*.log

总结

关键要点

进入环境：sudo -u postgres psql 是第一步
密码字符限制：PostgreSQL 不允许在密码中使用感叹号 !
认证配置：推荐使用 scram-sha-256 加密方式
连接测试：使用 .pgpass 文件避免 shell 转义问题
修改验证：每次修改密码后必须测试连接
安全实践：创建专用数据库用户，遵循最小权限原则
故障排查：善用日志文件，逐步排查配置问题

快速参考

bash

# 快速进入命令行
sudo -u postgres psql

# 退出
\q

# 查看用户
\du

# 修改密码
ALTER USER username WITH PASSWORD 'newpass';

# 测试连接
export PGPASSWORD='newpass'
psql -h 127.0.0.1 -U username -d database -c "SELECT 1"

# 快速连接
psql "postgresql://user:pass@host:5432/db"

# 重置密码
sudo -u postgres psql -c "ALTER USER username WITH PASSWORD 'newpass'"

# 查看配置
sudo -u postgres psql -c "SHOW all" | grep -E "listen|port|ssl"

# 重新加载
sudo systemctl reload postgresql

免责说明

⚠️ 免责声明
本文中的密码示例（如 Cloudflare@2026#Secure）仅用于演示 PostgreSQL 连接和认证机制。这些密码不具备足够的安全性，请勿在生产环境或任何实际系统中使用。
请始终遵循密码安全最佳实践：使用长度至少 16 位、包含大小写字母、数字和特殊字符的随机密码，并定期更换。

参考资源

📌 本文基于 PostgreSQL 14.23 和实际排错经验总结，适用于大多数 PostgreSQL 版本。

PostgreSQL 实战指南：从连接到认证的完整解决方案 ​

前言 ​

环境准备 ​

进入 PostgreSQL 命令行 ​

用户管理 ​

创建用户 ​

修改用户密码 ​

修改密码后验证 ​

查看用户信息 ​

删除用户 ​

认证配置 ​

pg_hba.conf 配置详解 ​

认证方法说明 ​

配置示例 ​

配置生效 ​

密码策略 ​

支持的密码加密方式 ​

密码中的特殊字符测试结果 ​

可以使用的字符 ​

不能使用的字符 ​

密码强度建议 ​

实战示例：完整的密码设置与验证流程 ​

步骤1：进入 PostgreSQL ​

步骤2：设置密码 ​

步骤3：退出并测试 ​

步骤4：清理（可选） ​

连接测试 ​

为什么需要测试？ ​

命令行测试 ​

基础测试流程 ​

.pgpass 文件格式 ​

连接测试脚本 ​

常见问题排查 ​

问题1：密码认证失败 ​

问题2：bash 中感叹号导致的问题 ​

问题3：用户同名校验 ​

Go语言连接示例 ​

安装驱动 ​

基础连接示例 ​

封装连接函数 ​

连接池配置 ​

最佳实践建议 ​

1. 安全配置 ​

2. 密码策略 ​

3. 监控和日志 ​

4. 故障排查清单 ​

总结 ​

关键要点 ​

快速参考 ​

免责说明 ​

参考资源 ​

PostgreSQL 实战指南：从连接到认证的完整解决方案

前言

环境准备

进入 PostgreSQL 命令行

用户管理

创建用户

修改用户密码

修改密码后验证

查看用户信息

删除用户

认证配置

pg_hba.conf 配置详解

认证方法说明

配置示例

配置生效

密码策略

支持的密码加密方式

密码中的特殊字符测试结果

可以使用的字符

不能使用的字符

密码强度建议

实战示例：完整的密码设置与验证流程

步骤1：进入 PostgreSQL

步骤2：设置密码

步骤3：退出并测试

步骤4：清理（可选）

连接测试

为什么需要测试？

命令行测试

基础测试流程

.pgpass 文件格式

连接测试脚本

常见问题排查

问题1：密码认证失败

问题2：bash 中感叹号导致的问题

问题3：用户同名校验

Go语言连接示例

安装驱动

基础连接示例

封装连接函数

连接池配置

最佳实践建议

1. 安全配置

2. 密码策略

3. 监控和日志

4. 故障排查清单

总结

关键要点

快速参考

免责说明

参考资源