信创数据库之OpenGauss的Schema【华为根技术】

举报
tea_year 发表于 2025/05/26 16:33:32 2025/05/26
【摘要】 创建了数据库之后,还需要创建模式(Schema)才能够存储数据库对象。通常在创建一个新的数据库时,默认会创建一个模式 public。背景信息schema又称作模式。通过管理schema,允许多个用户使用同一数据库而不相互干扰,可以将数据库对象组织成易于管理的逻辑组,同时便于将第三方应用添加到相应的schema下而不引起冲突。管理schema包括:创建schema、使用schema、删除sch...

创建了数据库之后,还需要创建模式(Schema)才能够存储数据库对象。通常在创建一个新的数据库时,默认会创建一个模式 public

背景信息

schema又称作模式。通过管理schema,允许多个用户使用同一数据库而不相互干扰,可以将数据库对象组织成易于管理的逻辑组,同时便于将第三方应用添加到相应的schema下而不引起冲突。管理schema包括:创建schema、使用schema、删除schema、设置schema的搜索路径以及schema的权限控制。

注意事项

openGauss包含一个或多个已命名数据库。用户和用户组在openGauss范围内是共享的,但是其数据并不共享。任何与服务器连接的用户都只能访问连接请求里声明的那个数据库。

一个数据库可以包含一个或多个已命名的schema,schema又包含表及其他数据库对象,包括数据类型、函数、操作符等。同一对象名可以在不同的schema中使用而不会引起冲突。例如,schema1和schema2都可以包含一个名为mytable的表。

和数据库不同,schema不是严格分离的。用户根据其对schema的权限,可以访问所连接数据库的schema中的对象。进行schema权限管理首先需要对数据库的权限控制进行了解。

不能创建以PG_为前缀的schema名,该类schema为数据库系统预留的。

在每次创建新用户时,系统会在当前登录的数据库中为新用户创建一个同名Schema。对于其他数据库,若需要同名Schema,则需要用户手动创建。

通过未修饰的表名(名称中只含有表名,没有“schema名”)引用表时,系统会通过search_path(搜索路径)来判断该表是哪个schema下的表。pg_temp和pg_catalog始终会作为搜索路径顺序中的前两位,无论二者是否出现在search_path中,或者出现在search_path中的任何位置。search_path(搜索路径)是一个schema名列表,在其中找到的第一个表就是目标表,如果没有找到则报错。(某个表即使存在,如果它的schema不在search_path中,依然会查找失败)在搜索路径中的第一个schema叫做“当前schema”。它是搜索时查询的第一个schema,同时在没有声明schema名时,新创建的数据库对象会默认存放在该schema下。

每个数据库都包含一个pg_catalog schema,它包含系统表和所有内置数据类型、函数、操作符。pg_catalog是搜索路径中的一部分,始终在临时表所属的模式后面,并在search_path中所有模式的前面,即具有第二搜索优先级。这样确保可以搜索到数据库内置对象。如果用户需要使用和系统内置对象重名的自定义对象时,可以在操作自定义对象时带上自己的模式。

Schema操作步骤

创建管理用户及权限schema

执行如下命令来创建一个schema。

语法格式

创建SCHEMA

CREATE SCHEMA schema_name   [ AUTHORIZATION user_name ] ;

修改SCHEMA  修改模式的名称

ALTER SCHEMA schema_name  RENAME TO new_name;

修改模式的所有者。

ALTER SCHEMA schema_name OWNER TO new_owner;

删除SCHEMA及其对象。

DROP SCHEMA schema_name;

不要随意删除pg_temp或pg_toast_temp开头的模式,这些模式是系统内部使用的,如果删除,可能导致无法预知的结果。

参数说明

schema_name

模式名称

须知: 模式名不能和当前数据库里其他的模式重名。 模式的名称不可以“pg_”开头。

取值范围:字符串,要符合标识符的命名规范。

AUTHORIZATION user_name

指定模式的所有者。当不指定schema_name时,把user_name当作模式名,此时user_name只能是角色名。

取值范围:已存在的用户名/角色名。

schema_element

在模式里创建对象的SQL语句。目前仅支持CREATE TABLE、CREATE VIEW、CREATE INDEX、CREATE PARTITION、CREATE SEQUENCE、CREATE TRIGGER、GRANT子句。

子命令所创建的对象都被AUTHORIZATION子句指定的用户所拥有。

