PostgreSQLのユーザー、ロールとデータベース

2019.12.10 (火)

PostgreSQLにはユーザーとかロールとかデータベースとか権限や認証関連でややこしいっていうのがあります。ユーザーとロールは何が違うんだ?って話とか。オーナーとロールとユーザーって何が違うんだとか。ほんとややこしい。

  • ロール (role)とは
  • PostgreSQLのロール
  • ロールの新規作成
  • ロールの削除
  • ロールをデータベースに紐付ける
    • データベースの作成)
    • データベースの削除

ロール (role)とは

一般的にはPostgreSQLのロールとは「ユーザーと呼ばれるものとグループと呼ばれるものの二つの性質を兼ね備えたもの」ということらしいです。つまりユーザーとグループのことですね。

role
  ├── user
  └── group

ロールのことをユーザーと言ったり、ユーザーのことをロールと言ったりするのでややこしいです。要はロールでユーザーとそのグループを設定できるというわけです。通常DBの接続情報提示するときなどには、他のデータベースと同様にロールのことをユーザーと言ったりするというのが慣例になっている感じがします。

PostgreSQLのロール

ロールの表示は\duで表示します。

postgres=# \du
                                                ロール一覧
   ロール名   |                                    属性                                    | 所属グループ
--------------+----------------------------------------------------------------------------+--------------
 postgres     | スーパーユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
 role_sample  |                                                                            | {}

こんなような感じになってます。インストール時のスーパーユーザーとしてpostgresというユーザーが作成されます。いわゆるroot = postgresという風に思っていてだいたい合ってます。

  • ロールの新規作成
  • ロールの削除

ロールの新規作成

foobarというロールにfoobarというパスワードを設定します。

postgres=# CREATE ROLE foobar WITH LOGIN PASSWORD 'foobar';

\duで確認します。習わしとして新規で作成するロールがこれから実用するロールとなるので、通常はパスワードの設定をしておきます。つまりリモートホストからのアクセスを考慮しておくということです。普通に作成するとロール属性や所属グループには何も設定されていません。これがデフォルトのロールの状態になります。
psqlからログアウトしてから以下のログインも確認しましょう。設定によりますが、通常パスワードなしでログインできます。

$ psql -U foobar

削除するときは、

postgres=# drop role foobar;

ロールをデータベースに紐付ける

  • データベースの作成
  • データベースの削除

例によってデータベースはロールと紐付けることになります。スーパーユーザーのpostgresでログインして以下のようにデータベースを作成します。紐付けるロールはfoobarですが、データベース作成時はオーナーになってます。これまたややこしい。データベースの場合は「どのロールが所有者(OWNER)になっているか」という話なんだそうです。なのでfoobarというロールが今回は所有者ということになります。ややこしい。

postgres=# CREATE DATABASE foobardb OWNER foobar;

\lで確認すると、

          名前           |  所有者  | エンコーディング |  照合順序   | Ctype(変換演算子) |     アクセス権限
-------------------------+----------+------------------+-------------+-------------------+-----------------------
 foobardb                | foobar   | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |
 postgres                | postgres | UTF8             | ja_JP.UTF-8 | ja_JP.UTF-8       |

こんな感じで、foobardbデータベースの所有者がfoobarになっています。
ここまで設定すると次回からは、ロール(所有者)とデータベースの両方で直接ログインできるようになります。

$ psql -U foobar -d foobardb
psql (11.0)
"help" でヘルプを表示します。

foobardb=>

となります。postgres(スーパーユーザー)からデータベースを操作するときは、データベースに直接コネクトします。この際はpostgresユーザー(ロール)でコネクトします。

postgres-# \c foobardb

データベースの削除は

postgres=# drop database foobardb;

だいたいここまでが基本的なところです。テーブルの作成や中身の操作はだいたいSQL文でできちゃうので、また別項。