文章详情页_Greenplum 数据库管理及权限控制

一个 Greenplum 实例中可以创建多个数据库，但是客户端程序一次只能连接上并且访问一个数据库，无法跨数据库进行查询。

greenplum-consist

在 Greenplum 中，角色（Role）、模式（Schema）、数据库（Database）是三个不同的概念。

一个 Database 下可以有多个 Schema，一个 Schema 只属于一个 Database。Schema 在 Greenplum 中也叫做 Namespace，不同 Database 之间的 Schema 没有关系，可以重名
Language 在使用前必须创建，一个语言只属于一个 Schema
Table 、View 、Sequence 、Function 必须属于一个 Schema
一个 FileSpace 可以有多个 TableSpace，一个 TableSpace 只属于一个 FileSpace
TableSpace 与 Table 是一对多的关系，一个 Schema 下的表可以分布在多个 TableSpace 下
上图中，除了 FileSpace 之外，其他的权限管理都是通过 Role 来实现，在这些层次结构中，用户必须对上一层有访问权限，才能够访问该层的内容
Group 与 Role 是一样的概念，在 Greenplum 中，使用 Role 就可以了

数据库管理

数据库是模式（Schema）的集合，而模式包含表、索引、视图、存储过程等数据库对象。层次结构如下：服务器 -> 数据库 -> 模式 -> 数据库对象。

创建数据库

使用 CREATE DATABASE 命令创建一个新的数据库，命令如下：

CREATE DATABASE <dbname> [ [WITH] [OWNER [=] <dbowner>] ] 
    [ENCODING [=] <encoding>];

说明：

<dbname>：待创建的数据库名
<dbowner>：拥有新数据库的数据库用户名，默认为执行该命令的用户
<encoding>：在新数据库中使用的字符集编码。指定一个字符串常量（例如'SQL_ASCII'），默认为 utf-8

示例：

postgres=# create database demo;

查看数据库列表：

postgres=# \l
                               List of databases
   Name    |  Owner  | Encoding |  Collate   |   Ctype    |  Access privileges
-----------+---------+----------+------------+------------+---------------------
 demo      | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +
           |         |          |            |            | gpadmin=CTc/gpadmin
 template1 | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +
           |         |          |            |            | gpadmin=CTc/gpadmin
(4 rows)

切换至新创建的数据库：

postgres=# \c demo;
You are now connected to database "demo" as user "gpadmin".

模板数据库

CREATE DATABASE 实际上是通过拷贝一个现有的数据库进行工作的。缺省时，它拷贝名为 template1 的标准系统数据库。如果给 template1 增加对象，这些对象将被拷贝到随后创建的新数据库中。

系统里还有名为 template0 的第二个标准系统数据库，这个数据库包含和 template1 初始时一样的数据内容，也就是说，只包含标准的 PostgreSQL 对象。在数据库集群初始化之后，我们不应该对 template0 做任何修改。通过告诉 CREATE DATABASE 使用 template0 而不是 template1 进行拷贝，就可以创建一个完全干净的数据库，它不会包含任何 template1 里所特有的东西。

删除数据库

使用 DROP DATABASE 命令删除一个数据库。它会移除该数据库的元数据并且删除该数据库在磁盘上的目录及其中包含的数据，命令如下：

DROP DATABASE <dbname>

注：只有数据库的所有者或者超级用户才可以删除数据库，数据库的删除是不可恢复的。

表空间管理

表空间允许数据库管理员在每台机器上拥有多个文件系统并且决定如何最好地使用物理存储来存放数据库对象。表空间允许用户为频繁使用和不频繁使用的数据库对象分配不同的存储。

表空间需要一个主机文件系统位置来存储其数据库文件。在 Greenplum 数据库中，文件系统位置必须存在于包括运行 master 、standby master 和每个 primary 和 mirror 的所有主机上。

表空间是 Greenplum 数据库系统对象（全局对象），如果有权限的话，可以使用任何数据库中的表空间。

创建表空间

表空间必须由超级用户才可以创建，使用语法如下:

