GreenPlum 权限管理

greenplum

发布日期: 2020-05-15

文章字数: 6.7k

阅读时长: 29 分

阅读次数:

1. GreenPlum 权限说明

GreenPlum 中的用户与角色在整个数据库中都是全局性的。
在安装数据库时已指定超级管理员、系统管理员，例如超级管理员: gpadmin
每个数据库都有一个所有者，数据看的所有者默认拥有数据库的所有权限，不需要重新赋予，也不能被撤销。
可以把操作该数据库的权限赋予别人。授权用命令GRANT，撤销授权用命令REVOKE。

2. 测试用户方面的权限

2.1 用户角色列表

用户类型	描述
superuser	决定角色是否为超级用户，如要创建一个新的超级用户，那么该用户本身需要为超级用户。默认：nosuperuser
nosuperuser	决定角色是否为超级用户，如要创建一个新的超级用户，那么该用户本身需要为超级用户。默认：nosuperuser
createdb	决定该角色是否被允许创建数据库，默认值：nocreatedb
nocreatedb	决定该角色是否被允许创建数据库，默认值：nocreatedb
createrole	决定该角色是否能够创建和管理其他角色，默认值：nocreaterole
nocreaterole	决定该角色是否能够创建和管理其他角色，默认值：nocreaterole
inherit	决定一个角色是否从父角色中继承权限，具有inherit属性的角色拥有其父角色及其间接父角色的所有权限，默认值：inherit
noinherit
login	决定是否允许一个用户登录，带有 loing 属性的角色可以被认为是一个用户。默认值：nologin
nologin	决定是否允许一个用户登录，带有 loing 属性的角色可以被认为是一个用户。默认值：nologin
connection limit n	指定登录用户能建立的并发连接数，默认：-1，无限制
createexttable	设置是否允许用户创建外部表，有createexttable 角色的用户，默认创建的外部表类型是readable，默认的协议是gpfdist，使用file 或者execute协议的外部表只能由超级用户创建。默认：nocreateexttable
nocreateexttable
password 'pwd'	设置用户密码，如果没有设置，则用户密码为空，且用户密码验证时总是失败
valid untiy 'time'	设置一个日期和时间，在此之后该角色的口令不再有效。如果省略，则口令将会永久有效。
resource queue qname	起到负载管理的目的，为用户分配到指定的资源队列，用户进行的操作必须在所属队列可用资源范围内。 resource queue 不能被继承，必须在每个用户级（login）角色上设置
deny {deny_interval \| deny_point}	在一个间隔期间限制访问，用日期或日期+时间指定，更多信息详见基于时间的认证。

2.2 角色相关的元数据表

pg_roles 视图提供关提供了关于数据库角色的信息。它是 pg_authid 的一个公共可读视图，它隐藏了密码。

列	类型	参考	描述
rolname	name		角色名
rolsuper	bool		角色是否具有超级用户权限
rolinherit	bool		如果此角色是另一个角色的成员，角色是否能自动继承另一个角色的权限
rolcreaterole	bool		角色能否创建更多角色
rolcreatedb	bool		角色能否创建数据库
rolcatupdate	bool		角色能否直接更新系统目录（除非该列设置为真，否则超级用户也不能执行该操作）
rolcanlogin	bool		角色是否能登录？即此角色能否被作为初始会话授权标识符
rolconnlimit	int4		对于一个可登录的角色，这里设置角色可以发起的最大并发连接数。 -1表示无限制。
rolpassword	text		登录密码
rolvaliduntil	timestamptz		密码失效时间（只用于口令认证），如果永不失效则为NULL
rolconfig	text[]		运行时配置变量的角色特定默认值
rolresqueue	oid	pg_resqueue.oid	该角色指定的资源队列的对象ID
oid	oid	pg_authid.oid	角色的ID
rolcreaterextgpfd	bool		角色能够创建使用gpfdist协议的可读的外部表
rolcreaterexthttp	bool		角色能够创建使用http协议的可读的外部表
rolcreatewextgpfd	bool		角色能否创建使用了gpfdist协议的可写外部表
rolresgroup	oid	pg_resgroup.oid	分配了此角色的资源组的对象ID

pg_authid 表存储了有关数据库角色的信息。角色包含用户和组的概念。用户是设置了 rolcanlogin 标志的角色。任何角色都可能是其他用户组的成员。

由于此目录包含密码，因此不是公众可读的。 pg_roles 是 pg_authid 上的一个公开可读的视图，其中模糊化了密码字段。

由于用户身份是全系统范围的，因此 pg_authid 在 Greenplum 数据库系统中的所有数据库之间共享：每个系统只有一个 pg_authid副本，而不是每个数据库一个。

