Posts

1 逻辑复制背景 1.1 逻辑复制场景 在使用数据库时，为提高整个系统的可靠性，或实现不同部门数据同步等场景，很多客户的部署模型存在：一主多备、异构数据库等特点，在保证性能的前提下，常见部署模型如下： +-------------+ sql +---------------------+ wal +--------------------+ | application | --------> | postgresql (master) | -------> | postgresql (slave) | +-------------+ +---------------------+ +--------------------+ | | | decoded-wal +--------------------+ +---------------------> | mysql, oracle, .. | +--------------------+ 1.2 逻辑复制功能 1.3 逻辑复制基础 首先，需自行了解一些存储的基本知识，包括事务特性、mvcc机制，表的物理存储格式：filenode、page、tuple等。 此处，以一个例子，介绍什么wal日志的特点。需了解一些 应用执行事务 假设同一时间，有2个事务，事务id分别为10和11。 时间 应用1 应用2 0 CREATE TABLE t1 (c1 INT,c2 INT) - 1 CREATE TABLE t2 (c1 INT,c2 INT) - 2 BEGIN (xid=10) - 3 INSERT INTO t1 VALUES(10, 1) - 4 - BEGIN (xid=11) 5 - INSERT INTO t1 VALUES (11, 1) 6 INSERT INTO t2 VALUES (10, 2) - 7 COMMIT 8 - DELETE FROM t1 WHERE c1 = 10 9 - INSERT INTO t2 VALUES (11, 2) 10 - COMMIT 内核产生wal日志 所有表的wal日志，按生成wal的顺序，组织在一起。上述示例中，产生的wal如下：（此处仅列举关键信息） ...

1 物理复制基本原理 1.1 使用场景 介绍为什么需要物理复制 1.2 相关原理 介绍什么是wal，wal是用来干嘛的 1.3 实现思路 介绍如何基于wal，实现物理复制，并解决1.1中的问题 2 物理复制使用方式 2.1 使用物理复制 3 物理复制工作流程 3.1 整体架构 包括Postgres生成wal，walwriter存储wal，walsender发送wal，walreceive接收wal，startup重放wal，checkpoint/bgwriter等。 3.2 工作流程 参考2.1，梳理完整的工作流程，从主机执行SQL语句开始，到最终备机达到同样效果 4 物流复制实现源码 参考2.1，介绍每一阶段的关键原理，另外，包括wal日志格式等 你好，我要基本当前目录下的代码，生成一份流复制的skill，所以，接下来的对话，都和这件事有关。

深信服支持pg scram-sha256需求 需求目标 本需求的根本目标时，当vastbase server要求使用sha256算法进行身份认证时，postgresql client能与vastbase server建立连接。 该需求目前仅合入深信服分支 需求分析 身份认证的详细流程有点复杂，此处简要概述为2个阶段： 标识：新建用户时，server计算并存储密码哈希值 鉴别：用户登录时，server向client发送计算密码哈希值的关键信息，client计算并向server发送密码哈希值，server校验其是否符合存储的值 vastbase和postgresql对比，server计算密码哈希值的算法不同，server向client发送关键信息也不同。为解决pg-client能够连接vb-server的问题，需解决问题如下： vb-server计算并存储与pg-server一致的密码哈希值 vb-server向pg-client发送密码哈希值关键信息时，需要pg-server一致 postgreql的标识与鉴别机制，符合标准RFC-5802，涉及的算法与协议，本文统一称为scram-sha256。vastbase涉及的算法与协议，本文统一称为sha256。 需求实现 密码存储：新建用户时，修改存储用户密码的值 修改前：guc参数password_encrypt_type，取值1时，vb-server存储密码的[sha256, md5]值；取值为2时，存储密码的[sha256]值 SELECT rolpassword FORM pgauthid; -- 示例：长度为209 rolpassword -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- sha256af99537f242c661a88acea99ac626a2cf8fe196ac5671db6f02ed6818b420cf84e7ada61d66602c6eb5bf4ced40911d3f51865959784ebc3b0d385ca4c08f3398f05e16268fe9acc95a2d662bf5033b15d53abd1f835c1a3f1e96343a5cbf800ecdfecefade 修改后：guc参数password_encrypt_type，取值1时，vb-server存储密码的[sha256, md5, scram-sha256]值；取值为2时，存储密码的[sha256, scram-sha256]值 SELECT rolpassword FORM pgauthid; -- 示例：长度342 (新增长度：133) rolpassword -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- sha256af99537f242c661a88acea99ac626a2cf8fe196ac5671db6f02ed6818b420cf84e7ada61d66602c6eb5bf4ced40911d3f51865959784ebc3b0d385ca4c08f3398f05e16268fe9acc95a2d662bf5033b15d53abd1f835c1a3f1e963SCRAM-SHA-256$4096:sp5V1hrLKzLsS4HtQgqTuA==$QO/UVdrXxIypzBEGqEuIWe96A80elghFQs25ZbdPsLg=:J4lPXmXPANtTMOkaHokMjmM19K1Di7fU6WRWuZ5VmS0= 认证协议：用户登录时，修改向客户端发送的密码哈希值关键信息 修改前： # 身份认证协议 1. client <-> server : tcp connect 2. client --> server : send [消息类型:0, 协议版本:3.51，连接参数:user_name, databse, ...] 3. server --> client : send [消息类型:R, 哈希算法:10, 系统表存储的密码的算法:2, 计算密码哈希关键信息：salt,token,iteration] 4. client <-> server : # 省略3次交互 修改后： ...

