GBase 8c 学习笔记 009 —— GBase 8c 安全特性
👉 本文共约3626个字，系统预计阅读时间或需14分钟。
GBase 8c 学习笔记 009 —— GBase 8c 安全特性
安全特性简介
安全策略
- 账户安全策略
- 自动锁定和解锁帐户、手动锁定和解锁异常帐户和删除不再使用的帐户
- 账号的有效期
- 限制用户的操作期限（有效开始时间和有效结束时间）
- 密码的安全策略
- 密码的加密算法，密码复杂度，密码重用，密码有效期限，设置用户密码失效等
权限控制
管理用户对数据库的访问控制权限，涵盖数据库系统权限和对象权限。
支持基于角色的访问控制机制，将角色和权限关联起来，通过将权限赋予给对应的角色，再将角色授予给用户，可实现用户访问控制权限管理。其中登录访问控制通过用户标识和认证技术来共同实现，而对象访问控制则基于用户在对象上的权限，通过对象权限检查实现对象访问控制。用户为相关的数据库用户分配完成任务所需要的最小权限从而将数据库使用风险降到最低。
支持三权分立权限访问控制模型，数据库角色可分为系统管理员、安全管理员和审计管理员。其中安全管理员负责创建和管理用户，系统管理员负责授予和撤销用户权限，审计管理员负责审计所有用户的行为。
默认情况下，使用基于角色的访问控制模型。客户可通过设置参数来选择是否开启三权分立控制模型。
审计
数据库安全对数据库系统来说至关重要。
GBase 8c将用户对数据库的所有操作写入审计日志。数据库安全管理员可以利用这些日志信息，重现导致数据库现状的一系列事件，找出非法操作的用户、时间和内容等。
常用的审计项
- 用户登录、注销审计
- 数据库启动、停止、恢复和切换审计
- 用户锁定和解锁审计
- 用户访问越权审计
- 授权和回收权限审计
- 数据库对象的CREATE，ALTER，DROP操作审计
- 具体表的INSERT、UPDATE和DELETE操作审计
- SELECT操作审计
- COPY审计
- 存储过程和自定义函数的执行审计
- SET审计
黑名单
通过进行pg_hba.conf配置对应的黑名单或白名单
查看该文件路径：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 | show data_directory; |
操作演练
账户安全策略
账户自动锁定
密码错误一定次数（数据库参数：failed_login_attempts）账户自动锁定
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | --密码错误一定次数 postgres=# show failed_login_attempts; failed_login_attempts ----------------------- 10 (1 row) postgres=# create user testuser identified by 'Gbase_123'; CREATE ROLE postgres=# [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U testuser -W 'Gbase_124'; gsql: FATAL: Invalid username/password,login denied. [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U testuser -W 'Gbase_124'; gsql: FATAL: Invalid username/password,login denied. [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U testuser -W 'Gbase_124'; gsql: FATAL: Invalid username/password,login denied. [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U testuser -W 'Gbase_124'; gsql: FATAL: Invalid username/password,login denied. [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U testuser -W 'Gbase_124'; gsql: FATAL: Invalid username/password,login denied. [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U testuser -W 'Gbase_124'; gsql: FATAL: Invalid username/password,login denied. [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U testuser -W 'Gbase_124'; gsql: FATAL: Invalid username/password,login denied. [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U testuser -W 'Gbase_124'; gsql: FATAL: Invalid username/password,login denied. [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U testuser -W 'Gbase_124'; gsql: FATAL: Invalid username/password,login denied. [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U testuser -W 'Gbase_124'; gsql: FATAL: Invalid username/password,login denied. [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U testuser -W 'Gbase_124'; gsql: FATAL: The account has been locked. [gbase@gbase8c_5_151 ~]$ |
账户自动解锁
帐户被锁定时间超过设定值（数据库参数：password_lock_time）
可通过参数查看，默认是1天
1 2 3 4 5 6 7 8 9 10 11 12 13 | --帐户被锁定时间超过设定值 [gbase@gbase8c_5_151 ~]$ gsql -r postgres gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. -- 默认1天 postgres=# show password_lock_time; password_lock_time -------------------- 1d (1 row) |
修改参数除了上一个alter system之外，还可以使用gs_guc修改
1 2 3 4 5 6 7 | gs_guc reload -Z coordinator -N all -I all -c "password_lock_time='7d'" # 其中： # Z代表分支类型，coordinator为CN节点，datanode是dn节点 # N代表节点名称,如dn1、dn2等，all 指所有节点 # I代表实例名称，默认值为all，代表所有实例（不支持单个实例指定） # c代表修改的参数，如password_lock_time='7d' |
手动锁定账户
1 2 | ---手动锁定账号 ALTER USER testuser ACCOUNT LOCK; |
手动解锁账户
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | --手动解锁账号 postgres=# ALTER USER test ACCOUNT UNLOCK; ERROR: role "test" does not exist postgres=# \q [gbase@gbase8c_5_151 ~]$ [gbase@gbase8c_5_151 ~]$ [gbase@gbase8c_5_151 ~]$ [gbase@gbase8c_5_151 ~]$ gsql -r postgres gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. postgres=# ALTER USER testuser ACCOUNT UNLOCK; ALTER ROLE postgres=# \q [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U testuser -W 'Gbase_123'; gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. postgres=> |
删除不使用的账户
1 2 3 | --删除不用的账号 postgres=# DROP USER testuser CASCADE; DROP ROLE |
账户有效期
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [gbase@gbase8c_5_151 ~]$ gsql -r postgres gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. postgres=# create user test_user identified by 'Gbase_123' valid begin '2023-02-20 08:00:00' valid until '2023-02-28 08:00:00'; CREATE ROLE postgres=# postgres=# \q [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U test_user -W 'Gbase_123'; gsql: FATAL: The account is not within the period of validity. [gbase@gbase8c_5_151 ~]$ [gbase@gbase8c_5_151 ~]$ gsql -r postgres gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. -- 修改有效期 postgres=# alter user test_user VALID BEGIN '2023-02-20 08:00:00' VALID UNTIL '2023-03-30 14:23:00'; ALTER ROLE postgres=# postgres=# \q [gbase@gbase8c_5_151 ~]$ [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U test_user -W 'Gbase_123'; gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. postgres=> |
密码加密算法
数据库参数：password_encryption_type 值：0，1，2，3（md5/sha256/sm3）
1 2 3 4 5 | postgres=# show password_encryption_type; password_encryption_type -------------------------- 1 (1 row) |
修改使用alter system set password_encryption_type=2;修改后创建用户加密策略生效
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | ---查看所有的账户策略信息 select* from pg_authid where rolname ='test_user'; postgres=# select* from pg_authid where rolname ='test_user'; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | rolpassword | rolvalidbegin | rolvaliduntil | rolrespool | roluseft | rolparentid | roltabspace | rolkind | rolnodegroup | roltempspace | rolspillspace | rolexcpdata | rolmonitoradmin | roloperatoradmin | rolpolicyadmin -----------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+---------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----- -------------------+------------------------+--------------+----------+-------------+-------------+---------+--------------+--------------+---------------+-------------+-----------------+------------------+ ---------------- test_user | f | t | f | f | f | t | f | f | f | -1 | sha256e3094a7a3a7ecc68437ea2d2c8f10272e8e8f19 3c3fed7794f0d35eae26e1cffcce513fbd56d4bf3d1f5600ec4a5ad6a15986d5034ae4b05a48f61f329c6e9549bff89a4a4c64e75ede3b157e9fd52f5b5e2daef9e689a411bdf4b96e013174cmd5fc8685344ebd6d940eb0cf9036d1d3a5ecdfecefade | 2023 -02-20 08:00:00+08 | 2023-03-30 14:23:00+08 | default_pool | f | 0 | | n | | | | | f | f | f (1 row) |
密码复杂度
数据库参数password_policy （1、 0）1为校验密码复杂度
- 包含大写字母（A-Z）的最少个数（password_min_uppercase）
- 包含小写字母（a-z）的最少个数（password_min_lowercase）
- 包含数字（0-9）的最少个数（password_min_digital）
- 包含特殊字符的最少个数（password_min_special）
- 密码的最小长度（password_min_length）
- 密码的最大长度（password_max_length）
- 至少包含上述四类字符中的三类。
- 不能和用户名、用户名倒写相同，本要求为非大小写敏感。
- 不能和当前密码、当前密码的倒写相同。
- 不能是弱口令
1 2 3 4 5 6 7 8 9 10 | postgres=# show password_policy; password_policy ----------------- 1 (1 row) postgres=# create user u1 identified by '111111'; ERROR: Password must contain at least 8 characters. postgres=# create user u1 identified by '123456qw'; ERROR: Password must contain at least three kinds of characters. |
密码重用
用户修改密码时，只有超过不可重用天数（数据库参数：password_reuse_time）或不可重用次数（数据库参数：password_reuse_max）的密码才可以使用，也可以进行参数的修改。
1 2 3 4 5 6 7 8 9 10 11 12 | ---密码重用（多少次、多少天内修改的密码不能重复） postgres=# show password_reuse_time; password_reuse_time --------------------- 60 (1 row) postgres=# show password_reuse_max; password_reuse_max -------------------- 0 (1 row) |
密码有效期限
数据库用户的密码都有密码有效期（password_effect_time），当达到密码到期提醒天数（password_notify_time）时，系统会在用户登录数据库时提示用户修改密码
1 2 3 4 5 6 7 8 9 10 11 12 13 | ---密码有效期 postgres=# show password_effect_time; password_effect_time ---------------------- 90 (1 row) ---密码到期天数提醒 postgres=# show password_notify_time; password_notify_time ---------------------- 7 (1 row) |
设置密码失效
确保用户首次登录进行密码修改。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | postgres=# create user u1 password 'Gbase_123' expired; CREATE ROLE postgres=# postgres=# \q [gbase@gbase8c_5_151 ~]$ gsql -r postgres -U u1 -W 'Gbase_123'; gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. postgres=> \d ERROR: Please use "ALTER ROLE user_name IDENTIFIED BY 'password' REPLACE 'old password';" to modify the expired password of user u1 before operation! postgres=> alter user u1 password 'Gbase_123' expired; |
权限控制
grant/revoke
- 将系统权限授权给角色或用户；
- 将对象权限授权给用户或者角色
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]; postgres=# create database test_1; CREATE DATABASE postgres=# postgres=# create user test1 identified by 'Gbase_123'; CREATE ROLE --dba 权限 postgres=# grant ALL privileges to test1; ALTER ROLE postgres=# \du+ List of roles Role name | Attributes | Member of | Description -----------+------------------------------------------------------------------------------------------------------------------+-----------+------------- gbase | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} | gha | Sysadmin | {} | test1 | Sysadmin | {} | test_user | Role valid begin 2023-02-20 08:00:00+08 +| {} | | Role valid until 2023-03-30 14:23:00+08 | | u1 | | {} | postgres=# --回收DBA权限 postgres=# revoke ALL privileges from test1; ALTER ROLE postgres=# \du+ List of roles Role name | Attributes | Member of | Description -----------+------------------------------------------------------------------------------------------------------------------+-----------+------------- gbase | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {} | gha | Sysadmin | {} | test1 | | {} | test_user | Role valid begin 2023-02-20 08:00:00+08 +| {} | | Role valid until 2023-03-30 14:23:00+08 | | u1 | | {} | postgres=# postgres=# \q [gbase@gbase8c_5_151 ~]$ gsql -d test_1 -U test1 -W 'Gbase_123' -p 5432 -r gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. test_1=> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | --回收publibc权限连接权限，授予链接权限 postgres=# revoke connect on database test_1 from public; REVOKE postgres=# \q [gbase@gbase8c_5_151 ~]$ gsql -d test_1 -U test1 -W 'Gbase_123' -p 5432 -r gsql: FATAL: permission denied for database "test_1" DETAIL: User does not have CONNECT privilege. [gbase@gbase8c_5_151 ~]$ postgres=# grant CONNECT ON DATABASE test_1 to test1; GRANT postgres=# \q --授权后可以登陆成功 [gbase@gbase8c_5_151 ~]$ gsql -d test_1 -U test1 -W 'Gbase_123' -p 5432 -r gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. test_1=> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | [gbase@gbase8c_5_151 ~]$ gsql -r postgres gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. --使用拥有权限的用户创建表 postgres=# create table t3(id int,name varchar(10)); CREATE TABLE postgres=# \q [gbase@gbase8c_5_151 ~]$ gsql -d postgres -U test1 -W 'Gbase_123' -p 5432 -r gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. --登录新用户进行查询等操作 postgres=> select * from t3; ERROR: permission denied for relation t3 DETAIL: N/A postgres=> insert into t3 values(1,'a'); ERROR: permission denied for relation t3 DETAIL: N/A postgres=> update t3 set name='update' where id =1; ERROR: permission denied for relation t3 DETAIL: N/A postgres=> delete from t3 where id =1; ERROR: permission denied for relation t3 DETAIL: N/A postgres=> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | [gbase@gbase8c_5_151 ~]$ gsql -r postgres gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. postgres=# grant select ON table t3 to test1; GRANT postgres=# grant insert ON table t3 to test1; GRANT postgres=# grant update ON table t3 to test1; GRANT postgres=# grant delete ON table t3 to test1; GRANT postgres=# \q [gbase@gbase8c_5_151 ~]$ gsql -d postgres -U test1 -W 'Gbase_123' -p 5432 -r gsql ((multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 ) Non-SSL connection (SSL connection is recommended when requiring high-security) Type "help" for help. postgres=> insert into t3 values(1,'a'); INSERT 0 1 postgres=> postgres=> select * from t3; id | name ----+------ 1 | a (1 row) postgres=> update t3 set name='update' where id =1; UPDATE 1 postgres=> select * from t3; id | name ----+-------- 1 | update (1 row) postgres=> delete from t3 where id =1; DELETE 1 postgres=> select * from t3; id | name ----+------ (0 rows) |
1 2 3 4 5 | --权限回收 revoke select ON table t3 from test1; revoke insert ON table t3 from test1; revoke update ON table t3 from test1; revoke delete ON table t3 from test1; |
审计
数据库参数
- audit_enabled：开启审计日志开关
- audit_system_object：判断是否开启审计项的值
- audit_dml_state：dml语句审计
- audit_dml_state_select：select 语句审计开关
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | --打开审计日志开关 alter system set audit_enabled=on; -- 审计日志开关 postgres=# show audit_enabled; audit_enabled --------------- on (1 row) -- 查看审计日志目录 postgres=# show audit_directory; audit_directory --------------------------------------- /home/gbase/gbase_db/log/pg_audit/cn1 (1 row) |
查看审计结果
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | select time,type,result,userid,username,database,detail_info from pg_query_audit(sysdate- 1,sysdate) where database='test'; -- 其中type代表审计类型，detail_info代表审计详细信息，database代表的数据库名称 postgres=# select time,type,result,userid,username,database,detail_info from pg_query_audit(sysdate- 1,sysdate) where database='test_1'; time | type | result | userid | username | database | detail_info ------------------------+---------------+--------+--------+----------+----------+----------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- 2023-03-29 21:26:01+08 | login_success | ok | 10 | gbase | test_1 | login db(test_1) success,the current user is:gbase, SSL=off 2023-03-29 21:26:01+08 | set_parameter | ok | 10 | gbase | test_1 | SET connection_info = '{"driver_name":"libpq","driver_version":"(multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 "}' 2023-03-29 21:26:01+08 | login_success | ok | 10 | gbase | test_1 | login db(test_1) success,the current user is:gbase, SSL=off 2023-03-29 21:26:01+08 | set_parameter | ok | 10 | gbase | test_1 | SET connection_info = '{"driver_name":"libpq","driver_version":"(multiple_nodes GBase8cV5 3.0.0B76 build 47948f99) compiled at 2023-02-27 16:04:20 commit 0 last mr 1232 "}'; 2023-03-29 21:26:01+08 | set_parameter | ok | 10 | gbase | test_1 | SET SESSION AUTHORIZATION DEFAULT;RESET ALL; 2023-03-29 21:26:01+08 | set_parameter | ok | 10 | gbase | test_1 | SET SESSION AUTHORIZATION DEFAULT;RESET ALL; 2023-03-29 21:26:01+08 | user_logout | ok | 10 | gbase | test_1 | logout db(test_1) success 2023-03-29 21:27:01+08 | login_success | ok | 10 | gbase | test_1 | login db(test_1) success,the current user is:gbase, SSL=off …………………………………………………………………………………………………………………………………… |
1 2 3 4 5 6 | --查看审计中的锁表记录 postgres=# select detail_info,type,result from pg_query_audit(sysdate-1,sysdate) where type ='lock_user'; detail_info | type | result ------------------------------------+-----------+-------- the user(testuser) has been locked | lock_user | ok (1 row) |
1 2 3 4 5 6 7 8 9 10 11 12 13 | --使用审计对象进行查看 postgres=# select detail_info,type,result from pg_query_audit(sysdate-1,sysdate) where object_name='test1'; detail_info | type | result ---------------------------------------------+------------+-------- create user test1 identified by '********'; | ddl_user | ok grant ALL privileges to test1; | ddl_user | ok revoke ALL privileges from test1; | ddl_user | ok grant CONNECT ON DATABASE test_1 to test1; | grant_role | ok grant select ON table t3 to test1; | grant_role | ok grant insert ON table t3 to test1; | grant_role | ok grant update ON table t3 to test1; | grant_role | ok grant delete ON table t3 to test1; | grant_role | ok (8 rows) |
1 2 3 4 5 6 | --使用审计查看数据库删除的操作 postgres=# select detail_info,type,result from pg_query_audit(sysdate-1,sysdate) where type ='ddl_database'; detail_info | type | result -------------------------+--------------+-------- create database test_1; | ddl_database | ok (1 row) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | --查看审计内容 postgres=# show audit_system_object; audit_system_object --------------------- 12295 (1 row) postgres=# alter system set audit_system_object=127; ALTER SYSTEM SET --打开DML日志 postgres=# alter system set audit_dml_state=1; ALTER SYSTEM SET postgres=# postgres=# create table test3(a int); CREATE TABLE postgres=# postgres=# alter table test3 add (name varchar(50)); ALTER TABLE postgres=# postgres=# select detail_info,type,result from pg_query_audit(sysdate-1/3600,sysdate) where object_name='test3'; detail_info | type | result -------------+------+-------- (0 rows) postgres=# select detail_info,type,result from pg_query_audit(sysdate-1,sysdate) where object_name='test3'; detail_info | type | result -------------------------------------------+-----------+-------- create table test3(a int); | ddl_table | ok alter table test3 add (name varchar(50)); | ddl_table | ok (2 rows) --打开DQL日志 postgres=# alter system set audit_dml_state_select=1; ALTER SYSTEM SET postgres=# postgres=# select * from test3; a | name ---+------ (0 rows) postgres=# select detail_info,type,result from pg_query_audit(sysdate-1,sysdate) where object_name='test3'; detail_info | type | result -------------------------------------------+-------------------+-------- create table test3(a int); | ddl_table | ok alter table test3 add (name varchar(50)); | ddl_table | ok select * from test3; | dml_action_select | ok (3 rows) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | --查看登录失败的审计记录 select detail_info,type,result from pg_query_audit(sysdate-1,sysdate) where type='login_failed'; --查看登陆成功的审计记录 select detail_info,type,result from pg_query_audit(sysdate-1,sysdate) where type='login_success'; postgres=# select detail_info,type,result from pg_query_audit(sysdate-1,sysdate) postgres-# where type='login_failed'; detail_info | type | result ----------------------------------------------------------------------------+--------------+-------- login db(postgres)failed,authentication for user(testuser)failed, SSL=off | login_failed | failed login db(postgres)failed,authentication for user(testuser)failed, SSL=off | login_failed | failed login db(postgres)failed,authentication for user(testuser)failed, SSL=off | login_failed | failed login db(postgres)failed,authentication for user(testuser)failed, SSL=off | login_failed | failed login db(postgres)failed,authentication for user(testuser)failed, SSL=off | login_failed | failed login db(postgres)failed,authentication for user(testuser)failed, SSL=off | login_failed | failed login db(postgres)failed,authentication for user(testuser)failed, SSL=off | login_failed | failed login db(postgres)failed,authentication for user(testuser)failed, SSL=off | login_failed | failed login db(postgres)failed,authentication for user(testuser)failed, SSL=off | login_failed | failed login db(postgres)failed,authentication for user(testuser)failed, SSL=off | login_failed | failed login db(postgres)failed,the account(testuser)has been locked, SSL=off | login_failed | failed login db(postgres)failed,authentication for user(test_user)failed, SSL=off | login_failed | failed login db(postgres)failed,authentication for user(test_user)failed, SSL=off | login_failed | failed login db(test_1)failed,authentication for user(postgres)failed, SSL=off | login_failed | failed (14 rows) postgres=# select detail_info,type,result from pg_query_audit(sysdate-1,sysdate) postgres-# where type='login_success'; detail_info | type | result -------------------------------------------------------------------+---------------+-------- login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok login db(postgres) success,the current user is:gbase, SSL=off | login_success | ok ……………………………………………………………………………………………………………………………………………… |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | --审计删除 --审计文件占用的磁盘空间或者审计文件的个数超过指定的最大值时，系统将删除最早的审计文件，并记录审计文件删除信息到审计日志中 SHOW audit_space_limit; --自动删除 postgres=# SHOW audit_space_limit; audit_space_limit ------------------- 1GB (1 row) --手动删除 SELECT pg_delete_audit('2012-09-20 00:00:00','2012-09-21 23:59:59'); postgres=# SELECT pg_delete_audit('2023-03-29 21:00:00','2023-03-29 22:00:00'); pg_delete_audit ----------------- (1 row) |
