前提（動機）
簡易的なデータ分析用のデータストアとして使う、という想定で PostgreSQL を構築してみた。 インフラ屋ではない人間による勉強目的での遊びなので、 いつか詳しい人にレビューしてもらいたいところではある。
雑に要件を書くと次のような感じ:
- データ分析のプロジェクトごとにデータベースを作る
- プロジェクト担当メンバー以外による当該データベースへのアクセスを禁止する
- プロジェクトごとに担当メンバーは異なる
- メンバーは複数のプロジェクトに担当できる
また以下も要件として考える:
- OS のパッケージマネージャー (apt) でセキュリティ更新を実施できる
- PostgreSQL の管理は原則として OS のユーザー
postgresで、 データベースのユーザーpostgresとして、 同一ホスト上で（UNIX ドメインソケットで）接続して実施する
さらに、以下のようなプロジェクト・メンバーの関係を想定する。
- mercury プロジェクトと venus プロジェクトが進行中
- mercury プロジェクトの担当メンバーは alice と bob
- venus プロジェクトの担当メンバーは bob と carol
- bob は mercury と venus の両方の担当メンバーである（別人ではない）
動作検証環境
- Ubuntu Server 20.04
- PostgreSQL 14
なお anvil というホスト名のサーバー機を使用している。
PostgreSQL 14 のインストール
まず PostgreSQL 公式の Ubuntu 用レポジトリを登録し、 そこからインストールを行った。Ubuntu Server 20.04 の標準レポジトリにも PostgreSQL 12 が登録されているけれど、少し古いため今回は最新 LTS 版の 14 を使うことにした。
# 以下は公式手順そのまま sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt-get -y install postgresql-14
以上で、次の設定が自動的に行われるようだ:
PostgreSQL 14 のサービスのインストール（OS 起動時に自動起動する設定）
$ systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Fri 2022-04-01 23:27:07 UTC; 10min ago Main PID: 5496 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 19034) Memory: 0B CGroup: /system.slice/postgresql.service Apr 01 23:27:07 anvil systemd[1]: Starting PostgreSQL RDBMS... Apr 01 23:27:07 anvil systemd[1]: Finished PostgreSQL RDBMS.
Linux ユーザー
postgresqlおよびグループpostgresqlの作成$ ls /etc/{passwd,group} | xargs grep -E '^postgres' | wc -l 2
簡易動作確認
ちゃんと動いているか、簡単に使ってみて確認した。
$ # postgres ユーザーに代わる $ sudo -i -u postgres postgresql@anvil:~$ psql -h localhost -c "CREATE DATABASE mydb;" CREATE DATABASE postgresql@anvil:~$ # mydb データベースを試しに使ってみる postgresql@anvil:~$ psql mydb -h localhost -c "CREATE TABLE item (id int);" CREATE TABLE postgresql@anvil:~$ psql mydb -h localhost -c "INSERT INTO item VALUES (47);" INSERT 0 1 postgresql@anvil:~$ psql mydb -h localhost -c "SELECT * FROM item;" id ---- 47 (1 row) postgresql@anvil:~$ psql mydb -c "DROP TABLE item;" DROP TABLE
ユーザーとユーザーグループの作成
続いてユーザーおよびユーザーグループとして使う ROLE を登録した。 登録する ROLE は前述の通りで、具体的に実行した SQL は以下の通り。
CREATE ROLE alice LOGIN WITH PASSWORD 'password_for_alice'; CREATE ROLE bob LOGIN WITH PASSWORD 'password_for_bob'; CREATE ROLE carol LOGIN WITH PASSWORD 'password_for_carol'; CREATE ROLE mercury NOLOGIN NOINHERIT; GRANT mercury TO alice; GRANT mercury TO bob; CREATE ROLE venus NOLOGIN NOINHERIT; GRANT venus TO bob; GRANT venus TO carol;
作成された alice, bob, carol のユーザーとして使う ROLE および mercury_pj,
venus_pj のユーザーグループとして使う ROLE が期待通りに登録されていることを
\du メタコマンドで確認する:
postgres@anvil:~$ psql -c "\du" # 表示結果は抜粋＆編集してある List of roles Role name | Attributes | Member of -----------+------------------------------+----------------- alice | | {mercury} bob | | {mercury,venus} carol | | {venus} mercury | No inheritance, Cannot login | {} venus | No inheritance, Cannot login | {}
データベースの作成
プロジェクトのメンバーが所属するユーザーグループ用 ROLE を owner とした データベースをプロジェクトごとに作成する。
CREATE DATABASE mercury OWNER mercury; CREATE DATABASE venus OWNER venus;
アクセス権の設定
PostgreSQL 14 では新規作成したデータベースへの接続 (CONNECT)が、
あらゆる ROLE (PUBLIC) に許可されている。
冒頭に書いた要件に合わないため、各データベースの owner （プロジェクトメンバー）
以外のアクセスを禁止しておく。
REVOKE CONNECT ON DATABASE mercury FROM PUBLIC; GRANT CONNECT ON DATABASE mercury TO mercury; REVOKE CONNECT ON DATABASE venus FROM PUBLIC; GRANT CONNECT ON DATABASE venus TO venus;
アクセス権をチェックしておく:
postgres@anvil:~$ psql -c "\l" # 結果は一部抜粋 List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- mercury | mercury | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/mercury + | | | | | mercury=CTc/mercury venus | venus | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/venus + | | | | | venus=CTc/venus postgres@anvil:~$ psql mercury -h localhost -U alice --csv -c "SELECT version();" Password for user alice: version "PostgreSQL 14.2 (Ubuntu 14.2-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit" postgres@anvil:~$ postgres@anvil:~$ psql mercury -h localhost -U carol --csv -c "SELECT version();" Password for user carol: psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: permission denied for database "mercury" DETAIL: User does not have CONNECT privilege. postgres@anvil:~$ postgres@anvil:~$ psql venus -h localhost -U alice --csv -c "SELECT version();" Password for user alice: psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: permission denied for database "venus" DETAIL: User does not have CONNECT privilege. postgres@anvil:~$ postgres@anvil:~$ psql venus -h localhost -U carol --csv -c "SELECT version();" Password for user carol: version "PostgreSQL 14.2 (Ubuntu 14.2-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit"
余談: public スキーマについて
ところで、PostgreSQL 14 の仕様では新規作成直後のデータベースにおける public
スキーマに対して、あらゆるユーザー (PUBLIC) への CREATE と USAGE
の許可が与えられる。
今回は各プロジェクトのデータ分析担当者が自由にテーブルやユーザー定義関数等を
作れる方が望ましいと考えて標準状態のままとしておきたい。
けれど、もし Web アプリケーション等で使うために構築するのであれば
安全な設定に変更しておいた方が良いだろう。
…データベースの素人である自分には適切な例が出せないけれど、
きっと SQL インジェクション等の脆弱性問題が見つかり、
攻撃されたときの被害を少し軽減できる等の話になるのだと思っている。
参考までに SQL 例を書いておくと、前述の許可設定を取り除くのが次で:
REVOKE ALL ON SCHEMA public FROM PUBLIC;
改めて特定の ROLE に CREATE 等の権限を許可するのが以下 （下記の例では "ALL"、つまりあらゆる操作を許可している）:
GRANT ALL ON SCHEMA public TO a_role_which_can_manage_the_schema;
あとがき
pg_hba.conf についても調べてはあるものの、
もう少しちゃんと整理しておかないと書き残せないな。
今週は時間切れ。ここまで。