2.3 创建用户测试不同权限

2.3.1 创建超级用户

# 使用超级用户操作 创建超级用户 s1
create user s1 with superuser password '123456';

# 查看 s1 用户角色

select 
  rolname, -- 角色名
  rolsuper, -- 是否是超级用户
  rolinherit, -- 能否继承父角色权限
  rolcreaterole, -- 能否创建角色
  rolcreatedb, -- 能否创建数据库
  rolcatupdate, -- 能否更新系统目录
  rolcanlogin, -- 能否登录
  rolconnlimit -- 连接数限制
from pg_roles
where rolname = 's1';

 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit
---------+----------+------------+---------------+-------------+--------------+-------------+--------------
 s1      | t        | t          | f             | f           | t            | t           |           -1 

--  从上面结果可以看出 
-- 刚创建的用户如果只设置为 superuser 角色
-- 则权限为
-- 是超级用户
-- 能够继承父角色权限
-- 不能创建角色
-- 不能创建数据库
-- 能更新系统目录
-- 能登录
-- 连接数没有限制

登录刚刚创建的用户验证权限

编辑配置文件

# 编辑配置文件
vim /data/master/gpseg-1/pg_hba.conf

host    all      s1    192.168.71.100/32   md5

# 编辑完成之后 使用 gpstop -u 命令 更新配置
gpstop -u
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:-Starting gpstop with args: -u
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:-Gathering information and validating the environment...
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:-Obtaining Segment details from master...
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 6.12.0 build commit:4c176763c7619fb678ce38095e6b3e8fb9548186'
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:-Signalling all postmaster processes to reload
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:---------------------------------------------
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:-Some segment postmasters were not reloaded
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:---------------------------------------------
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:-   Host      Datadir                       Port   Status
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:-   gpnode2   /data/segment/mirror/gpseg1   7001   d
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:-   gpnode2   /data/segment/mirror/gpseg2   7002   d
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:-   gpnode2   /data/segment/mirror/gpseg0   7000   d
20211102:17:40:46:001227 gpstop:gpnode1:gpadmin-[INFO]:---------------------------------------------

# 登录 s1 用户
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1  -d test
Password for user s1: #(输入密码)
psql (9.4.24)
Type "help" for help.

# 查看登录信息
test=# \conninfo
You are connected to database "test" as user "s1" on host "192.168.71.100" at port "5432".

# 测试创建 数据库
test=# create database s1_db;
CREATE DATABASE

# 查看 s1 用户创建的数据库
test=# select datname,rolname from pg_database,pg_roles where pg_database.datdba = pg_roles.oid and pg_roles.rolname='s1' ;

 datname | rolname
---------+---------
 s1_db   | s1

注意：

在查看用户权限的时候 s1 用户是没有创建数据库与创建角色权限的，为什么能够创建成功呢？

原因是 s1 用户是 super 角色，super 用户的创建数据库与角色是不受 nocreateuser 、nocreatedb 限制的。

2.3.2 创建普通用户

# 创建普通角色，不给登录权限（没有登录权限的用户只能称为角色，有了登录权限之后才能被称之为用户）
test=# create user s1_u1 with nologin password '123456';
NOTICE:  resource queue required -- using default resource queue "pg_default" # 使用默认队列
CREATE ROLE # 创建成功

-- 查看 s1_u1 角色与权限
test=# select rolname,rolsuper,rolinherit,rolcreaterole,rolcreatedb,rolcatupdate,rolcanlogin,rolconnlimit,rolvaliduntil from pg_roles where rolname = 's1_u1';
 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolvaliduntil
---------+----------+------------+---------------+-------------+--------------+-------------+--------------+---------------
 s1_u1   | f        | t          | f             | f           | f            | f           |           -1 |

-- 查看 s1_u1 角色与权限
test=# \duS+ s1_u1
                   List of roles
 Role name |  Attributes  | Member of | Description
-----------+--------------+-----------+-------------
 s1_u1     | Cannot login | {}        |

2.3.3 用户登录权限测试

编辑登录配置文件

vim /data/master/gpseg-1/pg_hba.conf

host    test     s1_u1 192.168.71.100/32   md5

# 编辑完成之后 使用 gpstop -u 命令 更新配置
gpstop -u

登录用户

# 使用 s1_u1 用户登录
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
could not change directory to "/root": 权限不够
Password for user s1_u1:
psql: FATAL:  role "s1_u1" is not permitted to log in # 提示没有登录权限

# 使用 super 用户给 s1_u1 用户赋予登录权限
test=# alter role s1_u1 login;
ALTER ROLE

