谁在连接
  -> 从哪里、通过哪条加密路径
      -> 以哪个 login 通过认证
          -> 当前使用哪个 effective role
              -> 对哪个对象有什么权限
                  -> 哪些行可见、哪些新值可写
                      -> 谁能变更这些规则
                          -> 事件能否被安全地调查和撤销

SCRAM 成功              != 网络中的服务器身份正确
TLS 已加密              != 客户端验证了证书名称
HBA 匹配                != 角色有对象权限
GRANT SELECT            != 能看见全部行
RLS 生效                != table owner / superuser 也受约束
密码已经修改            != 旧连接已经断开
PostgreSQL role 已创建   != PgBouncer 已交付该身份
日志很多                != 有完整、受保护、可检索的审计链

资产与主体
  -> 信任边界和攻击路径
      -> HBA/认证/TLS
          -> login 与 effective role
              -> 对象所有权和最小权限
                  -> RLS 行边界
                      -> transaction-pool 上下文
                          -> secret 生命周期
                              -> 日志、审计和脱敏
                                  -> Pigsty 声明/渲染/运行差异
                                      -> 双租户对抗性验收

login identity
  ├─ SET TRUE, INHERIT FALSE -> runtime NOLOGIN
  └─ SET TRUE, INHERIT FALSE -> readonly NOLOGIN

migration identity
  -> migrate NOLOGIN
      -> SET TRUE, INHERIT FALSE -> owner NOLOGIN

break-glass
  -> 独立控制；不属于应用正常路径

GRANT pg36_ch23_runtime TO test
WITH ADMIN FALSE, INHERIT FALSE, SET TRUE;

BEGIN;
SET LOCAL ROLE pg36_ch23_runtime;
SELECT set_config('app.tenant_id', $1, true);

-- 所有业务 SQL；$1 必须来自已认证、已授权的应用身份映射

COMMIT;

ALTER TABLE pg36_ch23.account ENABLE ROW LEVEL SECURITY;
ALTER TABLE pg36_ch23.account FORCE ROW LEVEL SECURITY;

SELECT            USING
INSERT            WITH CHECK
UPDATE            USING + WITH CHECK
owner             USING + WITH CHECK，且 FORCE RLS

target          pg36-l2-vagrant/pg-test
Pigsty          v4.5.0
PostgreSQL      18.6
PgBouncer       1.25.2, transaction mode
database        test
fixture         schema pg36_ch23, two tenants, four synthetic rows
topology        pg-test-1 primary, pg-test-2/3 streaming replicas
timeline        11 before and after

five synthetic roles             all NOLOGIN after drill
superuser/CREATEDB/CREATEROLE     false
REPLICATION/BYPASSRLS             false
runtime table ACL                 SELECT, INSERT, UPDATE
readonly table ACL                SELECT
schema CREATE for runtime         false
RLS / FORCE RLS                   true / true
policies                           5
tenant row counts                  2 + 2

runtime tenant A                  exactly 2 A rows
runtime tenant B                  exactly 2 B rows
missing context                   0 rows
malformed context                 SQLSTATE 22P02
cross-tenant INSERT               SQLSTATE 42501
cross-tenant UPDATE               SQLSTATE 42501
runtime disable RLS               SQLSTATE 42501
runtime CREATE/TRUNCATE           SQLSTATE 42501
readonly INSERT                   SQLSTATE 42501
row_security=off                  SQLSTATE 42501, not a bypass
raw sandbox login                 SQLSTATE 42501
owner without context             0 rows under FORCE RLS
owner with tenant A               2 A rows
superuser break-glass             all 4 rows

default_pool_size=50
reserve_pool_size=30
reserve_pool_timeout=1
query_wait_timeout=120

default_pool_size=1
reserve_pool_size=0
reserve_pool_timeout=1
query_wait_timeout=15

tenant A local context        A 的 2 行
missing context               0 行
tenant B local context        B 的 2 行
missing context               0 行

PostgreSQL ssl                          on
minimum protocol                       TLSv1.2
direct sslmode=require                  TLSv1.3 / AES-256-GCM
direct verify-full                      成功
wrong certificate name                 拒绝
verify-full + channel_binding=require   成功
direct sslmode=disable                  成功，生产缺口
PgBouncer client TLS                    disable
pooled sslmode=disable                  成功
pooled sslmode=require                  拒绝，生产缺口
HBA parser errors                       0
server private-key mode                 0600
certificate SAN                         覆盖各节点 DNS 与 IP
CRL file/directory                      未配置

