备份与权限管理

# 导出单个数据库
pg_dump -U postgres -d myapp > myapp_backup.sql

# 自定义格式（支持并行恢复）
pg_dump -U postgres -d myapp -Fc -f myapp_backup.dump

# 只导出表结构
pg_dump -U postgres -d myapp --schema-only -f schema.sql

# 只导出数据
pg_dump -U postgres -d myapp --data-only -f data.sql

# 导出指定表
pg_dump -U postgres -d myapp -t users -t orders -f users_orders.sql

# 恢复
psql -U postgres -d myapp < myapp_backup.sql

# 自定义格式恢复（支持并行 -j）
pg_restore -U postgres -d myapp -j 4 myapp_backup.dump

# postgresql.conf —— WAL 归档配置
wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'
```bash

```bash
# 基础备份
pg_basebackup -U postgres -D /backup/base -Ft -z -P

# 恢复时配合 recovery.conf 指定恢复到哪个时间点
# restore_command = 'cp /archive/%f %p'
# recovery_target_time = '2024-01-15 14:30:00'

-- 创建登录角色（= 用户）
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';

-- 创建只读角色
CREATE ROLE read_only;

-- 授予权限
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- 将只读角色赋给用户
GRANT read_only TO app_user;

-- 自动对新表授权
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO read_only;

-- 撤销权限
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM app_user;

-- 删除角色
DROP ROLE read_only;

# 格式：TYPE  DATABASE  USER  ADDRESS  METHOD

# 本地连接：信任
local   all   all            trust

# 本地连接：密码
local   all   all            scram-sha-256

# 局域网：密码认证
host    all   all   192.168.0.0/24   scram-sha-256

# 特定数据库只读
host    myapp read_only  10.0.0.0/8   scram-sha-256

# 拒绝所有外部连接（兜底规则）
host    all  all  0.0.0.0/0   reject
```bash

修改后重新加载（无需重启）：

```bash
pg_ctl reload
# 或
SELECT pg_reload_conf();

-- 1. 修改超级用户密码
ALTER USER postgres PASSWORD 'strong_password';

-- 2. 禁用远程超级用户登录
-- 在 pg_hba.conf 中只允许 postgres 本地连接

-- 3. 检查所有用户密码有效期
SELECT usename, valuntil FROM pg_user;

-- 4. 检查权限过大的角色
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'public';