Knowledge/1 使用/数据库/OpenGauss/基本操作.md

操作用户

su - openGauss

登录

gsql -d postgres -p 15000

• 用户和数据库状态一键查询脚本

SELECT 
    u.usename as "用户名",
    d.datname as "数据库名",
    CASE WHEN d.datdba = u.usesysid THEN '所有者' ELSE '使用者' END as "关系类型",
    CASE WHEN u.usesysid = (SELECT datdba FROM pg_database WHERE datname = d.datname) 
         THEN '是' ELSE '否' END as "是否为所有者",
    has_database_privilege(u.usename, d.datname, 'CONNECT') as "连接权限",
    has_database_privilege(u.usename, d.datname, 'CREATE') as "创建表权限"
FROM pg_user u
CROSS JOIN pg_database d
WHERE d.datname NOT LIKE 'template%'
ORDER BY u.usename, d.datname;

查看当前用户权限

SELECT 
    rolname,
    rolcreatedb,
    rolsuper 
FROM pg_roles 
WHERE rolname = current_user;

• 创建用户

CREATE USER future WITH PASSWORD 'FutureV587?'
  SYSADMIN    -- 授予系统管理员权限
  CREATEDB    -- 授予创建数据库的权限
  CONNECTION LIMIT 100; -- 限制最大连接数为100

• 创建数据库

CREATE DATABASE futrue
  WITH 
  OWNER = future    -- 指定所有者为 omm
  ENCODING = 'UTF8'  -- 设置编码
  LC_COLLATE = 'en_US.UTF-8' -- 排序规则
  LC_CTYPE = 'en_US.UTF-8'   -- 字符分类
  CONNECTION LIMIT = -1;     -- 连接数限制（-1表示无限制）

退出

\q