PostgreSQL オブジェクトへのアクセス権確認の必要性
アクセス権を確認する必要性がある具体的なケースは、セキュリティや運用管理に関わる重要な場面で頻繁に発生します。ここでは、下記項目について紹介します。
- アクセス権確認が必要なケース
- コマンドラインインタエース での確認方法
- システムカタログをクエリして権限を確認する方法
- 関数を使用して権限を確認する方法
新しいユーザーやロールの作成後
新しくユーザーやロールを作成した後、そのユーザーが正しく必要な権限を持っているか、確認する必要があります。
必要以上の管理権限やデータベース全体へのアクセス権を持っているいたとしたら、セキュリティ上好ましくありません。
データベースのセキュリティ監査
システム全体のセキュリティを維持するために、定期的にデータベースのセキュリティを監査する事は重要です。
例えば、内部の人間が機密データにアクセスできる場合、監査で発見し対応する必要があります。
権限変更後の確認
権限の付与や取り消しが行われた後、正しく反映されているかを確認します。
誤った権限を（例：参照権限だけのつもりが更新権限まで与えてしまった場合等）与えてしまった場合は、修正する必要があります。
不正アクセスの疑いがある場合
データベースに対して不正アクセスが疑われる場合、アクセス権を確認して、誰がどのデータにアクセスできるかを把握する必要があります。
退職者や異動者の権限管理
組織内でユーザーが退職したり部署が変わった場合、不要なアクセス権が残っていないか確認します。残っている場合は、セキュリティリスク上削除します。
アプリケーションの権限チェック
アプリケーションがデータベースと連携する場合、アプリケーションユーザーの権限が過剰でないか確認します。
アプリケーションに与えるデータベースユーザーは、最小限の権限（SELECTやINSERT）しか持たせないことが一般的です。過剰な権限（DROPやTRUNCATE）を持っていないか確認します。
共有データベース環境でのアクセス制御
複数のチームや部署が同じデータベースを共有する場合、各チームがアクセスできるデータを制限し、他のチームのデータにアクセスできないようにするために、権限の管理が必要です。
パフォーマンス問題の調査
パフォーマンスの低下が発生している場合、特定のユーザーが過剰にリソースを消費していないか確認するために、権限の確認が必要です。
誤って与えられた権限により、特定のユーザーが大量のデータに対して重いクエリを実行している場合それが原因かもしれません。
psql コマンドラインインタフェース での確認方法
psqlコマンドラインツールを使用してPostgreSQLの権限を確認するには、いくつかの方法があります。以下に具体的な手順やコマンドを紹介します。
\dp コマンドでの権限確認
psqlで権限を確認する最も簡単な方法の一つは、\dpコマンドを使用することです。
このコマンドは、データベース内のテーブルやビューに対するアクセス権を表示します。
\dp 基本構文
- \dp：データベース内のすべてのテーブルとビューの権限を表示します。
- \dp [テーブル名]：指定したテーブルの権限を表示します。
\dp [テーブル名]■使用例
下は、\dpコマンドの使用例です。「アクセス権限」欄で、=に続く「arwdDxt」が使用できる権限を示していて、/以降の部分「postgres」は誰によって権限を与えられたを示しています。
■権限
- a： INSERT（挿入）
- r： SELECT（参照）
- w： UPDATE（更新）
- d： DELETE（削除）
- D： TRUNCATE（全削除）
- x： REFERENCES（参照制約）
- t： TRIGGER（トリガー）
\z コマンドでの権限確認
\zコマンドは、すべてのオブジェクト（テーブル、ビュー、シーケンスなど）のアクセス権限を表示します。
\z 基本構文
\z■使用例
表示内容は、\dp コマンドど同じです。
システムカタログをクエリして権限を確認する
システムカタログや情報スキーマを直接クエリすることで、より詳細な権限情報を取得することができます。
information_schema.role_table_grantsビューを使用
このビューには、テーブルに対する権限が保存されており、特定のユーザーに付与された権限を確認することができます。
■使用例
下のクエリは、ユーザーaliceがどのテーブルに対してどの権限を持っているかを表示します。
pg_rolesテーブルをクエリする
このクエリは、ユーザーがスーパーユーザー権限、ロール作成権限、データベース作成権限を持っているかを確認できます。
■使用例
pg_tablesで特定のテーブルの所有者を確認する
指定したテーブルの所有者が誰かを確認きでます。
■使用例
関数を使用して権限を確認する
PostgreSQLには、権限を確認するための関数がいくつか用意されています。これにより、特定のユーザーが特定のオブジェクトに対して権限を持っているかどうかを確認できます。
has_table_privilege() 関数
特定のユーザーがテーブルに対して「指定した権限」を持っているか確認できます。
■使用例
下の例は、aliceがmytableテーブルに対してSELECT権限を持っているかどうかを確認します。結果はtrueまたはfalseです。
has_sequence_privilege()関数
特定のユーザーがシーケンスに対して特定の権限を持っているかどうかを確認できます。
■基本構文
user：ユーザー名/ロール名
sequence：対象となるシーケンス名
privilege：確認する権限の種類
’SELECT’: シーケンスの現在の値を取得できる（currval()、nextval()の使用が可能）
’USAGE’: シーケンスのnextval()やsetval()関数を使用できる
’UPDATE’: シーケンスの値を変更できる（nextval()、setval()の使用が可能）
’ALL’: すべての権限
■戻り値：
TRUE: 指定された権限がある
FALSE: 指定された権限がない
has_schema_privilege() 関数
指定したユーザーがスキーマに対して特定の権限を持っているかどうかを確認できます。
■基本構文
user：ユーザー名/ロール名
schema：対象となるスキーマ名
privilege：確認する権限の種類
’CREATE’: スキーマ内でオブジェクト（テーブル、ビューなど）を作成する権限
’USAGE’: スキーマ内のオブジェクトにアクセスできる権限
’ALL’: すべての権限（CREATEおよびUSAGE）
■戻り値：
TRUE: 指定された権限がある
FALSE: 指定された権限がない
has_database_privilege() 関数
指定したユーザーがデータベースに対して特定の権限を持っているかどうかを確認できます。
■基本構文
user：ユーザー名/ロール名
database：チェックしたいデータベース名
privilege：確認する権限の種類
’CONNECT’：データベースに接続できる権限
’CREATE’：データベース内で新しいスキーマを作成できる権限
’TEMP’：一時テーブルを作成できる権限
’ALL’: 上記すべての権限
■戻り値
TRUE: 指定された権限がある
FALSE: 指定された権限がない
まとめ
- アクセス権は、セキュリティや運用管理に関わる重要な場面で頻繁に確認する
- アクセス権は下記の方法で確認できる
- コマンドラインインタエース での確認方法
- システムカタログをクエリして権限を確認する方法
- 関数を使用して権限を確認する方法
