※この記事にはアフィリエイト広告（プロモーション）が含まれます。
PostgreSQL ではユーザーもグループも「ロール」という1つの仕組みで扱います。CREATE USER は、ログインできるロールを作る CREATE ROLE ... LOGIN の別名です。この記事の出力は PostgreSQL 18.6（Ubuntu 26.04 の標準パッケージ）で採りました。
まず見る（結論）
CREATE USER app1 WITH PASSWORD 'パスワード';
GRANT CREATE ON SCHEMA public TO app1; -- public に表を作らせるなら
\du -- 一覧CREATE ROLEで作るとLOGINが付かず、ログインするとrole "..." is not permitted to log in- PostgreSQL 15 以降、作ったユーザーは
publicスキーマに表を作れない（permission denied for schema public） - 表などを持っているユーザーは
DROP USERできない。REASSIGN OWNED→DROP OWNEDの後に消す
ユーザーを作る
サーバーに入ったら、スーパーユーザーの postgres で psql を開きます。Ubuntu の標準パッケージなら sudo -u postgres psql です。
CREATE USER app1 WITH PASSWORD 'Passw0rd1';
CREATE ROLE app2 WITH PASSWORD 'Passw0rd2';
CREATE USER App3;3つとも CREATE ROLE と返ります。一覧は psql の \du で出します。
fsv=# \du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
app1 |
app2 | Cannot login
app3 |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLSapp2 のまま接続すると次のエラーです。
$ psql -h 127.0.0.1 -U app2 -d fsv
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: role "app2" is not permitted to log inALTER ROLE app2 LOGIN; を実行すると接続できました。CREATE ROLE は、権限をまとめて持たせるグループ用のロールを作るときに使います。
名前は引用符で囲まないと小文字になります。CREATE USER App3 で作られたのは app3 で、psql -U App3 で接続すると password authentication failed for user "App3" になりました。接続時のユーザー名は大文字小文字を区別するので、作るときも接続するときも小文字にそろえます。
同じ名前で作ると ERROR: role "app1" already exists です。
SQL で一覧を出す
SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolvaliduntil
FROM pg_roles WHERE rolname !~ '^pg_' ORDER BY rolname;
rolname | rolcanlogin | rolsuper | rolcreatedb | rolvaliduntil
----------+-------------+----------+-------------+---------------
app1 | t | f | f |
app2 | f | f | f |
app3 | t | f | f |
postgres | t | t | t |pg_ で始まるのは PostgreSQL が最初から持っている定義済みロール（pg_monitor など）です。これらは消せません（cannot drop role pg_monitor because it is required by the database system）。
シェルから作る
$ sudo -u postgres createuser --login --createdb app4
$ sudo -u postgres psql -c "ALTER USER app4 WITH PASSWORD 'Passw0rd4';"createuser は中で CREATE ROLE を実行するコマンドです。--pwprompt（-P）を付けるとパスワードを対話で聞かれます。
作ったユーザーで表を作れない
app1 で接続して表を作ります。
$ psql -h 127.0.0.1 -U app1 -d fsv -c "CREATE TABLE t_app1 (id int);"
ERROR: permission denied for schema public
LINE 1: CREATE TABLE t_app1 (id int);
^PostgreSQL 15 から、public スキーマに表を作る権限が全員には付かなくなりました。表を作れるようにする方法は3つあります。アプリの表を共有の public にまとめるなら1つ目、ユーザーごとに分けるなら2つ目、アプリ専用のデータベースを用意するなら3つ目です。
| やり方 | コマンド（postgres で実行） |
|---|---|
| public に作る権限を付ける | GRANT CREATE ON SCHEMA public TO app1; |
| ユーザー専用のスキーマを作る | CREATE SCHEMA app1 AUTHORIZATION app1; |
| データベースごと持たせる | CREATE DATABASE appdb OWNER app1; |
ユーザー名と同じ名前のスキーマを作ると、スキーマ名を付けずに作った表はそちらに入ります。search_path の既定が "$user", public のためです。
$ psql -h 127.0.0.1 -U app1 -d fsv -c "CREATE TABLE t_own (id int);"
schemaname | tablename | tableowner
------------+-----------+------------
public | t_app1 | app1
app1 | t_own | app1データベースの所有者にしたユーザー（3つ目）は、そのデータベースの public にも表を作れました。他のユーザーが作った表を読ませる権限は permission denied の記事にまとめています。
パスワードと属性を変える
ALTER USER app1 WITH PASSWORD 'NewPassw0rd1';
ALTER USER app1 VALID UNTIL '2027-03-31';
ALTER USER app1 VALID UNTIL 'infinity';
ALTER USER app1 RENAME TO app1x;VALID UNTIL はパスワードの有効期限です。過去の日付にすると、正しいパスワードでも password authentication failed for user "app1" で弾かれました。期限切れだという表示は出ないので、パスワードが合っているのに入れないときは pg_roles.rolvaliduntil も見ます。'infinity' で期限なしに戻ります。
パスワード認証の失敗の切り分けは password authentication failed の記事にあります。
ユーザーを消す
DROP USER app1;
ERROR: role "app1" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public
owner of table t_app1
owner of schema app1
owner of table app1.t_ownDETAIL に残っているものが並びます。持ち物を別のユーザーに移してから、残った権限を外して消します。
REASSIGN OWNED BY app1 TO postgres; -- 持ち物を postgres に移す
DROP OWNED BY app1; -- 残った権限を外す
DROP USER app1;移さずに持ち物ごと消してよいなら、DROP OWNED BY app1; だけで表やスキーマも消えます。どちらもデータベース単位で効きます。ユーザーが複数のデータベースに表を持っているなら、それぞれのデータベースに接続して実行します。
関連: PostgreSQLの「permission denied」 ／ PostgreSQLの「password authentication failed」 ／ MySQLのユーザー作成と権限付与
