PostgreSQL 用户权限 回答ORACLE DBA 的问题

2020年是收割主角的一年， 2021年是收割配角的一年，当我们觉得生命还有些年头的时候，其实每天都是倒计时，到底来着一生是为什么，能做自己想做的就已经很幸运了。

今天的文字来自于一个同学的要求

那么就的

我们先从上到下的方式来说说POSTGRESQL 的用户怎么管理,实际上POSTGRESQL 的用户管理的方式,如果你是 SQL SERVER 的DBA ,那么基本上不用去学,那是一样一样一样的. (也有略微的区别,但和其他数据库比较,理解上是最快的并且没有隔阂)

上图是一个POSTGRESQL 自上而下的从POSTGRESQL CLUSTER ,到OBJECT 的一个图.

那么创建一个数据库后,创建者有什么权限, owner 拥有者的权限,拥有者又有什么权限. Owner 是一个数据库的创建者,(一般来说), Owner 也是对于这个他创建数据库的object 拥有所有的对象的权限的账号属性.

这些权限包含,select , insert, update,delete,trucate,references (创建外键),trigger, create , connect, temporary, execute , usage (函数与存储过程权限).

当然有篇英文文章上写着 owner is small superuser ,也有点道理.

那么下面有些东西就开始不好理解了

问题1 PG 和 ORACLE 之间,我拿他当ORACLE 用SCHEMA 来管理,可以吗?

当然,当然可以,这应该也是被推荐的方法, PG 个人觉得, 在表和用户的管理上,和ORACLE 的方法是很类似的. 我们通过schema 来管理表, 一个用户掌握一个SCHEMA 的方法也是不错的管理的方法.

我们按照上面的方式来对数据库进行一个管理的操作的

1 创建一个用户, 创建一个数据库,

2 将某个用户更改为数据库的OWNER

3 创建一个schema

4 我们利用新创建的账号 dba 来登陆到 dba_database

我们创建并且创建一个表,这里注意schema 还是public 但表的tableowner 是 dba , 那么此时除了 SUPERUSER ,或者你grant 表给用户,或者创建者自己, 以外的用户是不能访问这个表.

例如我们在创建一个用户 tma

那么我们通过这个用户去访问sys_a ,必然是失败的

那么到这里我们去小结一下,当前的操作

1 postgresql 默认的schema 是 public

2 数据库的owner 拥有这个数据库的所有权限

3 不是这个数据库的owner,并且不是自己创建的表或者object 是无法访问的

4 建立的数据库对所有的用户都具有连接的权利 (这个和权限无关)

下一个问题是为什么什么用户即使不是这个数据库的owner 也拥有在这个数据库创建OBJECT的权利???? 我可以不可以不让没有这个数据库权限的用户,连接不了这个数据库.

我们可以直接回收除 super user 和 db owner 以外任何人对这个数据库的

登陆的权限.

revoke CONNECT ON DATABASE dba_database from public;

在此登陆就无法进入了.

那么其实还有另外一个问题,我可以让所有用户对于我建立的数据库具有访问connect的权限,但仅仅是这样权限, 不能在public 中建立任何的OBJECT

grant CONNECT ON DATABASE dba_database to public;

我们回收在任何数据中每个用户对于public 都具有的 create 和 usage 的权限

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

REVOKE USAGE ON SCHEMA public FROM PUBLIC;

在此登陆那么除了db owner 以及superuser 都对这个数据库不具有操作的权限了.

这里在小结一下

1 新建立一个数据库,如果你想使用public schema ,则可以直接先将create 和usage 的权限回收,这样陌生的用户全部无法在这个数据库创建任何OBJECT

2 我们建议新建的业务的数据库,不要使用public 作为你默认的schema,自己建立一个schema 并且设为默认,也可以解决上面的问题

那么POSTGRESQL 的权限和使用有什么好的方法

1 如果表和表之间需要有关联性的查询, 不要把他们放到不同的数据库databases 下, 两个业务的数据库如果硬件可以的话,是可以将他们放到一个POSTGRESQL 的 cluster 下的不同的数据库下.

2 如果我按照ORACLE 的管理方式, 我通过账号+SCHEMA 的方式管理表,分割业务属性,以及权限的使用,也是一种方法

但不建议在一个数据库下放置过多的表,具体的数量这个并没有定义,但数据库中对表进行 vacuum 操作以及对 autovacuum_works 设置的工作的works number 都说明如果把一个数据库里面放置过多的表,在vacuum 的操作中,都不是什么太好的安排.

部分内容参考

https://aws.amazon.com/cn/premiumsupport/knowledge-center/rds-postgresql-revoke-public-schema/