# 在新的窗口登录 s1_u1 用户
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
Password for user s1_u1:
psql (9.4.24)
Type "help" for help.

2.3.4 创建数据库权限测试

# 使用 s1_u1 用户创建数据库
test=> create database s1_u1_db;
ERROR:  permission denied to create database # 提示没有创建数据库权限

# 使用 super 用户赋予 s1_u1 用户 创建数据库的权限
test=# alter role s1_u1 createdb;
ALTER ROLE

# 再次使用 s1_u1 用户创建数据库
test=> create database s1_u1_db;
CREATE DATABASE

# 查看数据库所有者
test=> \l s1_u1_db
                             List of databases
   Name   | Owner | Encoding |  Collate   |   Ctype    | Access privileges
----------+-------+----------+------------+------------+-------------------
 s1_u1_db | s1_u1 | UTF8     | en_US.utf8 | en_US.utf8 |

# 测试删除 s1_u1_db 数据库
test=> drop database s1_u1_db;
DROP DATABASE

2.3.5 创建角色测试

# 使用 s1_u1 用户创建新的角色
test=> create user s1_u1_u1 with nologin password '123456';
ERROR:  permission denied to create role # 提示没有创建角色权限

# 使用 super 用户赋予 s1_u1 用户 创建数据库的权限
test=# alter role s1_u1 createrole;
ALTER ROLE

# 再次使用 s1_u1 用户创建数据库
test=> create user s1_u1_u1 with nologin password '123456';
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE

编辑配置文件

vim /data/master/gpseg-1/pg_hba.conf

host    all     s1_u1_u1 192.168.71.100/32   md5

# 编辑完成之后 使用 gpstop -u 命令 更新配置
gpstop -u

授权测试

# 使用 s1_u1_u1 登录
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1_u1  -d test
Password for user s1_u1_u1:
psql: FATAL:  role "s1_u1_u1" is not permitted to log in

# 使用 s1_u1 用户 给 s1_u1_u1 用户赋予 登录权限
alter role s1_u1 login;

# 再次使用 s1_u1_u1 登录
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1_u1  -d test
Password for user s1_u1_u1:
psql (9.4.24)
Type "help" for help.

test=> \conninfo # 登录成功，查看登录信息
You are connected to database "test" as user "s1_u1_u1" on host "192.168.71.100" at port "5432".


# 测试 撤销 s1_u1 的创建角色权限，在使用s1_u1 赋予 s1_u1_u1 创建角色权限
# 1. 使用 s1_u1_u1 用户创建 juese 
test=> create user s1_u1_u1_u1 with nologin password '123456';
ERROR:  permission denied to create role

# 2. 使用 super 用户 撤销 s1_u1 角色的创建角色权限
alter role s1_u1 nocreaterole;

# 3. 使用 s1_u1 用户创建角色，看刚刚撤销其创建角色操作是否成功
test=> create user s1_u1_u2 with nologin password '123456';
ERROR:  permission denied to create role

# 4. 使用 s1_u1 赋予 s1_u1_u1 创建角色权限
test=> alter role s1_u1_u1 nocreaterole;
ERROR:  permission denied 

# 测试 s1_u1 用户赋予 s1_u1_u1 用户创建数据库的权限，发现普通用户不能将其拥有的创建数据库权限赋予其创建的子用户
test=> alter role s1_u1_u1 createdb;
ERROR:  permission denied

测试普通角色删除其创建的角色

# 使用 super 用户赋予 s1_u1_u1 用户创建数据库的权限
alter role s1_u1_u1 createdb;

# 使用 s1_u1_u1 用户创建数据库
test=> create database s1_u1_u1_db;
CREATE DATABASE

# 使用 s1_u1_u1 用户查看创建的数据库
test=> \l s1_u1_u1_db
                                List of databases
    Name     |  Owner   | Encoding |  Collate   |   Ctype    | Access privileges
-------------+----------+----------+------------+------------+-------------------
 s1_u1_u1_db | s1_u1_u1 | UTF8     | en_US.utf8 | en_US.utf8 |

# 使用 s1_u1 用户，删除 s1_u1_u1 用户, 无法删除
test=> drop role s1_u1_u1;
ERROR:  permission denied to drop role

# 使用 super 用户，删除 s1_u1_u1 用户, 因为 s1_u1_u1 用户拥有 数据库资源，所以该用户不能被删掉
test=# drop role s1_u1_u1;
ERROR:  role "s1_u1_u1" cannot be dropped because some objects depend on it
DETAIL:  owner of database s1_u1_u1_db

测试用户删除子用户创建的资源