secret v1 new connection               成功
pool connection                        失败；身份面未声明
change to secret v2
secret v1 new connection               失败
secret v2 new connection               成功
already-authenticated v1 session       仍可用
ALTER ROLE ... NOLOGIN
new connection                         失败
already-authenticated session          仍可用
final PASSWORD NULL + NOLOGIN          已验证

identity/role separation                通过
object ACL                              通过
two-tenant FORCE RLS                    通过
transaction-local pool context          通过
credential lifecycle semantics          通过
public cert and key-mode checks          通过
topology/pool restoration                通过

direct business TLS enforcement          未通过
PgBouncer client TLS                     未通过
CRL/revocation drill                     未完成
client CA distribution/rotation          未完成
pgAudit                                  未安装/未加载
log bind-parameter policy                待整改
production approval                      pending

EX23-TRUSTED-INTRANET-NO-TLS
  普通业务 HBA 使用 host + SCRAM；内网明文 TCP 可成功。

EX23-PGBOUNCER-CLIENT-TLS-DISABLED
  池化客户端入口没有 TLS；不能通过生产传输门禁。

EX23-NO-CRL-OR-ROTATION-DRILL
  证书命名正确，但没有执行 CA/cert/CRL 双版本轮换与撤销。

EX23-NO-PGAUDIT
  shared_preload_libraries 没有 pgAudit，扩展也未安装。

EX23-FULL-NONERROR-BIND-PARAMETERS
  log_parameter_max_length=-1；与慢 SQL 日志组合时可能记录完整 bind 值。

EX23-SYNTHETIC-TWO-TENANT
  只有四行合成数据、一个 schema；不能推出复杂产品的 policy 正确。

EX23-MULTIPLEXED-TEST-LOGIN
  为复用已有 PgBouncer 声明，formal lab 用 test 切换 runtime/readonly；
  生产应为 workload 配置独立 login 与 credential。

保护什么
防谁做什么
跨过哪条边界
造成什么后果
由哪一层阻止、发现、限制和恢复

human end user
  -> application service identity
      -> PostgreSQL session_user
          -> PostgreSQL current_user
              -> business tenant / subject

SELECT session_user, current_user;

session_user = test
current_user = pg36_ch23_runtime

HTTP header X-Tenant-ID
  -> 只能作为一个待校验输入
  -> 应用根据已认证 actor 和授权关系求出 authorized tenant
  -> 再用 bind parameter 写入 transaction-local database context

SET app.tenant_id = request.headers["X-Tenant-ID"]

routine operator
  read catalogs / metrics / logs
  run approved bounded procedures
  no arbitrary data access by default

break-glass operator
  time-bound elevation
  ticket + reason + peer/after-the-fact review
  short credential lifetime
  complete action evidence
  explicit revoke

OS root                    可以读数据目录和进程内存
PostgreSQL superuser       可以绕过数据库权限
Pigsty/Ansible controller  可以改 inventory 并重渲染大量节点
secret administrator       可以改变认证材料
backup administrator       可能读出全量历史数据

它获得什么数据和 metadata
credential 存在哪里、有效多久
是否能进一步委托
失败时是否 fail open
日志/备份保存在哪里
删除与撤销如何传播
供应链版本如何验证

client
  -> DNS / VIP / load balancer
      -> HAProxy
          -> PgBouncer
              -> PostgreSQL primary / replica
                  -> WAL archive / backup repository
                  -> logs / metrics / traces

0.0.0.0 listen + broad security group
intranet CIDR 被当成永久可信主体
TLS 可用但客户端允许降级
证书验证了 CA，却没有验证 hostname
管理面与业务面共用网络和 credential
监控接口可读 SQL 文本、role、database 和拓扑
DCS/API 被暴露后可以影响选主

database password / SCRAM verifier
client private key
server private key / CA private key
SSH key / sudo authority
Patroni / etcd / backup credentials
cloud access token
application secret-manager token
session cookie / OAuth token

repository identity
encryption at rest/in transit
key separation
immutable/retention policy
delete/legal-hold semantics
restore sandbox access
evidence cleanup

记录足以调查的 actor/action/resource/outcome/time/correlation
不记录不必要的 secret 和敏感 payload
限制谁能读、改、删
确保时间、完整性、保留和检索可用