https://www.tapd.cn/60475194/markdown_wikis/show/#1160475194001006330 3 异常参数 3.1 DES encrypt 封装函数 CREATE OR REPLACE FUNCTION desencrypt(hex_plain VARCHAR2, hex_key VARCHAR2) RETURN VARCHAR2 IS cipher RAW(100); BEGIN dbms_obfuscation_toolkit.desencrypt(hextoraw(hex_plain), hextoraw(hex_key), encrypted_data=>cipher); RETURN rawtohex(cipher); END; / 函数格式 desencrypt(plain, key, cipher); 约束： 构造用例 明文长度 密钥长度 输出：密文长度 示例 结果 一、正常用例 8 8 8 desencrypt() abc 16 8 16 desencrypt() abc 一、明文长度异常 0 8 16 desencrypt() 明文不是8的倍数 1 8 - desencrypt() 明文不是8的倍数 7 8 - desencrypt() 明文不是8的倍数 15 8 - desencrypt() 明文不是8的倍数 二、密钥长度异常 8 0 8 desencrypt() 密钥长度不是8 8 1 8 desencrypt() 密钥长度不是8 8 15 8 desencrypt() 密钥长度不是8 8 16 8 desencrypt() 密钥长度不是8 3.2 DES decrypt 3.3 3DES encrypt 封装函数 ...

