データベースの所有権とデフォルトの振る舞い

PostgreSQLにおいて、データベースを作成したユーザーがそのデータベースの所有者（オーナー）となります。データベースの作成には CREATEDB 権限が必要ですが、所有者であっても、そのデータベース内に他のユーザーが作成したオブジェクトへのアクセス権限を自動的に持つわけではありません。

データベース作成時のデフォルトのセキュリティとアクセス設定は以下の通りです：

• 接続権限: 作成直後は public ロールに対して接続権限が与えられており、原則として誰でも接続可能です。
• スキーマ作成制限: スーパーユーザーとデータベース所有者以外のユーザーは、デフォルトでは新しいスキーマを作成できません。
• publicスキーマ: 自動的に public スキーマが生成され、public ロールに対してすべての権限（ALL）が付与されます。これにより、誰でもこのスキーマ内にオブジェクトを作成できますが、他ユーザーが作成した既存のテーブルに対する権限は持ちません。

データベースレベルの権限管理

データベースに対する権限は、以下の構文で付与または取り消しを行います。

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE target_database [, ...]
    TO target_role [, ...] [ WITH GRANT OPTION ];

各権限の役割は以下の通りです：

• CREATE: データベース内に新しいスキーマを作成する権限。
• CONNECT: データベースに接続する権限。
• TEMPORARY (または TEMP): 一時テーブルを作成する権限。
• ALL: 上記すべての権限。

接続権限の剥奪と制御

デフォルトでは public ロールに接続権限があるため、特定のユーザーの接続を制限するには、まず public から接続権限を剥奪する必要があります。

-- publicロールからの接続権限剥奪
REVOKE CONNECT ON DATABASE app_db FROM public;

-- 特定のユーザーへの接続権限付与
GRANT CONNECT ON DATABASE app_db TO app_user;

-- 特定のユーザーからの接続権限剥奪
REVOKE CONNECT ON DATABASE app_db FROM app_user;

特定のデータベースに対する接続権限を持つユーザーを確認するには、システムカタログをクエリします。

SELECT datname, datacl 
FROM pg_database 
WHERE datname = 'app_db';

データベース固有の環境パラメータ設定

PostgreSQLの設定パラメータは、インスタンス、データベース、ユーザー、セッションの4つのレベルで管理されます。優先順位は「セッション ＞ ユーザー ＞ データベース ＞ インスタンス」の順に高くなります。

ALTER DATABASE db_name SET config_param = 'value';
ALTER DATABASE db_name SET config_param FROM CURRENT;
ALTER DATABASE db_name RESET config_param;
ALTER DATABASE db_name RESET ALL;

主要なパラメータ設定例

以下は、特定のデータベースに接続した際のみ適用されるパラメータのカスタマイズ例です。

-- 1. スキーマ検索パスの拡張
ALTER DATABASE analytics_db SET search_path TO "$user", public, reporting, raw_data;

-- 2. クエリ実行用のワークメモリ割り当て (16MBに設定)
ALTER DATABASE analytics_db SET work_mem = '16MB';

-- 3. VACUUMやインデックス作成用のメンテナンスメモリ (512MBに設定)
ALTER DATABASE analytics_db SET maintenance_work_mem = '512MB';

-- 4. ステートメントのタイムアウト設定 (3秒 = 3000ミリ秒)
ALTER DATABASE analytics_db SET statement_timeout = 3000;

-- 5. ログ出力レベルの抑制 (このDBへのアクセスログを記録しない)
ALTER DATABASE analytics_db SET log_statement = 'none';

-- 6. WALの同期コミットレベル (ローカルディスクへの書き込みのみで完了とする)
ALTER DATABASE analytics_db SET synchronous_commit = 'local';

-- 7. 特定のクエリプランナーの無効化 (Index Only Scanをオフにする)
ALTER DATABASE analytics_db SET enable_indexonlyscan = off;

データベースに設定されたカスタムパラメータ一覧は、psqlのメタコマンド \drds で確認できます。また、接続数制限（datconnlimit）などの属性はシステムカタログから取得します。

SELECT datname, datconnlimit FROM pg_database;

設定をデフォルトに戻す場合は RESET を使用します。

-- 特定のパラメータをリセット
ALTER DATABASE analytics_db RESET exit_on_error;

-- すべてのカスタム設定をリセット
ALTER DATABASE analytics_db RESET ALL;

データベースのメタデータ属性変更

作成済みのデータベースについて、名前、所有者、表領域（テーブルスペース）といった属性を変更することが可能です。

-- データベース名の変更
ALTER DATABASE old_db_name RENAME TO new_db_name;

-- 所有者の変更
ALTER DATABASE target_db OWNER TO new_admin_role;

-- 表領域の移行
ALTER DATABASE target_db SET TABLESPACE new_storage_space;

実践的なデータベース構築と権限付与のフロー

以下は、新規ユーザーと専用表領域を作成し、それらを紐付けたデータベースを構築する一連のオペレーション例です。

-- 1. 専用ロール（ユーザー）の作成
postgres=# CREATE ROLE sales_admin WITH LOGIN ENCRYPTED PASSWORD 'secure_password_123';
CREATE ROLE

-- 2. OSレベルでの表領域用ディレクトリ作成
[postgres@db-server ~]$ mkdir -p /var/lib/postgresql/tablespaces/sales_data

-- 3. 表領域の定義
postgres=# CREATE TABLESPACE sales_storage 
           OWNER sales_admin 
           LOCATION '/var/lib/postgresql/tablespaces/sales_data';
CREATE TABLESPACE

-- 4. データベースの作成（テンプレート、エンコーディング、表領域を指定）
postgres=# CREATE DATABASE sales_db
           WITH OWNER = sales_admin
           TEMPLATE = template0
           ENCODING = 'UTF8'
           TABLESPACE = sales_storage;
CREATE DATABASE

-- 5. 作成したリソースに対する権限の付与
postgres=# GRANT ALL PRIVILEGES ON DATABASE sales_db TO sales_admin WITH GRANT OPTION;
postgres=# GRANT ALL PRIVILEGES ON TABLESPACE sales_storage TO sales_admin;