# 使用 s1_u1 用户，删除 s1_u1_u1 用户创建的 s1_u1_u1_db 数据库
test=> drop database s1_u1_u1_db;
ERROR:  must be owner of database s1_u1_u1_db; # 提示进行这个操作的必须是 s1_u1_u1_db 的 拥有者

# 使用 super 用户，删除s1_u1_u1 用户创建的 s1_u1_u1_db 数据库，删除成功
test=# drop database s1_u1_u1_db;
DROP DATABASE

总结：

普通用户可以给其创建的用户赋予权限，但是只能将该用户拥有的权限赋予其创建的用户，

如上面的例子：

将 s1_u1 拥有创建数据库权限，可以成功将该权限赋予 s1_u1_u1 角色。

将 s1_u1 拥有创建角色权限收回以后，其不能将创建角色权限赋予 s1_u1_u1 角色。

普通用户不能给其子用户赋予创建数据库的权限
普通用户没有删除子用户的权限
普通用户没有删除子用户创建出的资源的权限
super 用户也不能删除一个拥有数据库资源的用户，需要先将数据库删除，再删除用户。

2.3.6 指定连接数测试

# 首先在 3 个 shell 窗口中登录 s1_u1 用户

# 创建 第一个 s1_u1 连接
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
Password for user s1_u1:
psql (9.4.24)
Type "help" for help.

test=>
# 创建 第二个 s1_u1 连接
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
could not change directory to "/root": 权限不够
Password for user s1_u1:
psql (9.4.24)
Type "help" for help.

test=>

# 创建 第三个 s1_u1 连接
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
could not change directory to "/root": 权限不够
Password for user s1_u1:
psql (9.4.24)
Type "help" for help.

test=>

# 退出上面所有的用户

# 使用 super 用户将 s1_u1  连接数设置为 2 
alter role s1_u1 connection limit 2

# 再次创建 第一个 s1_u1 连接
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
Password for user s1_u1:
psql (9.4.24)
Type "help" for help.

test=>
# 再次创建 第二个 s1_u1 连接
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
could not change directory to "/root": 权限不够
Password for user s1_u1:
psql (9.4.24)
Type "help" for help.

test=>

# 再次创建 第三个 s1_u1 连接
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
Password for user s1_u1:
psql: FATAL:  too many connections for role "s1_u1" # 并未登陆成功，提示连接数过多

2.3.7 密码有效期测试

# 使用 super 用户设置 s1_u1 用户的密码有效期为 2021-11-03 17:03:49
alter role s1_u1 valid until '2021-11-03 17:03:49';

# 查看时间
[gpadmin@gpnode1 root]$ date
2021年 11月 03日 星期三 17:04:04 CST

# 重新登录，发现密码已经失效
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
Password for user s1_u1:
psql: FATAL:  password authentication failed for user "s1_u1"

# 使用 super 用户将 s1_u1 用户密码有效时间延长，用原密码依旧可以登录
alter role s1_u1 valid until '2022-11-03 17:03:49'

# 使用 s1_u1 用户原密码登录成功
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
Password for user s1_u1:
psql (9.4.24)
Type "help" for help.

测试在密码失效后修改密码用户能否登录

# 使用 super 用户设置 s1_u1 用户的密码有效期为 2021-11-03 17:03:49
alter role s1_u1 valid until '2021-11-03 17:03:49';

# 重新登录 s1_u1 用户，发现密码已经失效
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
Password for user s1_u1:
psql: FATAL:  password authentication failed for user "s1_u1"

# 使用 super 用户修改 s1_u1 用户的密码
alter role s1_u1 password '111';

# 重新登录 s1_u1 用户，依旧无法登录