摘要：介绍常见数据库安全机制的原理： 1.身份认证 2.安全传输 3.安全审计 4.访问控制 5.数据发现 6.数据防篡改 7.加密函数 8.透明加密 9.数据脱敏 10.全密态加密 11.机密计算 1 数据库安全概述 1.1 数据库基本功能 本文讨论数据库安全，为方便理解，将大部分软件架构简化为2个主体：应用端与数据库端。 应用：实现业务逻辑，收集业务数据，将数据发送至数据库，由数据库提供数据存储功能，并且，要求数据库提供数据查询功能 数据库：存储业务数据，向应用提供SQL接口，接收应用发送的SQL语句，并处理SQL语句。同时，解决高并发、高性能、高可靠的问题 大部分场景中，应用与数据库部署于不同主机上，以下是一个应用使用数据库实现数据存储与查询的示例流程： 建立连接：应用与数据库建立网络连接 定义数据：应用向数据库发送以下SQL语句，创建1个表 CREATE TABLE t1 (c1 INT, c2 TEXT); 存储数据：应用向数据库发送以下SQL语句，向表中存储数据 INSERT INTO t1 VALUES (1, 'abc'); 查询数据：应用向数据库发送以下SQL语句，从表中查询数据 SELECT * FROM t1; 1.2 数据库基本架构 数据库架构较复杂，此处先将数据库架构简单划分为以下3个部分： 数据库驱动：集成到应用内部，向应用提供编程接口，常见接口有：连接数据库、执行SQL语句等 计算模块：解析SQL语句，并按合理地执行流程处理SQL语句中的请求，调用存储模块接口实现数据存储与读取 存储模块：合理组织数据，调用IO接口，实现磁盘读写操作。向计算模块提供接口 1.3 数据库安全威胁 在应用使用数据库的过程中，攻击者可能对整个系统造成恶意攻击，攻击的最终目标是： 破坏业务数据：窃取、伪造、篡改、损坏等 破坏业务系统：拒绝服务等 攻击者针对多个系统薄弱点进行功能： 数据库网络：协议等 数据库应用：身份认证机制等 数据库主机：操作系统、软件（除数据库外的其他软件）、硬件等 假设应用与数据库部署在不同主机，分为是下图中的主机c和主机z，常见安全威胁的架构图如下： 1.4 数据安全架构 针对各类威胁，数据库提供多种安全机制，保护数据与数据库安全，常见的安全机制如下： 身份认证：阻止非法用户与数据库建立网络连接 安全传输：阻止非法用户窃取、篡改、伪造、..传输数据 安全审计：记录非法/合法用户对数据库的操作 访问控制：缩小非法用户对数据库的访问范围 数据发现：协助合法用户识别敏感数据 数据防篡改：识别非法用户篡改、伪造、..存储数据 加密函数：协助合法用户加密存储数据 透明加密：阻止非法用户窃取、篡改、伪造、..存储数据 数据脱敏：缩小非法用户对数据内容的访问范围 全密态加密：阻止非法用户窃取、篡改、伪造、..传输、计算、存储数据 机密计算：阻止非法用户窃取、篡改、伪造计算数据 按对数据处理的顺序，各安全机制的架构如下图所示： ...

1 图的介绍 1.1 定义 G = (V, E) 一组节点V 一组边E 节点的度：相邻节点的数量 完备图：所有节点都有n-1个相邻节点，即任意2节点间可达 1.2 分类 根据边分类 无向图 n1 ---- n2 | | | | n3 ---- n4 有向图 --> n2 n1 --> n4 --> n3 带权有向图 3 ---> n2 6 n1 ---> n4 ---> n3 5 图的性质分类 同构图：G和H，通过重新标记G的节点产生H。阶相等，顶点度数相等 异构图 1.3 表示 邻接矩阵 如果节点间ni,nj之间有弧，则[ni, nj] = 1 n1 n2 n3 n4 +------------+ n1 | 0 1 1 0 | n2 | 0 0 0 1 | n3 | 0 0 0 1 | n4 | 0 0 0 0 | +------------+ 无向图的斜下半部分是多余的 ...

