PostgreSQL教程FG084-PG角色管理实战：企业级权限规划方案
本文档风哥主要介绍企业级PostgreSQL角色管理和权限规划方案，包括角色层次结构设计、权限模型、角色创建与管理、权限分配与管理等内容。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 企业级PostgreSQL角色管理的概念
企业级PostgreSQL角色管理是指在企业环境中，通过合理的角色设计和权限分配，实现对数据库资源的有效访问控制。企业级角色管理需要考虑安全性、可管理性和可扩展性，确保权限分配符合最小权限原则，同时满足业务需求。更多视频教程www.fgedu.net.cn
from oracle:www.itpux.com
- 基于角色的访问控制（RBAC）
- 角色层次结构设计
- 精细的权限粒度
- 权限继承机制
- 定期权限审计
1.2 PostgreSQL角色层次结构
PostgreSQL角色层次结构是指通过角色继承关系，构建一个层次化的角色体系。通过角色层次结构，可以实现权限的集中管理和继承，减少权限管理的复杂度。
– 超级用户角色：pgsql – 系统管理员角色：sys_admin
– 数据库管理员角色：db_admin
– 开发人员角色：dev_fgedu
– 测试人员角色：test_fgedu
– 安全管理员角色：sec_admin
– 审计人员角色：audit_fgedu
– 备份管理员角色：backup_admin
– 应用角色：fgapp_roles
– 只读角色：read_only
– 读写角色：read_write
– 管理角色：fgapp_admin
– 业务角色：biz_roles
– HR角色：hr_role
– 财务角色：finance_role
– 销售角色：fgfgfgfgsales_role
1.3 企业级权限模型
企业级权限模型是指在企业环境中，通过合理的权限设计，实现对数据库资源的有效访问控制。企业级权限模型需要考虑业务需求、安全要求和管理复杂度，确保权限分配符合最小权限原则。
1. 系统级权限：数据库服务器级别的权限
– 超级用户权限
– 系统管理权限
– 数据库创建权限
– 角色创建权限
2. 数据库级权限：数据库级别的权限
– 数据库连接权限
– 模式创建权限
– 临时表创建权限
3. 模式级权限：模式级别的权限
– 模式使用权限
– 模式创建权限
4. 对象级权限：数据库对象级别的权限
– 表权限：SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
– 视图权限：SELECT, INSERT, UPDATE, DELETE, TRIGGER
– 函数权限：EXECUTE
– 序列权限：USAGE, SELECT, UPDATE
5. 列级权限：表列级别的权限
– 列的SELECT, INSERT, UPDATE权限
6. 行级权限：表行级别的权限
– 通过行级安全策略（RLS）实现
Part02-生产环境规划与建议
2.1 企业级PostgreSQL角色规划
企业级PostgreSQL角色规划要点：
1. 分析业务需求：了解不同部门和用户的权限需求
2. 设计角色层次结构：基于业务需求设计角色层次
3. 定义角色职责：明确每个角色的职责和权限范围
4. 确定角色关系：建立角色之间的继承关系
5. 制定角色管理流程：建立角色创建、修改、删除的流程
# 角色类型
– 系统角色：系统级管理角色
– 超级用户角色
– 系统管理员角色
– 安全管理员角色
– 备份管理员角色
– 应用角色：应用级角色
– 应用管理员角色
– 应用用户角色
– 只读角色
– 读写角色
– 业务角色：业务级角色
– 部门角色
– 功能角色
– 数据访问角色
# 角色规划原则
– 最小权限原则：只授予必要的权限
– 职责分离原则：不同职责的权限分离
– 层次化原则：通过角色继承实现权限层次
– 标准化原则：统一角色命名和权限分配标准
– 可审计原则：确保权限变更可追溯
2.2 企业级权限规划
企业级权限规划要点：
1. 识别敏感数据：识别需要保护的敏感数据
2. 分析访问需求：分析不同用户对数据的访问需求
3. 设计权限粒度：根据访问需求设计权限粒度
4. 制定权限分配策略：制定权限分配的标准和流程
5. 建立权限审计机制：定期审计权限分配情况
# 权限分配策略
– 基于角色的权限分配：通过角色分配权限
– 基于部门的权限分配：按部门分配权限
– 基于功能的权限分配：按功能模块分配权限
– 基于数据的权限分配：按数据范围分配权限
# 权限管理流程
– 权限申请：用户提交权限申请
– 权限审批：相关人员审批权限申请
– 权限授予：DBA授予权限
– 权限审计：定期审计权限分配
– 权限回收：回收不必要的权限
# 权限规划原则
– 最小权限原则：只授予必要的权限
– 权限分离原则：不同职责的权限分离
– 权限继承原则：通过角色继承简化权限管理
– 权限审计原则：定期审计权限分配
– 权限变更原则：权限变更需要审批和记录
2.3 角色命名规范
角色命名规范要点：
– 系统角色：sys_xxx
– 系统管理员：sys_admin
– 安全管理员：sys_sec_admin
– 备份管理员：sys_backup_admin
– 应用角色：fgapp_xxx
– 应用管理员：fgapp_admin
– 应用用户：fgapp_fgedu
– 只读角色：fgapp_readonly
– 读写角色：fgapp_readwrite
– 业务角色：biz_xxx
– HR角色：biz_hr
– 财务角色：biz_finance
– 销售角色：biz_fgfgfgfgsales
– 部门角色：dept_xxx
– 技术部：dept_tech
– 市场部：dept_marketing
– 运营部：dept_operation
– 功能角色：func_xxx
– 报表角色：func_report
– 数据导入角色：func_import
– 数据导出角色：func_export
# 命名原则
– 清晰明确：名称应清晰反映角色的职责
– 一致统一：命名风格应保持一致
– 简洁明了：名称应简洁明了，避免过长
– 易于理解：名称应易于理解和记忆
– 可扩展性：命名应具有可扩展性，便于添加新角色
Part03-生产环境项目实施方案
3.1 企业级PostgreSQL角色创建与管理
3.1.1 创建系统角色
$ psql -U pgsql
# 创建系统管理员角色
postgres=# CREATE ROLE sys_admin WITH
LOGIN
PASSWORD ‘SysAdmin123’
CREATEDB
CREATEROLE
NOREPLICATION
INHERIT;
CREATE ROLE
# 创建安全管理员角色
postgres=# CREATE ROLE sys_sec_admin WITH
LOGIN
PASSWORD ‘SecAdmin123’
NOCREATEDB
NOCREATEROLE
NOREPLICATION
INHERIT;
CREATE ROLE
# 创建备份管理员角色
postgres=# CREATE ROLE sys_backup_admin WITH
LOGIN
PASSWORD ‘BackupAdmin123’
NOCREATEDB
NOCREATEROLE
REPLICATION
INHERIT;
CREATE ROLE
# 授予系统管理员必要的权限
postgres=# GRANT ALL PRIVILEGES ON DATABASE pgsql TO sys_admin;
GRANT
# 授予安全管理员必要的权限
postgres=# GRANT CONNECT ON DATABASE pgsql TO sys_sec_admin;
GRANT
# 授予备份管理员必要的权限
postgres=# GRANT CONNECT ON DATABASE pgsql TO sys_backup_admin;
GRANT
3.1.2 创建应用角色
postgres=# CREATE ROLE fgapp_roles NOLOGIN;
CREATE ROLE
# 创建应用管理员角色
postgres=# CREATE ROLE fgapp_admin WITH
LOGIN
PASSWORD ‘AppAdmin123’
NOCREATEDB
NOCREATEROLE
NOREPLICATION
INHERIT;
CREATE ROLE
# 创建只读角色
postgres=# CREATE ROLE fgapp_readonly WITH
LOGIN
PASSWORD ‘AppReadonly123’
NOCREATEDB
NOCREATEROLE
NOREPLICATION
INHERIT;
CREATE ROLE
# 创建读写角色
postgres=# CREATE ROLE fgapp_readwrite WITH
LOGIN
PASSWORD ‘AppReadwrite123’
NOCREATEDB
NOCREATEROLE
NOREPLICATION
INHERIT;
CREATE ROLE
# 将应用角色添加到应用角色组
postgres=# GRANT fgapp_roles TO fgapp_admin, fgapp_readonly, fgapp_readwrite;
GRANT ROLE
3.1.3 创建业务角色
postgres=# CREATE ROLE biz_roles NOLOGIN;
CREATE ROLE
# 创建HR角色
postgres=# CREATE ROLE biz_hr WITH
LOGIN
PASSWORD ‘BizHR123’
NOCREATEDB
NOCREATEROLE
NOREPLICATION
INHERIT;
CREATE ROLE
# 创建财务角色
postgres=# CREATE ROLE biz_finance WITH
LOGIN
PASSWORD ‘BizFinance123’
NOCREATEDB
NOCREATEROLE
NOREPLICATION
INHERIT;
CREATE ROLE
# 创建销售角色
postgres=# CREATE ROLE biz_fgfgfgfgsales WITH
LOGIN
PASSWORD ‘BizSales123’
NOCREATEDB
NOCREATEROLE
NOREPLICATION
INHERIT;
CREATE ROLE
# 将业务角色添加到业务角色组
postgres=# GRANT biz_roles TO biz_hr, biz_finance, biz_fgfgfgfgsales;
GRANT ROLE
3.2 企业级权限分配与管理
3.2.1 分配数据库权限
postgres=# CREATE DATABASE fgedu_enterprise;
CREATE DATABASE
# 切换到企业数据库
postgres=# \c fgedu_enterprise
# 创建业务表
fgedu_enterprise=# CREATE TABLE fgedu_employees (
id serial PRIMARY KEY,
name varchar(50),
department varchar(50),
position varchar(50),
salary numeric(10,2),
hire_date date
);
CREATE TABLE fgedu_
fgedu_enterprise=# CREATE TABLE fgedu_finances (
id serial PRIMARY KEY,
employee_id integer REFERENCES fgedu_employees(id),
transaction_date date,
amount numeric(10,2),
description varchar(255),
type varchar(20)
);
CREATE TABLE fgedu_
fgedu_enterprise=# CREATE TABLE fgedu_fgfgfgfgsales (
id serial PRIMARY KEY,
employee_id integer REFERENCES fgedu_employees(id),
sale_date date,
customer_name varchar(100),
amount numeric(10,2),
status varchar(20)
);
CREATE TABLE fgedu_# 授予数据库连接权限
fgedu_enterprise=# GRANT CONNECT ON DATABASE fgedu_enterprise TO fgapp_roles, biz_roles;
GRANT
# 授予模式使用权限
fgedu_enterprise=# GRANT USAGE ON SCHEMA public TO fgapp_roles, biz_roles;
GRANT
# 授予表权限
# 授予HR角色权限
fgedu_enterprise=# GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_employees TO biz_hr;
GRANT
fgedu_enterprise=# GRANT USAGE, SELECT ON SEQUENCE fgedu_employees_id_seq TO biz_hr;
GRANT
# 授予财务角色权限
fgedu_enterprise=# GRANT SELECT ON fgedu_employees TO biz_finance;
GRANT
fgedu_enterprise=# GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_finances TO biz_finance;
GRANT
fgedu_enterprise=# GRANT USAGE, SELECT ON SEQUENCE fgedu_finances_id_seq TO biz_finance;
GRANT
# 授予销售角色权限
fgedu_enterprise=# GRANT SELECT ON fgedu_employees TO biz_fgfgfgfgsales;
GRANT
fgedu_enterprise=# GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_fgfgfgfgsales TO biz_fgfgfgfgsales;
GRANT
fgedu_enterprise=# GRANT USAGE, SELECT ON SEQUENCE fgedu_fgfgfgfgsales_id_seq TO biz_fgfgfgfgsales;
GRANT
# 授予应用角色权限
fgedu_enterprise=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO fgapp_readonly;
GRANT
fgedu_enterprise=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO fgapp_readwrite;
GRANT
fgedu_enterprise=# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO fgapp_readwrite;
GRANT
3.2.2 管理权限
fgedu_enterprise=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————————+———–
pgsql | Superfgedu, Create role, Create DB, Replication, Bypass RLS | {}
sys_admin | Create role, Create DB | {}
sys_sec_admin | | {}
sys_backup_admin | Replication | {}
fgapp_admin | | {fgapp_roles}
fgapp_readonly | | {fgapp_roles}
fgapp_readwrite | | {fgapp_roles}
biz_hr | | {biz_roles}
biz_finance | | {biz_roles}
biz_fgfgfgfgsales | | {biz_roles}
# 查看表权限
fgedu_enterprise=# \dp
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
——–+————-+——-+——————-+——————-+———-
public | fgedu_employees | table | biz_hr=arwd/__pgsql | | | biz_finance=r/__pgsql | |
| | | biz_fgfgfgfgsales=r/__pgsql | |
| | | fgapp_readonly=r/__pgsql | |
| | | fgapp_readwrite=arwd/__pgsql | |
public | fgedu_finances | table | biz_finance=arwd/__pgsql | |
| | | fgapp_readonly=r/__pgsql | |
| | | fgapp_readwrite=arwd/__pgsql | |
public | fgedu_fgfgfgfgsales | table | biz_fgfgfgfgsales=arwd/__pgsql | |
| | | fgapp_readonly=r/__pgsql | |
| | | fgapp_readwrite=arwd/__pgsql | |
# 回收权限
fgedu_enterprise=# REVOKE DELETE ON fgedu_employees FROM biz_hr;
REVOKE
# 验证权限回收
fgedu_enterprise=# \dp fgedu_employees
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
——–+————-+——-+——————-+——————-+———-
public | fgedu_employees | table | biz_hr=arw/__pgsql | | | biz_finance=r/__pgsql | |
| | | biz_fgfgfgfgsales=r/__pgsql | |
| | | fgapp_readonly=r/__pgsql | |
| | | fgapp_readwrite=arwd/__pgsql | |
3.3 企业级角色维护与审计
3.3.1 角色维护
fgedu_enterprise=# ALTER ROLE biz_hr WITH PASSWORD ‘NewBizHR123’;
ALTER ROLE
# 禁用角色
fgedu_enterprise=# ALTER ROLE biz_hr NOLOGIN;
ALTER ROLE
# 启用角色
fgedu_enterprise=# ALTER ROLE biz_hr LOGIN;
ALTER ROLE
# 删除角色
fgedu_enterprise=# DROP ROLE IF EXISTS test_role;
DROP ROLE
# 管理角色成员关系
fgedu_enterprise=# GRANT fgapp_readonly TO test_fgedu;
GRANT ROLE
fgedu_enterprise=# REVOKE fgapp_readonly FROM test_fgedu;
REVOKE ROLE
3.3.2 角色审计
fgedu_enterprise=# SELECT
r.rolname,
r.rolsuper,
r.rolcreaterole,
r.rolcreatedb,
r.rolcanlogin,
r.rolreplication
FROM
pg_roles r
ORDER BY
r.rolname;
# 审计角色成员关系
fgedu_enterprise=# SELECT
r1.rolname AS role_name,
r2.rolname AS member_name,
r3.rolname AS grantor_name,
m.admin_option
FROM
pg_auth_members m
JOIN
pg_roles r1 ON m.roleid = r1.oid
JOIN
pg_roles r2 ON m.member = r2.oid
JOIN
pg_roles r3 ON m.grantor = r3.oid
ORDER BY
r1.rolname, r2.rolname;
# 审计对象权限
fgedu_enterprise=# SELECT
grantee,
table_schema,
table_name,
privilege_type,
is_grantable
FROM
information_schema.role_table_grants
WHERE
table_schema = ‘public’
ORDER BY
grantee, table_schema, table_name, privilege_type;
Part04-生产案例与实战讲解
4.1 企业级PostgreSQL角色管理案例
4.1.1 案例描述
场景：一个大型企业，需要为不同部门和用户分配不同的数据库权限，确保数据安全和业务需求。
4.1.2 实施方案
# 创建角色组
postgres=# CREATE ROLE dept_roles NOLOGIN;
CREATE ROLE
postgres=# CREATE ROLE func_roles NOLOGIN;
CREATE ROLE
# 创建部门角色
postgres=# CREATE ROLE dept_tech WITH LOGIN PASSWORD ‘DeptTech123’;
CREATE ROLE
postgres=# CREATE ROLE dept_marketing WITH LOGIN PASSWORD ‘DeptMarketing123’;
CREATE ROLE
postgres=# CREATE ROLE dept_operation WITH LOGIN PASSWORD ‘DeptOperation123’;
CREATE ROLE
# 创建功能角色
postgres=# CREATE ROLE func_report WITH LOGIN PASSWORD ‘FuncReport123’;
CREATE ROLE
postgres=# CREATE ROLE func_import WITH LOGIN PASSWORD ‘FuncImport123’;
CREATE ROLE
postgres=# CREATE ROLE func_export WITH LOGIN PASSWORD ‘FuncExport123’;
CREATE ROLE
# 将角色添加到角色组
postgres=# GRANT dept_roles TO dept_tech, dept_marketing, dept_operation;
GRANT ROLE
postgres=# GRANT func_roles TO func_report, func_import, func_export;
GRANT ROLE
# 2. 分配权限
# 创建业务数据库
postgres=# CREATE DATABASE fgedu_business;
CREATE DATABASE
postgres=# \c fgedu_business
# 创建业务表
fgedu_business=# CREATE TABLE fgedu_products (
id serial PRIMARY KEY,
name varchar(100),
description text,
price numeric(10,2),
stock integer
);
CREATE TABLE fgedu_
fgedu_business=# CREATE TABLE fgedu_orders (
id serial PRIMARY KEY,
customer_id integer,
order_date date,
total_amount numeric(10,2),
status varchar(20)
);
CREATE TABLE fgedu_
fgedu_business=# CREATE TABLE fgedu_reports (
id serial PRIMARY KEY,
report_name varchar(100),
report_date date,
data jsonb
);
CREATE TABLE fgedu_# 授予数据库权限
fgedu_business=# GRANT CONNECT ON DATABASE fgedu_business TO dept_roles, func_roles;
GRANT
fgedu_business=# GRANT USAGE ON SCHEMA public TO dept_roles, func_roles;
GRANT
# 授予部门角色权限
fgedu_business=# GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_products TO dept_tech;
GRANT
fgedu_business=# GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_orders TO dept_operation;
GRANT
fgedu_business=# GRANT SELECT ON fgedu_products, fgedu_orders TO dept_marketing;
GRANT
# 授予功能角色权限
fgedu_business=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO func_report;
GRANT
fgedu_business=# GRANT INSERT, UPDATE ON fgedu_products TO func_import;
GRANT
fgedu_business=# GRANT SELECT ON fgedu_orders, fgedu_reports TO func_export;
GRANT
# 授予序列权限
fgedu_business=# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dept_tech, dept_operation, func_import;
GRANT
# 3. 测试权限
# 测试技术部门角色
$ psql -U dept_tech -d fgedu_business
fgedu_business=> INSERT INTO fgedu_products (name, description, price, stock) VALUES (‘产品1’, ‘描述1’, 100, 1000);
INSERT 0 1
fgedu_business=> SELECT * FROM fgedu_products;
id | name | description | price | stock
—-+——-+————-+——-+——-
1 | 产品1 | 描述1 | 100 | 1000
(1 row)
# 测试市场部门角色
$ psql -U dept_marketing -d fgedu_business
fgedu_business=> SELECT * FROM fgedu_products;
id | name | description | price | stock
—-+——-+————-+——-+——-
1 | 产品1 | 描述1 | 100 | 1000
(1 row)
# 尝试修改产品（应该失败）
fgedu_business=> UPDATE fgedu_products SET price = 120 WHERE id = 1;
ERROR: permission denied for relation fgedu_products
# 测试报表角色
$ psql -U func_report -d fgedu_business
fgedu_business=> SELECT * FROM fgedu_products;
id | name | description | price | stock
—-+——-+————-+——-+——-
1 | 产品1 | 描述1 | 100 | 1000
(1 row)
fgedu_business=> SELECT * FROM fgedu_orders;
id | customer_id | order_date | total_amount | status
—-+————-+————+————–+——–
(0 rows)
4.2 企业级权限规划案例
4.2.1 案例描述
场景：一个金融企业，需要严格的权限控制，确保敏感数据的安全。
4.2.2 实施方案
# 创建安全角色
postgres=# CREATE ROLE sec_admin WITH LOGIN PASSWORD ‘SecAdmin123’ CREATEROLE;
CREATE ROLE
# 创建业务角色
postgres=# CREATE ROLE fin_admin WITH LOGIN PASSWORD ‘FinAdmin123’;
CREATE ROLE
postgres=# CREATE ROLE fin_fgedu WITH LOGIN PASSWORD ‘FinUser123’;
CREATE ROLE
postgres=# CREATE ROLE fin_readonly WITH LOGIN PASSWORD ‘FinReadonly123’;
CREATE ROLE
# 2. 创建金融数据库
postgres=# CREATE DATABASE fgedu_finance;
CREATE DATABASE
postgres=# \c fgedu_finance
# 创建敏感数据表
fgedu_finance=# CREATE TABLE fgedu_customers (
id serial PRIMARY KEY,
name varchar(100),
id_number varchar(20),
address varchar(200),
phone varchar(20),
balance numeric(12,2)
);
CREATE TABLE fgedu_
fgedu_finance=# CREATE TABLE fgedu_transactions (
id serial PRIMARY KEY,
customer_id integer REFERENCES fgedu_customers(id),
transaction_date timestamp,
amount numeric(12,2),
type varchar(10),
description varchar(255)
);
CREATE TABLE fgedu_# 3. 分配权限
# 授予数据库权限
fgedu_finance=# GRANT CONNECT ON DATABASE fgedu_finance TO fin_admin, fin_fgedu, fin_readonly;
GRANT
fgedu_finance=# GRANT USAGE ON SCHEMA public TO fin_admin, fin_fgedu, fin_readonly;
GRANT
# 授予表权限
# 财务管理员权限
fgedu_finance=# GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_customers, fgedu_transactions TO fin_admin;
GRANT
# 财务用户权限
fgedu_finance=# GRANT SELECT, INSERT, UPDATE ON fgedu_transactions TO fin_fgedu;
GRANT
fgedu_finance=# GRANT SELECT ON fgedu_customers TO fin_fgedu;
GRANT
# 只读权限
fgedu_finance=# GRANT SELECT ON fgedu_customers, fgedu_transactions TO fin_readonly;
GRANT
# 授予序列权限
fgedu_finance=# GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO fin_admin, fin_fgedu;
GRANT
# 4. 实施行级安全策略
# 启用RLS
fgedu_finance=# ALTER TABLE fgedu_customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE
fgedu_finance=# ALTER TABLE fgedu_transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE
# 创建RLS策略
fgedu_finance=# CREATE POLICY customer_policy ON fgedu_customers
USING (true);
CREATE POLICY
fgedu_finance=# CREATE POLICY transaction_policy ON fgedu_transactions
USING (true);
CREATE POLICY
# 5. 测试权限
# 测试财务管理员
$ psql -U fin_admin -d fgedu_finance
fgedu_finance=> INSERT INTO fgedu_customers (name, id_number, address, phone, balance) VALUES (‘风哥1号’, ‘110101199001011234’, ‘北京市朝阳区’, ‘13800138000’, 10000);
INSERT 0 1
fgedu_finance=> INSERT INTO fgedu_transactions (customer_id, transaction_date, amount, type, description) VALUES (1, now(), 1000, ‘存款’, ‘工资’);
INSERT 0 1
# 测试财务用户
$ psql -U fin_fgedu -d fgedu_finance
fgedu_finance=> INSERT INTO fgedu_transactions (customer_id, transaction_date, amount, type, description) VALUES (1, now(), 500, ‘取款’, ‘生活费’);
INSERT 0 1
# 尝试删除客户（应该失败）
fgedu_finance=> DELETE FROM fgedu_customers WHERE id = 1;
ERROR: permission denied for relation fgedu_customers
# 测试只读用户
$ psql -U fin_readonly -d fgedu_finance
fgedu_finance=> SELECT * FROM fgedu_customers;
id | name | id_number | address | phone | balance
—-+——+—————–+—————+————-+———
1 | 风哥1号 | 110101199001011234 | 北京市朝阳区 | 13800138000 | 10000
(1 row)
# 尝试插入交易（应该失败）
fgedu_finance=> INSERT INTO fgedu_transactions (customer_id, transaction_date, amount, type, description) VALUES (1, now(), 200, ‘存款’, ‘奖金’);
ERROR: permission denied for relation fgedu_transactions
4.3 企业级角色审计案例
4.3.1 案例描述
场景：一个企业需要定期审计角色权限，确保权限分配符合安全策略。
4.3.2 实施方案
postgres=# CREATE ROLE audit_admin WITH LOGIN PASSWORD ‘AuditAdmin123’;
CREATE ROLE
# 2. 授予审计权限
postgres=# GRANT CONNECT ON DATABASE pgsql TO audit_admin;
GRANT
postgres=# GRANT SELECT ON pg_roles, pg_auth_members TO audit_admin;
GRANT
postgres=# GRANT SELECT ON information_schema.role_table_grants, information_schema.role_column_grants, information_schema.role_routine_grants TO audit_admin;
GRANT
# 3. 执行权限审计
# 查看所有角色
$ psql -U audit_admin -d pgsql -c “SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles ORDER BY rolname;”
rolname | rolsuper | rolcreaterole | rolcreatedb | rolcanlogin
——————-+———-+—————+————-+————-
audit_admin | f | f | f | t
biz_finance | f | f | f | t
biz_hr | f | f | f | t
biz_fgfgfgfgsales | f | f | f | t
biz_roles | f | f | f | f
dept_marketing | f | f | f | t
dept_operation | f | f | f | t
dept_roles | f | f | f | f
dept_tech | f | f | f | t
fin_admin | f | f | f | t
fin_readonly | f | f | f | t
fin_fgedu | f | f | f | t
func_export | f | f | f | t
func_import | f | f | f | t
func_report | f | f | f | t
func_roles | f | f | f | f
pgsql | t | t | t | t
sec_admin | f | t | f | t
sys_admin | f | t | t | t
sys_backup_admin | f | f | f | t
sys_sec_admin | f | f | f | t
(21 rows)
# 查看角色成员关系
$ psql -U audit_admin -d pgsql -c “SELECT r1.rolname AS role_name, r2.rolname AS member_name FROM pg_auth_members m JOIN pg_roles r1 ON m.roleid = r1.oid JOIN pg_roles r2 ON m.member = r2.oid ORDER BY r1.rolname, r2.rolname;”
role_name | member_name
—————-+——————
fgapp_roles | fgapp_admin
fgapp_roles | fgapp_readonly
fgapp_roles | fgapp_readwrite
biz_roles | biz_finance
biz_roles | biz_hr
biz_roles | biz_fgfgfgfgsales
dept_roles | dept_marketing
dept_roles | dept_operation
dept_roles | dept_tech
func_roles | func_export
func_roles | func_import
func_roles | func_report
(12 rows)
# 查看表权限
$ psql -U audit_admin -d fgedu_enterprise -c “SELECT grantee, table_name, privilege_type FROM information_schema.role_table_grants WHERE table_schema = ‘public’ ORDER BY grantee, table_name, privilege_type;”
grantee | table_name | privilege_type
—————+——————+—————-
fgapp_readonly | fgedu_employees | SELECT
fgapp_readonly | fgedu_finances | SELECT
fgapp_readonly | fgedu_fgfgfgfgsales | SELECT
fgapp_readwrite | fgedu_employees | DELETE
fgapp_readwrite | fgedu_employees | INSERT
fgapp_readwrite | fgedu_employees | REFERENCES
fgapp_readwrite | fgedu_employees | SELECT
fgapp_readwrite | fgedu_employees | TRIGGER
fgapp_readwrite | fgedu_employees | TRUNCATE
fgapp_readwrite | fgedu_employees | UPDATE
fgapp_readwrite | fgedu_finances | DELETE
fgapp_readwrite | fgedu_finances | INSERT
fgapp_readwrite | fgedu_finances | REFERENCES
fgapp_readwrite | fgedu_finances | SELECT
fgapp_readwrite | fgedu_finances | TRIGGER
fgapp_readwrite | fgedu_finances | TRUNCATE
fgapp_readwrite | fgedu_finances | UPDATE
fgapp_readwrite | fgedu_fgfgfgfgsales | DELETE
fgapp_readwrite | fgedu_fgfgfgfgsales | INSERT
fgapp_readwrite | fgedu_fgfgfgfgsales | REFERENCES
fgapp_readwrite | fgedu_fgfgfgfgsales | SELECT
fgapp_readwrite | fgedu_fgfgfgfgsales | TRIGGER
fgapp_readwrite | fgedu_fgfgfgfgsales | TRUNCATE
fgapp_readwrite | fgedu_fgfgfgfgsales | UPDATE
biz_finance | fgedu_employees | SELECT
biz_finance | fgedu_finances | DELETE
biz_finance | fgedu_finances | INSERT
biz_finance | fgedu_finances | REFERENCES
biz_finance | fgedu_finances | SELECT
biz_finance | fgedu_finances | TRIGGER
biz_finance | fgedu_finances | TRUNCATE
biz_finance | fgedu_finances | UPDATE
biz_hr | fgedu_employees | INSERT
biz_hr | fgedu_employees | REFERENCES
biz_hr | fgedu_employees | SELECT
biz_hr | fgedu_employees | TRIGGER
biz_hr | fgedu_employees | UPDATE
biz_fgfgfgfgsales | fgedu_employees | SELECT
biz_fgfgfgfgsales | fgedu_fgfgfgfgsales | DELETE
biz_fgfgfgfgsales | fgedu_fgfgfgfgsales | INSERT
biz_fgfgfgfgsales | fgedu_fgfgfgfgsales | REFERENCES
biz_fgfgfgfgsales | fgedu_fgfgfgfgsales | SELECT
biz_fgfgfgfgsales | fgedu_fgfgfgfgsales | TRIGGER
biz_fgfgfgfgsales | fgedu_fgfgfgfgsales | TRUNCATE
biz_fgfgfgfgsales | fgedu_fgfgfgfgsales | UPDATE
pgsql | fgedu_employees | DELETE
pgsql | fgedu_employees | INSERT
pgsql | fgedu_employees | REFERENCES
pgsql | fgedu_employees | SELECT
pgsql | fgedu_employees | TRIGGER
pgsql | fgedu_employees | TRUNCATE
pgsql | fgedu_employees | UPDATE
pgsql | fgedu_finances | DELETE
pgsql | fgedu_finances | INSERT
pgsql | fgedu_finances | REFERENCES
pgsql | fgedu_finances | SELECT
pgsql | fgedu_finances | TRIGGER
pgsql | fgedu_finances | TRUNCATE
pgsql | fgedu_finances | UPDATE
pgsql | fgedu_fgfgfgfgsales | DELETE
pgsql | fgedu_fgfgfgfgsales | INSERT
pgsql | fgedu_fgfgfgfgsales | REFERENCES
pgsql | fgedu_fgfgfgfgsales | SELECT
pgsql | fgedu_fgfgfgfgsales | TRIGGER
pgsql | fgedu_fgfgfgfgsales | TRUNCATE
pgsql | fgedu_fgfgfgfgsales | UPDATE
(63 rows)
Part05-风哥经验总结与分享
5.1 PostgreSQL角色管理最佳实践
PostgreSQL角色管理最佳实践：
- 角色设计：
- 基于业务需求设计角色层次结构
- 使用角色组管理相关角色
- 遵循最小权限原则
- 明确角色职责和权限范围
- 权限分配：
- 通过角色分配权限
- 使用权限继承简化管理
- 定期审查权限分配
- 建立权限变更审批流程
- 角色管理：
- 使用标准化的角色命名规范
- 定期清理未使用的角色
- 监控角色活动
- 备份角色配置
- 安全措施：
- 使用强密码
- 限制超级用户数量
- 启用审计日志
- 定期更换密码
- 审计与监控：
- 定期审计角色权限
- 监控权限变更
- 分析权限使用情况
- 生成权限审计报告
5.2 PostgreSQL角色管理常见问题
PostgreSQL角色管理常见问题及解决方案：
学习交流加群风哥QQ113257174
- 角色过多：使用角色组管理相关角色，减少角色数量
- 权限混乱：建立权限分配标准和流程，定期审计权限
- 权限过度分配：遵循最小权限原则，回收不必要的权限
- 权限不足：及时授予必要的权限，确保用户可以正常工作
- 角色命名不规范：使用标准化的角色命名规范
- 权限变更无记录：建立权限变更审批流程，记录变更历史
- 角色管理流程不明确：建立明确的角色管理流程和责任分工
- 审计不足：定期执行权限审计，确保权限分配符合安全策略
5.3 PostgreSQL角色管理建议
PostgreSQL角色管理建议：
- 建立角色管理规范：
- 制定角色设计和管理的标准
- 明确角色管理的责任人和流程
- 定期培训角色管理相关人员
- 使用自动化工具：
- 使用脚本自动化角色创建和管理
- 使用监控工具监控角色活动
- 使用审计工具生成权限报告
- 加强安全意识：
- 定期进行安全培训
- 提高用户的安全意识
- 建立安全事件响应机制
- 持续改进：
- 定期回顾角色管理流程
- 根据业务需求调整角色设计
- 学习和应用角色管理的最佳实践
- 文档管理：
- 记录角色设计和权限分配
- 维护角色管理文档
- 更新角色管理相关文档
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