wrong server name
  -> verify-full connection must fail

client disables TLS
  -> production endpoint must fail；本章 sandbox 反而成功，所以 gate pending

runtime attempts ALTER TABLE
  -> SQLSTATE 42501

tenant A writes tenant B row
  -> WITH CHECK rejects

session tenant context survives pool reuse
  -> reproduce, then replace with transaction-local context

old password after rotation
  -> new connection fails；existing connection remains and must be drained

new PostgreSQL role through pool
  -> absent from pool auth surface, connection fails

tenant 数量和规模
监管/密钥/驻留要求
故障与 noisy-neighbor 边界
备份/恢复粒度
迁移频率
operator 信任模型
成本

primary row
  -> indexes / materialized views / search index
  -> logical replication / CDC
  -> cache
  -> analytics warehouse
  -> backup / PITR restore
  -> logs / traces / support evidence

触发条件       正常路径不可用且存在明确风险
批准者         谁能批准，单人还是双人
身份           独立账号，禁止共享
时限           自动过期
范围           cluster/database/action/source
证据           ticket/reason/session/action/outcome
约束           禁止删审计、禁止无关数据浏览
退出           revoke/terminate/rotate
复盘           为什么需要、正常能力缺什么

1. 限制暴露面和新认证
2. 保存时间线、连接、日志与配置证据
3. 判断是 login、role、host、CA 还是控制面失陷
4. 创建/验证替代凭据和管理路径
5. 双版本切换合法客户端
6. NOLOGIN / HBA reject / revoke
7. 终止仍有风险的既有 session
8. 轮换上下游与 PgBouncer
9. 验证旧凭据失败
10. 查找未授权动作并恢复

service: pg36_shop
asset:
  - tenant orders
  - credentials
  - backups and logs
subjects:
  - api runtime
  - migration pipeline
  - operator
trust_crossings:
  - client -> pooled endpoint
  - login -> runtime role
  - runtime role -> tenant row
abuse_cases:
  - wrong tenant context
  - stolen password
  - session state reused by another client
  - unapproved DDL
controls:
  - verify-full + SCRAM
  - SET LOCAL ROLE
  - FORCE RLS
  - separate owner
  - rotation and session drain
evidence:
  - HBA/TLS/role/policy projections
  - positive and negative transactions
  - immutable audit correlation
owner: data-platform
reviewers: application-owner, security
production_exceptions: []

[ ] 列出数据、凭据、备份、日志和控制面资产
[ ] 区分 end user、service identity、session_user、current_user、tenant
[ ] 每个 workload 有独立能力与撤销边界
[ ] OS、database、platform、secret、backup 管理权没有无意合并
[ ] 画出 client 到 database、backup、logs 的每条信任跨越
[ ] 网络位置不被当作充分身份
[ ] SQL、role、owner、RLS 的攻击路径都有负向测试
[ ] 备份、WAL、日志和 support evidence 纳入数据分级
[ ] 租户隔离选择覆盖 backup/recovery/noisy-neighbor
[ ] break-glass 有触发、时限、证据、撤销和复盘
[ ] 凭据失陷流程会处理既有 session 与 PgBouncer
[ ] production exception 有 owner、期限和补偿控制

network reachability
  -> listener / proxy entry
      -> TLS negotiation and peer verification
          -> HBA first matching record
              -> authentication method
                  -> database CONNECT privilege

database -> user -> client address -> authentication method/options

client -> PgBouncer HBA/auth/TLS
PgBouncer -> PostgreSQL HBA/auth/TLS or local socket

SELECT
    rule_number,
    file_name,
    line_number,
    type,
    database,
    user_name,
    address,
    netmask,
    auth_method,
    options,
    error
FROM pg_hba_file_rules
ORDER BY rule_number NULLS LAST, file_name, line_number;

REVOKE CONNECT ON DATABASE appdb FROM app_login;

GRANT CONNECT ON DATABASE appdb TO app_login;

1. 从 inventory / policy source 生成候选配置
2. 检查宽规则、shadowed rule、host/hostssl 与来源 CIDR
3. 在节点上进行语法/解析检查
4. 保留当前管理连接与独立 break-glass 路径
5. reload，不把 reload 误写成 restart
6. 从允许来源做正例，从禁止来源做负例
7. 核对 pg_hba_file_rules 和认证日志
8. 失败则恢复上一份已验证配置并 reload

