---
title: PostgreSQL 預設權限設定
tags: FAQ
---
# PostgreSQL 設定預設權限
預先規劃好資料庫權限,有助於安全原則的一致性。
本文提供一個範例,比較容易瞭解 PostgreSQL 的資料庫權限設定。
詳細指令說明請參閱使用手冊。
- [ALTER DEFAULT PRIVILEGES](https://docs.postgresql.tw/reference/sql-commands/alter-default-privileges)
- [到社群討論吧!](https://www.facebook.com/groups/pgsql.tw/?multi_permalinks=2360908527547744)
## 目錄
[toc]
## 情境
- 新資料庫 `mydb`,僅能讓 `pguser1`, `pguser2` 和 `pgreader` 登入。
- `pguser1`, `pguser2` 和 `pgreader` 都只是資料使用者,無權變更結構。
- pguser1 可以讀取/變更 schema1 所屬的 TABLE 內容。
- 可能代表某業務A的資料。
- pguser2 可以讀取/變更 schema2 所屬的 TABLE 內容。
- 可能代表某業務B的資料。
- pgreader 可以讀取所有的 TABLE 內容。
- 可以讀取所有業務內容,進行綜合分析的帳號。
## 範例
- **以下指令均使用 postgres 操作**
### 建立使用者
```sql=
CREATE USER pguser1;
CREATE USER pguser2;
CREATE USER pgreader;
```
### 建立資料庫並設定權限
1. 移除資料庫每個使用者都能登入的權限
2. 指定可以登入的使用者
```sql=
CREATE DATABASE mydb;
REVOKE CONNECT ON DATABASE mydb FROM public;
GRANT CONNECT ON DATABASE mydb TO pguser1, pguser2, pgreader;
```
### 設定預設權限
- 預設移除 public 權限,並加入 pgreader 的讀取權限
- **登入資料庫 mydb**
```sql=
REVOKE USAGE ON SCHEMA public FROM public;
ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM public;
ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM public;
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO pgreader;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO pgreader;
```
### 建立 Schema 及並設定預設權限
```sql=
CREATE SCHEMA schema1;
GRANT USAGE ON SCHEMA schema1 TO pguser1;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema1 GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pguser1;
CREATE SCHEMA schema2;
GRANT USAGE ON SCHEMA schema2 TO pguser2;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema2 GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pguser2;
```
### 建立 TABLE
```sql=
CREATE TABLE schema1.mytable (id INT, value TEXT);
CREATE TABLE schema2.mytable (id INT, value TEXT);
```
## 測試
### 角色 pguser1
1. 切換角色為 pguser1
2. 可以 INSERT schema1.mytable
3. 不可以 INSERT schema2.mytable
4. SELECT OK
```psql=
mydb=# SET ROLE pguser1;
SET
mydb=> INSERT INTO schema1.mytable (id, value) VALUES (1, 'abc');
INSERT 0 1
mydb=> INSERT INTO schema2.mytable (id, value) VALUES (1, 'abc');
ERROR: permission denied for schema schema2
LINE 1: INSERT INTO schema2.mytable (id, value) VALUES (1, 'abc');
^
mydb=> SELECT * FROM schema1.mytable;
id | value
----+-------
1 | abc
(1 row)
```
### 角色 pguser2
1. 切換角色為 pguser2
2. 可以 INSERT schema2.mytable
3. 不可以 INSERT schema1.mytable
4. SELECT OK
```psql=
mydb=# SET ROLE pguser2;
SET
mydb=> INSERT INTO schema2.mytable (id, value) VALUES (2, 'def');
INSERT 0 1
mydb=> INSERT INTO schema1.mytable (id, value) VALUES (2, 'def');
ERROR: permission denied for schema schema2
LINE 1: INSERT INTO schema1.mytable (id, value) VALUES (2, 'def');
^
mydb=> SELECT * FROM schema2.mytable;
id | value
----+-------
2 | def
(1 row)
```
### 角色 pgreader
1. 切換角色為 pgreader
2. 可以 SELECT 兩個 Schema 的 TABLE
3. 但無權更新 TABLE
```psql=
mydb=# set role pgreader;
SET
mydb=> SELECT * FROM schema1.mytable UNION SELECT * FROM schema2.mytable;
id | value
----+-------
2 | def
1 | abc
(2 rows)
mydb=> UPDATE schema1.mytable SET value='def' WHERE id=1;
ERROR: permission denied for table mytable
```
## pg_dump
把資料庫結構 dump 出來,看備份時權限會如何還原:
```bash
$ pg_dump --schema-only mydb
```
結果:(去除前面資料庫參數的部份)
```sql=
--
-- Name: schema1; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA schema1;
ALTER SCHEMA schema1 OWNER TO postgres;
--
-- Name: schema2; Type: SCHEMA; Schema: -; Owner: postgres
--
CREATE SCHEMA schema2;
ALTER SCHEMA schema2 OWNER TO postgres;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: mytable; Type: TABLE; Schema: schema1; Owner: postgres
--
CREATE TABLE schema1.mytable (
id integer,
value text
);
ALTER TABLE schema1.mytable OWNER TO postgres;
--
-- Name: mytable; Type: TABLE; Schema: schema2; Owner: postgres
--
CREATE TABLE schema2.mytable (
id integer,
value text
);
ALTER TABLE schema2.mytable OWNER TO postgres;
--
-- Name: SCHEMA public; Type: ACL; Schema: -; Owner: postgres
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA public TO PUBLIC;
--
-- Name: SCHEMA schema1; Type: ACL; Schema: -; Owner: postgres
--
GRANT USAGE ON SCHEMA schema1 TO pgreader;
GRANT USAGE ON SCHEMA schema1 TO pguser1;
--
-- Name: SCHEMA schema2; Type: ACL; Schema: -; Owner: postgres
--
GRANT USAGE ON SCHEMA schema2 TO pgreader;
GRANT USAGE ON SCHEMA schema2 TO pguser2;
--
-- Name: TABLE mytable; Type: ACL; Schema: schema1; Owner: postgres
--
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE schema1.mytable TO pguser1;
GRANT SELECT ON TABLE schema1.mytable TO pgreader;
--
-- Name: TABLE mytable; Type: ACL; Schema: schema2; Owner: postgres
--
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE schema2.mytable TO pguser2;
GRANT SELECT ON TABLE schema2.mytable TO pgreader;
--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: schema1; Owner: postgres
--
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA schema1 REVOKE ALL ON TABLES FROM postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA schema1 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO pguser1;
--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: schema2; Owner: postgres
--
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA schema2 REVOKE ALL ON TABLES FROM postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA schema2 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO pguser2;
--
-- Name: DEFAULT PRIVILEGES FOR SCHEMAS; Type: DEFAULT ACL; Schema: -; Owner: postgres
--
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT USAGE ON SCHEMAS TO pgreader;
--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: -; Owner: postgres
--
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON TABLES TO pgreader;
--
-- PostgreSQL database dump complete
--
```