PostgreSQL user、db、schema、权限-阿里云开发者社区

开发者社区> 数据库> 正文

PostgreSQL user、db、schema、权限

简介: 本文探讨 PostgreSQL中的 user、db、schema、权限

pg中角色(role)和用户(user)之间只有1个区别。
创建一个用户啥参数也不带,那这个用户具有登录权限。
创建角色啥参数也不代表,这个角色没有登录权限,默认pg认为你是创建一个权限的集合,是给用户快速赋权用的,那你要登录权限干啥呢

示例:
--encrypted 用于控制存储在系统表中的口令是否加密,这里是加密的意思,如果你对这些关键字不理解,最好买本书参照。
create user user1 with encrypted password 'user1';
create role role1 with encrypted password 'role1';
select current_database();
select "current_user"();
-- 成功
c postgres user1;
-- 失败
c postgres role1;

模式schema
模式可以理解为是mysql中的数据库,一个mysql中可以有多个数据库,层级可以理解为 数据库-用户
pg中多了一层,一个数据库下可以有多个schema,层级可以理解为 数据库-schema-用户
为什么PG要多了一层,因为PG既要兼容mysql又要兼容oracle,所以PG得全都有才行,这样无论从mysql或者oracle移植到PG中都可以正常使用。

创建一个模式 h create schema;这里为什么用gao数据库,因为默认咱们是使用此数据库,PG真正用起来需要自己创建数据库使用。
c gao postgres;
如果你创建schema,后面不加认证用户的话,默认你schema的权限是属于postgres用户的。其他用户无法访问。
create schema schema1;
dn 列出模式
drop schema schema1;

创建schema 名为schema1,同时把该schema的使用权限赋予gao用户;如果你不授权,那么你的数据库的用户gao是无法访问此schema内的资源的。
因为一个数据库内可以有多个schema,多个用户,搞这么复杂就是为了权限控制,有的用户可以访问某个schema,有的用户没有权限访问。
你创建一个数据库,默认就会给你生成public schema
create schema schema1 authorization gao;
drop schema gao;
dn 列出模式

日常中我们使用的表,前缀没加过schema名,比如说gaoschema.test,是因为默认数据库认为你是在public模式中,那么更改默认模式名是如下
show search_path ;
set search_path = schema1;
show search_path ;

下面用一个完整的实验让大家理解下schema和user之间权限的控制
首先你要用postgres超级用户进入到gao数据库内
c gao postgres
select current_user;
select current_database();

-- 这里是创建不了gao用户的,因为咱们在postgres数据库里,创建用户,表空间,数据库的时候就已经创建了。
create user gao with encrypted password 'gao';
create schema schema1 authorization gao;

此时你用的是postgres超级用户是可以看得到该schema的。那用gao用户能不能看得到呢?

也是可以看到的

那我现在用gao用户在gao数据库下创建表test,看看这个test是属于什么schema的。

可以看到,你创建的表默认是属于public模式的,那我真正用起来难道gao用户在gao数据库下创建表就要放到publicschema下吗,当然是不是的。
规划的时候要规划好,用户A的表只能用户A访问;用户B的表只能用户B访问;用户A和用户B共同访问的数据放到一个公共的schema下,你可以用public,也可以自己创建一个公共的schema。这里演示的是自己新创建一个多用户共用schema;而不是用默认的public。

先使用表pg_tables;查看表是属于什么schema的;

先看看表结构都是啥

可以看到test表是属于public schema的。

那我刚刚创建了一个schema,名字叫schema1,想让创建的表属于schema1,而不是public,该怎么做?
之前说了默认schema设置,难道是跟这个有关的吗?

查看自己的默认schema

设置默认schema为schema1后,创建表test1,再次查看,发现test1表是属于schema1了,而不是public

我们再详细的查一下

结果还是如此

此时我们还是用的gao数据库,gao用户。

那gao用户对test1对象的权限都有哪些呢,自己创建的东西,自己是拥有所有权限的。

那难道每次创建都要指定schema吗?也不是的。

你可以像oracle 用户名.表名 那样去在pg中创建对象。

之所以会创建成功,是因为gao用户对schema1有使用权限。自己创建的东西,自己是拥有其所有权限的。

那此时如果我们把默认的schema改回public,还能查到这个test1和test2吗?
d命令是不可以的,因为默认schema设置后,只会查找默认schema的设置的范围。

那此时指定schema.table可以看到吗?

是可以的,指定全路径的话,就不会到public去搜索了。

那postgres用户在postgres数据库中可以查找到gao数据库gao用户下的test1和test2吗?