[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
Password for user s1_u1:
psql: FATAL:  password authentication failed for user "s1_u1"

# 使用 super 用户将 s1_u1 用户密码有效时间延长，检查使用哪个密码登录 s1_u1 用户
alter role s1_u1 valid until '2022-11-03 17:03:49'

# 登录 s1_u1 用户使用修改之前的密码（123456），登录失败
gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
Password for user s1_u1:
psql: FATAL:  password authentication failed for user "s1_u1"

# 登录 s1_u1 用户使用修改之后的密码（111），登录成功
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U s1_u1  -d test
Password for user s1_u1:
psql (9.4.24)
Type "help" for help.

2.3.8 删除用户

# 使用普通用户 s1_u1 删除其创建的用户 s1_u1_u1
test=> drop role s1_u1_u1;
ERROR:  permission denied to drop role

# 使用 super 用户 s1_u1 删除普通用户 s1_u1_u1
test=# drop role s1_u1_u1;
DROP ROLE

3. 数据库各层对象权限

对象类型	可授权权限
数据库	connect	连接数据库权限
	create	创建 schema / table权限
	temporary\|temp	创建临时表权限
	all	所有权限
schema	create	创建表的权限
	usage	对于表的使用权限,读取/写入
	all	schema 中的所有权限
表、视图、序列	select	select2
	insert	insert2
	update	update2
	delete	delete22
	rule	rule2
	all	关于 table 的所有权限
外部表	select	select2
	rule	rule
	all	all2
函数	execute	execute2
过程语言	usage	usage2

4. 数据库上的权限操作

权限列表

权限	说明
connect	连接数据库权限
create	创建 schema / table权限
temporary\|temp	创建临时表权限
all	所有权限

创建测试用户

-- 创建用户
create user gpadmin_u1 with 
nosuperuser  -- 不是超级用户
nocreatedb -- 不允许创建数据库
nocreaterole -- 不允许 创建和管理其他角色
noinherit -- 不从父角色中继承权限
nocreateexttable -- 不允许创建外部表
login -- 允许用户登录
noreplication -- 不给许复制权限
password '111';

编辑配置文件

# 编辑配置文件
vim /data/master/gpseg-1/pg_hba.conf

# 只在配置文件中配置 连接 test 库
host    test     gpadmin_u1 192.168.71.100/32   md5

# 重新加载配置文件
gpstop -u

4.1 测试 connect 权限

连接配置文件中未配置的数据库

# 连接 test 数据库
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U gpadmin_u1  -d test
Password for user gpadmin_u1:
psql (9.4.24)
Type "help" for help.

# 查看登录信息
test=> \conninfo
You are connected to database "test" as user "gpadmin_u1" on host "192.168.71.100" at port "5432".

直接登录 (连接失败)

# 使用 gpadmin_u1 连接 gpadmin_db 数据库, 连接失败
test=> \c gpadmin_db
FATAL:  no pg_hba.conf entry for host "192.168.71.100", user "gpadmin_u1", database "gpadmin_db", SSL off
Previous connection kept

使用 super 用户赋予 gpadmin_u1 连接 gpadmin_db 的权限 (连接失败)

# 使用 super 用户 赋予 gpadmin_u1 连接 gpadmin_db 的权限
grant connect on database gpadmin_db to gpadmin_u1;

# 再次 使用 gpadmin_u1 连接 gpadmin_db 数据库, 依然连接失败
test=> \c gpadmin_db
FATAL:  no pg_hba.conf entry for host "192.168.71.100", user "gpadmin_u1", database "gpadmin_db", SSL off
Previous connection kept

修改配置文件, 赋予 gpadmin_u1连接 gpadmin_db 的权限 (连接成功)

# 修改配置文件,给 gpadmin_u1 赋予 连接 gpadmin_db 的权限
vim /data/master/gpseg-1/pg_hba.conf

# 只在配置文件中配置 连接 test 库
host    test     gpadmin_u1 192.168.71.100/32   md5

# 重新加载配置文件
gpstop -u

# 使用 gpadmin_u1 连接 gpadmin_db 数据库
test=> \c gpadmin_db
You are now connected to database "gpadmin_db" as user "gpadmin_u1".
\conninfo
You are connected to database "gpadmin_db" as user "gpadmin_u1" on host "192.168.71.100" at port "5432".

撤销 gpadmin_u1连接 gpadmin_db 的权限

# 撤销连接权限
postgres=# REVOKE connect on database gpadmin_db from gpadmin_u1;
REVOKE

# # 使用 gpadmin_u1 连接 gpadmin_db 数据库, 依然可以连接成功
test=> \c gpadmin_db
You are now connected to database "gpadmin_db" as user "gpadmin_u1".

\conninfo
You are connected to database "gpadmin_db" as user "gpadmin_u1" on host "192.168.71.100" at port "5432".

总结

只要在配置文件中配置用户连接到某个数据库的权限, 用户就可以到指定连接到指定数据库
如果未在配置文件中配置连接权限, 而只用 connect 赋予权限, 用户并不能连接到指定数据库
在配置文件中配置用户连接到某个数据库的权限后, 使用 REVOKE connect on database ... 并不能回收用户连接数据库的权限

4.2 测试 create 权限

使用 gpadmin_u1 创建 schema, (报错, 此时并没有赋予 gpadmin_u1 创建 schema 的权限)

# 使用 gpadmin_u1 创建名为 gpadmin_u1_schema 的 schema
create schema gpadmin_u1_schema;
ERROR:  permission denied for database gpadmin_db

使用 super 用户赋予 gpadmin_u1创建 schema 的权限

gpadmin_db=# grant create on database gpadmin_db to gpadmin_u1;
GRANT

# 再次使用 gpadmin_u1 创建 schema (创建成功)
create schema gpadmin_u1_schema;
CREATE SCHEMA

测试使用 gpadmin_u1 用户在其创建的 schema 下创建表

# 测试在 schema 下创建表
create table  gpadmin_u1_schema.t1(id int,  name varchar);
CREATE TABLE

# 插入数据
insert into gpadmin_u1_schema.t1 values(1,'xiaoming');

# 查询数据
select * from gpadmin_u1_schema.t1;
 id |   name
----+----------
  1 | xiaoming

使用 gpadmin_u1 用户删除创建的 schema

# 删除 表
drop table gpadmin_u1_schema.t1;
DROP TABLE

# 删除 schema
drop schema gpadmin_u1_schema;
DROP SCHEMA

使用 super 用户回收 gpadmin_u1创建 schema 的权限

gpadmin_db=# revoke create on database gpadmin_db from gpadmin_u1;
REVOKE

# 再次使用 gpadmin_u1 创建 schema (创建失败)
create schema gpadmin_u1_schema2;
ERROR:  permission denied for database gpadmin_db

4.3 temporary / temp 权限

创建临时表 (此时并没有手动给用户创建临时表的权限)

# 创建临时表
create temporary table  temp_gpadmin_u1(id int,  name varchar);
CREATE TABLE

# 查看当前库有哪些表
\d
                       List of relations
    Schema     |      Name       | Type  |   Owner    | Storage
---------------+-----------------+-------+------------+---------
 pg_temp_51292 | temp_gpadmin_u1 | table | gpadmin_u1 | heap
(1 row)

# 插入数据
insert into temp_gpadmin_u1 values(1,'xiaoming');
INSERT 0 1

# 查询数据
select * from temp_gpadmin_u1;
 id |   name
----+----------
  1 | xiaoming

退出会话,重新登录查看临时表

# 退出会话
\q

# 重新登录
[gpadmin@gpnode1 root]$ psql -h 192.168.71.100 -U gpadmin_u1  -d gpadmin_db
gpadmin_db=>\conninfo
You are connected to database "gpadmin_db" as user "gpadmin_u1" on host "192.168.71.100" at port "5432".
\d
No relations found.

# 重新登陆后, 临时表已经被销毁

撤销 gpadmin_u1 创建临时表的权限

# 撤销 gpadmin_u1 用户 在 gpadmin_db 创建临时表的权限
gpadmin_db=# REVOKE temporary on database gpadmin_db from gpadmin_u1;
NOTICE:  no privileges could be revoked # 提示没有可以撤销的权限
REVOKE

gpadmin_db=# REVOKE temp on database gpadmin_db from gpadmin_u1;
NOTICE:  no privileges could be revoked
REVOKE

测试创建临时表

# 撤销 gpadmin_u1 用户创建临时表的权限, 后测试创建临时表
# 依旧创建成功
create temporary table  temp_gpadmin_u1(id int,  name varchar);
CREATE TABLE

总结:

连接到数据库后, 用户默认就拥有了创建临时表的权限
临时表会在会话结束后自动删除
即使回收了用户创建临时表的权限,用户依然可以创建临时表

4.4 总结数据库上权限

数据库上有 CREATE、CONNECT、TEMP 几种权限
需要在配置文件中配置用户连接某个数据库的权限, 用户可以连接数据库后, 无论是否赋予 connect 权限都能连接到数据库
用户连接上数据库后, 默认拥有 temp 权限, 即使回收后依旧能创建临时表
数据库的 CREATE 权限，控制是否可以在库中创建 schema，以及是否可以在schema下创建表与查询表中的数据。
可以在public schema中创建表。不能在 owner 为其他用户的schema下创建表。

5. SCHEMA上的权限

5.1 创建 schema

-- 使用 super 用户创建 schema
create schema gpadmin_schema;

\dns
        List of schemas
       Name        |   Owner
-------------------+------------
 gp_toolkit        | gpadmin
 gpadmin_schema    | gpadmin
 gpadmin_u1_schema | gpadmin_u1
 public            | gpadmin

5.2 创建表

-- 使用 gpadmin_u1 用户, 在 gpadmin_schema 中创建表
create table gpadmin_schema.t1(id int,  name varchar,PRIMARY KEY(id)) ;
ERROR:  permission denied for schema gpadmin_schema # 没有权限

--  设置 search_path
set search_path="$user",public,gpadmin_u1_schema,gpadmin_schema;

-- 使用 super 用户, 为 gpadmin_u1 赋予 create 权限
grant create on schema gpadmin_schema to gpadmin_u1;

-- 再次创建表
create table gpadmin_schema.t1(id int,  name varchar,PRIMARY KEY(id)) ;
ERROR:  permission denied for schema gpadmin_schema # 创建失败

-- 使用 super 用户, 为 gpadmin_u1 赋予 usage 权限
grant usage on schema gpadmin_schema to gpadmin_u1;

-- 再次创建表
create table gpadmin_schema.t1(id int,  name varchar,PRIMARY KEY(id)) ;
CREATE TABLE

-- 查看列表
\d
                        List of relations
      Schema       |     Name      | Type  |   Owner    | Storage
-------------------+---------------+-------+------------+---------
 gpadmin_schema    | t1            | table | gpadmin_u1 | heap
 gpadmin_u1_schema | gpadmin_u1_t1 | table | gpadmin_u1 | heap
 gpadmin_u1_schema | gpadmin_u1_t3 | table | gpadmin_u1 | heap
 public            | gpadmin_u1_t2 | table | gpadmin_u1 | heap

-- 写入数据
insert into gpadmin_schema.t1 values(1,'xiaoming');
INSERT 0 1
select * from gpadmin_schema.t1;
 id |   name
----+----------
  1 | xiaoming

5.3 撤销 create 权限

-- 使用 super 用户, 撤销 gpadmin_u1 对 gpadmin_schema 的 create 权限
revoke create on schema gpadmin_schema from gpadmin_u1;

-- 创建表
create table gpadmin_schema.t2(id int,  name varchar,PRIMARY KEY(id)) ;
ERROR:  permission denied for schema gpadmin_schema

-- 查询 gpadmin_schema.t1 表数据
select * from gpadmin_schema.t1;
 id |   name
----+----------
  1 | xiaoming

5.4 撤销 usage 权限

-- 使用 super 用户, 撤销 gpadmin_u1 对 gpadmin_schema 的 usage 权限
revoke usage on schema gpadmin_schema from gpadmin_u1;

-- 使用 gpadmin_u1 用户查询 t1 表(查询失败)
select * from gpadmin_schema.t1;
ERROR:  permission denied for schema gpadmin_schema
LINE 1: select * from gpadmin_schema.t1;

-- 使用 gpadmin_u1 用户, 向 t1 表写入数据 (写入失败)
insert into gpadmin_schema.t1 values(3,'xiaoming');
ERROR:  permission denied for schema gpadmin_schema

5.5 总结 SCHEMA 上的权限汇总

在用户自己的 schema 中创建表默认拥有表的 all 权限
用户默认无法在 owner 为别个用户的 schema 中创建表。
如果要在别人的 schema 中创建表，用户需要拥有该 shema 的CREATE、USAGE 权限
用户默认无法看到 owner 为别个用户的 schema 中的表，注意设置search_path 。（\dt命令查看）。
只赋予USAGE权限后可以看到 owner 为别个用户的 schema 中的表，但无法在里面创建表。
如果没有USAGE权限，仍无法看到表，无法查询表中的数据，也无法更改表，即使 owner 也是不行。
赋予 USAGE 后可以查询自己创建的表，可以更改自己创建的表，但无法查询别人的表。

6. 在 TABLE 上的权限

对象类型	可授权权限
create	创建表的权限
usage	查询/写入数据的权限
all	所有权限

权限	说明
select	select2
insert	insert2
update	update2
delete	delete2
rule	rule2
all	all2

-- 使用 super 创建一张表, 供测试使用
create table gpadmin_schema.t1(
  id int,  
  age varchar,
  name varchar,
  PRIMARY KEY(id)
);

-- 写入几条测试数据
insert into gpadmin_schema.t1 values(1,18,'xiaoming');
insert into gpadmin_schema.t1 values(2,19,'xiaogang');
insert into gpadmin_schema.t1 values(3,20,'xiaohuang');

-- 为用户赋予 gpadmin_schema 的 usage 权限 (如果没有 usage 权限则用户看不到这张表)
grant usage on schema gpadmin_schema to gpadmin_u1;

6.1 select 权限

-- 在默认情况下, 使用 gpadmin_u1 用户查询 t1 表(查询失败,没有权限)
select * from t1;
ERROR:  permission denied for relation t1

-- 使用 super 用户,为 gpadmin_u1 用户,赋予 t1表的 select 权限
grant select on gpadmin_schema.t1 to gpadmin_u1;

-- 使用 gpadmin_u1 用户查询 t1 表(查询成功)
select * from t1;
 id | age |   name
----+-----+-----------
  2 | 19  | xiaogang
  3 | 20  | xiaohuang
  1 | 18  | xiaoming

6.2 insert 权限

-- 在默认情况下, 使用 gpadmin_u1 用户向 t1 表写入数据 (写入失败,没有权限)
insert into gpadmin_schema.t1 values(4,21,'xiaodong');
ERROR:  permission denied for relation t1

-- 使用 super 用户,为 gpadmin_u1 用户,赋予 t1表的 insert 权限
grant insert on gpadmin_schema.t1 to gpadmin_u1;

-- 再次写入数据
insert into gpadmin_schema.t1 values(4,21,'xiaodong');
INSERT 0 1

select * from t1;
 id | age |   name
----+-----+-----------
  2 | 19  | xiaogang
  3 | 20  | xiaohuang
  4 | 21  | xiaodong
  1 | 18  | xiaoming
(4 rows)

6.3 update 权限


-- 在默认情况下, 使用 gpadmin_u1 用户更新 t1 表中 id 为 1 的数据 (更新失败,没有权限)
update gpadmin_schema.t1 set age=30 where id = 1;
ERROR:  permission denied for relation t1

-- 使用 super 用户,为 gpadmin_u1 用户,赋予 t1表的 insert 权限
grant update on gpadmin_schema.t1 to gpadmin_u1;

-- 再次更新数据
update gpadmin_schema.t1 set age=30 where id = 1;
UPDATE 1
select * from t1;
 id | age |   name
----+-----+-----------
  2 | 19  | xiaogang
  3 | 20  | xiaohuang
  4 | 21  | xiaodong
  1 | 30  | xiaoming -- 数据已经更新

6.4 delete 权限

-- 在默认情况下, 使用 gpadmin_u1 用户删除 t1 表中 id 为 1 的数据 (操作失败,没有权限)
delete from gpadmin_schema.t1 where id = 1;


-- 使用 super 用户,为 gpadmin_u1 用户,赋予 t1表的 delete 权限
grant delete on gpadmin_schema.t1 to gpadmin_u1;

-- 再次删除数据
delete from gpadmin_schema.t1 where id = 1;
DELETE 1

select * from t1;
 id | age |   name
----+-----+-----------
  2 | 19  | xiaogang
  3 | 20  | xiaohuang
  4 | 21  | xiaodong

6.5 rule 权限

-- 使用 super 用户,为 gpadmin_u1 用户,赋予 t1表的 rule 权限
grant rule on gpadmin_schema.t1 to gpadmin_u1;

-- 首先创建一个规则 (做 insert 操作之后, 做一次查询)
create rule t1_insert_rule as 
on insert to t1 where id < 10 
do also select * from t1;

ERROR:  must be owner of relation t1 ?? -- 赋予 rule 权限之后依旧无法在 t1 表上使用规则

-- 使用 owner 用户, 在表上使用规则
gpadmin_db=# insert into gpadmin_schema.t1 values(5,21,'xiaodong');
 id | age |   name
----+-----+-----------
  5 | 21  | xiaodong
  2 | 19  | xiaogang
  3 | 20  | xiaohuang
  4 | 21  | xiaodong

hnbian

https://www.hnbian.cn/posts/1412106c.html

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 hnbian !

greenplum

Ambari 添加 Ranger 服务

1. 准备工作# 创建数据库 mysql> create database ranger character set utf8; Query OK, 1 row affected (0.00 sec) # 创建 rangeradmin 用

2020-05-18 ambari

ranger

GreenPlum 数据存储介绍

1. 数据存储分布式数据存储基本原理相对简单，实现比较容易，很多数据库中间件也可以做到基本的分布式数据存储。Greenplum 在这方面不单单做到了基本的分布式数据存储，还提供了很多更高级灵活的特性，譬如多级分区、多态存储。Greenplu

2020-05-06 greenplum

greenplum

GreenPlum 权限管理

1. GreenPlum 权限说明

2. 测试用户方面的权限

2.1 用户角色列表

2.2 角色相关的元数据表

2.3 创建用户测试不同权限

2.3.1 创建超级用户

2.3.2 创建普通用户

2.3.3 用户登录权限测试

2.3.4 创建数据库权限测试

2.3.5 创建角色测试

2.3.6 指定连接数测试

2.3.7 密码有效期测试

2.3.8 删除用户

3. 数据库各层对象权限

4. 数据库上的权限操作

4.1 测试 connect 权限

4.2 测试 create 权限

4.3 temporary / temp 权限

4.4 总结数据库上权限

5. SCHEMA上的权限

5.1 创建 schema

5.2 创建表

5.3 撤销 create 权限

5.4 撤销 usage 权限

5.5 总结 SCHEMA 上的权限汇总

6. 在 TABLE 上的权限

6.1 select 权限

6.2 insert 权限

6.3 update 权限

6.4 delete 权限

6.5 rule 权限

你的赏识是我前进的动力