# 创建管理角色
$ psql -U postgres -c “CREATE ROLE fgedu_admin SUPERUSER LOGIN PASSWORD ‘password’;”
$ psql -U postgres -c “CREATE ROLE fgedu_dbadmin CREATEDB CREATEROLE LOGIN PASSWORD ‘password’;”

# 创建应用角色
$ psql -U postgres -c “CREATE ROLE fgedu_fgapplication LOGIN PASSWORD ‘password’;”
$ psql -U postgres -c “CREATE ROLE fgedu_readonly LOGIN PASSWORD ‘password’;”

# 创建组角色
$ psql -U postgres -c “CREATE ROLE fgedu_group_fgapp;”
$ psql -U postgres -c “CREATE ROLE fgedu_group_report;”

# 将用户角色添加到组角色
$ psql -U postgres -c “GRANT fgedu_group_fgapp TO fgedu_fgapplication;”
$ psql -U postgres -c “GRANT fgedu_group_report TO fgedu_readonly;”

# 授予组角色权限
$ psql -U postgres -d fgedudb -c “GRANT CONNECT ON DATABASE fgedudb TO fgedu_group_fgapp, fgedu_group_report;”
$ psql -U postgres -d fgedudb -c “GRANT USAGE ON SCHEMA public TO fgedu_group_fgapp, fgedu_group_report;”
$ psql -U postgres -d fgedudb -c “GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE fgedu_users TO fgedu_group_fgapp;”
$ psql -U postgres -d fgedudb -c “GRANT SELECT ON TABLE fgedu_users TO fgedu_group_report;”

# 验证角色和权限
$ psql -U postgres -c “\du”
List of roles
Role name | Attributes | Member of
——————-+————————————————————+———–
fgedu_admin | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
fgedu_fgapplication | | {fgedu_group_fgapp}
fgedu_dbadmin | Create role, Create DB | {}
fgedu_group_fgapp | Cannot login | {}
fgedu_group_report | Cannot login | {}
fgedu_readonly | | {fgedu_group_report}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

# 创建测试表
$ psql -U fgedu_admin -d fgedudb -c “CREATE TABLE fgedu_employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255),
salary DECIMAL(10,2),
department VARCHAR(100)
);”

# 创建HR角色
$ psql -U fgedu_admin -c “CREATE ROLE fgedu_hr LOGIN PASSWORD ‘password’;”

# 创建财务角色
$ psql -U fgedu_admin -c “CREATE ROLE fgedu_finance LOGIN PASSWORD ‘password’;”

# 授予HR角色权限（可以查看所有列）
$ psql -U fgedu_admin -d fgedudb -c “GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE fgedu_employees TO fgedu_hr;”

# 授予财务角色权限（只能查看salary列）
$ psql -U fgedu_admin -d fgedudb -c “GRANT SELECT (id, name, salary, department) ON TABLE fgedu_employees TO fgedu_finance;”

# 验证权限
$ psql -U fgedu_hr -d fgedudb -c “SELECT * FROM fgedu_employees;”
id | name | email | salary | department
—-+——+——-+——–+————
(0 rows)

$ psql -U fgedu_finance -d fgedudb -c “SELECT * FROM fgedu_employees;”
ERROR: permission denied for column email of relation fgedu_employees

$ psql -U fgedu_finance -d fgedudb -c “SELECT id, name, salary, department FROM fgedu_employees;”
id | name | salary | department
—-+——+——–+————
(0 rows)

# 启用审计日志
$ sudo vi /postgresql/fgdata/postgresql.conf
log_statement = ‘all’
log_line_prefix = ‘%t [%p]: [%l-1] user=%u,db=%d,fgapp=%a,client=%h ‘
log_min_duration_statement = 0

# 重启PostgreSQL
$ sudo systemctl restart postgresql

# 执行测试操作
$ psql -U fgedu_fgapp -d fgedudb -c “INSERT INTO fgedu_users (name, email) VALUES (‘Test User’, ‘test@fgedu.net.cn’);”
$ psql -U fgedu_fgapp -d fgedudb -c “UPDATE fgedu_users SET name = ‘Updated User’ WHERE id = 1;”
$ psql -U fgedu_fgapp -d fgedudb -c “DELETE FROM fgedu_users WHERE id = 1;”

# 查看审计日志
$ grep -i “fgedu_fgapp” /postgresql/fgdata/log/postgresql-2026-04-06_100000.log
2026-04-06 10:05:00 UTC [12345]: [1-1] user=fgedu_fgapp,db=fgedudb,fgapp=[unknown],client=192.168.1.100 LOG: statement: INSERT INTO fgedu_users (name, email) VALUES (‘Test User’, ‘test@fgedu.net.cn’);
2026-04-06 10:05:01 UTC [12345]: [2-1] user=fgedu_fgapp,db=fgedudb,fgapp=[unknown],client=192.168.1.100 LOG: duration: 1.234 ms statement: INSERT INTO fgedu_users (name, email) VALUES (‘Test User’, ‘test@fgedu.net.cn’);
2026-04-06 10:05:02 UTC [12345]: [3-1] user=fgedu_fgapp,db=fgedudb,fgapp=[unknown],client=192.168.1.100 LOG: statement: UPDATE fgedu_users SET name = ‘Updated User’ WHERE id = 1;
2026-04-06 10:05:02 UTC [12345]: [4-1] user=fgedu_fgapp,db=fgedudb,fgapp=[unknown],client=192.168.1.100 LOG: duration: 0.987 ms statement: UPDATE fgedu_users SET name = ‘Updated User’ WHERE id = 1;
2026-04-06 10:05:03 UTC [12345]: [5-1] user=fgedu_fgapp,db=fgedudb,fgapp=[unknown],client=192.168.1.100 LOG: statement: DELETE FROM fgedu_users WHERE id = 1;
2026-04-06 10:05:03 UTC [12345]: [6-1] user=fgedu_fgapp,db=fgedudb,fgapp=[unknown],client=192.168.1.100 LOG: duration: 0.765 ms statement: DELETE FROM fgedu_users WHERE id = 1;