PostgreSQL の権限管理

daido1976

postgres の権限管理の仕様についてのだいぶ雑多なメモ

参考

daido1976

postgres では「ロール」のみがあり、「ユーザ」は「ロール」の同義語である（ログイン可能なロールのことを区別するためにユーザと呼ぶ場合もある）
- https://www.postgresql.jp/document/13/html/user-manag.html
- ロールの概念には、「ユーザ」という概念と「グループ」という概念が含まれます。 PostgreSQLバージョン8.1より前まででは、ユーザとグループは異なる種類の実体として扱われていました。しかし、現在ではロールしか存在しません。すべてのロールは、ユーザとして、グループとして、またはその両方として動作することができます。
public ロールと public スキーマがある
postgres では全てのユーザはデフォルトで public ロールに属する
- https://www.postgresql.jp/document/13/html/ddl-priv.html
https://www.postgresql.jp/document/12/html/ddl-priv.html#PRIVILEGES-SUMMARY-TABLE
- アクセス権限を見たときに =UC/postgres のように = の左辺が空文字の場合は public ロールを表す
- psql コマンドの \l は select * from pg_database;、\dn+ は select * from pg_namespace; で代替できる。他のアクセス権限見たい時も大体システムカタログに載ってるやつを select してやれば良い

daido1976

\z を叩いて admin=arwdDxt/postgres+ という権限付与がされていた時、

a: INSERT
r: SELECT
w: UPDATE
d: DELETE
D: TRUNCATE
x: REFERENCES
t: TRIGGER

上記の通りで、admin ロールに対してテーブルに対する全ての権限が付与されている。（付与者は postgres ロール）
See. https://www.postgresql.jp/document/12/html/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE

また、末尾の + には特に意味はなくデータが複数行にまたがってることを示してるだけ。

daido1976

Readsh 用などに readolny な DB ユーザを作成したい場合は以下を参考にやればよい。
Setting up Read Only Users in PostgreSQL

PostgreSQL 14 から追加される pg_read_all_data というロールを使えばより簡単になるはず。
PostgreSQL 14から参照・更新ユーザーを簡単に作れるようになりました | DevelopersIO

作成時

対象のデータが postgres データベースの public スキーマに存在している前提

-- `redash` というユーザ（ロール）を作成しつつパスワードをセット
CREATE USER redash WITH PASSWORD 'xxx';
-- `redash` というユーザに対して postgres データベースへの接続権限を付与する
GRANT CONNECT ON DATABASE postgres TO redash;
-- `redash` というユーザに対して public スキーマの利用権限を付与する（セキュリティ的な理由で public スキーマへの public ロールでのアクセス権限を revoke しているケースがあるため）
GRANT USAGE ON SCHEMA public TO redash;
-- 既存のテーブルに対する SELECT の権限を `redash` に付与
GRANT SELECT ON ALL TABLES IN SCHEMA public TO redash;
-- 今後追加されるテーブルに対して、デフォルトで SELECT の権限を `redash` に付与するようにする
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO redash;

削除時

REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM redash;
REVOKE CONNECT ON DATABASE postgres FROM redash;
REVOKE USAGE ON SCHEMA public FROM redash;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM redash;
DROP ROLE redash;