CREATE TABLESPACE tablespace_name
    [ OWNER user_name ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

参数说明：

tablespace_name: 要创建的表空间的名称
username: 拥有表空间的用户名。如果省略，则默认为执行命令的角色
'directory': 目录的绝对路径（主机系统文件位置），它将是表空间的根目录（该目录应该为空，并且必须归 Greenplum 数据库系统用户所有）

创建表空间步骤：

在每台服务器 gpadmin 用户下创建目录

# master 
[gpadmin@mdw ~]$ mkdir /home/gpadmin/data/mytbl

# standby master
[gpadmin@smdw ~]$ mkdir /home/gpadmin/data/mytbl

# segment1（primary 和 mirror 位于同一主机上）
[gpadmin@sdw1 ~]$ mkdir /home/gpadmin/data/mytbl

# segment2（primary 和 mirror 位于同一主机上）
[gpadmin@sdw2 ~]$ mkdir /home/gpadmin/data/mytbl

postgres=# create tablespace mytbl_space location '/home/gpadmin/data/mytbl';

查看创建的表空间

postgres=# select * from pg_tablespace;

   spcname   | spcowner | spcacl | spcoptions
-------------+----------+--------+------------
 pg_default  |       10 |        |
 pg_global   |       10 |        |
 mytbl_space |       10 |        |
(3 rows)

表空间创建好后，需要赋权给普通角色使用的权限

postgres=# grant create on tablespace mytbl_space to role_name;

使用表空间

在一个表空间上拥有 CREATE 权限的角色可以在其中创建数据库对象，例如，下述命令在表空间 mytbl_space 中创建一个表：

postgres=# create table foo(id int) tablespace mytbl_space distributed randomly;

也可以使用 default_tablespace 参数为没有指定表空间的 CREATE TABLE 和 CREATE INDEX 等命令指定默认表空间：

postgres=# set default_tablespace = mytbl_space;

postgres=# create table bar(id int) distributed randomly;

查看表所属的表空间：

postgres=# \d foo;

      Table "public.foo"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
Distributed randomly
Tablespace: "mytbl_space"

如果在创建数据库时没有指定表空间，则使用与该数据库模板库一样的表空间。

查看表空间

每个 Greenplum 数据库系统有如下默认表空间：

pg_global 保存各个数据库之间的通用信息
pg_default 默认表空间，保存每个数据库特有的数据，包括数据字典及用户数据

这些表空间使用系统的默认文件空间，其数据目录位置在系统初始化时被创建。

查看表空间信息

postgres=# select oid, * from pg_tablespace;

  oid  |   spcname   | spcowner | spcacl | spcoptions
-------+-------------+----------+--------+------------
  1663 | pg_default  |       10 |        |
  1664 | pg_global   |       10 |        |
 16385 | mytbl_space |       10 |        |
(3 rows)

显示表空间路径

postgres=# select * from gp_tablespace_location(16385);

 gp_segment_id |        tblspc_loc
---------------+--------------------------
             2 | /home/gpadmin/data/mytbl
             1 | /home/gpadmin/data/mytbl
             3 | /home/gpadmin/data/mytbl
             0 | /home/gpadmin/data/mytbl
            -1 | /home/gpadmin/data/mytbl
(5 rows)

使用 gp_tablespace_location() 和 catalog 表 gp_segment_configuration 显示包含 mytbl 表空间文件系统路径的节点实例信息

with spc as (
  select * from  gp_tablespace_location(16385)
)
select 
  seg.role, 
  spc.gp_segment_id as seg_id, 
  seg.hostname, 
  seg.datadir, 
  tblspc_loc 
from spc, gp_segment_configuration seg 
where spc.gp_segment_id = seg.content 
order by hostname, seg_id;

 role | seg_id | hostname |              datadir              |        tblspc_loc
------+--------+----------+-----------------------------------+--------------------------
 p    |     -1 | mdw      | /home/gpadmin/data/master/gpseg-1 | /home/gpadmin/data/mytbl
 p    |      0 | sdw1     | /home/gpadmin/data/primary/gpseg0 | /home/gpadmin/data/mytbl
 p    |      1 | sdw1     | /home/gpadmin/data/primary/gpseg1 | /home/gpadmin/data/mytbl
 m    |      2 | sdw1     | /home/gpadmin/data/mirror/gpseg2  | /home/gpadmin/data/mytbl
 m    |      3 | sdw1     | /home/gpadmin/data/mirror/gpseg3  | /home/gpadmin/data/mytbl
 m    |      0 | sdw2     | /home/gpadmin/data/mirror/gpseg0  | /home/gpadmin/data/mytbl
 m    |      1 | sdw2     | /home/gpadmin/data/mirror/gpseg1  | /home/gpadmin/data/mytbl
 p    |      2 | sdw2     | /home/gpadmin/data/primary/gpseg2 | /home/gpadmin/data/mytbl
 p    |      3 | sdw2     | /home/gpadmin/data/primary/gpseg3 | /home/gpadmin/data/mytbl
 m    |     -1 | smdw     | /home/gpadmin/data/master/gpseg-1 | /home/gpadmin/data/mytbl
(10 rows)

要删除表空间，必须是表空间的所有者或者超级用户。直到所有依赖该表空间的对象都被删除才可以删除该表空间。

DROP TABLESPACE tablespace_name;

注：无法删除一个非空或存储了临时或事务文件的表空间。

Schema 管理

Schema 是数据库的命名空间，它是一个数据库内部的对象（表、索引、视图、存储过程、操作符）的集合。Schema 在每个数据库中是唯一的。每个数据库都有一个名为 public 的默认 Schema。

如果用户没有创建任何 Schema，对象会被创建在 public schema 中。所有的该数据库角色（用户）都在默认的 public schema 中拥有 CREATE 和 USAGE 特权。

创建 Schema

使用 CREATE SCHEMA 命令来创建一个新的 Schema，命令如下：

CREATE SCHEMA <schema_name> [AUTHORIZATION <username>]

参数说明：

<schema_name>：schema 名称
<username>：如果指定 authorization username，则创建的 schema 属于该用户。否则，属于执行该命令的用户

示例：

create schema myschema;

设置 Schema 搜索路径

详见 AnalyticDB for PostgreSQL 中的 search_path 参数

查看当前 Schema

使用 current_schema() 函数可以查看当前的 Schema：

select current_schema();

也可以使用 SHOW 命令查看当前的搜索路径：

show search_path;

删除 Schema

使用 DROP SCHEMA 命令删除一个空的 Schema：

drop schema myschema;

删除一个 Schema 连同其中的所有对象（表、数据、函数等等），可以使用：

drop schema myschema cascade;

系统 Schema

下列系统级 Schema 存在于每一个数据库中：

序号	系统 Schema	描述
1	pg_catalog	包含着系统目录表、内建数据类型、函数和操作符。即便在 schema 搜索路径中没有显式地提到它，它也总是 schema 搜索路径的一部分
2	information_schema	由一个包含数据库中对象信息的视图集合组成。这些视图以一种标准化的方式从系统目录表中得到系统信息
3	pg_toast	存储大型对象，如超过页面尺寸的记录。这个 schema 由 Greenplum 数据库系统内部使用
4	pg_bitmapindex	存储位图索引对象，例如值的列表。这个 schema 由 Greenplum 数据库系统内部使用
5	pg_aoseg	存储追加优化表对象。这个 schema 由 Greenplum 数据库系统内部使用
6	gp_toolkit	是一个管理用途的 schema，它包含用户可以用 SQL 命令访问的外部表、视图和函数。所有的数据库用户都能访问 gp_toolkit 来查看和查询系统日志文件以及其他系统指标

角色管理

Greenplum 数据库使用 roles 管理数据库访问权限。角色的概念包含用户和组的概念。

一个角色可能是一个数据库用户、一个组或者两者兼具。

角色可以拥有数据库对象（例如表）并且可以将那些对象上的特权分配给其他角色来控制对象的访问。

角色可以是其他角色的成员，因此一个成员角色能够继承其父角色的对象特权。

创建新角色

一个用户级的角色被认为是能够登入数据库并且发起数据库会话的数据库角色。因此，当使用 CREATE ROLE 命令创建新的用户级角色时，必须指定 LOGIN 权限：

postgres=# create role jsmith with login;

使用新角色登录数据库：

[gpadmin@mdw ~]$ psql -d postgres -U jsmith
psql: FATAL:  no pg_hba.conf entry for host "[local]", user "jsmith", database "postgres", SSL off

创建新角色后，需要在 pg_hba.conf 中添加认证：

# 添加认证
[gpadmin@mdw ~]$ echo "host all jsmith 0.0.0.0/0 trust" >> /home/gpadmin/data/master/gpseg-1/pg_hba.conf

# 重新加载数据库配置
gpstop -u 

# 登录
[gpadmin@mdw ~]$ psql -d postgres -U jsmith -h 192.168.10.31

数据库角色可以具有许多属性，这些属性定义角色可以在数据库中执行的任务类型。可以在创建角色时设置这些属性，也可以稍后使用 ALTER ROLE 命令。

pg_hba.conf 文件

创建 testrole1 角色，该角色有登录权限、创建数据库与创建角色权限：

postgres=# create role testrole1 with createdb createrole login;

创建 testrole2 角色，该角色的密码为 test，有效期到 “2050-12-31 00:00:00”，连接数限制为 5，并且继承了 testrole1 的所有属性：

postgres=# create role testrole2 with password 'test' valid until '2050-12-31 00:00:00' connection limit 5 inherit in role testrole1 login;

查看角色列表：

postgres=# \du+
                                                                  List of roles
 Role name |                                                Attributes                                                |  Member of  | Description
-----------+----------------------------------------------------------------------------------------------------------+-------------+-------------
 gpadmin   | Superuser, Create role, Create DB, Ext gpfdist Table, Wri Ext gpfdist Table, Ext http Table, Replication | {}          |
 jsmith    |                                                                                                          | {}          |
 scott     |                                                                                                          | {}          |
 testrole1 | Create role, Create DB                                                                                   | {}          |
 testrole2 | 5 connections                                                                                           +| {testrole1} |
           | Password valid until 2050-12-31 00:00:00+08                                                              |             |

使用密码登录角色 testrole2：

# 添加认证（密码认证）
[gpadmin@mdw ~]$ echo "host all testrole2 0.0.0.0/0 md5" >> /home/gpadmin/data/master/gpseg-1/pg_hba.conf

# 重新加载数据库配置
gpstop -u 

# 登录
[gpadmin@mdw ~]$ psql -d postgres -U testrole2 -h 192.168.10.31

testrole2-with-password

角色属性

数据库角色可以具有许多属性，这些属性定义角色可以在数据库中执行的任务类型。

序号	属性	描述
1	SUPERUSER \| NOSUPERUSER	确定角色是否为超级用户，必须自己是超级用户才能创建新的超级用户，默认值是 NOSUPERUSER
2	CREATEDB \| NOCREATEDB	确定是否允许角色创建数据库，默认值是 NOCREATEDB
3	CREATEROLE \| NOCREATEROLE	确定是否允许角色创建和管理其他角色，默认值是 NOCREATEROLE
4	INHERIT \| NOINHERIT	确定角色是否继承其所属角色的权限，默认值是 INHERIT
5	LOGIN \| NOLOGIN	确定是否允许角色登录，默认值是 NOLOGIN
6	CONNECTION LIMIT	如果角色可以登录，则指定角色可以使用的并发连接数，默认值 -1 表示没有限制
7	CREATEEXTTABLE \| NOCREATEEXTTABLE	确定是否允许角色创建外部表，默认值是 NOCREATEEXTTABLE
8	PASSWORD 'password'	设置角色的密码，如果不打算使用密码身份验证，则可以省略此选项。如果未指定密码，则密码将设置为空，并且该用户的密码验证将始终失败
9	ENCRYPTED \| UNENCRYPTED	控制是否将新密码在 pg_authid 系统目录中存储为哈希字符串，该参数默认值为 on
10	VALID UNTIL 'timestamp'	设置角色密码失效的日期和时间，如果省略，密码将始终有效
11	RESOURCE QUEUE queue_name	将角色分配给指定的资源队列以进行工作负载管理，任何角色问题的声明都受资源队列限制的约束
12	DENY {deny_interval \| deny_point}	限制时间间隔期间的访问，按日期或日期时间指定

角色成员

将橘色组合在一起以便于管理对象权限，这样，可以将权限授予整个组或从组中撤销。在 Greenplum 数据库中，可以创建表示组的角色，然后将组角色的成员身份授予单个角色。

使用 CREATE ROLE 创建一个新的组角色：

[gpadmin@mdw ~]$ psql -d postgres

postgres=# create role admin with createrole createdb login;

创建两个新的角色：

postgres=# create role role1 with login;

postgres=# create role role2 with login;

使用 GRANT 和 REVOKE 命令，来添加和删除成员（角色）：

postgres=# grant admin to role1, role2;

postgres=# \du+
                                                                 List of roles
 Role name |                                                Attributes                                                | Member of | Description
-----------+----------------------------------------------------------------------------------------------------------+-----------+-------------
 admin     | Create role, Create DB                                                                                   | {}        |
 gpadmin   | Superuser, Create role, Create DB, Ext gpfdist Table, Wri Ext gpfdist Table, Ext http Table, Replication | {}        |
 role1     |                                                                                                          | {admin}   |
 role2     |                                                                                                          | {admin}   |

为组角色授予适当的对象权限后，成员角色将继承组角色的对象权限：

postgres=# grant all on table table_name to admin;

postgres=# grant all on schema schema_name to admin;

postgres=# grant all on database database_name to admin;

注：角色属性 LOGIN、SUPERUSER、CREATEDB、 CREATEROLE 等不会像数据库对象上的普通权限那样被继承，成员角色必须使用 SET ROLE 才能使用组角色上的角色属性。

postgres=> create database oradb;
ERROR:  permission denied to create database

postgres=> set role admin;

postgres=> create database oradb;

postgres=> \l
                               List of databases
   Name    |  Owner  | Encoding |  Collate   |   Ctype    |  Access privileges
-----------+---------+----------+------------+------------+---------------------
 demo      | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |
 oradb     | admin   | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +
           |         |          |            |            | gpadmin=CTc/gpadmin
 template1 | gpadmin | UTF8     | en_US.utf8 | en_US.utf8 | =c/gpadmin         +
           |         |          |            |            | gpadmin=CTc/gpadmin
(5 rows)

权限管理

当一个数据库对象（表、视图、序列、数据库、函数、表空间）被创建时，它会被分配一个拥有者。拥有者通常是执行创建语句的角色。对于大部分类型的对象，初始状态是只有拥有者（或者超级用户）可以对该对象做任何事情。要允许其他角色使用它，必须授予权限。

管理对象权限

Greenplum 数据库对每种对象类型支持下列权限（可通过 \h grant 或 \h revoke 查看）。

序号	对象类型	权限
1	表、视图、序列	SELECT 、INSERT 、UPDATE 、DELETE 、RULE 、ALL
2	外部表	SELECT 、RULE 、ALL
3	数据库	CONNECT 、CREATE 、TEMPORARY \| TEMP 、ALL
4	函数	EXECUTE
5	过程语言	USAGE
6	模式	CREATE 、USAGE 、ALL
7	自定义协议	SELECT 、INSERT 、UPDATE 、DELETE 、RULE 、ALL

必须单独为每个对象授予权限。

赋权与回收权限

使用 GRANT 命令，为 jsmith 角色赋予表 mytable 上的插入权限：

postgres=> grant insert on mytable to jsmith;

使用 REVOKE 命令，撤销权限：

postgres=> revoke insert on mytable from jsmith;

为某个用户授予表的访问权限

a. 授予 schema 的权限

postgres=> grant all on schema schema_name to user_name;

b. 授予表的访问权限

postgres=> grant all on table_name to user_name;

参考资料

https://help.aliyun.com/document_detail/149857.html

https://gp-docs-cn.github.io/docs/common/gpdb-features.html

http://www.postgres.cn/docs/9.3/index.html

原创文章，转载请注明出处：http://www.opcoder.cn/article/60/