PostgreSQL許可權管理之建立可更新表的普通使用者

標籤：

一、環境

$ psql --versionpsql (PostgreSQL) 9.4.4

我們都知道，超級使用者的許可權太大了，為了資料庫的安全，對於非管理員帳號，需要建立普通使用者。

二、文法

$ psql psql (9.4.4)Type "help" for help.postgres=# \h create role Command:     CREATE ROLEDescription: define a new database roleSyntax:CREATE ROLE name [ [ WITH ] option [ ... ] ]where option can be:      SUPERUSER | NOSUPERUSER    | CREATEDB | NOCREATEDB    | CREATEROLE | NOCREATEROLE    | CREATEUSER | NOCREATEUSER    | INHERIT | NOINHERIT    | LOGIN | NOLOGIN    | REPLICATION | NOREPLICATION    | CONNECTION LIMIT connlimit    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD ‘password‘    | VALID UNTIL ‘timestamp‘    | IN ROLE role_name [, ...]    | IN GROUP role_name [, ...]    | ROLE role_name [, ...]    | ADMIN role_name [, ...]    | USER role_name [, ...]    | SYSID uid

三、建立唯讀使用者 1. 先建立表t1

postgres=# create table t1 ( id serial, name varchar(64) );CREATE TABLEpostgres=# \dt        List of relations Schema | Name | Type  |  Owner   --------+------+-------+---------- public | t1   | table | postgres(1 row)

2. 建立使用者u1

postgres=# create role u1 with login password ‘123456‘;CREATE ROLE

login是賦予登入許可權，否則是不能登入的

3. 賦予u1對錶的唯讀許可權

因為建立的普通使用者預設是沒有任何許可權的

postgres=# grant select on all tables in schema public to u1;GRANTpostgres=# \c - u1You are now connected to database "postgres" as user "u1".postgres=> select * from t1; id | name ----+------(0 rows)

4. 建立表t2

postgres=> \c - postgresYou are now connected to database "postgres" as user "postgres".postgres=# create table t2 ( id serial, name varchar(64) );CREATE TABLEpostgres=# \dt        List of relations Schema | Name | Type  |  Owner   --------+------+-------+---------- public | t1   | table | postgres public | t2   | table | postgres(2 rows)

5. 驗證u1的許可權

postgres=# \c - u1You are now connected to database "postgres" as user "u1".postgres=> select * from t1; id | name ----+------(0 rows)postgres=> select * from t2;ERROR:  permission denied for relation t2

可見u1是有t1表的讀許可權，但沒有t2表的讀許可權，這樣是不是意味著每次建立表就要賦一次許可權？

6. 解決辦法

postgres=> \c - postgresYou are now connected to database "postgres" as user "postgres".postgres=# alter default privileges in schema public grant select on tables to u1;ALTER DEFAULT PRIVILEGESpostgres=# create table t3 ( id serial, name varchar(64) );CREATE TABLEpostgres=# \dt        List of relations Schema | Name | Type  |  Owner   --------+------+-------+---------- public | t1   | table | postgres public | t2   | table | postgres public | t3   | table | postgres(3 rows)postgres=# \c - u1You are now connected to database "postgres" as user "u1".postgres=> select * from t3; id | name ----+------(0 rows)

賦予schema中所有表的預設許可權給u1,這樣以後建立表就不用再賦許可權了。

四、建立可更新使用者 1. 建立u2使用者

postgres=# create role u2 with login password ‘123456‘;CREATE ROLE

2. 賦予更新許可權

postgres=# alter default privileges in schema public grant select,insert,update,delete on tables to u2;ALTER DEFAULT PRIVILEGES

3. 建立表t4

postgres=# create table t4 ( id serial, name varchar(64) );CREATE TABLEpostgres=# \dt        List of relations Schema | Name | Type  |  Owner   --------+------+-------+---------- public | t1   | table | postgres public | t2   | table | postgres public | t3   | table | postgres public | t4   | table | postgres(4 rows)

4. 查看許可權

postgres=# \c - u2You are now connected to database "postgres" as user "u2".postgres=> insert into t4 values ( 1, ‘aa‘ );INSERT 0 1postgres=> select * from t4; id | name ----+------  1 | aa(1 row)postgres=> update t4 set name = ‘bb‘ where id = 1;UPDATE 1postgres=> select * from t4; id | name ----+------  1 | bb(1 row)postgres=> delete from t4 where id = 1;DELETE 1postgres=> select * from t4; id | name ----+------(0 rows)

可以正常增刪改查

5. 序列的許可權與解決辦法

在insert的時候，指定列插入，主鍵id是serial類型會預設走sequence的下一個值，但前面只賦予了表的許可權，所以會出現下面的問題：

postgres=> insert into t4 ( name ) values ( ‘aa‘ );ERROR:  permission denied for sequence t4_id_seq

解決方案就是再賦一次sequence的值就行了

postgres=> \c - postgresYou are now connected to database "postgres" as user "postgres".postgres=# alter default privileges in schema public grant usage on sequences to u2;ALTER DEFAULT PRIVILEGESpostgres=# create table t5 ( id serial, name varchar(64) );CREATE TABLEpostgres=# \c - u2You are now connected to database "postgres" as user "u2".postgres=> insert into t5 ( name ) values ( ‘cc‘ );INSERT 0 1postgres=> select * from t5; id | name ----+------  1 | cc(1 row)

五、刪除使用者

postgres=> \c - postgresYou are now connected to database "postgres" as user "postgres".postgres=# drop role u2;ERROR:  role "u2" cannot be dropped because some objects depend on itDETAIL:  privileges for table t5privileges for sequence t5_id_seqprivileges for default privileges on new sequences belonging to role postgres in schema publicprivileges for table t4privileges for default privileges on new relations belonging to role postgres in schema public

當我們刪除使用者的時候，會提示有許可權依賴，所以我們要刪除這些許可權

postgres=# alter default privileges in schema public revoke usage on sequences from u2;ALTER DEFAULT PRIVILEGESpostgres=# alter default privileges in schema public revoke select,insert,delete,update on tables from u2;ALTER DEFAULT PRIVILEGESpostgres=# revoke select,insert,delete,update on all tables in schema public from u2;REVOKEpostgres=# revoke usage on all sequences in schema public from u2;REVOKEpostgres=# drop role u2;DROP ROLE

這樣太麻煩了，有沒有更快的辦法？

postgres=# drop role u1;ERROR:  role "u1" cannot be dropped because some objects depend on itDETAIL:  privileges for table t5privileges for table t4privileges for table t3privileges for default privileges on new relations belonging to role postgres in schema publicprivileges for table t1postgres=# drop owned by u1;DROP OWNEDpostgres=# drop role u1;DROP ROLE

PostgreSQL許可權管理之建立可更新表的普通使用者