SHOW password_encryption;

bad:  psql postgresql://user:plain-password@host/db
bad:  ALTER ROLE user PASSWORD 'plain-password';  # copied into ticket/log
good: secret manager -> short-lived private file/fd/env contract -> client

sslmode=verify-full
channel_binding=require

verify-full          验证证书链和目标名称
channel_binding      把 SCRAM 认证绑定到已建立的 TLS channel

external principal -> PostgreSQL login role -> effective business role

auth_file       PgBouncer 本地认证材料
auth_query      从受控数据库函数/视图获取认证材料
auth_user       执行 auth_query 的受限身份

direct PostgreSQL new authentication     succeeds
PgBouncer authentication                 fails

application DSN host=pg-primary.example.com
certificate SAN DNS:pg-primary.example.com

sslmode=require       success, TLS 1.3
sslmode=verify-full   success with matching name
verify-full wrong name rejected

SELECT
    a.pid,
    a.usename,
    a.application_name,
    a.client_addr,
    s.ssl,
    s.version,
    s.cipher,
    s.bits,
    s.client_dn,
    s.issuer_dn
FROM pg_stat_activity AS a
JOIN pg_stat_ssl AS s USING (pid)
WHERE a.pid = pg_backend_pid();

ssl       true
version   TLSv1.3
cipher    TLS_AES_256_GCM_SHA384
bits      256

client ==TLS==> PgBouncer --Unix socket--> PostgreSQL

prepare
  -> distribute trust for old + new
      -> deploy new identity material
          -> reload/reconnect
              -> prove new works
                  -> prove fleet migrated
                      -> revoke old
                          -> prove old fails

client trust store: old CA + new CA
server certificate: switch old-signed -> new-signed
fleet evidence: all active clients trust and use new chain
client trust store: remove old CA

create new secret version
change database verifier
roll clients to new version
terminate/drain old sessions when policy requires
destroy old secret version

1. password v1 建立连接                         success
2. 改为 password v2
3. 使用 v1 建立新连接                           rejected
4. 使用 v2 建立新连接                           success
5. 原来用 v1 建立的 session 继续查询             success
6. ALTER ROLE ... NOLOGIN
7. 新认证                                       rejected
8. 已建立 session 继续查询                       success
9. 最终 PASSWORD NULL，NOLOGIN                  verified

credential revocation != session revocation

mechanism proof        PASS
production security    PENDING remediation

human/workload identity
  -> LOGIN role
      -> explicitly SET approved NOLOGIN role
          -> object ACL
              -> row policy

CREATE ROLE app_login LOGIN;
CREATE ROLE app_runtime NOLOGIN;

LOGIN
SUPERUSER
CREATEDB
CREATEROLE
REPLICATION
BYPASSRLS
CONNECTION LIMIT
VALID UNTIL

CREATE ROLE app_login
  LOGIN
  NOSUPERUSER NOCREATEDB NOCREATEROLE
  NOINHERIT NOREPLICATION NOBYPASSRLS;

app_login
  ├─ SET TRUE / INHERIT FALSE -> app_runtime
  └─ SET TRUE / INHERIT FALSE -> app_readonly

release identity
  -> app_migrate
      -> SET TRUE / INHERIT FALSE -> app_owner

app_login LOGIN
  -> owns schema
  -> owns tables
  -> can ALTER/DROP policies
  -> credential copied to every app instance

SELECT session_user, current_user, current_role;

SET ROLE app_runtime;

session_user   仍是完成认证的 login
current_user   变为权限检查使用的 effective role
current_role   与 current_user 对应

GRANT app_runtime TO app_login
WITH ADMIN FALSE, INHERIT FALSE, SET TRUE;

BEGIN;
SET LOCAL ROLE app_runtime;
-- business statements
COMMIT;

SELECT
    parent.rolname AS granted_role,
    member.rolname AS member_role,
    m.admin_option,
    m.inherit_option,
    m.set_option
FROM pg_auth_members AS m
JOIN pg_roles AS parent ON parent.oid = m.roleid
JOIN pg_roles AS member ON member.oid = m.member
ORDER BY 1, 2;

SELECT
    rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole,
    rolreplication, rolbypassrls, rolconnlimit, rolvaliduntil
