きっかけ

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

マニュアル確認

マニュアル CREATE FUNCTION

この他に注意すべき点として、新しく作成された関数ではデフォルトで実行権限がPUBLICに付与されていることがあります。 SECURITY DEFINER関数の使用を一部のユーザのみに制限したいことはよくあります。このためには、デフォルトのPUBLIC権限を取り消し、そして、実行権限の付与を選択して行ってください。新しい関数がすべてのユーザに実行可能となる隙間が存在することを防ぐためには、単一トランザクション内で作成と権限設定を行ってください。

マニュアル権限

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テーブルの件数を返す関数

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';

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で実行が出来るはずです。

デフォルト権限確認

postgres=> select * from pg_default_acl;
 oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl
-----+------------+-----------------+---------------+-----------
(0 rows)

デフォルト権限の設定はありません。

FUNCTION権限確認

postgres=> select proname, proacl from pg_proc where proname = 'sp_func1';
 proname  | proacl
----------+--------
 sp_func1 |
(1 row)

proaclの値が空なのでPUBLICにEXECUTE権限が付与されているか気づきませんでした。

sp_user2でsp_func1を実行

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権限取り消しました。

-- 権限取り消し
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で実行しました。

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エラーになります。

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テーブルの参照でエラーになりました。

DEFINERの場合は、セキュリティ的に危険！！

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

検証の為に実行権限をSECURITY DEFINERに変更

postgres=> ALTER FUNCTION sp_work.sp_func2() SECURITY DEFINER;
ALTER FUNCTION

sp_user2で実行

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を正常に実行することが出来ました。。。。

対策

PUBLICのEXECUTE権限を取り消す

関数、プロシージャ作成後にPUBLICのEXECUTE権限を取り消します。そして、実行してもよいユーザーのみにEXECUTE権限を付与します。

-- 取り消し
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権限をデフォルト付与しないようには出来ませんでした。

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権限をデフォルト付与しないようには出来ました。

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権限はデフォルトで付与されなくなります。