OracleやMySQLを使っていて、PostgreSQLの環境構築をはじめてすると、ユーザー・ロール・スキーマに戸惑いを感じたので、今回はこの違いをまとめておきたいと思う。
ユーザーとロールの違い
- ユーザー ＝ ロール + ログイン権限
上記の通りユーザーはログイン権限を持ったロールと同じです。つまり以下の２つのクエリは同じことを指します。
以下でログイン権限だけを与えた『myuser』ロールを作成
CREATE USER myuser WITH PASSWORD 'secret_passwd';
CREATE ROLE myuser WITH LOGIN PASSWORD 'secret_passwd';
PublicスキーマーとPublicロールって何？
新しいDBを作成するとデフォルトでPublicスキーマーが作成されます。 同時にPublicスキーマーへのアクセスはPublicというロールに付与されます。 新規にユーザーを作成した場合、このPublicロールというのはデフォルトで設定されます。 そのため、全てのユーザーはデフォルトでは、全てのPublicスキーマにアクセスし、オブジェクトの作成をすることが可能になります。
検索パスの概念
Select文を書く際に以下のようなクエリを書くことがある
select * from [schema].[table]
上記のようにスキーマーを指定し、ピリオドでつなぎ、テーブル名を続けて書く。 これで、対象のテーブルが、どのスキーマにあるかをDBに命令していることになる。
しかし、中にはスキーマ名を書かずにテーブル名だけでデータを参照することもある。 この場合は、PostgreSQL は検索パスにリストされているスキーマ内でこのテーブルを探すことになる。
検索パス自体は以下から確認ができる
postgres=# show search_path; search_path ----------------- "$user", public (1 row)
$ user"はログインしているユーザー名を置き換えられる。すなわち、myuserというユーザーの場合は myuserスキーマを参照することになる。そしてmyuserスキーマがない、 もしくはmyuserスキーマー内に対象のテーブルがない場合、publicスキーマを参照しにいくことになる。
問題はCreate時だ。ReadOnlyのユーザーがCeate文を発行すると、自身の名前のスキーマには作成はできないが Publicスキーマーには自動で作成が可能であるため、そちらにオブジェクトがCreateされてしまう。
これを修正するには次のSQLステートメントを使用して、public ロールから public スキーマに対するデフォルトの作成権限を取り消す必要がある。 PostgreSQLを利用する際にデフォルト以下のコマンドは実行をしても良いくらいのクエリである。
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
また、合わせて以下のクエリを実行して、public ロールがデータベースに接続する機能を無効にしておきたい。
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;
まとめ
今回はPostgreSQLをはじめて最初に疑問に思ったことを整理しました。今後もPostgreSQLについて学んだことをまとめていきたいと思います。最後に以下の記事が非常にまとまっていてわかりやすい。この記事も以下のURLを参考に整理させていただきました。最後まで読んでいただきありがとうございました。