FROM pg_roles
WHERE rolname LIKE 'app_%'
ORDER BY rolname;

BEGIN;
SET LOCAL ROLE app_runtime;
SELECT current_user;
COMMIT;
SELECT current_user;  -- 回到原 login

transaction A: SET LOCAL ROLE app_runtime; COMMIT
transaction B: business query

SELECT id FROM app.account;

database CONNECT
schema USAGE
table SELECT
column privilege, if table privilege is absent
RLS policy
role membership / ownership / bypass attributes

CONNECT
CREATE
TEMPORARY

USAGE   可以按名称访问 schema 中已获授权对象
CREATE  可以在 schema 中创建对象

GRANT CONNECT ON DATABASE appdb TO app_runtime;
GRANT USAGE ON SCHEMA app TO app_runtime;
REVOKE CREATE ON SCHEMA app FROM app_runtime;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

SELECT INSERT UPDATE DELETE
TRUNCATE REFERENCES TRIGGER
MAINTAIN

GRANT SELECT, INSERT, UPDATE ON TABLE app.account TO app_runtime;
REVOKE DELETE, TRUNCATE, REFERENCES, TRIGGER
ON TABLE app.account FROM app_runtime;

GRANT SELECT (id, display_name) ON app.account TO reporting_role;

GRANT USAGE, SELECT ON SEQUENCE app.account_id_seq TO app_runtime;

INSERT permission okay
nextval(...) -> permission denied for sequence

BEGIN;

CREATE FUNCTION app.rotate_secret(...)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, app_private
AS $function$
...
$function$;

REVOKE ALL ON FUNCTION app.rotate_secret(...) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION app.rotate_secret(...) TO security_operator;

COMMIT;

effective privilege =
  PUBLIC
  + direct grant
  + inherited membership
  + owner rights
  + special attributes / predefined roles

SELECT
    has_database_privilege('app_runtime', 'appdb', 'CONNECT') AS db_connect,
    has_schema_privilege('app_runtime', 'app', 'USAGE') AS schema_usage,
    has_schema_privilege('app_runtime', 'app', 'CREATE') AS schema_create,
    has_table_privilege('app_runtime', 'app.account', 'SELECT') AS can_select,
    has_table_privilege('app_runtime', 'app.account', 'TRUNCATE') AS can_truncate;

ALTER DEFAULT PRIVILEGES
FOR ROLE app_owner
IN SCHEMA app
GRANT SELECT, INSERT, UPDATE ON TABLES TO app_runtime;

以后由 app_owner 在 app schema 创建的 table
  -> 自动给 app_runtime 指定权限

BEGIN;
SET LOCAL ROLE app_owner;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

GRANT USAGE ON SCHEMA app TO app_runtime, app_readonly;
GRANT SELECT, INSERT, UPDATE
  ON ALL TABLES IN SCHEMA app TO app_runtime;
GRANT SELECT
  ON ALL TABLES IN SCHEMA app TO app_readonly;
GRANT USAGE, SELECT
  ON ALL SEQUENCES IN SCHEMA app TO app_runtime;

ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE ON TABLES TO app_runtime;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_runtime;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

COMMIT;

SELECT
    n.nspname,
    c.relname,
    c.relkind
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
JOIN pg_roles AS r ON r.oid = c.relowner
WHERE r.rolname = 'legacy_app'
ORDER BY 1, 2;

database, schema
table, sequence, view, materialized view
function, procedure
type, domain
publication/subscription
large object
default privileges
extension-owned dependencies

inventory all databases
  -> create NOLOGIN owner
      -> transfer ownership in a reviewed change
          -> recreate/verify default privileges
              -> run positive and negative tests
                  -> stop old workload
                      -> NOLOGIN + PASSWORD NULL
                          -> terminate old sessions if required

NOLOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
NOREPLICATION
NOBYPASSRLS

raw login SELECT table       SQLSTATE 42501
runtime CREATE               SQLSTATE 42501
runtime TRUNCATE             SQLSTATE 42501
readonly INSERT              SQLSTATE 42501

WHERE tenant_id = $tenant

authenticated end-user/workload
  -> authorized tenant mapping
      -> transaction-local database context
          -> effective role
              -> table ACL
                  -> RLS USING / WITH CHECK
                      -> positive + negative + reuse tests

table SELECT denied      -> permission denied
table SELECT allowed
  + RLS policy true      -> row visible
  + RLS policy false     -> row silently absent