1 使用pgaudt 1.1 常见SQL -- 1 ddl CREATE USER u1 PASSWORD 'u1.password'; CREATE TABLE t1(c1 INT, c2 TEXT); CREATE TABLE t2 (c1 INT, c2 TEXT); CREATE INDEX i1 ON t1(c2); -- 2 dml INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); INSERT INTO t2 VALUES (1, 'aaa'), (5, 'bbb'); UPDATE t1 SET c2 = 'ddd' WHERE c1 < 3; DELETE FROM t1 WHERE c1 = 2; -- 3 select SELECT * FROM t1; -- 4 function SELECT c2 || '_oper' FROM t1; SELECT concat(c2, '_func') FROM t1; INSERT INTO t1 VALUES (5, concat('aaa', '_func')), (6, concat('aaa', '_func')); -- 5 multi INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1; CREATE TABLE t3 AS SELECT * FROM t2; DROP TABLE t1,t2,t3; 1 安装pgaudit 1.1 安装postgresql # 1 配置环境变量 echo 'export BUILD_ROOT=`pwd`' > pgenv echo 'export PG_HOME=$BUILD_ROOT/install' >> pgenv echo 'export PATH="$PG_HOME/bin:$PATH"' >> pgenv source pgenv # 2 下载源码 wget https://ftp.postgresql.org/pub/source/v14.18/postgresql-14.18.tar.gz tar -zxvf postgresql-14.18.tar.gz cd postgresql-14.18.tar.gz # 3 编译源码 (不开启debug模式，提高性能) #./configure --prefix=$PG_HOME --enable-debug=yes ./configure --prefix=$PG_HOME --enable-debug=no --enable-cassert=no make -sj make install -sj # ========================================== cd $BUILD_ROOT/pg14/contrib/pgaudit make install USE_PGXS=1 PG_CONFIG=$PG_HOME/bin/pg_config cd - # ========================================== # 4 初始化集群 initdb -D $PG_HOME/data # 4 配置集群 # echo "port = 54000" >> $PG_HOME/data/postgresql.conf # echo "max_connections = 1000" >> $PG_HOME/data/postgresql.conf # ========================================== echo "logging_collector = on" >> $PG_HOME/data/postgresql.conf echo "shared_preload_libraries = 'pgaudit'" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log = 'ALL'" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log_catalog = true" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log_client = true" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log_parameter = true" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log_relation = true" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log_rows = true" >> $PG_HOME/data/postgresql.conf echo "pgaudit.log_statement = true" >> $PG_HOME/data/postgresql.conf # ========================================== # 5 启动集群 pg_ctl start -D $PG_HOME/data # ========================================== DROP EXTENSION pgaudit; CREATE EXTENSION pgaudit; CREATE UNLOGGED TABLE adt(c1 TEXT); CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); \! ls $PG_HOME/data/log \! tail $PG_HOME/data/log/ # ========================================== 1.2 tpcc优化 echo " shared_buffers = 200GB work_mem = 1GB maintenance_work_mem = 4GB effective_cache_size = 500GB wal_buffers = 1GB checkpoint_timeout = 55min " >> $PG_HOME/data/postgresql.conf show shared_buffers; show work_mem; show maintenance_work_mem; show effective_cache_size; show wal_buffers; show checkpoint_timeout; 1.3 安装pgaudit # 1 下载源码 wget https://codeload.github.com/pgaudit/pgaudit/tar.gz/refs/tags/1.6.2 -O pgaudit-1.6.2.tar.gz tar -zxvf pgaudit-1.6.2.tar.gz # 2 编译 cd pgaudit-1.6.2 make install USE_PGXS=1 PG_CONFIG=$PG_HOME/bin/pg_config # 3 验证编译成功 ll $PG_HOME/share/extension cat $PG_HOME/data/postgresql.conf | grep shared_preload_libraries # 4 开启日志功能 echo "logging_collector = on" >> $PG_HOME/data/postgresql.conf echo "log_statement = all" >> $PG_HOME/data/postgresql.conf # 5 开启 pgaudit echo "shared_preload_libraries = 'pgaudit'" >> $PG_HOME/data/postgresql.conf echo " pgaudit.log = 'ALL'" >> $PG_HOME/data/postgresql.conf -- 1 安装pgaudit CREATE EXTENSION pgaudit; -- 2 查看配置参数 SHOW pgaudit.log; -- ALL -- READ: SELECT -- WRITE: INSERT, UPDATE, DELETE -- ROLE -- DDL -- FUNCTION -- MISC -- MISC_SET SHOW pgaudit.role; SHOW pgaudit.log_level; -- 查看审计文件 SHOW log_directory; SHOW log_filename; 审计策略：客户 优先3.0 ...

