0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ktamido(緑川 京太)in株式会社BeeX

【PostgreSQL ふりかえり】2. データベース・スキーマ・ユーザー

0
Posted at

今まで適当にPostgreSQLを使ってきたので、学び直しの意味も兼ねて「完全攻略 PostgreSQL: 現場で使える最強データベース入門 」という書籍を読みました。
振り返りとして、自分なりにPostgreSQLの各種機能や使い方をまとめてみます。

作業リポジトリはこちら:

データベース・スキーマ・ユーザーの関係性

PostgreSQLサーバー
|- データベース (Database)
| |- スキーマ (Schema)
|   |- テーブル (Table)
|   |- ビュー (View)
|   |- 関数 (Function)
|   |- インデックス (Index)
|- ユーザー (User/Role)

データベースの概念

PostgreSQLにおけるデータベースは、データの集合とその管理環境を指します。
1つのPostgreSQLサーバーには複数のデータベースを作成でき、各データベースは独立したデータ管理領域を持ちます。データベース間で直接テーブルやデータを共有することはできません。

特徴

  • 独立性: データベース間は完全に独立しており、スキーマやテーブルは共有されない
  • アクセス制御: 接続ユーザーごとにアクセスできるデータベースを制限可能
  • 用途分離: 開発環境、本番環境、テスト環境などで異なるデータベースを作成し、運用できる。
-- データベースの作成
CREATE DATABASE sample_db;

-- データベース一覧
\l

スキーマの概念

スキーマはデータベース内のオブジェクト(テーブル、ビュー、関数、インデックスなど)を整理する名前空間です。
スキーマで名前空間を分けることで、同じ名前のテーブルを複数作成することができます。

PostgreSQLにはデフォルトで public というスキーマが存在し、データベース作成時に自動で付与されます。複雑な設計の場合、用途別にスキーマを分割することが推奨されます。

例:

  • public 汎用的なテーブル
  • sales 売上関連データ
  • hr 人事関連データ
-- スキーマの作成
CREATE SCHEMA sales;

-- スキーマの確認
\dn
/*
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   | 
 sales  | app               |                                        | 
(2 rows)
*/

-- スキーマを指定してテーブルを作成
CREATE TABLE IF NOT EXISTS sales.orders (
  id SERIAL PRIMARY KEY,
  order_date DATE NOT NULL,
  amount NUMERIC(10, 2) NOT NULL
);

スキーマを追加しただけだと、スキーマが検索パス(search_path) に含まれていないため、\d などの一覧に表示されません。

\d
/*
            List of relations
 Schema |     Name     |   Type   | Owner 
--------+--------------+----------+-------
 public | users        | table    | app
 public | users_id_seq | sequence | app
(2 rows)
*/

スキーマ名.* をつけると表示されます。

\d sales.*
/*
                                     Table "sales.orders"
   Column   |     Type      | Collation | Nullable |                 Default                  
------------+---------------+-----------+----------+------------------------------------------
 id         | integer       |           | not null | nextval('sales.orders_id_seq'::regclass)
 order_date | date          |           | not null | 
 amount     | numeric(10,2) |           | not null | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)

                    Sequence "sales.orders_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: sales.orders.id

      Index "sales.orders_pkey"
 Column |  Type   | Key? | Definition 
--------+---------+------+------------
 id     | integer | yes  | id
primary key, btree, for table "sales.orders"
*/

もしくは、検索パスに sales スキーマを追加することで表示されるようになります。
※ 検索パスはテーブル名が指定されたときにどのスキーマから探すかというリストです。 (ログインするたびに設定が必要なので、 .psqlrc に書いておくと良い))

-- 現在の検索パスの設定を表示
SHOW search_path;
--    search_path   
-- -----------------
--  "$user", public
-- (1 row)

-- 検索パスにsalesを追加
SET search_path TO sales, public;
-- SET

SHOW search_path;
--   search_path  
-- ---------------
--  sales, public
-- (1 row)

