Postgresql 數(shù)據(jù)庫(kù)權(quán)限功能的使用總結(jié)

CREATE ROLE guest LOGIN
 NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE ROLE "group"
 NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

GRANT "group" TO guest;

GRANT INSERT ON TABLE class TO "group";

Server [localhost]:
Database [postgres]:
Port [5433]:
Username [postgres]: guest
用戶(hù) guest 的口令：
psql (9.4.5)
輸入 "help" 來(lái)獲取幫助信息.
postgres=> \c TEST
您現(xiàn)在已經(jīng)連線到數(shù)據(jù)庫(kù) "TEST",用戶(hù) "guest".
TEST=> select * from class;
ERROR: permission denied for relation class
TEST=> insert into class values(2,'class1');
INSERT 0 1

TEST=> \c postgres postgres;
您現(xiàn)在已經(jīng)連線到數(shù)據(jù)庫(kù) "postgres",用戶(hù) "postgres".
postgres=# \c TEST;
您現(xiàn)在已經(jīng)連線到數(shù)據(jù)庫(kù) "TEST",用戶(hù) "postgres".
TEST=# grant select(num) on class to "group";
GRANT
TEST=# \echo 切換到postgres用戶(hù)連接TEST數(shù)據(jù)庫(kù)，對(duì)class表的num字段的select權(quán)限賦予group角色
切換到postgres用戶(hù)連接TEST數(shù)據(jù)庫(kù)，對(duì)class表的num字段的select權(quán)限賦予group角色
TEST=# \c TEST guest
用戶(hù) guest 的口令：
您現(xiàn)在已經(jīng)連線到數(shù)據(jù)庫(kù) "TEST",用戶(hù) "guest".
TEST=> \echo 切換回guest用戶(hù)登錄TEST數(shù)據(jù)庫(kù)
切換回guest用戶(hù)登錄TEST數(shù)據(jù)庫(kù)
TEST=> select * from class;
ERROR: permission denied for relation class
TEST=> select num from class;
 num
-----
  1
  2
(2 行記錄)

highgo=#\du
               List of roles
 Role name |          Attributes          | Member of
-----------+------------------------------------------------+-----------
 a     |                        | {}
 highgo  | Superuser, Create role, Create DB, Replication | {}

highgo=#select current_user;
 current_user
--------------
 highgo
(1row)

highgo=#\l
               List of databases
  Name  | Owner | Encoding | Collate |  Ctype  | Access privileges
-----------+--------+----------+------------+------------+-------------------
 highgo  | highgo | UTF8   | zh_CN.utf8 |zh_CN.utf8 |
 template0 | highgo | UTF8   | zh_CN.utf8 | zh_CN.utf8 | =c/highgo    +
      |    |     |      |      | highgo=CTc/highgo
 template1 | highgo | UTF8   | zh_CN.utf8 | zh_CN.utf8 | =c/highgo    +
      |    |     |      |      | highgo=CTc/highgo
(3rows)

highgo=#\c highgo a
Youare now connected to database "highgo" as user "a".
highgo=>select current_user;
 current_user
--------------
 a
(1row)
（5）使用超級(jí)用戶(hù)highgo連接數(shù)據(jù)庫(kù)正常
highgo=>\c highgo highgo
Youare now connected to database "highgo" as user "highgo".
highgo=#select current_user;
 current_user
--------------
 highgo
(1row)

highgo=#alter role a nologin;
ALTER ROLE
highgo=#\c highgo a
致命錯(cuò)誤: 不允許角色"a" 進(jìn)行登錄
Previousconnection kept
highgo=#

highgo=#alter role a superuser;
ALTERROLE
highgo=#\du
               List of roles
 Role name |          Attributes          | Member of
-----------+------------------------------------------------+-----------
 a    | Superuser, Cannot login            | {}
 highgo  | Superuser, Create role, Create DB, Replication | {}
 
highgo=#\c highgo a
致命錯(cuò)誤: 不允許角色"a" 進(jìn)行登錄
Previousconnection kept

highgo=#alter role a login;
ALTERROLE
highgo=#\c highgo a
Youare now connected to database "highgo" as user "a".
highgo=#select current_user;
 current_user
--------------
 a
(1row)