postgresql 创建用户与赋权

• 创建只读用户并分配只读权限

  CREATE USER readonly_user WITH PASSWORD 'readonly_password';
  

• 授予只读权限

  GRANT USAGE ON SCHEMA public TO readonly_user;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
  

• 查询用户在表级别的权限

  SELECT 
    grantee AS username,
    privilege_type
  FROM information_schema.role_table_grants
  GROUP BY username,privilege_type
  

• 创建增删改查用户并分配增删改查权限

  CREATE USER full_access_user WITH PASSWORD 'full_access_password';
  

• 授予增删改查权限

  GRANT USAGE ON SCHEMA public TO full_access_user;
  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO full_access_user;
  

• 删除用户

  DROP USER username;
  

• 重命名

  ALTER ROLE readonly_user RENAME TO readonly_user_new;