说明: 如果当前搜索路径上的模式中存在同名对象时,需要明确指定引用对象所在的模式。可以通过命令SHOW SEARCH_PATH来查看当前搜索路径上的模式。

根据用户名创建模式。

openGauss=# CREATE SCHEMA test AUTHORIZATION user1;
CREATE SCHEMA

查看当前搜索路径

openGauss=# SHOW SEARCH_PATH;
search_path
----------------
"$user",public
(1 row)

更改当前会话的默认Schema。

openGauss=# SET SEARCH_PATH TO test1, public;
SET

删除SCHEMA及其对象。

openGauss=# DROP SCHEMA test1;

DROP SCHEMA

使用以下命令查看现有的schema。

openGauss=# SELECT current_schema();

current_schema
----------------

myschema

(1 row)

使用schema

在特定schema下创建对象或者访问特定schema下的对象,需要使用有schema修饰的对象名。该名称包含schema名以及对象名,他们之间用“.”号分开。

执行如下命令在myschema下创建mytable表。

openGauss=# CREATE TABLE myschema.mytable(id int, name varchar(20));
CREATE TABLE

如果在数据库中指定对象的位置,就需要使用有schema修饰的对象名称。

执行如下命令查询myschema下mytable表的所有数据。

openGauss=# SELECT * FROM myschema.mytable;
id | name
----+------
(0 rows)

常见错误:

错误一:无法删除某个角色

1. 检查依赖关系

确认角色 test 是哪些模式的所有者:

sql复制代码

SELECT schema_name FROM information_schema.schemata WHERE schema_owner = 'test';

若返回结果包含 test 模式,说明该角色是模式的所有者。

2. 转移模式所有权

test 模式的所有权转移给其他角色(如 admin 或新建角色):

-- 方法1:直接转移给现有角色(需目标角色存在)

ALTER SCHEMA test OWNER TO admin;

再次删除,发现即可删除成功。

错误二:删除角色关联所有者、对象较多

openGauss=# drop role tony;
ERROR: role "tony" cannot be dropped because some objects depend on it
DETAIL: owner of database mydb
owner of database human_staff
1 object in database human_staff

1. 确认角色依赖关系

通过错误提示可知,角色tony是以下对象的所有者:

数据库mydb

数据库human_staff

数据库human_staff中的1个对象(如表、模式等)

2. 转移数据库所有权

若需保留数据库,需将其所有权转移给其他角色(如admin):

openGauss=# alter database mydb owner to gauss;

openGauss=# alter database human_staff owner to gauss;

3. 处理数据库内的对象

方法1:转移对象所有权

tony拥有的对象(如表、视图等)的所有权转移给其他角色:

-- 切换到目标数据库

\c human_staff
-- 转移所有对象的所有权(慎用,需超级用户权限)
REASSIGN OWNED BY tony TO admin;
-- 或逐个转移特定对象
ALTER TABLE <table_name> OWNER TO admin;
ALTER SCHEMA <schema_name> OWNER TO admin;

4. 撤销角色权限

tony在其他数据库(如postgres)中有权限,需撤销这些权限:

sql复制代码

-- 切换到postgres数据库

\c postgres

-- 撤销对特定对象的权限REVOKE ALL ON DATABASE postgres FROM tony;REVOKE ALL ON SCHEMA public FROM tony; -- 示例:撤销对public模式的权限REVOKE ALL ON TABLE pg_class FROM tony; -- 示例:撤销对系统表的权限

5. 清理共享依赖

tony的依赖关系涉及共享对象(如角色权限),需检查并清理:

sql复制代码

-- 查看共享依赖关系(需超级用户)SELECT * FROM pg_shdepend WHERE refobjid = (SELECT oid FROM pg_roles WHERE rolname = 'tony');

-- 手动撤销相关权限REVOKE USAGE ON SCHEMA <schema_name> FROM tony; -- 示例:撤销对模式的使用权

6. 删除角色

完成上述步骤后,尝试再次删除角色:

DROP ROLE tony;

注意事项

1. 权限要求:转移所有权、撤销权限等操作需由超级用户或具备足够权限的角色执行。

2. 数据安全:使用DROP OWNED BYDROP DATABASE前,务必确认无需保留相关数据。

3. 系统依赖:若tony是系统管理员(rolsuperrolsystemadmin),需先转移系统权限。

总结

通过转移数据库和对象的所有权、撤销角色权限、清理依赖关系,可以安全删除角色tony。具体操作需根据实际依赖关系调整,建议先备份数据再执行敏感操作。



【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。