在PostgreSQL中，創(chuàng)建用戶和授權(quán)是非常常見的管理任務(wù)。

需求

（1）給用戶a創(chuàng)建一個數(shù)據(jù)庫，并且給a用戶對這個庫有所有權(quán)限

（2）給read_a用戶對這個數(shù)據(jù)庫有只讀權(quán)限

步驟

1.創(chuàng)建用戶a

2.創(chuàng)建數(shù)據(jù)庫db_a， 并設(shè)置owner為a

3.回收默認(rèn)的public schema create權(quán)限

4.設(shè)置db_a的public schema 默認(rèn)的owner 為a

5.創(chuàng)建只讀用戶read_a

6.用a用戶給read_a用戶設(shè)置默認(rèn)的權(quán)限

7.給read_a用戶設(shè)置對public schema 查詢權(quán)限

具體操作如下：

db_test=# create user a with password '1234';        # 1. 創(chuàng)建用戶a

CREATE ROLE

db_test=# create database db_a with owner a;         # 2. 創(chuàng)建數(shù)據(jù)庫db_a， owner為a

CREATE DATABASE    

db_test=# \c db_a;

You are now connected to database "db_a" as user "postgres".

db_a=# revoke create on schema public from public;    # 3. 回收默認(rèn)public create權(quán)限， 這樣就不是每個人都可以在這里創(chuàng)建表了

REVOKE

db_a=# \dn

  List of schemas

  Name  |  Owner   

--------+----------

 public | postgres

(1 row)

db_a=# alter schema  public owner to a;              # 4. 設(shè)置db_a 的public schema的owner 為a

ALTER SCHEMA

db_a=# \dn

List of schemas

  Name  | Owner 

--------+-------

 public | a

(1 row)

db_a=# create user read_a with password '1234';       # 5. 創(chuàng)建只讀用戶read_a

CREATE ROLE

db_a=# \c - a                                         # 切換到用戶a, db_a數(shù)據(jù)庫

You are now connected to database "db_a" as user "a".

db_a=> alter default privileges in schema public grant select on tables to read_a;     # 6. 修改默認(rèn)權(quán)限   

ALTER DEFAULT PRIVILEGES

db_a=> GRANT USAGE ON SCHEMA public to read_a;        # 6.授權(quán)read_a 對public schema權(quán)限

GRANT

db_a=> GRANT SELECT ON ALL TABLES IN SCHEMA  public to read_a;    # 授權(quán)read_a 對public schema權(quán)限

GRANT

db_a=> \ddp

         Default access privileges

 Owner | Schema | Type  | Access privileges 

-------+--------+-------+-------------------

 a     | public | table | read_a=r/a

(1 row)

postgres=> \c db_a                # 用a用戶創(chuàng)建一個表t2，插入語句，用read_a查詢測試一下

You are now connected to database "db_a" as user "a".

db_a=> \dt

       List of relations

 Schema | Name | Type  | Owner 

--------+------+-------+-------

 public | t1   | table | a

(1 row)

db_a=> create table t2(id int);

CREATE TABLE

db_a=> insert into t2(id) values(1);

INSERT 0 1

db_a=> \c - read_a;            # 切換到read_a用戶，測試查詢t2表

You are now connected to database "db_a" as user "read_a".

db_a=> 

db_a=> \dt

       List of relations

 Schema | Name | Type  | Owner 

--------+------+-------+-------

 public | t1   | table | a

 public | t2   | table | a

(2 rows)

db_a=> select * from t2;

 id 

----

  1

(1 row)