環境

Ubuntu20.4
PostgreSQL12.4

はじめに

PostgreSQLにロールを作成するコマンドには、createuserとcreate userの２種類が存在します。createuserはPostgreSQLのクライアントツールとして提供されているLinuxのコマンドであるのに対し、create userは、PostgreSQLにログインした後、PostgreSQLのプロンプトで入力するSQL文です。

PostgeSQLではユーザーとロールは同義です。createuserコマンドで作成されるのは厳密にいえばユーザーではなくロールです。createuserコマンドを実行すると、裏側では、create roleというSQLコマンドが流れることになります。

PostgeSQLでロールを作成するLinuxのコマンドが用意されているのであれば、当然、ロールを削除するコマンドもcreatedorpとして用意されています。ただし、alter roleに該当する、alterroleというコマンドは用意されておりません。

参考
https://www.postgresql.jp/document/12/html/reference-client.html

peer認証

PostgreSQLをインストールすると、PostgreSQLにpostgresユーザーが自動で作成されます。postgresユーザーはデフォルトで用意されているパワーユーザーです。そして、Linux側にもpostgresといったユーザーが自動で作成されています。

$cat /etc/passwd | grep postgres

postgresユーザーでPostgreSQLにログインするとき、デフォルトでpeer認証という仕組みでログインされます。peer認証とはLinuxとPostgreSQLで同じ名前のユーザーを用意しておき、Linuxにログインしているユーザー名を利用して、PostgreSQLにもログインできるようにする仕組みのことです。

Linuxにログインするときに認証されていれば、PostgreSQLへのログイン時の認証も、同じユーザー名であれば、そのままスルーしてOKにさせる仕組みです。よって、PostgreSQLへのログイン時にパスワードを再度聞かれることがありません。

セキュリティをもっと強化したい場合は、認証の仕組みを変更することはできます。しかし、開発で利用する分にはpeer認証の方が便利だと思います。

PostgreSQLにログインするときの認証方法はpg_hab.confの下記で設定されています。ローカルからログインするとき、全てのデータベースに対し、postgresユーザーでログインするときは、peer認証で認証しますといった意味です。

pg_hab.conf

#TYPE  DATABASE        USER            ADDRESS                 METHOD
 local all             postgres                                peer

pg_hab.confの場所は、次のコマンドで検索してください。

$sudo find / -name "pg_hba.conf"
/etc/postgresql/12/main/pg_hba.conf

参考（公式サイト）
https://www.postgresql.jp/document/12/html/auth-peer.html

ロールの操作

PostgreSQLへのログインします。

$sudo su - postgres
#psql

ロールの一覧を表示します。初期状態ではpostgresだけが存在します。

#\du

testロールを作成します。パラメータをつけなければ、権限は何も付与されません。

#create user test;
#\du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test      |                                                            | {}

ロール作成時にパスワードを一緒につけることもできます。

#create user test with password 'test'

ロールのパスワードを変更、削除するコマンドです。

#alter role postgres with password 'postgres';
#alter role postgres with password null;

権限の有無は、システムカタログからも確認することができます。

参考
https://www.javadrive.jp/postgresql/role/index1.html

# select * from pg_roles where rolname = 'test';

ロールの権限の付与

create userを実行する時に、パラメータで任意の権限を付与することができますが、後から、権限を追加したり外したりことができます。

alter roleの詳細
https://pgsql-jp.github.io/jpug-doc/12.4/html/sql-alterrole.html

testユーザーにデータベースが作成できる権限を付与します。

#alter role test with createdb;
#du
                                  List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 test      | Create DB                                                  | {}

Create DBが付与されました。次に、testユーザーからデータベースが作成できる権限を外します。

#alter role test with nocreatedb;
#du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 test      |                                                            | {}

Create DBの権限が削除されました。

ロールを削除する

ロールを削除します。

#drop user test;

create userとcreate roleの違い

ロールを作成するとき、create userコマンドで作りますが、実際に作られるのはユーザーではなくロールです。だが、create roleというコマンドも存在しています。create userとcreate roleの違いは何なのだろうか。結果的にはcreate roleをしても同じようにロールが作成されます。しかし、creae roleをすると、デフォルトでlogin権限が付与されない形でロールが作成されます。login権限が付与されてないと、ロールの一覧でCannot loginと表示されます。折角、ロールを作ってもそのロールでログインができないことになります。

#create role test4;
#\du
                                      List of roles
    Role name    |                         Attributes                         | Member of
-----------------+------------------------------------------------------------+-----------
  test4           | Cannot login                                               | {}

login権限を付与するには、このようにすればOKです。ロールの一覧からCannot loginの文字が消えました。

#alter role test4 with login;
#\du
List of roles
    Role name    |                         Attributes                         | Member of
-----------------+------------------------------------------------------------+-----------
  test4          |                                                            | {}