-- salesスキーマ内のテーブルが表示される
\d
--              List of relations
--  Schema |     Name      |   Type   | Owner 
-- --------+---------------+----------+-------
--  public | users         | table    | app
--  public | users_id_seq  | sequence | app
--  sales  | orders        | table    | app
--  sales  | orders_id_seq | sequence | app
-- (4 rows)

ユーザーの概念

PostgreSQLにおけるユーザー(あるいはロール) は、データベースへの接続や操作の権限を持つ主体です。
ユーザーはログイン可能なロールとして定義され、必要に応じて権限を付与できます。

一般的なユーザーの作成

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'hogehoge';
CREATE ROLE app_user      WITH LOGIN PASSWORD 'hogehoge';
CREATE ROLE dev_user      WITH LOGIN PASSWORD 'hogehoge';

ユーザー一覧の確認

\du
/*
   Role name   |                         Attributes                         
---------------+------------------------------------------------------------
 admin_user    | Superuser
 app           | Superuser, Create role, Create DB, Replication, Bypass RLS
 app_user      | 
 dev_user      | 
 readonly_user | 
*/

ユーザーへの権限付与

-- 読み取り専用ユーザー
GRANT CONNECT ON DATABASE sample TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- アプリケーションユーザー（CRUD操作）
GRANT CONNECT ON DATABASE sample TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- 開発者ユーザー（オブジェクト作成も可能）
GRANT CONNECT ON DATABASE sample TO dev_user;
GRANT USAGE, CREATE ON SCHEMA public TO dev_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev_user;

特権ユーザーの作成

SUPERUSERができること:

  • すべての権限チェックをバイパス
  • ほかユーザーのオブジェクトを操作・削除
  • データベースの作成・削除
  • ロールの作成・削除
  • レプリケーションの設定
  • 設定ファイルの再読み込み
  • すべてのテーブル・スキーマへのアクセス
-- 既存ユーザーにSUPERUSER属性を付与
ALTER ROLE test_user SUPERUSER;

-- 新規作成時にSUPERUSER属性を付与
CREATE ROLE admin_user WITH LOGIN SUPERUSER PASSWORD 'hogehoge';

SUPERUSER以外の特権属性について

SUPERUSER以外の特権属性には以下のようなものがあります。

ALTER ROLE admin_user WITH
  SUPERUSER    -- すべての権限チェックをバイパス
  CREATEDB     -- データベースを作成できる
  CREATEROLE   -- ロールを作成できる
  REPLICATION  -- レプリケーション接続ができる
  BYPASSRLS;   -- Row Level Securityをバイパス

特権属性の確認 (pg_roles)

ユーザーに付与した特権属性は pg_roles テーブルから確認できます。

SELECT *  FROM pg_roles WHERE rolname NOT LIKE 'pg_%';
/*
    rolname    | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid  
---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 ...
 admin_user    | t        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 16434
(5 rows)
*/

権限の確認

データベースに対する権限の確認 (pg_database)

書式 grantee=権限/grantor:

エントリ grantee 権限 grantor 解釈
=Tc/app PUBLIC（全員） T, c app TEMPORARY, CONNECT
app=CTc/app app C, T, c app CREATE, TEMPORARY, CONNECT
readonly_user=c/app readonly_user c app CONNECT
app_user=c/app app_user c app CONNECT
dev_user=c/app dev_user c app CONNECT 
SELECT datname, datacl FROM pg_database WHERE datname = 'sample';
/*
-[ RECORD 1 ]--------------------------------------------------------------------
datname | sample
datacl  | {=Tc/app,app=CTc/app,readonly_user=c/app,app_user=c/app,dev_user=c/app}
*/

スキーマに対する権限の確認 (pg_namespace)

書式 grantee=権限/grantor:

エントリ grantee
(被権限付与者)		 権限 grantor
(権限付与者)
pg_database_owner=UC/pg_database_owner pg_database_owner U (USAGE), C (CREATE) pg_database_owner
=U/pg_database_owner 全員 U (USAGE) pg_database_owner
readonly_user=U/pg_database_owner readonly_user U (USAGE) pg_database_owner
app_user=U/pg_database_owner app_user U (USAGE) pg_database_owner
dev_user=UC/pg_database_owner dev_user U (USAGE), C (CREATE) pg_database_owner 
SELECT nspname, nspacl FROM pg_namespace WHERE nspname = 'public';
/*
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------------------------
nspname | public
nspacl  | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner,readonly_user=U/pg_database_owner,app_user=U/pg_database_owner,dev_user=UC/pg_database_owner}
*/

テーブルに対する権限の確認 (information_schema.table_privileges)

SELECT
  grantee,
  table_catalog,
  table_schema,
  table_name,
  string_agg(privilege_type, ', ')
FROM
  information_schema.table_privileges
WHERE
  grantee = 'dev_user'
GROUP BY
  grantee, table_catalog, table_schema, table_name;
/*
-[ RECORD 1 ]-+--------------------------------------------------------------
grantee       | dev_user
table_catalog | sample
table_schema  | public
table_name    | users
string_agg    | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
*/

各オブジェクトに対する権限のまとめ

データベース（DATABASE）

権限 説明
CONNECT データベースに接続する。これがないとログインできない
CREATE データベース内に新しいスキーマを作成する
TEMPORARY / TEMP 一時テーブルを作成する
ALL / ALL PRIVILEGES すべての権限

スキーマ（SCHEMA）

※ 自ユーザーが作成したスキーマに対しては自動的に全権限を持ちます。他のユーザーが作成したスキーマを操作したい場合に権限設定が必要になります。

権限 説明
USAGE スキーマ内のオブジェクトにアクセスする。これがないとスキーマ内のテーブル等が見えない
CREATE スキーマ内に新しいオブジェクト（テーブル、関数など）を作成する
ALL / ALL PRIVILEGES すべての権限

テーブル（TABLE）

権限 説明
SELECT テーブルの任意の列から行を読み取る。COPY TOの使用にも必要
INSERT テーブルに新しい行を挿入する。COPY FROMの使用にも必要
UPDATE テーブルの行を更新する。実際にはSELECTも必要になることが多い（WHERE句で列を参照するため）
DELETE テーブルから行を削除する。同様にSELECTが必要になることが多い
TRUNCATE テーブルの全行を高速に削除する（DELETEより速いがロールバック不可）
REFERENCES このテーブルを参照する外部キー制約を作成する
TRIGGER このテーブルにトリガーを作成する
ALL / ALL PRIVILEGES すべての権限

シーケンス（SEQUENCE）

権限 説明
USAGE currvalとnextval関数を使用する(採番)
SELECT currval関数を使用する（現在値の取得のみ）
UPDATE setval関数を使用する（値のリセットや調整(管理用)）
ALL / ALL PRIVILEGES すべての権限

関数・プロシージャ（FUNCTION / PROCEDURE）

権限 説明
EXECUTE 関数やプロシージャを実行する。デフォルトでPUBLICに付与されている
ALL / ALL PRIVILEGES すべての権限

型（TYPE）

権限 説明
USAGE 型をテーブル列の定義や関数の引数・戻り値として使用する。デフォルトでPUBLICに付与されている
ALL / ALL PRIVILEGES すべての権限

テーブルスペース（TABLESPACE）

権限 説明
CREATE このテーブルスペース内にテーブル、インデックス、一時ファイルを作成する
ALL / ALL PRIVILEGES すべての権限

ラージオブジェクト（LARGE OBJECT）

権限 説明
SELECT ラージオブジェクトを読み取る
UPDATE ラージオブジェクトに書き込む（上書き含む）
ALL / ALL PRIVILEGES すべての権限

外部データラッパー（FOREIGN DATA WRAPPER）

権限 説明
USAGE このラッパーを使用して外部サーバーを作成する
ALL / ALL PRIVILEGES すべての権限

外部サーバー（FOREIGN SERVER）

権限 説明
USAGE このサーバーを使用して外部テーブルやユーザーマッピングを作成する
ALL / ALL PRIVILEGES すべての権限
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?