Hologres Privileges — Ho记录res Privileges

Prerequisites

This 技能 requires ho记录res-命令行工具 to be 安装ed first:

pip 安装 ho记录res-命令行工具 导出 HO记录RES_技能=ho记录res-privileges

All SQL execution depends on ho记录res-命令行工具 commands (ho记录res sql 运行 --write).

Ho记录res Privilege Management (Expert 权限 模型)

Manage fine-grAIned 访问 control in Ho记录res using standard PostgreSQL GRANT/REVOKE syntax.

权限 模型 Overview

Ho记录res provides three 权限 模型s. This 技能 focuses on the Expert 模型.

模型 Granularity Use Case Expert (PostgreSQL Standard) Table/Column/View level Fine-grAIned control, per-table/per-user SPM (Simple 权限 模型) Database level Quick 设置up, 4 pre设置 角色 groups SLPM (模式-Level 权限 模型) 模式 level Multi-team isolation with simplified management

The expert 模型 uses standard PostgreSQL GRANT/REVOKE syntax. It only 应用lies to existing objects — use ALTER DEFAULT PRIVILEGES for future objects.

Quick 启动 -- 1. 创建 user (RAM user 格式化: p4_) 创建 USER "p4_1822780xxx";

-- 2. Grant 模式 访问 (required for any table 查询) GRANT USAGE ON 模式 public TO "p4_1822780xxx";

-- 3. Grant table read 权限 GRANT SELECT ON TABLE public.orders TO "p4_1822780xxx";

-- 4. 验证 权限 SELECT has_table_privilege('p4_1822780xxx', 'public.orders', 'SELECT');

User Management Account Types Type 格式化 Example Alibaba Cloud mAIn account Numeric UID 11822780xxx RAM sub-account p4_ + UID p4_1822780xxx Custom user (BASIC) BASIC$ + name BASIC$dev_user 创建 Users -- 创建 user with 记录in privilege 创建 USER "p4_1822780xxx";

-- 创建 user as Superuser 创建 USER "p4_1822780xxx" SUPERUSER;

-- 创建 custom user with password 创建 USER "BASIC$dev_user" WITH PASSWORD '安全_password';

Alter Users -- Promote to Superuser ALTER USER "p4_1822780xxx" SUPERUSER;

-- Demote to normal user ALTER USER "p4_1822780xxx" NOSUPERUSER;

-- Change custom user password ALTER USER "BASIC$dev_user" WITH PASSWORD 'new_password';

删除 Users -- Drop user (no owned objects) DROP USER "p4_1822780xxx";

-- Drop user with owned objects (transfer first) REAS签名 OWNED BY "p4_old_uid" TO "p4_new_uid"; DROP USER "p4_old_uid";

Core Grant Syntax 模式 Privileges -- Grant 模式 访问 (required before any table 查询) GRANT USAGE ON 模式 模式_name TO "user_id";

-- Grant ability to 创建 tables in 模式 GRANT 创建 ON 模式 模式_name TO "user_id";

Table Privileges -- Grant specific privileges on a single table GRANT SELECT ON TABLE 模式_name.table_name TO "user_id"; GRANT SELECT, INSERT, 更新, 删除 ON TABLE 模式_name.table_name TO "user_id";

-- Grant on all existing tables in a 模式 GRANT SELECT ON ALL TABLES IN 模式 public TO "user_id";

-- Grant to all users GRANT SELECT, INSERT, 更新 ON ALL TABLES IN 模式 public TO PUBLIC;

Column Privileges -- Grant SELECT on specific columns only GRANT SELECT (column1, column2) ON TABLE 模式_name.table_name TO "user_id";

View Privileges GRANT SELECT ON view_name TO "user_id";

Grant with Transfer (WITH GRANT OPTION) -- Allow the grantee to re-grant this privilege to others GRANT SELECT ON TABLE 模式_name.table_name TO "user_id" WITH GRANT OPTION;

Owner Transfer

Only the table Owner or Superuser can DROP/ALTER a table.

-- Transfer table ownership ALTER TABLE 模式_name.table_name OWNER TO "user_id";

-- Transfer ownership to a 角色 group ALTER TABLE 模式_name.table_name OWNER TO 角色_name;

Default Privileges (Future Objects)

GRANT only 应用lies to existing objects. Use ALTER DEFAULT PRIVILEGES so that future tables automatically inherit 权限s.

-- All future tables 创建d by user1 in public 模式 are readable by everyone ALTER DEFAULT PRIVILEGES FOR 角色 "user1" IN 模式 public GRANT SELECT ON TABLES TO PUBLIC;

-- Only user2 can read future tables 创建d by user1 ALTER DEFAULT PRIVILEGES FOR 角色 "user1" IN 模式 public GRANT SELECT ON TABLES TO "user2";

-- Revoke a default privilege rule ALTER DEFAULT PRIVILEGES FOR 角色 "user1" IN 模式 public REVOKE SELECT ON TABLES FROM PUBLIC;

-- 检查 current default privilege 设置tings SELECT pg_cata记录.pg_获取_userbyid(d.defacl角色) AS "Owner", n.nspname AS "模式", CASE d.defaclobjtype WHEN 'r' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'function' WHEN 'T' THEN 'type' END AS "Type", pg_cata记录.array_to_string(d.defaclacl, E'\n') AS "访问 privileges" FROM pg_cata记录.pg_default_acl d LEFT JOIN pg_cata记录.pg_namespace n ON n.oid = d.defaclnamespace ORDER BY 1, 2, 3;

导入ant: ALTER DEFAULT PRIVILEGES FOR 角色 "X" only 应用lies when user X 创建s the object. If another user 创建s tables, the rule does not trigger.

Revoke Privileges Scope SQL Single table REVOKE SELECT ON TABLE 模式.table FROM "user_id"; All tables in 模式 REVOKE ALL ON ALL TABLES IN 模式 public FROM "user_id"; 模式 访问 REVOKE USAGE ON 模式 模式_name FROM "user_id"; Column privilege REVOKE SELECT (col1) ON TABLE 模式.table FROM "user_id"; Permi