POSTGRESQL 用户怎么乱糟糟,出自其他DB的评论, 与SCHEMA 移魂大法

近一些同学反应,POSTGRESQL 的用户到底怎么回事,怎么这么乱,说这些话的是MYSQL的DB 同学.是的,如果之前是操作MYSQL 在使用了POSTGRESQL 后,是会感觉到,有点怪怪的,但如果你之前是使用ORACLE , SQL SERVER 这一类的数据库那说怪怪的就有点,你是不是怪怪的. 所以我们来说说POSTGRESQL 的账号和用户的问题.

我们通过这个图,我们先从概念上理解一下POSTGRESQL 的账号到底是什么.首先POSTGRESQL 中用户和角色可以认为是一个概念,之间的不同就是,角色没有密码,不能登录,所以下面的图大致的意思

如果一个POSTGRESQL Role 给了登录的权限并且赋予密码,那这个角色我们就可以认为他是一个用户, 而反过来,如果我们不给这个账号赋予登录的权限,而是一个权限的集合,并且我们用这个"用户" 来给更多的用户赋予权限,那这个"用户" 就是ROLE.

说完上面的这个概念,下面就的说说schema 的这个事情了,如果你用过SQL SERVER  或许你能很快的理解POSTGRESQL 的schema 到底是什么意思,而如果你是MYSQL  或者 ORACLE 的DB ,那可能会稍微的说一下.

1 ORACLE 数据库本身是没有 database 这个概念的, 他是通过SCHEMA 来将一堆的表进行划分的,这对表属于这个SCHEMA 而另一堆表属于另一个SCHEMA

2 MYSQL 没有SCHEMA 的概念,但有一个INSTANCE 下挂载多个数据库的概念. 

将ORACLE 的SCHEMA + MYSQL DATABASE  =  SQL SERVER  AND  POSTGRESQL  的用户与SCHEMA 和DATABASE 之间的关系.

上面的图就完美的解释了DATABASE  SCHEMA  USER 之间的关系, 你中有我我中有你.

说完这个问题,其实还有一个问题,让初学者很苦恼,就是建立一个用户可以登陆的ROLE 后,新建一个数据库,上去就可以做任何操作包括建表,那我们就来看看到底这是怎么回事, 一般上来的使用过其他数据库的管理员,可能不大能理解这个问题.

我们要强调的是 ,新建的用户一定会对于各种数据库的PUBLIC,具有权限,如果用数据库的角度可能SQL SERVER 的管理员 也不会能想的通,但是如果你用WINDOWS 的文件夹的权限的 PUBLIC 的想法去想就能理解了.

那这里我们建立一个用户首先要做的就是,

去掉数据库的PUBLIC 下的所有权限,到你所属的数据库下执行下面的语句,这样新建的用户对于数据库就不在有public schema下的所有权限了

revoke all on schema public from public;

这样新建立的用户就不会在登陆一个数据库上可以随意在PUBLIC SCHEMA 上建立任何的OBJECT

解决这个问题后,ORACLE 的DB 人员在schema 和用户之间的关系方面,这对POSTGRESQL 是有不理解的情况.

首先public 的schema 我们可以删除吗,可以

我们可以看到public是可以删除的,同时在创建表会失败,那我们创建一个schema 看看会怎么样

那这怎么办呢,我们可以将POSTGRESQL的SCHEMA 的探测顺序改变一下

show search_path;

我们通过 search_path 调整对

在创建表就不会报错,而是根据相关的顺序,将own 作为当前数据库的个SCHEMA 进行默认.

这样的做法可以改变在用户默认的状态下,创建的东西都默认到PUBLIC中的一些安全方面的问题.

其实这个问题和SQL SERVER 中默认的SCHEMA 是DBO是一样的意思,每个数据库主要创建表均在DBO SCHEMA 的模式下,进行权限的统一管理,这样也没有什么不好,但换到ORACLE的 DB 则不大会理解.

所以POSTGRESQL 在权限的管理和SCHEMA 的模式下的权限授予,ORACLE 和 MYSQL 的DB 可能会觉得迷惑.