PostgreSQL ロールと権限管理 - データベースセキュリティ

はじめに

この実験（Lab）では、PostgreSQL のロールと権限管理について学びます。主な目標は、特定の属性を持つロールを作成し、データベースの権限を管理する方法を習得することです。

まず、reporting_role という名前のロールを NOLOGIN 属性で作成し、データベースに直接接続できないようにします。次に、ロールの作成を確認します。以降のステップでは、データベースの権限の付与とテスト、ユーザーの切り替えによるアクセス確認、ロールからの権限の取り消しについて説明します。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_setup("Create New Database") postgresql/PostgreSQLGroup -.-> postgresql/db_access("Connect To Database") postgresql/PostgreSQLGroup -.-> postgresql/user_setup("Create User Account") postgresql/PostgreSQLGroup -.-> postgresql/perm_read("Grant Table Read") postgresql/PostgreSQLGroup -.-> postgresql/perm_write("Grant Table Write") postgresql/PostgreSQLGroup -.-> postgresql/perm_remove("Revoke User Privilege") postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") subgraph Lab Skills postgresql/db_setup -.-> lab-550960{{"PostgreSQL のロールと権限管理"}} postgresql/db_access -.-> lab-550960{{"PostgreSQL のロールと権限管理"}} postgresql/user_setup -.-> lab-550960{{"PostgreSQL のロールと権限管理"}} postgresql/perm_read -.-> lab-550960{{"PostgreSQL のロールと権限管理"}} postgresql/perm_write -.-> lab-550960{{"PostgreSQL のロールと権限管理"}} postgresql/perm_remove -.-> lab-550960{{"PostgreSQL のロールと権限管理"}} postgresql/table_init -.-> lab-550960{{"PostgreSQL のロールと権限管理"}} end

ロールの作成

このステップでは、PostgreSQL でロールを作成する方法を学びます。ロールは、データベースへのアクセスと権限を管理するために使用されます。

PostgreSQL サーバーへの接続：

ターミナルを開き、postgres ユーザーとして psql コマンドラインツールを使用して PostgreSQL サーバーに接続します。
```
sudo -u postgres psql
```
次のようなプロンプトが表示されます。
```
postgres=#
```
reporting_role ロールの作成:

次に、CREATE ROLE コマンドを使用して reporting_role を作成します。NOLOGIN 属性を指定します。これは、このロールがデータベースに直接接続するために使用できないことを意味します。他のロールまたはユーザーに権限を付与するためにのみ使用されます。
```
CREATE ROLE reporting_role NOLOGIN;
```
このコマンドはロールを作成します。次の出力が表示されます。
```
CREATE ROLE
```

ロールの作成の確認：

ロールが作成されたことを確認するには、\du コマンド（\list users の省略形）を使用します。このコマンドは、データベース内のすべてのロールを一覧表示します。

\du

ロールのリストに reporting_role が表示されるはずです。出力は次のようになります。

                                 ロール一覧 (List of roles)
   ロール名 (Role name)    |                         属性 (Attributes)                         | メンバー (Member of)
----------------+------------------------------------------------------------+-----------
 labex          | スーパーユーザー (Superuser), ロール作成 (Create role), DB 作成 (Create DB), レプリケーション (Replication), Bypass RLS| {}
 postgres       | スーパーユーザー (Superuser), ロール作成 (Create role), DB 作成 (Create DB)                         | {}
 reporting_role | ログイン不可 (Cannot login)                                               | {}

psql シェルを終了します。

\q

データベース権限の付与

このステップでは、ロールにデータベース権限を付与する方法を学びます。前のステップで作成した reporting_role を基に進めます。

PostgreSQL サーバーへの接続：

postgres ユーザーとして psql コマンドラインツールを使用して PostgreSQL サーバーに接続します。
```
sudo -u postgres psql
```
データベースの作成：

reporting_role がアクセスできる reporting_db という名前のデータベースを作成します。
```
CREATE DATABASE reporting_db;
```
次の出力が表示されます。
```
CREATE DATABASE
```
reporting_db データベースへの接続:

新しく作成したデータベースに接続します。
```
\c reporting_db
```
プロンプトが reporting_db=# に変わります。
テーブルの作成：

reporting_db データベース内に sales_data という名前の簡単なテーブルを作成します。
```
CREATE TABLE sales_data (
    id SERIAL PRIMARY KEY,
    product VARCHAR(255),
    amount DECIMAL
);
```
次の出力が表示されます。
```
CREATE TABLE
```
sales_data テーブルに対する SELECT 権限を reporting_role に付与:

次に、sales_data テーブルに対する SELECT 権限を reporting_role に付与します。これにより、reporting_role のメンバーであるロールまたはユーザーは、sales_data テーブルからデータを読み取ることができます。
```
GRANT SELECT ON sales_data TO reporting_role;
```
次の出力が表示されます。
```
GRANT
```
psql シェルを終了します。
```
\q
```

ユーザーの作成と権限のテスト

このステップでは、ユーザーを作成し、権限をテストするために reporting_role のメンバーシップを付与します。

PostgreSQL サーバーへの接続：

postgres ユーザーとして psql コマンドラインツールを使用して PostgreSQL サーバーに接続します。
```
sudo -u postgres psql
```
reporting_db データベースへの接続:

reporting_db データベースに接続します。
```
\c reporting_db
```
ユーザーの作成と reporting_role へのメンバーシップの付与:

パスワードを持つ report_user という名前の新しいユーザーを作成します ( your_password は強力なパスワードに置き換えてください)。次に、このユーザーに reporting_role へのメンバーシップを付与します。これは、report_user が reporting_role に付与された権限を継承することを意味します。
```
CREATE USER report_user WITH PASSWORD 'your_password';
GRANT reporting_role TO report_user;
```
次の出力が表示されます。
```
CREATE ROLE
GRANT
```
sales_data テーブルへのデータの挿入:

テスト目的で、サンプルデータを sales_data テーブルに挿入します。
```
INSERT INTO sales_data (product, amount) VALUES ('Laptop', 1200.00), ('Mouse', 25.00), ('Keyboard', 75.00);
```
次の出力が表示されます。
```
INSERT 0 3
```
psql シェルを終了します。
```
\q
```
report_user として reporting_db データベースに接続:

新しいターミナルウィンドウを開き、次のコマンドを使用します。
```
psql -d reporting_db -U report_user -W
```
report_user に設定したパスワードの入力を求められます。入力してください。
権限のテスト：

次に、sales_data テーブルからデータを選択してみます。
```
SELECT * FROM sales_data;
```
挿入したデータが表示されるはずです。
```
 id | product  | amount
----+----------+--------
  1 | Laptop   | 1200.00
  2 | Mouse    |   25.00
  3 | Keyboard |   75.00
(3 行 (rows))
```
この出力が表示された場合、report_user が sales_data テーブルに対する SELECT 権限を持っていることを意味します。これは、reporting_role を通じて付与されたものです。

psql シェルを終了します。
```
\q
```

権限の取り消し

このステップでは、reporting_role から SELECT 権限を取り消し、report_user が sales_data テーブルにアクセスできなくなったことを確認します。

postgres ユーザーとして PostgreSQL サーバーに接続:

postgres ユーザーとして接続しているターミナルに戻ります。postgres ユーザーとして psql コマンドラインツールを使用して PostgreSQL サーバーに接続します。
```
sudo -u postgres psql
```
reporting_db データベースへの接続:

reporting_db データベースに接続します。
```
\c reporting_db
```
reporting_role からの SELECT 権限の取り消し:

次に、reporting_role から sales_data テーブルに対する SELECT 権限を取り消します。
```
REVOKE SELECT ON sales_data FROM reporting_role;
```
次の出力が表示されます。
```
REVOKE
```
psql シェルを終了します。
```
\q
```
report_user として reporting_db データベースに接続:

report_user として接続しているターミナルに戻ります。
```
psql -d reporting_db -U report_user -W
```
report_user としてのアクセス拒否の確認:

report_user として、SELECT クエリを再度実行して、sales_data テーブルのデータにアクセスできなくなったことを確認します。
```
SELECT * FROM sales_data;
```
次のようなエラーメッセージが表示されるはずです。
```
ERROR:  permission denied for table sales_data
```
これは、reporting_role から権限が取り消されたため、report_user が sales_data テーブルに対する SELECT 権限を持たなくなったことを確認するものです。

psql シェルを終了します。
```
\q
```

まとめ

この実験 (Lab) では、PostgreSQL でのロールの作成、権限の付与、ユーザーの作成、ユーザーへのロールの割り当て、および権限の取り消し方法を学びました。これらは、データベースのセキュリティとアクセス制御を管理するための基本的な概念です。