PostgreSQLユーザーとロール作成完全ガイド！初心者でもわかるデータベース権限管理

PostgreSQLのユーザーとロールの基本概念

PostgreSQLのユーザーとロールの基本概念" onerror="this.onerror=null; this.src='/img/view/java-exception-introduce.jpg';">

PostgreSQLのユーザーとロールの基本概念

PostgreSQLでは、データベースにアクセスする人や権限を管理するために「ロール」という仕組みを使います。ロールには二つの種類があります。一つはログインできるロール（これを一般的にユーザーと呼びます）、もう一つはログインできないロール（権限のグループとして使います）です。

例えるなら、会社の組織図のようなものです。「営業部」という部署（ロール）があって、その中に「田中さん」「佐藤さん」という社員（ユーザー）がいるイメージです。営業部に権限を与えれば、その部署に所属する全員がその権限を持つことができます。

PostgreSQLでは、ユーザーもロールも内部的には同じもので、ログイン権限があるかどうかの違いだけです。そのため、CREATE USERコマンドとCREATE ROLEコマンドは、ほとんど同じ機能を持っています。

基本的なユーザー作成方法

基本的なユーザー作成方法" onerror="this.onerror=null; this.src='/img/view/java-exception-introduce.jpg';">

基本的なユーザー作成方法

PostgreSQLでユーザーを作成するには、CREATE USERコマンドまたはCREATE ROLEコマンドを使います。まずは最もシンプルなユーザー作成方法から見ていきましょう。

パスワード付きユーザーの作成

実際の運用では、セキュリティのためにパスワードを設定することが必須です。次のSQLコマンドで、パスワード付きのユーザーを作成できます。

CREATE USER sales_user WITH PASSWORD 'secure_password123';

このコマンドは、「sales_user」という名前のユーザーを作成し、パスワードを「secure_password123」に設定します。WITHは「～と一緒に」という意味で、PASSWORDの後にシングルクォーテーション（'）で囲んだパスワードを指定します。

ユーザー作成の確認方法

ユーザーが正しく作成されたか確認するには、psqlコマンドラインツールで「\du」というコマンドを使います。これはデータベースユーザーの一覧を表示するコマンドです。

                                   List of roles
 Role name   |                         Attributes                         
-------------+------------------------------------------------------------
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS
 sales_user  | 

このように、作成したsales_userが一覧に表示されていれば成功です。Attributesの欄には、そのユーザーが持っている特別な権限が表示されます。

ロールの作成と権限設定

ロールの作成と権限設定" onerror="this.onerror=null; this.src='/img/view/java-exception-introduce.jpg';">

ロールの作成と権限設定

ロールを作成する場合は、CREATE ROLEコマンドを使います。ただし、CREATE ROLEで作成したロールは、デフォルトではログインできません。ログイン権限を与えるには、明示的にLOGIN属性を付ける必要があります。

CREATE ROLE manager_role WITH LOGIN PASSWORD 'manager_pass456';

このコマンドは、「manager_role」という名前のロールを作成し、ログイン権限とパスワードを設定します。これは実質的にユーザーを作成したのと同じ結果になります。

グループロールの作成

ログインできないロールは、権限をまとめるグループとして使います。例えば、営業部門全員に共通の権限を与えたい場合、グループロールを作成すると便利です。

CREATE ROLE sales_group;

このコマンドは、ログイン権限を持たない「sales_group」というロールを作成します。このロールに権限を設定してから、個別のユーザーをこのグループに所属させることで、権限管理が簡単になります。

ユーザーに様々な属性を付与する方法

ユーザーに様々な属性を付与する方法" onerror="this.onerror=null; this.src='/img/view/java-exception-introduce.jpg';">

ユーザーに様々な属性を付与する方法

PostgreSQLのユーザーには、様々な属性を付けることができます。これらの属性によって、ユーザーができることが決まります。

スーパーユーザー権限

スーパーユーザーは、データベース内で全ての操作ができる最高権限を持つユーザーです。会社でいえば社長のような存在で、何でもできる代わりに責任も重大です。通常の業務では、スーパーユーザー権限は必要最小限のユーザーにのみ与えます。

CREATE USER admin_user WITH PASSWORD 'admin_pass789' SUPERUSER;

SUPERUSERキーワードを付けることで、全権限を持つユーザーが作成されます。

データベース作成権限

一般ユーザーでも、新しいデータベースを作成できる権限を与えることができます。これはCREATEDB属性で指定します。

CREATE USER developer_user WITH PASSWORD 'dev_pass321' CREATEDB;

このユーザーは、自分でデータベースを作成できるようになりますが、他のユーザーのデータベースを削除したりはできません。

ロール作成権限

CREATEROLE属性を付けると、そのユーザーは他のユーザーやロールを作成できるようになります。人事部門の担当者のように、他の社員の登録ができる権限です。

CREATE USER hr_manager WITH PASSWORD 'hr_pass999' CREATEDB CREATEROLE;

このように、スペースで区切って複数の属性を同時に指定できます。このユーザーは、データベースの作成と、他のユーザーの作成の両方ができます。

ユーザーをグループロールに追加する方法

ユーザーをグループロールに追加する方法" onerror="this.onerror=null; this.src='/img/view/java-exception-introduce.jpg';">