很明显,PG中,多个数据库之间是相互物理隔离的,你在postgres数据库是查看不了gao数据库的东西的。
如果你要多数据库查询,oracle有dblink,pg当然也有dblink,就可以跨数据库查询了。

上面创建的schema1是postgres超级用户在gao数据库中创建,并赋使用权限给gao用户的;
那此时我用postgres超级用户在gao数据库中创建schema2,不把权限赋予给gao用户,那gao用户可以访问schema2吗?
(在这里只是创建schema2,并没有任何普通用户对该schema有使用权限。)
理论上是不行的吧,因为没赋权,这里测试一下

使用postgres超级用户gao数据库,schema2下创建表test1

可以看到不同schema下对象的名字可以是重名的,不互相影响。

此时切换到gao用户到gao数据库下访问schema2的test1表

可以看到跟咱们想的是一样的,没权限。

举个真实场景的例子:
电信公司有一个通信业务和账单业务;
通信只能访问通信的对象(资源);
账单只能访问账单的对象(资源);
两者不可以相互访问;
但是通信和账单业务,还要有可以共同访问的对象,也就是公共资源;这样公共资源不用在每一个业务中创建,只用在公共资源里创建一次就够了。

create schema s1;
create schema s2;
create user u1 with password 'u1';
create user u2 with password 'u2';
create table s1.test (id int,name varchar(20));
create table s2.test (id int,name varchar(20));
insert into s1.test values (1,'PostgreSQL');
insert into s2.test values (1,'MySQL');

此时我们用u1去访问s1.test能访问吗?
当然是不能的,因为没有赋权。那就去赋权一下。
c gao postgres;
grant select on s1.test to u1;

切换到u1用户到gao数据库查看s1.test

可以看见即使在gao数据库用postgres用户,把是s1.test表,赋select权限给用户u1后,u1还是无法访问的。

这是因为之前说过,如果你创建schema后面不加认证用户,则该schema默认是属于postgres用户的,其他用户无法访问。即使已经赋select权限给其他用户。

u1用户对s1.test表是有select权限,这是显而易见的,但是u1用户对schema s1有权限吗?是没有的,因为我们没有赋权。
PG中赋权是一层一层的,上层schema级别没有权限,那你即使下层赋予了schema下的表的相关读写权限,也是无法使用该表的。

那我们就要让u1用户对s1 schema赋予使用权限。

garnt可以对非常多的对象授权,schema只是其中一个。从帮助中找到授权schema的命令

切换到gao数据库postgres超级用户

可以看到我们把schema上的usage权限赋予了用户u1;

这个usage权限是啥意思呢?
对于schema来说,表示允许授权者查找schema的对象。说人话就是你只能看到该schema下的对象,能select该对象吗,是不行的,你还要grant select on table s1.test to u1;赋予select权限才行。是不是感觉很麻烦。

像usage这样的,关于grant的权限关键字就太多了,建议你买本书当字典查。

刚刚好我们之前赋予了select权限,下面看看u1能不能select s1.test表了。

是可以看到了,而且不用加schema的前缀。

同样的对于u2和s2和s2.test,跟上面的u1是一样的。

那创建的schema的owner也是可以改变的,原来是postgres用户,现在我想把s2赋予u2用户。

更改属主

发现schema的属主已经变为了u2;

此时查看表s2.test的属主

可以看到test表还是属于postgres的,这说明即使schema的属主变为了u2,以前已经创建的表的属主不会改变。

那这里可以修改单个表的属主,也可以批量修改。
单个:

批量:
批量修改需要写自定义函数来实现,参考下面的文章
https://www.cnblogs.com/miaoweiye/p/11960369.html

这里还有个折中的办法

把s2下的所有表的所有对表的操作的权限都赋予给用户u2;

看到这里本示例就知道该如何做了。

那可能有问题,为啥我要搞这么麻烦呢,有没有一个创建用户后,直接给用户什么权限,该用户就可以直接使用的权限呢,就像oracle那样,创建一个用户,赋予rosurce和connect就完事了的方法呢。(grant connect,resource,dba to abc;)

首先PG不建议权限管理这方面这么省事的做法。因为有的函数和触发器啥的,一个用户有所有的权限,那我获取到你用户的密码后,可以随机创建自定义函数和触发器,如果这其中存在不良代码,将会很危险。
PG建议严格控制权限来管理数据库。

其次你创建schema的时候可以指定属主用户,用指定的用户到自己的schema下创建东西都是可以的。自己创建的东西拥有所有的权限嘛。

如果你想拥有其他schema的所有对象的所有权限,也是需要写一个自定义函数来实现的,PG没有内置的管理所有对象的所有权限的语句哦。参考 https://www.cnblogs.com/miaoweiye/p/11960369.html

