PostgreSQL DBA Guide: Permission Minimization, Log Analysis with pgBadger, and Security Hardening

1. Why DBAs need security hardening and log analysis?

As a database administrator (DBA), your core task is to keep the database both secure and efficient.

Security hardening : like locking the front door to prevent data theft.

Log analysis : like installing surveillance cameras to monitor issues.

Today we start from minimal‑privilege configuration and the pgBadger log tool to teach you how to become a reliable DBA.

2. Configure PostgreSQL account permission minimization strategy

Principle : grant users only the minimum permissions they need, just like giving a key that opens only the required doors.

1. Create "minimum privilege" role

Example: you want user report_user to read data only, without delete or update rights:

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

-- 授予查询权限
GRANT CONNECT ON DATABASE mydb TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

Key points :

SELECT : allows reading data but not modifying it.

Newly created tables do not inherit these permissions automatically; you must grant them or set default privileges.

2. Grant precise permissions

Avoid using ALL PRIVILEGES ; grant only what is required:

-- 只允许用户修改某张表
GRANT UPDATE ON orders TO order_manager;

3. Revoke redundant permissions

If a user was mistakenly given extra rights, revoke them immediately:

-- 收回删除权限
REVOKE DELETE ON customers FROM sales_team;

3. How to query redundant permissions?

Goal : find users that have extra "keys" and clean them up.

1. Check user permission list

-- 查看所有用户权限
SELECT * FROM information_schema.role_table_grants;

2. Identify excessive table permissions

Example: user dev_user should not have DELETE on table orders :

SELECT grantee, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'dev_user'
  AND table_name = 'orders'
  AND privilege_type = 'DELETE';

If anomalies are found, use REVOKE to remove the permission.

3. Check role inheritance

Roles may inherit extra permissions from other roles:

-- 查看角色成员
SELECT roleid, member FROM pg_auth_members;

If you discover that dev_user belongs to a high‑privilege role, adjust it promptly.

4. pgBadger log analysis tool: step‑by‑step tutorial

1. Install pgBadger

Method 1 (simple):

sudo apt-get install pgbadger

Method 2 (manual):

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

Verify installation with pgbadger --version .

2. Configure PostgreSQL logging

Edit postgresql.conf to enable detailed CSV logs:

# 日志格式设为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 秒的慢查询

Restart the database to apply changes: pg_ctl restart .

3. Generate analysis report

# 分析当天日志
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

Report highlights include:

Slow query Top 10 : which queries are slowing the database.

High‑frequency queries : candidates for optimization or caching.

Lock waits : operations causing blocking.

4. Schedule automatic daily analysis

Use crontab to run pgBadger each early morning for the previous day's 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

5. Summary: Continuous DBA optimization

Permission minimization : regularly audit user rights to avoid permission bloat.

Log analysis : generate weekly pgBadger reports to proactively discover issues.

Security and performance balance : hardening is the defense, logs are the eyes; both are indispensable.

Remember: an excellent DBA is not a fire‑fighter but an architect who prevents problems before they arise.

All commands are valid for PostgreSQL 9.2.4 and later; test in a non‑production environment before applying to production.