ユーザーをグループロールに追加する方法

作成したグループロールに、個別のユーザーを所属させることで、権限管理を効率化できます。これはGRANTコマンドを使って行います。

GRANT sales_group TO sales_user;

このコマンドは、先ほど作成した「sales_user」を「sales_group」というグループロールに所属させます。GRANTは「与える」という意味で、グループの権限をユーザーに与えることになります。

複数のユーザーをまとめて管理する利点

グループロールを使うと、権限の管理がとても楽になります。例えば、営業部門の全員に顧客データベースへのアクセス権を与えたい場合、一人ひとりに権限を設定するのではなく、営業グループに一度だけ権限を設定すれば良いのです。

新しい営業担当者が入社したときも、その人をグループに追加するだけで、自動的に必要な権限が付与されます。退職するときも、グループから外すだけで全ての権限を一括で削除できます。

既存ユーザーの権限を変更する方法

既存ユーザーの権限を変更する方法" onerror="this.onerror=null; this.src='/img/view/java-exception-introduce.jpg';">

既存ユーザーの権限を変更する方法

すでに作成したユーザーの権限を後から変更することもできます。これにはALTER USERコマンドまたはALTER ROLEコマンドを使います。

パスワードの変更

セキュリティのため、定期的にパスワードを変更することは重要です。次のコマンドでパスワードを変更できます。

ALTER USER sales_user WITH PASSWORD 'new_secure_password456';

このコマンドで、sales_userのパスワードが新しいものに変更されます。

権限の追加と削除

既存のユーザーに新しい権限を追加したり、不要になった権限を削除したりすることもできます。

ALTER USER developer_user WITH CREATEROLE;

このコマンドで、既存のdeveloper_userにロール作成権限が追加されます。

ALTER USER developer_user WITH NOCREATEROLE;

NOCREATEROLEのように、属性の前に「NO」を付けると、その権限を削除できます。他にもNOSUPERUSER、NOCREATEDBなどがあります。

データベースやテーブルへのアクセス権限設定

データベースやテーブルへのアクセス権限設定" onerror="this.onerror=null; this.src='/img/view/java-exception-introduce.jpg';">

データベースやテーブルへのアクセス権限設定

ユーザーを作成しただけでは、まだデータベースやテーブルにアクセスできません。具体的なデータベースやテーブルに対する権限を、別途設定する必要があります。

データベースへの接続権限

ユーザーが特定のデータベースに接続できるようにするには、CONNECT権限を与えます。

GRANT CONNECT ON DATABASE sales_db TO sales_user;

このコマンドで、sales_userがsales_dbというデータベースに接続できるようになります。

テーブルへの操作権限

テーブルに対しては、SELECT（データの読み取り）、INSERT（データの追加）、UPDATE（データの更新）、DELETE（データの削除）などの権限を個別に設定できます。

GRANT SELECT ON TABLE customers TO sales_user;

このコマンドで、sales_userはcustomersテーブルのデータを読み取ることができますが、追加・更新・削除はできません。

GRANT ALL PRIVILEGES ON TABLE orders TO sales_group;

ALL PRIVILEGESを指定すると、そのテーブルに対する全ての操作権限が付与されます。sales_groupに所属するユーザーは、ordersテーブルに対して自由に操作できるようになります。

ユーザーとロールの削除方法

ユーザーとロールの削除方法" onerror="this.onerror=null; this.src='/img/view/java-exception-introduce.jpg';">

ユーザーとロールの削除方法

不要になったユーザーやロールは、セキュリティのために削除することが重要です。退職した社員のアカウントを放置しておくと、セキュリティリスクになります。

基本的な削除方法

ユーザーやロールを削除するには、DROP USERまたはDROP ROLEコマンドを使います。

DROP USER sales_user;

ただし、このコマンドが実行できるのは、そのユーザーがデータベース内のオブジェクト（テーブルなど）を所有していない場合だけです。

削除時の注意点

ユーザーがテーブルやデータベースを所有している場合、先にそれらのオブジェクトの所有権を別のユーザーに移すか、オブジェクトを削除する必要があります。所有権の移行にはALTER TABLEコマンドのOWNER TOオプションを使います。

ALTER TABLE customers OWNER TO postgres;

このコマンドで、customersテーブルの所有者をpostgresユーザーに変更できます。これにより、元の所有者だったユーザーを削除できるようになります。

実践的なユーザー管理の例

実践的なユーザー管理の例" onerror="this.onerror=null; this.src='/img/view/java-exception-introduce.jpg';">

実践的なユーザー管理の例

ここでは、実際の運用を想定した、現実的なユーザー管理の流れを見ていきましょう。

会社のシステムを想定した権限設計

例えば、社内の販売管理システムを構築する場合を考えてみます。必要なロールとユーザーは次のようになります。

この構成を実現するには、まずグループロールを作成し、それぞれに適切な権限を設定します。そして、個別の社員アカウントを作成して、該当するグループに所属させます。

セキュリティのベストプラクティス

ユーザー管理では、セキュリティを考慮することが非常に重要です。以下のような点に注意してください。

よくあるトラブルと解決方法

よくあるトラブルと解決方法" onerror="this.onerror=null; this.src='/img/view/java-exception-introduce.jpg';">

よくあるトラブルと解決方法