首先自己整理了个权限表,如下:
lihao=# \dp+
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
----------+-----------------+--------+---------------------+--------------------------
public | t1 | table | lihao=arwdDxt/lihao |
public | t2 | table | lihao=arwdDxt/lihao |
在这个权限表,我觉得有两个比较特殊:LOGIN和SUPERUSER
1.LOGIN权限
很显然,这就是一个登陆权限,但它却是NO.1权限,如果没有这个权限,那么哪怕是超级用户也不能登陆数据库,如下实验:
lihao=# create role user1 nologin superuser password '123456';
CREATE ROLE
lihao=# \du+
List of roles
Role name | Attributes | Member of | Description
---------+-----------------------------------------+--------+-------------
lihao | Superuser, Create role, Create DB, Replication | {} |
user1 | Superuser, Cannot login, Replication | {} |
lihao=# \c lihao user1;
FATAL: role "user1" is not permitted to log in
Previous connection kept
lihao=# alter role user1 login;
ALTER ROLE
lihao=# \c lihao user1;
You are now connected to database "lihao" as user "user1".
lihao=#
与login权限对应的还有一个登陆权限,那就是数据库的CONNECT权限。在之前的博文《postgresql角色(一)》中提到过,默认创建的角色都会拥有public角色在所有数据库的connect权限,那么接下来做个实验看看:
lihao=# create user user1 nologin password 123456';
CREATE ROLE
lihao=# create user user2 password '123456';
CREATE ROLE
lihao=# revoke connect on database db1 from public;
REVOKE
lihao=# \c db1 user1
FATAL: role "user1" is not permitted to log in
Previous connection kept
lihao=# \c db1 user2
FATAL: permission denied for database "db1"
DETAIL: User does not have CONNECT privilege.
Previous connection kept
lihao=#
结合这个实验以及之前的实验,我们可以看出,对一个用户而言,只有同时拥有LOGIN和CONNECT权限才可以对数据库进行访问。
2.SUPERUSER权限
表格的“系统权限”所列的几个权限,除了LOGIN之外,对SUPERUSER而言,其他均可无视。
lihao=# create role role1 superuser nocreatedb nocreaterole login password 'oracle';
CREATE ROLE
lihao=# \c lihao role1
You are now connected to database "lihao" as user "role1".
lihao=# create database db1 owner "lihao";
CREATE DATABASE
lihao=# create role role2 ;
CREATE ROLE