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

はじめに

このラボでは、PostgreSQL のロールと権限管理について学習します。主な目的は、特定の属性を持つロールを作成し、データベース権限を管理する方法を学ぶことです。

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

ロールの作成

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

PostgreSQL サーバーへの接続：

ターミナルを開き、psql コマンドラインツールを使用して postgres ユーザーとして 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 | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
reporting_role | Cannot login | {}
```
psql シェルを終了します。
```
\q
```

データベース権限の付与

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

PostgreSQL サーバーへの接続：

psql コマンドラインツールを使用して postgres ユーザーとして 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 サーバーへの接続：

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

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

新しいユーザー report_user を作成しましょう。次に、このユーザーに reporting_role へのメンバーシップを付与します。これは、report_user が reporting_role に付与された権限を継承することを意味します。
```
CREATE USER report_user;
GRANT reporting_role TO report_user;
```
以下のような出力が表示されるはずです。
```
CREATE ROLE
GRANT ROLE
```
パスワードなし認証のための PostgreSQL の設定：

デフォルトでは、PostgreSQL は peer 認証を使用しており、この実験では接続の問題を引き起こす可能性があります。report_user のアクセスを簡素化するために、trust に変更します。

まず、psql シェルを終了します。
```
\q
```
次に、pg_hba.conf ファイルを変更して認証方法を変更します。以下の sed コマンドは、元のファイルを pg_hba.conf.bak としてバックアップし、ローカル接続に対して peer を trust に置き換えます。
```
sudo sed -i.bak 's/^local\s\+all\s\+all\s\+peer/local all all trust/' /etc/postgresql/14/main/pg_hba.conf
```
変更を適用するために PostgreSQL をリロードします。
```
sudo service postgresql reload
```
sales_data テーブルへのデータの挿入:

postgres ユーザーとしてデータベースに再度接続します。
```
sudo -u postgres psql -d reporting_db
```
テスト目的で、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
```
パスワードの入力を求められることなく、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 ユーザーとして接続しているターミナルに戻ります。psql コマンドラインツールを使用して postgres ユーザーとして 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
```
report_user としてアクセス拒否を確認:

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

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

まとめ

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