为什么要使用schema+user的方式来实现权限控制,而不使用database+schema+user的方式?

PG中各数据库之间是物理隔离的,默认不可以相互访问,除非使用dblink。
而使用schema,只要你有权限多schema之间可以隔离也可以相互访问,精确到每个对象;
所以对于短期需要各个schema之间进行隔离,未来schema之间又要相互访问,或者多个用户之间要访问公共资源的时候,用schema+user管理权限就非常简单直白。以后业务关系变得更复杂也不怕。

上面简单了解了下schema,下面进一步举例说明schema如何控制用户权限。

schema特点:

1. schema下不能再有schema嵌套,拒绝套娃。
2. 各个对象(表、函数,索引、触发器等)可以分好类放在各个schema下。

仅仅针对可以进行分类分开存放的对象。
表和索引触发器分开存放有没有意义不太清楚哦,对性能有啥影响也不太清楚。
但是函数跟表没啥关系,是可以分开存放到各个schema下的。

3. 同一个schema下不能有重复对象名字,不同schema没有此限制。

schema的作用:

1. 多个用户共享一个数据库,相互之间还可以互相独立
2. 方便管理多个类型的对象
3. 方便兼容某些第三方应用程序,创建对象在schema里创建,不影响其他schema。

常用数据库中的schema不同之处:
pg:
就不说了。
oracle:
不能直接新建一个schema,系统在创建一个用户的同时给这个用户创建了一个同名的schema,并且作为该用户的默认schema。咱们自用oracle的时候都只是创建用户,并没有跟着创建一个schema吧,但是各用户之间又都是相互隔离的。
mysql:
没有schema的说法,你创建一个database的效果就跟建立一个schema是等同的。

现在假设我已经有一个业务数据库,叫做gao了;现在想新建一个gao的测试用数据库gaotest。
gaotest的postgres用户我不想让别人使用。所以创建一个权限小一点的用户名字也叫作gaotest。

简单管理用户:gaotest
创建3个schema,s0,s1,s2
3个业务用户,master,standby1,standby2

master拥有对s0,s1,s2的所有权限。
Standby1拥有对s1的所有权限。
Standby2拥有对s2的所有权限。

首先要创建目录,用户,表空间,数据库;这个目录要跟生产gao数据库的目录要区分开。

先使用postgres用户进入到postgres库来操作。如果你不想用postgres用户和postgres数据库来操作,你也可以在gao数据库里,搞个别的有create database 权限的超级用户;因为数据库和数据库是同级别的没有高低之分;区别就是postgres数据库和postgres超级用户是PG自动生成的,方便你管理。建议除非PG DBA,否则其他人不得持有postgres用户的使用权限。
你可以在postgres数据库,另外创建一个超级用户admin来管理。当然你也可以用postgres用户。

创建表空间和数据库的时候都可以指定owner属主是哪个用户
如下图创建了gaotest用户,创建了表空间gaotest_tbs,创建了数据库gaotest。

然后我们进入到gaotest数据库,用gaotest用户;

此时我的想法是,生产数据库gao和测试数据库gaotest之间的两个用户,生产用户gao和测试用户gaotest隔离开。gao用户只能连接生产gao数据库;gaotest用户只能连接测试gaotest数据库;这样生产环境和测试环境隔离开,防止出现想在测试库测试,误连接到了生产的情况。
但是这里遇到了一点问题;

我使用gaotest用户登录到gaotest数据库中,是无法在创建删除用户的。此时的想法是,要创建的3个用户master、standby1和standby2都只创建在gaotest数据库中,因为测试数据库用户为什么要创建到生产数据库中呢?但是毫无疑问是失败了。
接着我猜测可能是没有在postgres数据库中使用postgres用户授予创建用户的权限。于是连接到postgres

把gaotest数据库的所有可赋予的操作权限都赋予给gaotest用户。这就是all privileges关键字的含义。

那我连接到gaotest测试能不能创建用户了呢,答案还是不行的

为什么会没权限?

这就要说到all privileges关键字的含义。
All privileges权限确实赋予了所有可以赋予的权限;但是所有可以赋予的权限里,没有create user和drop user,所有权限里,有一个create权限,这个权限对于数据库来说只可以create/drop schema,是不能create/drop user的;

在PG中user是高于database层级的。而在oracle中,user是被包含在database中的。如果你以前是搞oracle的,可能会因此走入误区。

那创建删除用户一定要用超级用户来搞吗,不能用普通用户来操作?
普通用户也是可以操作的。但是需要用超级用户授权,或者你在创建普通用户的时候就指定参数。

