-- 这里必须要切换到 `postgres` 这个超级用户才能进行客户端管理:
-- sudo -i -u postgres
-- 之后切换完成在命令行输入 
-- psql
-- 后续输入以下指令创建账号

-- 创建个 `manager` 的账号, 密码为 `qwerty123456`
CREATE ROLE manager WITH
    LOGIN
    SUPERUSER
    CREATEDB
    CREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1
    PASSWORD 'qwerty123456';
COMMENT ON ROLE manager IS '自定义对外数据库管理账号';
-- 这里需要说明 WITH 之后每个参数所代表的意义(生产环境中应谨慎分配 SUPERUSER 此权限)
-- LOGIN : 指定该角色可以登录数据库, 相当于创建了一个数据库用户. 没有 LOGIN 属性的角色只能作为权限组使用, 无法直接连接数据库
-- SUPERUSER : 赋予该角色超级用户权限. 拥有此属性的角色可以绕过数据库的所有权限检查(除了 LOGIN 权限限制), 能执行所有操作(如创建 / 删除数据库、修改系统配置、管理所有角色等)
-- CREATEDB : 允许该角色创建新的数据库. 没有此权限的角色无法执行 CREATE DATABASE 语句
-- CREATEROLE : 允许该角色创建、修改、删除其他数据库角色. 拥有此权限的角色可以管理其他角色的属性(如权限、密码等), 但需要注意：普通的 CREATEROLE 角色无法创建超级用户, 除非自身也是超级用户
-- INHERIT : 指定该角色可以继承其所属角色的权限. 如果这个 manager 角色被加入其他角色组, 那么 manager 会自动拥有该角色组的所有权限. 这是 PostgreSQL 角色的默认属性, 与之相对的是 NOINHERIT(不继承权限)
-- NOREPLICATION : 指定该角色不是复制角色. 复制角色(REPLICATION 属性)用于流复制场景, 通常是备库连接主库时使用的角色. NOREPLICATION 表示此角色不能用于复制操作
-- CONNECTION LIMIT -1 : 设置该角色的最大并发连接数限制. -1 表示无限制, 即该角色可以同时建立任意数量的数据库连接.如果设置为正数(如 10), 则最多只能有 10 个并发连接
-- PASSWORD 'qwerty123456': 设置该角色的登录密码为 qwerty123456. 密码会被加密后存储在系统表中, 用于客户端登录时的身份验证
-- COMMENT ON ROLE 账户名 IS '详情': 给角色添加注释, 方便管理员理解该角色的用途


-- 如果不满意的话可以删除账号
DROP ROLE manager;


-- 构建简单的底权限账户, 假设创建个 api 角色指定只能处理 sdk 库, 这类账号不允许设置超级权限
CREATE ROLE api WITH
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1
    PASSWORD 'qwerty123456';
COMMENT ON ROLE api IS '仅用于操作 sdk 数据库的低权限账号';
-- NOSUPERUSER  -- 明确无超级权限,生产环境必须
-- NOCREATEDB   -- 禁止创建数据库
-- NOCREATEROLE -- 禁止管理其他角色
-- 建议生产环境使用强密码

---- -----------------------------------
---- 注意: 以下需要用高权限登陆处理
---- -----------------------------------

-- 授予连接 sdk 数据库的权限
GRANT CONNECT ON DATABASE sdk TO api;

-- 切换到 sdk 数据库执行（否则无法授予模式权限）
\c sdk

-- 授予对 public 模式的使用权限
GRANT USAGE ON SCHEMA public TO sdk;

-- 授予对 public 模式下所有表增删改查的权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO sdk;

-- 自动授予未来新增表的权限(避免后续建表重复授权)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO sdk;

-- 查询数据库列表, 以下语句都是查询数据库列表
\l
SELECT datname
FROM pg_database;

-- 创建数据库指定编码
CREATE DATABASE sdk
    WITH
    ENCODING = 'UTF8';
-- PSQL 不需要处理类似 mysql 的 utf8/utf8mb3/utf8mb4 差别, 这是 mysql 之类的设计缺陷

-- 切换数据库
\c sdk

-- 创建表设置 id 为主键并设置自增长
-- SERIAL 对应 MYSQL 的 integer 类型（范围 -2147483648 ~ 2147483647）
-- BIGSERIAL 对应 MYSQL 的 bigint 类型, 范围更大
CREATE TABLE sdk_user_info
(
    id       BIGSERIAL    NOT NULL PRIMARY KEY,
    username VARCHAR(64)  NOT NULL,
    email    VARCHAR(255) NOT NULL DEFAULT ''
);

-- 给字段加注释, PSQL 和 MySQL 标识注释差别很大, 必须字符串采用单引号(字符串采用双引号可能会异常)
COMMENT ON COLUMN sdk_user_info.id IS '用户唯一标识，自增主键';
COMMENT ON COLUMN sdk_user_info.username IS '用户登录名';
COMMENT ON COLUMN sdk_user_info.email IS '邮箱地址';

-- 给表加注释
COMMENT ON TABLE sdk_user_info IS '测试用户表';

-- 查看建表详情
\d+ sdk_user_info

-- 插入单条数据（自增主键 id 可省略）
INSERT INTO sdk_user_info(username, email)
VALUES ('meteorcat', '[email protected]');

-- 查询所有字段、所有数据
SELECT *
FROM sdk_user_info;

-- 查询指定字段 + 条件过滤
SELECT username, email
FROM sdk_user_info
WHERE id > 1;

-- 排序 + 分页（PostgreSQL 推荐用 LIMIT + OFFSET）
SELECT *
FROM sdk_user_info
ORDER BY id DESC
LIMIT 10 OFFSET 0;
-- 取前10条

-- 去重查询
SELECT DISTINCT username
FROM sdk_user_info;

-- 聚合查询（统计总数）
SELECT COUNT(*) AS user_count
FROM sdk_user_info;

-- 修改单条数据(必须加 WHERE，否则修改全表！)
UPDATE sdk_user_info
SET email = '[email protected]'
WHERE id = 1;

-- 删除单条数据
DELETE
FROM sdk_user_info
WHERE id = 100;