---
# System prepended metadata

title: PostgreSQL 預設權限設定
tags: [FAQ]

---

---
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
--
```