使用postgres用户到postgres数据库中:
创建时:

或者是create user jia login createrole; 跟create role没有区别

创建后修改:

此时可以看du

也可以参考postgres所拥有的权限来赋权给gaotest哦。

PG中的权限分为两部分,一部分是用grant来赋权,一部分是用alter role来赋权。

alter role:创建用户时需要指定的权限,后期修改的话就用alter role;
grant:创建用户的时候不能指定的权限,后期修改的话就用grant;
grant的回收是revoke;alter role的回收还是alter role;

比如回收createrole权限:

那创建用户我们可以用自己创建的超级用户admin,或者用自带的postgres,或者用普通用户gaotest;这里就用gaotest用户在gaotest数据库创建用户。

其实在哪个数据库里创建用户都是无所谓的,因为用户是全局级别的,比数据库高一个层级。我这里还是像用oracle数据库一样进入到数据库中再操作用户,可能是习惯了。

(顺便一提,回收all privileges权限的话是用

回收的话是用revoke from
赋权的话是用grant to

那现在我们就使用postgres用户到gaotest数据库中让gaotest用户有创建用户的权限createrole和all privileges权限。
grant all privileges on database gaotest to gaotest;
alter role gaotest createrole ;

然后我们用gaotest用户到gaotest数据库中创建3个用户

然后我们用postgres用户到gaotest数据库赋予master all privileges权限。

然后我们用master用户登录到gaotest数据库创建自己的schema

使用master用户进入到gaotest数据库中。
-- 创建3个schema
drop schema if exists s00 cascade;
-- 不指定用户默认就是master的
create schema s00;
drop schema if exists s01 cascade ;
create schema s01 authorization standby1;
drop schema if exists s02 cascade ;
create schema s02 authorization standby2;
-- 主用户在每个schema中创建表test0
create table s00.test0(id int);
insert into s00.test0 values (1);
create table s01.test0(id int);
insert into s01.test0 values (1);
create table s02.test0(id int);
insert into s02.test0 values (1);

Standby1在自己的schema s01中创建表test1;

Standby2能在s01中创建表吗?

是不可以的

Standby2在自己的schema s02中创建表test2;

验证owner,首先gaotest数据库是属于gaotest用户的。

其次master的权限是包含两个子用户的

再其次3个schema属于不同的用户

其次表的owner

访问权限
master:每个子用户下的资源都可以访问

Standby1:

Standby2:
同理

S02这个schema原来属于standby2的,现在变更为standby1;

权限变更后

原来可以访问的表也不能访问了。
虽然s02.test2 standby2访问不了了,但是表的owner却没有改变。

必须通过赋权才能改变表的owner
此时s02原来所属用户standby2已经无法修改s02下的表的owner了。
就连s02现有的归属者standby1也无法修改。
可以用master更改。

此时master用户已经对s02没有修改权限了,只能用postgres用户去更改了。

那我用postgres用户再把s02.test2的owner改回到standby1;用standby1修改s02.test2的owner能成功吗

也是无法更改的。
现在master、standby1和standby2都无法修改了,只能用postgres用户修改了,但是master还是可以访问s01和s02的。

那我们把这些操作都还原,让s01的还是standby1的,s02的还是standby02的。

此时正常来说standby2是无法访问s01的资源的。

如果你不想变更表的owner,也就是s01还是standby1的,s02还是standby2的,让standby2可以访问s01的资源。

通过赋权可以把s01下的所有的表的所有操作权限都赋予给standby2;这就是all关键字的含义(把grant能赋予的权限都赋予给standby2);但是你只是把表级别的权限赋予了,schema级别的使用权限还没赋予。

所以还要在赋予schema级别的使用权限。

现在用standby2尝试访问s01

尝试插入语句

那我现在要恢复原样,s01的还是standby1的,s02还是standby2的,相互不能访问。

现在我们要把standby2的s02的属主变更为s01
把s02.test2的属主变更为s01;

这样standby2用户就没有任何访问权限了。

我们希望该用户在所有schema下都只能读,不可以写。
按照下图设置,是现有的表standby2都可以读取,新增的表还是没有权限读取。

所以需要让未来新增的表standby2也可以读取。
下面的意思是:对于master和standby1用户,不论在s00、s01、s02上创建的表,standby都可以进行select查询。

验证一下:

master用户新增表,看看standby2能不能查看和插入

是符合我们的预期的。

那现在研究一下gaotest用户只能登陆gaotest数据库,不能登陆gao数据库。
方法1:在pg_hba.conf中设置。
方法2:赋予用户connect只能连接某个数据库的权限。

方法1:
修改pg_hba.conf文件

Pg_ctl reload
此时可以连接吗?是不可以的,会提示密码错误。但是我明明输入对了密码啊,为什么还会报密码错误呢?

原来method是trust的时候创建的gaotest用户,密码是gaotest;
现在method改成了md5,需要对密码进行MD5加密,但是数据库中存储的是gaotest明文密码,是没有进行过md5加密,也没有md5加密后生成的秘钥;所以你当然无法成功连接。那此时需要先把pg_hba.conf中的method改为md5,然后到pg中修改gaotest用户的密码为gaotest,后台会对gaotest这个密码进行md5的解析,生成秘钥,那个时候才能用md5的方式来连接

按上面的操作md5的就可以满足gaotest用户只可以连接gaotest数据库了。

那我把method从md5改为scram-sha-256,也是需要重新生成秘钥的。也就是说你也得重新改下密码才行。
Step1:pg默认的验证方法是md5

在数据库里查也是md5

需要在postgresql.conf配置文件里改为scram-sha-256,然后该参数是user,reload就生效。

Step2:修改pg_hba.conf文件,把method修改为scram-sha-256

Step3: 更改gaotest用户的密码

能看到gaotest用户的scram-sha-256的秘钥已经生成了。

尝试连接,成功

尝试用gaotest用户连接其他的数据库比如说gao
报错。
方法1结束。

方法2如何做:
查看pg_hba.conf文件,是没有限制允许登录哪个数据库的。

那我们就只通过对用户的connect权限的控制来让gao用户只能登陆gao数据库,gaotest用户只能登陆gaotest数据库。
可以看到现在gaotest是可以登陆gao数据库的。

因为创建的普通用户,默认就是可以连接所有PG下的数据库
先看看现在都有什么数据库

使用revoke回收权限。

此时尝试使用gaotest用户连接到gaotest数据库,发现还是可以连接的。

经探究,发现你任意创建的具有login登录权限的普通用户,都具备连接到任意数据库的public模式的权限。即使像我这样已经回收了gaotest用户connect连接gaotest数据库的权限。我也可以使用gaotest用户连接到gaotest数据库下的public模式中。
那现在回收这个权限。

上图中很明显gaotest用户已经无法连接到gaotest数据库的public模式下了。

那又有一个问题产生了
回到还拥有普通用户可以连接connect 任意数据库下的public模式权限的时候。
那时我们只回收了gaotest用户connect连接所有其他数据库的权限。
那理论上来说此时gaotest用户是可以连接任意数据库下的public模式。

问题是:此时gaotest用户可以连接任意数据库下的其他非public模式吗?
Step1:先把两个权限都复原

连接到gaotest

创建schema test

创建表test.testgao

此时我们收回gaotest用户connect gaotest数据库的权限,让gaotest用户只能连接gaotest数据库的public模式

此时可以看到即使回收了connect权限,理论上只能访问owner=gaotest数据库下的public schema下的对象;但是实际上gaotest用户仍然可以访问之前自己创建的(或者说gaotest用户有权限操作的)test schema下的对象。这可能跟我们理解的不太相符。也许我这里理解错了也说不定。

那上面的猜想测试之后得出了结果,我们继续往下做。
先把connect 所有数据库的权限回收;
在把connect public 权限回收;

此时gaotest用户是无法连接gaotest数据库了。

这是符合我们预期的。

那现在单独授予gaotest用户只能连接gaotest数据库的权限。

也可以访问gaotest用户之前创建的对象

那现在又有问题来了,我没授权public模式给gaotest用户,那gaotest用户可以访问public下的东西吗?

显而易见是可以的。

那我们对public模式的理解还是不够深,所以才有疑问。
此时gaotest用户也不可以连接其他的数据库,生产库用户和测试库隔离的目的就达到了。

方法2就到此结束。

行级安全增强

1,任何用户都可以连接数据库,但是当前数据库用户所属者创建的数据库对象只能属于当前用户,实现了用户层面数据库对象隔离

2,任何用户都有属于自己的schema,任何用户都可以总有对schema的使用权限,但是schema下的对象只能是创建该schema的用户有效。

3,创建用户和数据库只能由超级用户postgres去完成,这保证了普通用户之间的安全性,换句话说,普通用户不会因为误操作删除某个用户或者数据库

4,共享数据库对象可以通过grant授权方式来实现,保证数据共享爱你维护数据一致性。

因此,在PostgreSQL中,任何普通用户数据对自身来说是安全的,对其他普通用户来说也安全。

以上对postgres用户无效,谨慎使用postgres用户。
image.png

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章