ALTER TABLE app.account ENABLE ROW LEVEL SECURITY;

0 visible rows / no modifiable rows

SELECT
    n.nspname,
    c.relname,
    c.relrowsecurity,
    c.relforcerowsecurity
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.oid = 'app.account'::regclass;

SELECT
    policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'app'
  AND tablename = 'account'
ORDER BY policyname;

CREATE POLICY account_runtime_update
ON app.account
FOR UPDATE
TO app_runtime
USING (
    tenant_id = app.current_tenant()
)
WITH CHECK (
    tenant_id = app.current_tenant()
);

CREATE POLICY tenant_rows
ON app.account
FOR SELECT TO app_runtime
USING (tenant_id = app.current_tenant());

CREATE POLICY support_all_rows
ON app.account
FOR SELECT TO app_runtime
USING (true);

ordinary role                  subject to applicable RLS
table owner                    normally bypasses RLS
SUPERUSER                      always bypasses RLS
role with BYPASSRLS            always bypasses RLS

ALTER TABLE app.account FORCE ROW LEVEL SECURITY;

CREATE POLICY account_owner_all
ON app.account
FOR ALL TO app_owner
USING (tenant_id = app.current_tenant())
WITH CHECK (tenant_id = app.current_tenant());

ALTER TABLE app.account ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.account FORCE ROW LEVEL SECURITY;

owner, no tenant context       0 rows
owner, tenant A context        exactly 2 A rows
superuser break-glass          all 4 rows

SET row_security = off;

insert guessed email
  -> unique violation       may reveal an invisible matching row

UNIQUE (tenant_id, external_key)   -- tenant-local uniqueness
UNIQUE (email)                     -- intentionally global uniqueness

CREATE VIEW app.account_visible
WITH (security_invoker = true)
AS
SELECT ... FROM app.account;

{
  "tenant_id": "user-supplied-value",
  "operation": "list_accounts"
}

SELECT set_config('app.tenant_id', $1, true);

verified token / mTLS / session
  -> immutable principal id
      -> server-side authorization mapping
          -> authorized tenant id
              -> database transaction context

CREATE FUNCTION app.current_tenant()
RETURNS uuid
LANGUAGE sql
STABLE
PARALLEL SAFE
SET search_path = pg_catalog
RETURN NULLIF(
    pg_catalog.current_setting('app.tenant_id', true),
    ''
)::uuid;

tenant_id = app.current_tenant()

tenant_id = NULL -> UNKNOWN -> row rejected

missing context       accepted query, 0 rows, current tenant NULL
malformed context     SQLSTATE 22P02

application_name
request/correlation id
actor id
authorization decision id
tenant id

client transaction begins
  -> assign one PostgreSQL server connection
      -> execute transaction
          -> COMMIT / ROLLBACK
              -> return server connection to pool
                  -> next client may receive it

SELECT set_config('app.tenant_id', 'tenant-a', false);

BEGIN;

SET LOCAL ROLE app_runtime;

SELECT set_config(
    'app.tenant_id',
    $1,      -- server-authorized tenant id
    true     -- transaction-local
);

-- all business statements for this request

COMMIT;

authenticate request
  -> authorize tenant
      -> borrow logical client
          -> BEGIN
              -> SET LOCAL ROLE
              -> set tenant context
              -> execute callback/unit of work
          -> COMMIT or ROLLBACK
      -> release client

plan/cache reuse may be allowed
authorization context must be established per transaction

default_pool_size       50
reserve_pool_size       30
reserve_pool_timeout    1
query_wait_timeout      120

default_pool_size       1
reserve_pool_size       0
reserve_pool_timeout    1
query_wait_timeout      15

client A
  session set tenant A
  BEGIN
  SET LOCAL ROLE runtime
  SELECT
  COMMIT
  disconnect

client B
  does not set tenant
  BEGIN
  SET LOCAL ROLE runtime
  SELECT
  COMMIT

client A backend pid                 72521
client B backend pid                 72521
same backend                         true
client B effective tenant            tenant A
client B visible rows                2 rows of tenant A

tenant A request
missing-context request
tenant B request
missing-context request

row count
minimum tenant id
maximum tenant id
backend pid
effective tenant context
session_user / current_user

secret_note_exported = false

