postgresql數據庫基礎

創建只讀賬號

1.1以初始化賬號登入

[root@localhost ~]# psql -U postgres

1.2創建用戶

postgres=# create role develop with login password '123456';  

CREATE ROLE

postgres=# select usename from pg_user;

 usename  

----------

 postgres

 test

 develop

(3 rows)

1.3切換數據庫

\c current_product

1.4賦予只讀權限

current_product=# grant select on all tables in schema public to develop;

GRANT

1.5切換到develop用戶

current_product=# \c - develop

You are now connected to database "current_product" as user "develop".

1.6檢測是否擁有只讀權限

current_product=> select * from test;    

 id 

----

(0 rows)

2創建讀寫賬號

2.1初始賬號登錄

psql -U postgres   

2.2查看用戶

postgres=# select usename from pg_user;

 usename  

----------

 postgres

 test

 test1

 u2

(4 rows)

2.3創建讀寫用戶

postgres=# create role test2 with login password '123456';

CREATE ROLE

postgres=# grant ALL  on all tables in schema public to test2;  #這種授權方式是不對的，test2用戶對current_product數據庫沒有權限

GRANT

2.4檢測用戶是否有讀寫權限

postgres=# \c - test2

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

切換數據庫

postgres=> \c current_product  

You are now connected to database "current_product" as user "test2".

current_product=> \dt

        List of relations

 Schema | Name | Type  |  Owner   

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

 public | aaa  | table | postgres

 public | test | table | postgres

(2 rows)

current_product=> select * from aaa;         #顯示沒有權限

ERROR:  permission denied for relation aaa

2.5 正確的授權方式是 ：切換到目標數據庫，執行授權語句

postgres=# \c current_product     #切換到目標數據庫

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

current_product=# grant ALL  on all tables in schema public to test2;   #執行授權語句

GRANT

2.6 切換到讀寫用戶，檢測是否有權限

current_product=# \c - test2       ###切換至讀寫用戶

You are now connected to database "current_product" as user "test2".

current_product=> \dt     ###查看幾個表

        List of relations

 Schema | Name | Type  |  Owner   

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

 public | aaa  | table | postgres

 public | test | table | postgres

(2 rows)

current_product=> select * from aaa;    #查權限正常

 id 

----

(0 rows)

current_product=> insert into aaa values(1);  #增權限正常

INSERT 0 1

current_product=> select * from aaa;        

 id 

----

  1

(1 row)

current_product=> delete from aaa;   #刪除權限正常

DELETE 1

2.7 切換至超級用戶

current_product=> \c - postgres

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

current_product=# create table bbb(id int);     ###新增一張表

CREATE TABLE

2.8 切換至讀寫用戶

current_product=# \c - test2

You are now connected to database "current_product" as user "test2".

current_product=> \dt

        List of relations

 Schema | Name | Type  |  Owner   

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

 public | aaa  | table | postgres

 public | bbb  | table | postgres

 public | test | table | postgres

(3 rows)

current_product=> select * from bbb;     #顯示無權限

ERROR:  permission denied for relation bbb

2.9 解決辦法：

每次新增表都執行一次授權語句，否則無權限（其它方法正在探索中……）

current_product=> \c - postgres

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

current_product=# grant ALL  on all tables in schema public to test2;

GRANT

切換至讀寫用戶 ， 檢測權限

current_product=# \c - test2

You are now connected to database "current_product" as user "test2".

current_product=> select * from bbb;

 id 

----

(0 rows) 

current_product=> insert into bbb  values(2222);

INSERT 0 1

current_product=> select * from bbb;

  id  

------

 2222

(1 row)

current_product=> delete from bbb;

DELETE 1

current_product=> select * from bbb;

 id 

----

(0 rows)