一、基本操作

1.主键列修改为自增

-- 新增语句：id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
ALTER TABLE 表名 
    ALTER COLUMN id SET NOT NULL,
    ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

注：GENERATED ALWAYS：禁止手动指定值，只能由系统生成，GENERATED BY DEFAULT：允许手动指定值（类似 SERIAL）。

2、修改表字段类型

ALTER TABLE 表名
    ALTER COLUMN balance TYPE numeric -- 列类型改为numeric类型
    USING balance::numeric; -- 将已有数据转为numeric类型的方式

二、用户创建、授权等

1.创建账号

CREATE USER username WITH PASSWORD '123456';

-- 和上面的效果一样
CREATE USER username WITH 
    LOGIN         -- 允许该角色登录数据库
    NOSUPERUSER   -- 禁止该角色拥有超级用户权限
    NOCREATEDB    -- 禁止该角色创建数据库
    NOCREATEROLE  -- 禁止该角色创建新角色（用户）或修改其他角色
    NOREPLICATION -- 禁止该角色用于流复制
    INHERIT       -- 该角色继承其所属组角色的权限
    PASSWORD '123456';

-- 查看当前用户
SELECT session_user;

SELECT current_user;

-- 查看角色属性
SELECT 
    rolname,
    rolsuper,      -- 是否为超级用户
    rolinherit,    -- 是否继承权限
    rolcreaterole, -- 是否能创建角色
    rolcreatedb,   -- 是否能创建数据库
    rolcanlogin,   -- 是否能登录
    rolreplication -- 是否能用于复制
FROM pg_roles 
WHERE rolname = 'username';

2.修改密码

-- 修改当前用户的密码
ALTER USER current_user PASSWORD '123456';

-- 修改指定用户的密码
ALTER USER username PASSWORD '123456';

3.锁定/解锁账号

-- 锁定
ALTER USER username NOLOGIN;

-- 解锁
ALTER USER username LOGIN;

4.查看用户密码算法、权限

-- 查看密码算法（需 superuser）
SELECT rolname, rolpassword FROM pg_authid;

-- 查看用户属性
SELECT rolname, rolcanlogin, rolsuper, rolcreaterole, rolcreatedb FROM pg_roles ORDER BY rolname;

5.用户授权

-- 允许连接数据库
GRANT CONNECT ON DATABASE appdb TO username;

-- 只给某 schema 使用权限（在当前连接库里）
GRANT USAGE ON SCHEMA public TO username;

-- 允许读写已有表的数据（在当前连接库里）
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO username;

-- 允许在 schema 下创建表（在当前连接库里）
GRANT CREATE ON SCHEMA public TO username;

-- 未来新建表也自动授权
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username;

-- 允许执行函数、存储过程
GRANT EXECUTE ON FUNCTION public.calculate_amount TO username;

-- 授权已有函数
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO username;

-- 授权未来新建函数（用 schema owner 执行）
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO username;

6.权限撤销

-- 撤销连接数据库
REVOKE CONNECT ON DATABASE appdb FROM username;

-- 撤销 schema 使用权限（在当前连接库里）
REVOKE USAGE ON SCHEMA public FROM username;

-- 撤销读写已有表的数据（在当前连接库里）
REVOKE SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM username;

-- 撤销在 schema 下创建表（在当前连接库里）
REVOKE CREATE ON SCHEMA public FROM username;

-- 撤销执行函数、存储过程
REVOKE EXECUTE ON FUNCTION public.calculate_amount FROM username;

-- 撤销已有函数
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM username;

7.删除用户

-- 先清依赖，否则会报 cannot drop role ... because some objects depend on it
REASSIGN OWNED BY username TO postgres;
DROP OWNED BY username;
DROP USER username;

三、系统配置

此种配置将写入自动配置文件postgresql.auto.conf中，postgresql.auto.conf中的同名参数优先级更高，会覆盖postgresql.conf。

-- 查看当前配置
SHOW lc_numeric;

-- 写入postgresql.auto.conf
ALTER SYSTEM SET lc_numeric = 'en_US.UTF-8';

-- 让运行中的实例重新加载配置
SELECT pg_reload_conf();

删除自动配置，回退到conf或内置默认

ALTER SYSTEM SET lc_numeric = DEFAULT;   -- 等价 ALTER SYSTEM RESET lc_numeric;
SELECT pg_reload_conf();