摘要：从5个方面，介绍PostgreSQL身份认证特性：背景、原理、使用、设计、实现 1 身份认证概述 1.1 非法访问风险 数据库进程 数据库运行时，本质是一个服务器进程，在特定的主机上，监听特定端口。应用如果知道IP和端口，都可与数据库进程建立TCP连接，并进行数据交互，包括发送SQL语句、接收查询结果等。 数据泄露风险 为避免非法用户访问数据库，构造特殊数据，造成数据损坏、数据泄露等问题，数据库接收应用建立TCP的请求，并成功建立TCP连接后，需先确认应用的身份是否合法，再接收应用的其他数据，比如接收SQL语句等。 1.2 身份认证机制 在信息系统中，身份认证是一种常见的机制，身份认证的概念较成熟，本文不详细介绍相关概念。身份认证分为标识与鉴别两个部分： 标识：用户向系统声明自己的身份，系统标识用户 鉴别：系统验证用户提供的信息，确认用户是否是已标识的用户 按照认证因素，可将身份认证划分为3类： 知识因素：用户知道某些秘密，比如口令、密码等 拥有因素：用户拥有某些实物，比如钥匙、证书、令牌等 生物因素：用户拥有某些特征，比如指纹、面部、虹膜、签名等 1.3 PostgrSQL的身份认证 PostgreSQL的认证方式有很多种，包括：密码认证、Ident认证、Peer认证、证书认证、LDAP认证等。本文仅介绍最常见的方案。 在大部分场景中，PostgreSQL采用以下2层认证机制： 主机认证： 标识：在PostgreSQL的配置文件中，配置IP白名单 鉴别：PostgreSQL与应用建立连接后，检查应用的IP是否在白名单内，判断应用是否合法 口令认证： 标识：PostgreSQL已存在的用户，创建新用户，为其设置用户信息，以及口令 备注：第一次安装PostgreSQL时，PostgreSQL要求用户创建初始用户 鉴别：在主机认证后，PostgreSQL先要求应用提供用户信息，再要求应用提供对应的口令，判断应用是否合法 2 身份认证的使用流程 2.1 基本流程 PostgreSQL中，从用户的角度观察，使用身份认证的流程可简单理解为： 用户：已存在的用户使用CRATE USER {username} .. PASSWORD {password} ..语法，创建新用户并设置口令 数据库：数据库先对口令进行哈希，再存储口令的哈希值，目前的版本中，默认哈希算法为sha256算法，用户可通过GUC参数配置哈希算法类型 用户：用户与数据建立连接，将{username}与{password}的哈希值发送给数据库，数据库比较用户发送的哈希值与系统表中的哈希值，判断{password}是否合法 具体实现中，第3步的验证过程较为复杂，RFC5 802协议提供了一套安全的交互流程。后文介绍内部实现的详细流程。 3 身份认证的实现 3.1 基于IP的身份认证 标识：访问者的IP 鉴别：数据库遍历IP白名单，判断访问者IP是否在白名单中，如果在，则认为访问者合法，允许与数据建立TCP连接。 PostmasterMain ServerLoop ConnCreate StreamConnection BackendStartup BackendRun PostgresMain InitPostgres PerformAuthentication ClientAuthentication hba_getauthmethod /* 从hba文件中，获取指定ip的认证方式 */ check_hba check_hostname check_ip /* 如果指定ip未获取到认证方式，则返回空 */ switch hba->auth_method: /* 根据ip采用不同的认证方式 */ case uaReject: case uaImplicitReject: case uaGSS: case uaSSPI: case uaPeer: ... case uaMD5: case uaPassword: case uaCert: case uaTrust: ... 3.2 基于证书的身份认证 创建主体的语法示例如下： ...

