CNDBA社区
在之前的博客，我们了解了openGauss的体系结构：
openGauss 数据库 逻辑结构 说明
https://www.cndba.cn/dave/article/116485
openGauss 数据库 物理（目录）结构 说明
https://www.cndba.cn/dave/article/116484
本文我们将从实际生产交付的角度，看生产环境如何来创建数据库、表空间、用户。
1 数据库操作示例
1.1 数据库说明
默认情况下，openGauss包含两个模板数据库template0、template1，以及一个默认的用户数据库postgres。
postgres默认的兼容数据库类型为Oracle（即 DBCOMPATIBILITY = A ），该兼容类型下将空字符串作为NULL处理。
openGauss 数据库 兼容类型（DBCOMPATIBILITY ） 说明
https://www.cndba.cn/dave/article/116479
CREATE DATABASE实际上通过拷贝模板数据库来创建新数据库。默认情况下，拷贝template0。模板数据库中没有用户表，可通过系统表PG_DATABASE查看模板数据库属性。
注意事项：
- openGauss中允许创建的数据库总数目上限为128个。
- 数据库名称长度最大为63字节，超过63字节，server端会对数据库名称进行截断，保留前63个字节。
- 数据库字符集编码推荐位UTF8。 不指定时，默认使用模版数据库的编码。模板数据库template0和template1的编码默认与操作系统环境相关。template1不允许修改字符编码，因此若要变更编码，需要使用template0创建数据库。
1.2 操作示例
[dave@www.cndba.cn ~]$ gsql -d postgres -p 15400
Password:
gsql ((openGauss 3.0.3 build 46134f73) compiled at 2023-01-10 22:42:07 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
postgres | omm | SQL_ASCII | C | C |
template0 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
ustc | cndba | SQL_ASCII | C | C |
(4 rows)
openGauss=#
openGauss=# create database cndba encoding='utf8' dbcompatibility = 'A';
CREATE DATABASE
openGauss=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
cndba | omm | UTF8 | C | C |
postgres | omm | SQL_ASCII | C | C |
template0 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
ustc | cndba | SQL_ASCII | C | C |
(5 rows)
openGauss=# select datname from pg_database;
datname
-----------
template1
ustc
template0
cndba
postgres
(5 rows)
openGauss=# alter database cndba rename to dave;
ALTER DATABASE
openGauss=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
dave | omm | UTF8 | C | C |
postgres | omm | SQL_ASCII | C | C |
template0 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
ustc | cndba | SQL_ASCII | C | C |
(5 rows)
openGauss=# drop database dave;
DROP DATABASE
openGauss=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
postgres | omm | SQL_ASCII | C | C |
template0 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
ustc | cndba | SQL_ASCII | C | C |
(4 rows)
注意：
使用DROP DATABASE命令删除数据库时，会删除了数据库中的系统目录，并且删除了磁盘上带有数据的数据库目录。当有人连接数据库时，删除操作会失败。删除数据库时请先连接到其他的数据库。
2 用户和模式操作示例
之前整理的PG的用户和模式管理参考：
PostgreSQL 学习笔记（8） — PG 模式 与 用户 管理
https://www.cndba.cn/dave/article/116380
2.1 用户模式说明
通过管理Schema（模式）允许多个用户使用同一数据库而不相互干扰，简单的理解，模式是用来管理数据库对象的，比如表，索引，函数之类的，每个数据库包含一个或多个Schema。
数据库创建时默认有一个名为public的Schema，且所有用户都拥有此Schema的usage权限，只有系统管理员和初始化用户可以在public Schema下创建函数、存储过程和同义词对象，其他用户即使赋予create权限后也不可以创建上述三种对象。
[dave@www.cndba.cn ~]$ gsql -d postgres -p 15400
Password:
gsql ((openGauss 3.0.3 build 46134f73) compiled at 2023-01-10 22:42:07 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=# /dn
List of schemas
Name | Owner
-----------------+-------
blockchain | omm
cndba | cndba
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_sql_util | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(12 rows)
openGauss=# /db
List of tablespaces
Name | Owner | Location
------------+-------+-----------------------------------
pg_default | omm |
pg_global | omm |
ustc | omm | /data/openGauss/install/data/ustc
(3 rows)
数据库对象是创建在数据库搜索路径中的第一个Schema内的。搜索路径由search_path参数控制。如果创建对象时未指定目标Schema，则该对象会被添加到搜索路径中列出的第一个Schema中。当不同Schema中存在同名的对象时，查询对象未指定Schema的情况下，将从搜索路径中包含该对象的第一个Schema中返回对象。
openGauss=# SHOW SEARCH_PATH;
search_path
----------------
"$user",public
(1 row)
search_path参数的默认值为：”$user”,public。$user表示与当前会话用户名同名的Schema名，如果这样的模式不存在，$user将被忽略。所以默认情况下，用户连接数据库后，如果数据库下存在同名Schema，则对象会添加到同名Schema下，否则对象被添加到Public Schema下。
可以使用set命令修改当前会话的默认Schema：
openGauss=# SET SEARCH_PATH TO myschema, public;
在每次创建新用户时，系统会在当前登录的数据库中为新用户创建一个同名 Schema。
通常情况下，在不指定模式名的情况下创建和访问表，访问的都是”public”模式。在创建一个新的数据库时，PostgreSQL都会自动创建一个名为”public”的模式。当登录到该数据库时，如果没有特殊的指定，都是以该模式（public）操作各种数据对象的。
2.2 操作示例
[dave@www.cndba.cn ~]$ gsql -d cndba -p 15400
Password:
gsql ((openGauss 3.0.3 build 46134f73) compiled at 2023-01-10 22:42:07 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
cndba=# SELECT current_schema();
current_schema
----------------
public
(1 row)
cndba=#
cndba=#
cndba=# /l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+-----------+---------+-------+-------------------
cndba | omm | UTF8 | C | C |
postgres | omm | SQL_ASCII | C | C |
template0 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | SQL_ASCII | C | C | =c/omm +
| | | | | omm=CTc/omm
ustc | cndba | SQL_ASCII | C | C |
(5 rows)
cndba=# /dn
List of schemas
Name | Owner
-----------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_sql_util | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(11 rows)
创建schema：
cndba=# CREATE SCHEMA cndba;
CREATE SCHEMA
注意：不能创建以PG_为前缀的schema名，该类schema为数据库系统预留的。
创建指定所有者（默认为omm）的schema。
cndba=# CREATE SCHEMA ustc AUTHORIZATION omm;
CREATE SCHEMA
cndba=# /dn
List of schemas
Name | Owner
-----------------+-------
blockchain | omm
cndba | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_sql_util | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
ustc | omm
(13 rows)
在特定schema下创建对象或者访问特定schema下的对象，需要使用有schema修饰的对象名。该名称包含schema名以及对象名，他们之间用“.”号分开。
cndba=# CREATE TABLE ustc.dave(id int, name varchar(20));
CREATE TABLE
此时我们查询dave表数据时就需要指定模式名，否则会报表不存在：
cndba=# select * from dave;
ERROR: relation "dave" does not exist on dn_6001
LINE 1: select * from dave;
^
cndba=# select * from ustc.dave;
id | name
----+------
(0 rows)
这个就是我们前面说的搜索路径的问题：
cndba=# SHOW SEARCH_PATH;
search_path
----------------
"$user",public
(1 row)
cndba=# SELECT current_schema();
current_schema
----------------
public
(1 row)
cndba=# select current_user;
current_user
--------------
omm
(1 row)
我们修改search_path后就可以正常查询到dave表了：
cndba=# SET SEARCH_PATH TO ustc,public;
SET
cndba=# select * from dave;
id | name
----+------
(0 rows)
cndba=#
默认情况下，用户只能访问属于自己的schema中的数据库对象。如果需要访问其他schema的对象，则该schema的所有者应该赋予他对该schema的usage权限。通过将模式的CREATE权限授予某用户，被授权用户就可以在此模式中创建对象。
默认情况下，所有角色都拥有在public模式上的USAGE权限，但是普通用户没有在public模式上的CREATE权限。普通用户能够连接到一个指定数据库并在它的public模式中创建对象是不安全的，如果普通用户具有在public模式上的CREATE权限则建议通过如下语句撤销该权限。
所以对于生产环境，建议回收public模式上的CREATE和USAGE 权限：
cndba=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
cndba=# REVOKE USAGE ON SCHEMA public FROM PUBLIC;
REVOKE
cndba=#
第一个”public”是模式的名称，第二个”PUBLIC”是一个特殊的角色，代表着所有用户。
创建用户dave，并将ustc的usage权限赋给用户dave：
cndba=# CREATE USER dave IDENTIFIED BY 'omm@123456';
CREATE ROLE
cndba=# GRANT USAGE ON schema ustc TO dave;
GRANT
回收dave用户ustc模式的usage权限：
cndba=# REVOKE USAGE ON schema ustc FROM dave;
REVOKE
cndba=#
删除schema：当schema为空时，使用DROP SCHEMA命令进行删除。当schema非空时，需要使用CASCADE关键字。
cndba=# DROP SCHEMA IF EXISTS ustc;
ERROR: cannot drop schema ustc because other objects depend on it
DETAIL: table dave depends on schema ustc
HINT: Use DROP ... CASCADE to drop the dependent objects too.
cndba=# DROP SCHEMA ustc CASCADE;
NOTICE: drop cascades to table dave
DROP SCHEMA
cndba=#
删除用户dave：
cndba=# DROP USER dave;
DROP ROLE
3 表空间操作示例
3.1 表空间说明
openGauss自带了两个表空间：pg_default和pg_global。
- 默认表空间pg_default：用来存储非共享系统表、用户表、用户表index、临时表、临时表index、内部临时表的默认表空间。对应存储目录为实例数据目录下的base目录。
- 共享表空间pg_global：用来存放共享系统表的表空间。对应存储目录为实例数据目录下的global目录。
cndba=# SELECT spcname FROM pg_tablespace;
spcname
------------
pg_default
pg_global
ustc
(3 rows)
cndba=# /db
List of tablespaces
Name | Owner | Location
------------+-------+-----------------------------------
pg_default | omm |
pg_global | omm |
ustc | omm | /data/openGauss/install/data/ustc
(3 rows)
cndba=#
3.2 操作示例
创建用户dave:
cndba=# CREATE USER dave IDENTIFIED BY 'omm@123456';
CREATE ROLE
创建表空间:
方法1：通过relative关键字，会在$PGDATA的pg_location目录创建目录
cndba=# create tablespace ustc1 relative location 'ustc1';
CREATE TABLESPACE
方法2：直接指定表空间路径和所有者：
cndba=# create tablespace ustc2 owner dave location '/data/openGauss/install/data/ustc2';
CREATE TABLESPACE
查看表空间信息：
cndba=# /db
List of tablespaces
Name | Owner | Location
------------+-------+------------------------------------
pg_default | omm |
pg_global | omm |
ustc | omm | /data/openGauss/install/data/ustc
ustc1 | omm | ustc1
ustc2 | dave | /data/openGauss/install/data/ustc2
(5 rows)
cndba=#
这里的ustc1 是个相对路径，我们来pg_location目录检查一下：
[dave@www.cndba.cn pg_location]$ pwd
/data/openGauss/install/data/dn/pg_location
[dave@www.cndba.cn pg_location]$ ls
ustc1
[dave@www.cndba.cn pg_location]$ cd ustc1/
[dave@www.cndba.cn ustc1]$ ls
PG_9.2_201611171_dn_6001
如果用户拥有表空间的CREATE权限，就可以在表空间上创建数据库对象，比如：表和索引等。
方式1：执行命令时指定表空间
cndba=# CREATE TABLE dave(i int) TABLESPACE ustc1;
CREATE TABLE
cndba=# /dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+-------+----------------------------------
public | dave | table | omm | {orientation=row,compression=no}
(1 row)
cndba=# /d dave
Table "public.dave"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
Tablespace: "ustc1"
查看默认表空间：
cndba=# /l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+-------+-----------+---------+-------+-------------------+-------+------------+--------------------------------------------
cndba | omm | UTF8 | C | C | | 13 MB | pg_default |
postgres | omm | SQL_ASCII | C | C | | 27 MB | pg_default | default administrative connection database
template0 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | |
template1 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | |
ustc | cndba | SQL_ASCII | C | C | | 13 MB | pg_default |
(5 rows)
我们可以看到默认的表空间是pg_default，我们这里修改默认的表空间：
方式2：先使用set default_tablespace设置默认表空间，再创建表。
cndba=# SET default_tablespace = 'ustc1';
SET
cndba=# /dt
List of relations
Schema | Name | Type | Owner | Storage
--------+-------+-------+-------+----------------------------------
public | dave | table | omm | {orientation=row,compression=no}
public | dave2 | table | omm | {orientation=row,compression=no}
(2 rows)
cndba=# /d dave2
Table "public.dave2"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
Tablespace: "ustc1"
修改表空间的所有者：
cndba=# /db
List of tablespaces
Name | Owner | Location
------------+-------+------------------------------------
pg_default | omm |
pg_global | omm |
ustc | omm | /data/openGauss/install/data/ustc
ustc1 | omm | ustc1
ustc2 | dave | /data/openGauss/install/data/ustc2
(5 rows)
cndba=# alter tablespace ustc2 owner to omm;
ALTER TABLESPACE
cndba=# /db
List of tablespaces
Name | Owner | Location
------------+-------+------------------------------------
pg_default | omm |
pg_global | omm |
ustc | omm | /data/openGauss/install/data/ustc
ustc1 | omm | ustc1
ustc2 | omm | /data/openGauss/install/data/ustc2
(5 rows)
修改表空间名字：
cndba=# alter tablespace ustc2 rename to ustc3;
ALTER TABLESPACE
cndba=# /db
List of tablespaces
Name | Owner | Location
------------+-------+------------------------------------
pg_default | omm |
pg_global | omm |
ustc | omm | /data/openGauss/install/data/ustc
ustc1 | omm | ustc1
ustc3 | omm | /data/openGauss/install/data/ustc2
(5 rows)
将表空间的访问权限赋予数据用户：
cndba=# GRANT CREATE ON TABLESPACE ustc3 TO dave;
GRANT
查询表空间的当前使用情况。
cndba=# SELECT PG_TABLESPACE_SIZE('ustc1');
pg_tablespace_size
--------------------
38 #表空间的大小，单位为字节。
(1 row)
删除表空间：
cndba=# drop tablespace ustc1;
ERROR: tablespace "ustc1" is not empty
cndba=# drop tablespace ustc3;
DROP TABLESPACE
cndba=#
非空的表空间无法删除，需要删删除上面的对象，再删除表空间。
4 总结
上面描述的内容有点多，我们这里简单总结一下，生产环境如何交付这些信息。
创建独立的表空间：hefei
cndba=# create tablespace hefei owner dave location '/data/openGauss/install/data/hefei';
CREATE TABLESPACE
cndba=# /db
List of tablespaces
Name | Owner | Location
------------+-------+------------------------------------
hefei | dave | /data/openGauss/install/data/hefei
pg_default | omm |
pg_global | omm |
ustc | omm | /data/openGauss/install/data/ustc
ustc1 | omm | ustc1
(5 rows)
创建兼容Oracle 语法的opengauss数据库web：
openGauss=# create database web encoding='utf8' dbcompatibility = 'A' tablespace = hefei;
CREATE DATABASE
openGauss=# /l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+-------+-----------+---------+-------+-------------------+-------+------------+--------------------------------------------
cndba | omm | UTF8 | C | C | | 13 MB | pg_default |
postgres | omm | SQL_ASCII | C | C | | 27 MB | pg_default | default administrative connection database
template0 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | |
template1 | omm | SQL_ASCII | C | C | =c/omm +| 13 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | |
ustc | cndba | SQL_ASCII | C | C | | 13 MB | pg_default |
web | omm | UTF8 | C | C | | 13 MB | hefei |
(6 rows)
连接web库：
[dave@www.cndba.cn ~]$ gsql -d web -p 15400
Password:
gsql ((openGauss 3.0.3 build 46134f73) compiled at 2023-01-10 22:42:07 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
web=# /conninfo
You are connected to database "web" as user "omm" via socket in "/data/openGauss/tmp" at port "15400".
web=#
安全考虑，回收public模式上的CREATE和USAGE 权限：
web=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
web=# REVOKE USAGE ON SCHEMA public FROM PUBLIC;
REVOKE
web=#
opengauss里创建用户时会自动创建同名的schema，所以我们直接创建用户即可：
web=# CREATE USER web1 IDENTIFIED BY 'omm@123456';
CREATE ROLE
web=# CREATE USER web2 IDENTIFIED BY 'omm@123456';
CREATE ROLE
web=# /dn
List of schemas
Name | Owner
-----------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
dbe_pldeveloper | omm
dbe_sql_util | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
web1 | web1
web2 | web2
(13 rows)
我们这里将web1 作为业务数据所有者,有DDL 和 DML权限，web2 作为程序用户，用于操作数据库表里面的数据，完成增删改查，仅有DML权限。
[dave@www.cndba.cn ~]$ gsql -d web -p 15400 -U web1
Password for user web1:
gsql ((openGauss 3.0.3 build 46134f73) compiled at 2023-01-10 22:42:07 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
web=> create table t1(id int);
CREATE TABLE
web=>
web=> /dt
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+-------+----------------------------------
web1 | t1 | table | web1 | {orientation=row,compression=no}
(1 row)
将web1模式usage权限赋给web2：
web=> grant USAGE ON SCHEMA web1 to web2;
GRANT
web=> GRANT update,delete,insert,select ON ALL TABLES IN SCHEMA web1 TO web2;
GRANT
DML 用户连接：
[dave@www.cndba.cn ~]$ gsql -d web -p 15400 -U web2
Password for user web2:
gsql ((openGauss 3.0.3 build 46134f73) compiled at 2023-01-10 22:42:07 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
web=> select * from web1.t1;
id
----
1
(1 row)
web=> insert into web1.t1 values(2);
INSERT 0 1
web=> select * from web1.t1;
id
----
1
2
(2 rows)
web=> drop table web1.t1;
ERROR: permission denied for relation t1
DETAIL: N/A
版权声明：本文为博主原创文章，未经博主允许不得转载。
