PostgreSQL 関数のデフォルト権限に気をつけよう

# きっかけ
作成した**FUNCTIONがPUBLICロールへのEXECUTE権限が付与されている**ことに気がつきました。PUBLICロールにEXECUTE権限が付与されていることは、だれでも実行出来る状態なので危険です。</mark>
GRANT文で権限付与していないのに何故？？ :thinking:
調査したところ、PostgreSQLには「デフォルト権限」というものがあることを知りました。

# マニュアル確認
   [マニュアル CREATE FUNCTION](https://www.postgresql.jp/docs/15/sql-createfunction.html)
 > この他に注意すべき点として、新しく作成された関数ではデフォルトで実行権限がPUBLICに付与されていることがあります。  SECURITY DEFINER関数の使用を一部のユーザのみに制限したいことはよくあります。 このためには、デフォルトのPUBLIC権限を取り消し、そして、実行権限の付与を選択して行ってください。 新しい関数がすべてのユーザに実行可能となる隙間が存在することを防ぐためには、単一トランザクション内で作成と権限設定を行ってください。


[マニュアル 権限](https://www.postgresql.jp/docs/15/ddl-priv.html)
> PostgreSQLはあるタイプのオブジェクトが作成された時に、そのオブジェクトに対する権限をデフォルトでPUBLICに付与します。 テーブル、テーブルの列、シーケンス、外部データラッパー、外部サーバ、ラージオブジェクト、スキーマ、テーブル空間、構成パラメータに対しては、デフォルトではPUBLICに権限を付与しません。 他のタイプのオブジェクトに対しては、PUBLICにデフォルトで付与される権限は次のものです。 CONNECT、TEMPORARY （データベース内で一時テーブルを作成する権限）、関数とプロシージャに対するEXECUTE権限、言語とデータ型（ドメインを含む）に対するUSAGE権限。 もちろんオブジェクトの所有者は、デフォルト、あるいは明示的に与えられた権限をREVOKEできます。 （セキュリティを最大限に高めるためには、REVOKEをオブジェクトを作成したのと同じトランザクション内で発行してください。そうすれば他のユーザがそのオブジェクトを使う隙が存在しません。） また、デフォルトの権限設定はALTER DEFAULT PRIVILEGESを使って上書きできます。

マニュアルによると確かに関数とプロシージャはデフォルトでPUBLICにEXECUTE権限が付与されるようです。

# 検証
## 検証環境
検証のDBはPostgreSQL15です。

### 検証ユーザー
| ユーザー | 補足 |
| --- | --- |
| sp_user1 | テーブル、関数のオーナー |
| sp_user2 | 関数実行ユーザー |

### 検証オブジェクト
| タイプ | オーナー | 名称 | 補足 |
| --- | --- | --- | --- |
| TABLE | sp_user1 | sp_table1 | 関数で件数を取得されるテーブル |
| FUNCTION | sp_user1 | sp_func1 | 権限検証する関数。ただ1を返す関数 |
| FUNCTION | sp_user1 | sp_func2 | 権限検証する関数。sp_table1テーブルの件数を返す関数 |
```sql:sp_func1
CREATE OR REPLACE FUNCTION sp_work.sp_func1()
RETURNS INTEGER AS '
DECLARE 
retval INTEGER;
  BEGIN
    SELECT 1 into retval;
    RETURN retval;
  END;
  '
LANGUAGE 'plpgsql';
```
```sql:sp_func2
CREATE OR REPLACE FUNCTION sp_work.sp_func2()
RETURNS INTEGER AS '
DECLARE 
retval INTEGER;
  BEGIN
    SELECT count(*) into retval from sp_work.sp_table1;
    RETURN retval;
  END;
  '
LANGUAGE 'plpgsql';
```

## 検証1
本当にPUBLICにEXECUTE権限が付与されているか。
もしPUBLICにEXECUTE権限が付与されているようであればsp_func1はsp_user2で実行が出来るはずです。

### デフォルト権限確認
```sql
postgres=> select * from pg_default_acl;
 oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl
-----+------------+-----------------+---------------+-----------
(0 rows)
```
デフォルト権限の設定はありません。

### FUNCTION権限確認
```sql
postgres=> select proname, proacl from pg_proc where proname = 'sp_func1';
 proname  | proacl
----------+--------
 sp_func1 |
(1 row)
```
proaclの値が空なのでPUBLICにEXECUTE権限が付与されているか気づきませんでした。 :thinking:

### sp_user2でsp_func1を実行
```sql
postgres=> select current_user; -- ログインユーザー確認
 current_user
--------------
 sp_user2
(1 row)

postgres=> select sp_func1();  -- sp_func1を実行
 sp_func1
----------
        1
(1 row)
```
実行出来ました！！。
やはりPUBLICにEXECUTE権限が付与されているようです。

### sp_func1からPUBLICのEXECUTE権限取り消してsp_user2で実行
sp_func1からPUBLICのEXECUTE権限取り消しました。
```sql
-- 権限取り消し
postgres=> REVOKE EXECUTE ON FUNCTION sp_work.sp_func1 FROM PUBLIC;
REVOKE

-- 権限確認
postgres=> select proname, proacl from pg_proc where proname = 'sp_func1';
 proname  |        proacl
----------+-----------------------
 sp_func1 | {sp_user1=X/sp_user1}
(1 row)

```
proaclaの値が「{sp_user1=X/sp_user1}」になりました。

再度、sp_user2で実行しました。
```sql
postgres=> select current_user; -- ログインユーザー確認
 current_user
--------------
 sp_user2
(1 row)

postgres=> select sp_func1();  -- sp_func1を実行
ERROR:  permission denied for function sp_func1
```
今度はpermission deniedエラーになりました。

この検証で、FUNCTIONを作成するとデフォルトでPUBLICにEXECUTE権限が付与されていることがわかりました。

# PUBLICにEXECUTE権限があるセキュリティの問題

## 関数、プロシージャの実行時の権限
関数、プロシージャの実行時の権限はINVOKER、DEFINERの2種類あります。
- SECURITY INVOKER（デフォルト）：  実行者の権限で実行。関数内で参照するテーブルは関数を実行するユーザーの権限で参照します。
- SECURITY DEFINER： オーナー権限で実行。関数内で参照するテーブルは関数のオーナーの権限で参照します。

## INVOKERの場合は、参照オブジェクトに権限がなければエラーになってくれる
sp_func2ではsp_table1テーブル（オーナー：sp_user1）を参照しています。
sp_user2にsp_table1のSELECT権限はありません。
なので、関数にPUBLICのEXECUTE権限があってもsp_table1参照でpermission deniedエラーになります。
```sql
postgres=> select current_user;  -- ログインユーザー確認
 current_user
--------------
 sp_user2
(1 row)

postgres=> select sp_func2();  -- sp_func2を実行
ERROR:  permission denied for table sp_table1
CONTEXT:  SQL statement "SELECT count(*)             from sp_work.sp_table1"
PL/pgSQL function sp_func2() line 5 at SQL statement
```
実行は出来ますが、sp_table1テーブルの参照でエラーになりました。

## <span style="color:#d70910;">DEFINERの場合は、セキュリティ的に危険！！</span>
DEFINERの場合、sp_func2関数はオーナーのsp_user1の権限で実行されます。
sp_user1はsp_table1のオーナーでもあります。
なので、<mark><span style="color:#d70910;">PUBLICにEXECUTE権限があると、実行ユーザーに関数内で参照しているテーブルのSELECT権限がなくても実行できます。つまりsp_func2がだれでも実行出来る状態であり、セキュリティ的に危険です。</span></mark>

### 検証の為に実行権限をSECURITY DEFINERに変更
```sql
postgres=> ALTER FUNCTION sp_work.sp_func2() SECURITY DEFINER;
ALTER FUNCTION
```
### sp_user2で実行
```sql  -- ログインユーザー確認
postgres=> select current_user;  -- ログインユーザー確認
 current_user
--------------
 sp_user2
(1 row)

postgres=> select sp_func2();  -- sp_func2を実行
 sp_func2
----------
        2
(1 row)
```
sp_user2でsp_func2を正常に実行することが出来ました。。。。

## <span style="color:#1453c6;">対策</span>
### PUBLICのEXECUTE権限を取り消す
関数、プロシージャ作成後にPUBLICのEXECUTE権限を取り消します。そして、実行してもよいユーザーのみにEXECUTE権限を付与します。
```sql
-- 取り消し
REVOKE EXECUTE ON FUNCTION sp_work.sp_func2 FROM PUBLIC; 

-- 実行してもよいユーザーに権限付与
GRANT EXECUTE ON FUNCTION sp_work.sp_func2 TO xxxxx; 

```

### デフォルト権限を変更する
ALTER DEFAULT PRIVILEGESでデフォルト権限を変更します。
ただし、作成される全て関数に影響があります。場合によっては拡張機能で作成される関数やプロシージャなど他のユーザーで作成される関数に影響があるかもしれない為、注意が必要です。

残念ながら**特定のスキーマ**で作成される関数のみPUBLICのEXECUTE権限をデフォルト付与しないようには出来ませんでした。
```sql
postgres=> ALTER DEFAULT PRIVILEGES 
IN SCHEMA sp_work -- スキーマ指定
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES

postgres=> select * from pg_default_acl;
 oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl
-----+------------+-----------------+---------------+-----------
(0 rows)
```
pg_default_aclに反映されていないため、無効だとわかります。

しかし**特定のロール**で作成される関数のみPUBLICのEXECUTE権限をデフォルト付与しないようには出来ました。
```sql
postgres=> ALTER DEFAULT PRIVILEGES
FOR ROLE sp_user1 -- ロール指定
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES

postgres=> select * from pg_default_acl;
  oid   | defaclrole | defaclnamespace | defaclobjtype |       defaclacl
--------+------------+-----------------+---------------+-----------------------
 368690 |     368661 |               0 | f             | {sp_user1=X/sp_user1}
```
これで、sp_user1ロールで作成する関数の場合、PUBLICのEXECUTE権限はデフォルトで付与されなくなります。