摘要：从5个方面，介绍openGauss审计特性：背景、原理、使用、设计、实现 1 审计的背景 1.1 审计的原理 在使用数据库存取数据的大部分场景中，数据库具有以下特点： 对象多：一个数据库集群中有多个database对象，1个database对象中有多个schema对象，1个schema对象中有多个table、index等对象 用户多：一个数据库中，有多个用户，每个用户可访问的数据库对象不同 应用多：多个应用将数据存储在同一数据库集群中，并同时访问数据库集群 操作多：数据库提供丰富的功能，包括DDL、DML、DCL、运维等 运行久：一个数据库集群，可能会保持数十年运行 审计的主要目的，使记录数据运行过程中发生了哪些事件。审计的3要素是： 主体：一般指数据库用户 客体：一般指数据库对象，包括：集群、用户、连接、数据对象（database、schema、table、index、function等） 操作：主体对客体的操作，比如某个用户访问了某个表 1.2 审计的目的 审计具有以下重要意义： 攻击检测：通过检查审计日志，可判断是否发生网络安全攻击事件 例行维护：通过检查审计日志，可跟踪对某个对象的访问信息 故障检测：当数据库出现故障是，协助快速定位问题 合法合规：《网络安全法》等法律法规与表中中，要求应用具备审计功能 … 1.3 审计的功能 审计功能包含以下3个部分： 设置审计范围：通过数据库配置参数，或者数据库语法，定义哪些对象需要被审计 设置审计存储：通过数据库配置参数，定义审计日志如何存储 生成审计日志：数据库运行过程中，访问某个对象时，记录访问信息 管理审计日志：查询，删除，归档审计日志 1.4 oracle审计 oracle审计的发展： - oracle 8i及更早：支持传统审计 - oracle 12c：新增支持统一审计 - oracle 23ai：不支持传统审计，仅支持统一审计 oracle统一审计的使用： -- 1 创建审计策略 CREATE AUDIT POLICY ap1 PRIVILEGES CREATE TABLE, ALTER USER -- privilege 子句，记录所有使用CreateTable, AlterUser权限的操作 ACTIONS SELECT,INSERT,UPDATE ON t1 -- aciton 子句，记录所有对表t1执行SELECT,INSERT,UPDATE的操作 ROLES r1; -- role 子句，记录所有使用r1角色的操作 -- 2 启用审计策略 AUDIT POLICY ap1; -- 3 查看审计日志 SELECT * FROM UNIFIED_AUDIT_TRAIL; -- DBA_COMMON_AUDIT_TRAIL, DBA_AUDIT_SESSION, DBA_AUDIT_OBJECT, DBA_FGA_AUDIT_TRAIL SELECT * FROM DBA_AUDIT_TRAIL; os_username | username | userhost | terminal | timestamp | owner | obj_name | action | action_name | ------------+----------+----------+----------+-----------+-------+----------+--------+-------------+ | new_owner | new_name | obj_privilege | sys_privilege | admin_option | grantee | audit_option | ses_actions | +----------+----------+---------------+---------------+--------------+---------+--------------+-------------+ logoff_time | logoff_lread | logoff_pwrite | logoff_dlock | comment_text | sessionid | entryid | statementid | returncode | +-----------+--------------+---------------+--------------+--------------+-----------+---------+-------------+------------+ | priv_used | client_id | econtext_id | session_cpu | extended_timestamp | proxy_sessionid | global_uid | instance_number | +-----------+-----------+-------------+-------------+--------------------+-----------------+------------+-----------------+ os_process | transactionid | scn | sql_bind | sql_text | obj_edition_name | dbid | rls_info | current_user | +----------+---------------+-----+----------+----------+------------------+------+----------+--------------+ 其中，可设置的权限、操作等范围如下： ...

1 访问控制的背景 在使用数据库存取数据的大部分场景中，数据库具有以下特点： 对象多：一个数据库集群中有多个database对象，1个database对象中有多个schema对象，1个schema对象中有多个table、index等对象 用户多：一个数据库中，有多个用户，每个用户可访问的数据库对象不同 在该场景中，通过访问控制机制，可限制某个对象访问数据库对象的范围 2 访问控制的原理 访问控制的模型是：主体、客体、操作。本文不解释基本概念。 主体 USER 角色： SYSADMIN 系统管理员 CREATEROLE 安全管理员 AUDITADMIN 审计管理员 MONADMIN 监控管理员 OPRADMIN 运维管理员 POLADMIN 安全策略管理员 客体 DATABASE SCHEMA TABLESPACE TABLE COLUMN FUNCTION PROCEDURE SEQUENCE FOREIGN SERVER LANGUAGE 操作 客体 CREATE DATABASE, SCHEMA, TABLESPACE ALTER DATABASE, SCHEMA, TABLESPACE, TABLE, FUNCTION, PRODUCE, FOREIGN SERVER, DIRECOTRY, PACKAGE DROP DATABASE, SCHEMA, TABLESPACE, TABLE, FUNCTION, PRODUCE, FOREIGN SERVER, DIRECOTRY, PACKAGE TRUNCATE TABLE VACUUM TABLE INDEX TABLE TRIGGER TABLE SELECT TABLE, COLUMN, SEQUENCE INSERT TABLE, COLUMN UPDATE TTABLE, COLUMN, SEQUENCE DELETE TABLE TEMPORARY DATABASE EXECUTE FUNCTION, PROCEDURE, PACKAGE 语法： ...