PostgreSQLのロール・ユーザー・権限周りはややこしいのでまとめようと思う。
そもそも、なぜユーザーや権限が必要なのか
全てのユーザーがすべてのデータを自由に操作出来たら危険。
そのためPostgreSQLでは、誰が何をしてよいか、を管理する。
その中心にある仕組みが「ロール」となる。
ロールとは
PostgresSQL内部で権限を持つ主体
ロールには、
- PostgreSQLにログインできる
- データベースを作成できる
- テーブルを読める
- ほかのロールを作れる
といった権限や属性を持たせることができる。
ユーザーとロールの関係
ユーザーもロールの一種。
LOGINできるロールを、一般的にユーザーとして扱う
ROLE
├── LOGINできる
│ └── ユーザーとして使える
│
└── LOGINできない
└── 権限をまとめるグループなどとして使える
ロールには2つの使い方がある
| 用途 | LOGIN | ロール名の例 |
|---|---|---|
| ユーザーとして使う | あり | app_user |
| 権限グループとして使う | なし | readonly |
ロール属性の代表例
| 属性 | 意味 |
|---|---|
LOGIN |
PostgreSQLへログインできる |
SUPERUSER |
ほぼすべての権限制限を無視できる |
CREATEDB |
データベースを作成できる |
CREATEROLE |
ロールを作成・管理できる |
REPLICATION |
レプリケーション関連操作ができる |
BYPASSRLS |
Row Level Securityを回避できる |
ロール属性とオブジェクト権限は別物
PostgreSQLの権限を考えるときは、
- ロールそのものの属性
- データベースやテーブルに対する権限
に分けて考える。
ロール属性
LIGIN
SUPERUSER
CREATEDB
CREATEROLE
オブジェクト権限
SELECT
INSERT
UPDATE
DELETE
オブジェクトとは
PostgreSQLでは、データベース内の様々なものをオブジェクトとして扱う。
これらには所有者や権限がある。
代表的なものは次に示す。
| オブジェクト | 例 |
|---|---|
| Database | myapp |
| Schema | public |
| Table | users |
| Sequence | users_id_seq |
| View | active_users |
| Function | calculate_total() |
オブジェクトごとに権限の種類が異なる
Database
| 権限 | 意味 |
|---|---|
CONNECT |
データベースへ接続する |
CREATE |
データベース内にスキーマを作る |
TEMPORARY |
一時テーブルを作る |
Schema
| 権限 | 意味 |
|---|---|
USAGE |
スキーマを利用できる |
CREATE |
スキーマ内にオブジェクトを作れる |
Table
| 権限 | 意味 |
|---|---|
SELECT |
データを読む |
INSERT |
データを追加する |
UPDATE |
データを更新する |
DELETE |
データを削除する |
TRUNCATE |
全行を削除する |
REFERENCES |
外部キーから参照する |
TRIGGER |
トリガーを作成する |
所有者とは
オブジェクトには所有者が存在する。
オブジェクトを作成したロールがその所有者になる。
認証と認可
認証
あなたは誰？
を確認する
認可
あなたは何をしてよいか？
を決める。
認証によってデータベースへ接続し、認可によって操作を行う。
ロールをグループとして使える
ロールは権限をまとめるためにも使える。
たとえば、
readonly
└── SELECT権限
というロールを作り、
readonly
├── user_a
├── user_b
└── user_c
のように複数ユーザーを所属させることができる。
ロールを作る
# 基本形
CREATE ROLE <ロール名>;
# 基本形
CREATE ROLE readonly;
デフォルトではログインできない。
ログインできるロールを作る
ログイン属性を付けるとログインできるロールになる。
CREATE ROLE app_user LOGIN;
CREATE USERを使う
これらは実質同じになる。
CREATE USER app_user;
CREATE ROLE app_user LOGIN;
パスワード付きロールを作る
CREATE USER app_user WITH PASSEORD 'password123';
CREATE ROLE app_user LOGIN PASSWORD 'password123';
パスワードがないと、利用可能なパスワードが設定されていない状態になる。
既存ロールにLOGIN属性を付ける
ALTER ROLE readonly LOGIN; # ログイン可能にする
ALTER ROLE readonly NOLOGIN; # ログイン不可能にする
CREATEDB属性を付ける
ALTER ROLE app_user CREATEDB; # データベースの作成を可能にする
ALTER ROLE app_user NOCREATEDB; # データベースの作成を不可能にする
CREATEROLE属性を付ける
ALTER ROLE app_user CREATEROLE; # ロールの作成を可能にする
ALTER ROLE app_user NOCREATEROLE; # ロールの作成を不可能にする
SUPERUSER属性を付ける
SUPERUSERは非常に強い権限を持つ。
ALTER ROLE app_user SUPERUSER; # スーパーユーザーにする
ALTER ROLE app_user NOSUPERUSER; # スーパーユーザーではなくする
ロール属性を作成時にまとめて指定する
CREATE ROLE app_user
LOGIN
PASSWORD 'password123'
CREATEDB;
ロールに対しオブジェクトへの権限を与える
基本形:
GRANT <権限>
ON <オブジェクト>
TO <ロール>;
テーブルに対する権限
INSERT
GRANT INSERT
ON users
TO app_user;
UPDATE
GRANT UPDATE
ON users
TO app_user;
DELETE
GRANT DELETE
ON users
TO app_user;
複数の権限
GRANT SELECT, INSERT, UPDATE, DELETE
ON users
TO app_user;
データベースへの接続権限を与える
GRANT CONNECT
ON DATABASE myapp
TO app_user;
スキーマの利用権限を与える
GRANT USAGE
ON SCHEMA public
TO app_user;
スキーマ内でオブジェクトを作れるようにする
GRANT CREATE
ON SCHEMA public
TO app_user;
権限を取り消す
基本形:
REVOKE <権限>
ON <オブジェクト>
FROM <ロール>;
例:
REVOKE DELETE
ON users
FROM app_user;
ロールを別のロールに所属させる
ロールを作成する。
CREATE ROLE readonly;
権限を与える。
GRANT SELECT
ON users
TO readonly;
このロールに別のロールを所属させる。
GRANT readonly TO user_a;
user_aはreadonlyの権限を持つことになる。
複数ユーザーに同じロールを付ける
全員readonlyの権限を持つ。
GRANT readonly TO user_a;
GRANT readonly TO user_b;
GRANT readonly TO user_c;
ロール一覧を見る
\du
現在のロールを確認する
SELECT current_user;
データベースの所有者を指定する
データベース作成時に所有者を指定できる。
CREATE DATABASE myapp
OWNER app_user;
オブジェクトの所有者を変更する
ALTER TABLE users
OWNER TO app_user;
ロールを削除する
DROP ROLE app_user;
DROP USER app_user;