[ ] tenant source is server-authorized
[ ] login cannot inherit object grants without intended role
[ ] table ACL and RLS policies both reviewed
[ ] ENABLE + FORCE flags match design
[ ] views/functions/partitions alternate paths reviewed
[ ] transaction-local initialization is centralized
[ ] missing/malformed/cross-tenant tests pass
[ ] same-backend reuse test passes
[ ] rollback/timeout/retry paths pass
[ ] break-glass path is separate and audited
[ ] policy performance is measured on production-like cardinality

password and SCRAM verifier
TLS private key and certificate
CA private key, trust bundle and CRL
OAuth client secret / token
LDAP bind credential
backup encryption key
replication credential
PgBouncer authentication material
break-glass credential

human memorable password
service-name + environment + year
one shared password for all replicas/apps
copy production password to staging

key algorithm and size
signature algorithm
SAN identities
extended key usage
issuer and path length
validity and renewal window
private-key exportability

secret manager / HSM
  -> authenticated workload
      -> short-lived retrieval
          -> memory or private runtime file
              -> database driver

psql "postgresql://app:plaintext@db/app"

\password app_login

ALTER ROLE app_login PASSWORD 'plaintext';

S0 old active
  -> S1 new generated and stored
      -> S2 database accepts new
          -> S3 workloads use new
              -> S4 old new-auth rejected
                  -> S5 old sessions drained/terminated
                      -> S6 old copies destroyed

password v1 new auth                  success
change to password v2
password v1 new auth                  rejected
password v2 new auth                  success
session opened with v1                still usable
ALTER ROLE ... NOLOGIN
all new auth                          rejected
existing session                      still usable
final NOLOGIN + PASSWORD NULL         verified

ALTER ROLE compromised_login NOLOGIN PASSWORD NULL;

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'compromised_login'
  AND pid <> pg_backend_pid();

client -> PgBouncer identity
PgBouncer -> PostgreSQL identity

trust distribution
certificate/key deployment
service reload
new connection negotiation
old connection lifetime
old trust removal
revocation publication

role name
credential version id / hash reference
change timestamp
new-auth accepted boolean
old-auth rejected boolean
existing-session observation
NOLOGIN/password-present boolean
operator/change id
artifact hash

plaintext passwords
SCRAM verifiers
raw PgBouncer userlist
server/CA private keys
connection strings containing credentials

who authenticated, and by which original identity
which login and effective role executed
from which source and entrypoint
which database/object/action
when it began and ended
whether it succeeded
which rows/records were affected, at safe granularity
which policy/config/role changed
which approved request/incident authorized it
whether evidence could have been altered

application audit       end-user and business action
database audit          login/effective role and SQL object
platform audit          configuration/deployment/operator
security audit          secret access and identity events

timestamp session-id pid user database application client SQLSTATE

matching pgAudit branch/version for PostgreSQL major
shared_preload_libraries includes pgaudit
restart completed
CREATE EXTENSION pgaudit before setting pgaudit.log
selected classes/object audit configured
volume and sensitive-data test passed

restrict direct superuser
  -> named operator identity
      -> approved, time-bounded escalation
          -> external control-plane/host audit
              -> remote immutable-ish log copy
                  -> post-use review

CREATE / ALTER / DROP ROLE
GRANT / REVOKE role membership
GRANT / REVOKE object privilege
ALTER ... OWNER
ALTER DEFAULT PRIVILEGES
ENABLE / DISABLE / FORCE RLS
CREATE / ALTER / DROP POLICY
SECURITY DEFINER function changes
HBA / TLS / proxy authentication changes
secret reads and rotations
break-glass use

user and client IP
database/schema/table names
SQL text and literal values
bind parameters
error context
tenant/request identifiers
certificate distinguished names
security policy and topology clues

pgAudit installed/preloaded       no
standard log_statement            ddl
log_min_duration_statement        100 ms

SELECT *
FROM app.account
WHERE email = $1;

SQL construction safety
log data exposure

log_parameter_max_length
  -1   非错误 statement log 可记录完整 bind 参数
   0   禁止在这类日志记录 bind 参数
  >0   每个参数截断到指定字节

log_parameter_max_length_on_error
   0   error message 不附 bind 参数
  -1   可附完整参数
  >0   截断

log_parameter_max_length             -1
log_parameter_max_length_on_error      0

password
API token prefix
email
phone
credit-card number
small JSON secret

