成为优秀DBA工程师：PostgreSQL安全加固与日志分析全攻略

-- 创建只读角色  

CREATE ROLE read_only;  

-- 授予查询权限  

GRANTCONNECTON DATABASE mydb TO read_only;  

GRANT USAGE ON SCHEMA public TO read_only;  

GRANTSELECTONALL TABLES IN SCHEMA public TO read_only;  

-- 只允许用户修改某张表  

GRANTUPDATEON orders TO order_manager;  

-- 收回删除权限  

REVOKEDELETEON customers FROM sales_team;  

-- 查看所有用户权限  

SELECT*FROM information_schema.role_table_grants;  

SELECT grantee, table_name, privilege_type  

FROM information_schema.table_privileges  

WHERE grantee ='dev_user'

AND table_name ='orders'

AND privilege_type ='DELETE';  

-- 查看角色成员  

SELECT roleid, memberFROM pg_auth_members;  

# Ubuntu/Debian  

sudo apt-get install pgbadger  

wget https://github.com/darold/pgbadger/archive/refs/tags/v12.0.tar.gz  

tar -zxvf v12.0.tar.gz  

cd pgbadger-12.0  

perl Makefile.PL  

make && sudo make install  

# 日志格式设为CSV（方便解析）  

log_destination = 'csvlog'

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d.log'



# 记录关键信息  

log_checkpoints = on

log_connections = on

log_disconnections = on

log_lock_waits = on

log_min_duration_statement = 1000  -- 记录超过1秒的慢查询  

# 分析当天日志  

pgbadger /var/lib/pgsql/data/pg_log/postgresql-*.log -o report.html  



# 分析指定时间段的日志  

pgbadger --begin='2025-04-20 00:00:00' --end='2025-04-24 23:59:59' *.log

0 2 * * * /usr/bin/pgbadger /var/lib/pgsql/data/pg_log/postgresql-$(date -d "yesterday" +"%Y-%m-%d").log -o /var/www/html/report.html