ALTER ROLE app PASSWORD 'secret';
INSERT INTO integration_config(api_token) VALUES ('secret');
SELECT call_remote_service('secret');

normalized query / query id
duration
rows
wait/error class
database/user/application
safe request correlation id
plan/statistics reference

full statement + all bind values for every request

unique fake password marker
unique fake token marker
unique fake PII marker

PostgreSQL local logs
PgBouncer / HAProxy logs
central log index
APM traces
application logs
CI artifacts
support bundles
chapter/evidence output

security event has enough attributable evidence
AND
credential/business values are absent unless explicitly required
AND
evidence readers and retention are controlled
AND
ingestion gaps and tampering attempts are observable

policy intent
  -> Pigsty inventory
      -> rendered files and service configuration
          -> PostgreSQL/PgBouncer runtime facts
              -> end-to-end positive and negative observations

pg_default_roles    环境级共享角色
pg_users            集群级业务角色/用户

pg_users:
  - name: app_owner
    login: false
    superuser: false
    createdb: false
    createrole: false
    inherit: false
    replication: false
    bypassrls: false
    comment: "NOLOGIN owner for app objects"

  - name: app_runtime
    login: false
    inherit: false
    comment: "NOLOGIN normal DML capability"

  - name: app_readonly
    login: false
    inherit: false
    comment: "NOLOGIN read-only capability"

  - name: app_login
    login: true
    password: "<SCRAM-VERIFIER-FROM-PRIVATE-SECRET-PIPELINE>"
    superuser: false
    createdb: false
    createrole: false
    inherit: false
    replication: false
    bypassrls: false
    connlimit: 100
    roles:
      - {name: app_runtime, admin: false, inherit: false, set: true}
      - {name: app_readonly, admin: false, inherit: false, set: true}
    pgbouncer: true
    pool_mode: transaction
    pool_connlimit: 80
    comment: "application authentication identity"

bin/pgsql-user <cluster> <username>

Pigsty inventory
  -> identity attributes, membership, pool admission

application migration
  -> schema/table owner, ACL, default privileges, RLS policies

pg_hba_rules:
  - title: "app direct path from approved application subnet"
    user: app_login
    db: appdb
    addr: "10.20.10.0/24"
    auth: ssl
    order: 50

  - title: "reject app direct path from all other sources"
    user: app_login
    db: appdb
    addr: world
    auth: deny
    order: 51

pgb_hba_rules:
  - title: "app pooled path from approved application subnet"
    user: app_login
    db: appdb
    addr: "10.20.10.0/24"
    auth: ssl
    order: 50

  - title: "reject app pooled path from all other sources"
    user: app_login
    db: appdb
    addr: world
    auth: deny
    order: 51

10.0.0.0/8
172.16.0.0/12
192.168.0.0/16

bin/pgsql-hba <cluster>

/pg/data/pg_hba.conf
/etc/pgbouncer/pgb_hba.conf

CA exists
server has certificate
listener supports TLS
HBA requires TLS
client trusts the intended CA
client verifies the intended DNS/IP name
client certificate, if required, is mapped and revocable

client ==TLS policy A==> PgBouncer
PgBouncer ==TLS/socket policy B==> PostgreSQL

PostgreSQL TLS supported
default intranet HBA may not require it
PgBouncer TLS controlled separately by pgbouncer_sslmode
Patroni REST TLS controlled separately

purpose
listener address/port
source allowlist
HBA/authentication
TLS name and CA
allowed roles/databases
pool mode
rate/connection limit
audit label
owner

source identity/network
destination service/address/port
direction
TLS/mTLS
authentication
authorization scope
availability dependency
log/audit
owner and review date

named human identity + MFA
short-lived SSH certificate/key
no shared permanent root password
separate automation service identity
command/change audit
restricted egress and inbound sources
workstation and bastion hardening
backup/recovery for control repository

cluster members
approved management/monitoring nodes

application subnet
  -> primary/replica service VIP/DNS
      -> HAProxy
          -> local/cluster PgBouncer
              -> PostgreSQL

management/migration subnet -> PostgreSQL direct
replication nodes            -> PostgreSQL replication
backup/monitor               -> dedicated minimum roles

cloud security group / network ACL
host firewall
listener + HBA

desired       reviewed inventory/policy
rendered      node-local file/service config
runtime       catalogs, settings, sockets, process state
observed      actual client connection or rejected action