🦁

PosrgresでのUUID,RLSの設定+外部キー設定

2024/09/01に公開

SQL

UUIDの設定

UUIDは、一意の識別子を生成するために広く使用されます。特に、複数のシステム間でデータを同期したり、複数のテーブル間で一意性を保つために有効です。PostgreSQLでは、UUIDをサポートしており、テーブルの主キーや他のカラムに使用することができます。

UUIDの設定
UUIDデータ型を使用するためには、PostgreSQLのuuid-ossp拡張機能を有効にする必要があります。

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

これで、uuid_generate_v4()関数を使用して、UUIDを生成することができるようになります。

テーブルでUUIDを使用
例えば、先ほどの商品テーブルでUUIDを主キーとして使用する場合、以下のように設定します。

CREATE TABLE 商品 (
    商品ID UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    商品名 VARCHAR(255) NOT NULL,
    数量 INTEGER NOT NULL CHECK (数量 >= 0),
    所有者 TEXT,
    カテゴリ VARCHAR(255)
);

ここで、商品IDはUUID型であり、デフォルトでuuid_generate_v4()関数を使って自動的に生成されます。

RLS（Row Level Security）の設定

RLSを使用すると、特定のユーザーがアクセスできる行を制限することができます。これにより、データベース全体に対するアクセス制御を細かく管理することが可能になります。

RLSを有効にする
まず、RLSを有効にするテーブルを指定します。ここでは、商品テーブルに対してRLSを有効にします。

ALTER TABLE 商品 ENABLE ROW LEVEL SECURITY;

ポリシーの作成
次に、特定のユーザーがどの行にアクセスできるかを定義するポリシーを作成します。例えば、ユーザーが自身が所有する商品のみを閲覧できるようにするには、以下のように設定します。

CREATE POLICY 商品閲覧ポリシー ON 商品
    FOR SELECT
    USING (所有者 = current_user);

このポリシーでは、所有者が現在のデータベースユーザーと一致する行のみが選択（SELECT）できるように制限しています。

外部キー設定

ユーザー認証テーブルの作成
まず、ユーザーのログイン情報を保存するためのユーザー認証テーブルを作成します。このテーブルには、ユーザー名(ユーザー名)とパスワード(パスワード)を保存します。

CREATE TABLE ユーザー認証 (
    ID SERIAL PRIMARY KEY,
    ユーザー名 VARCHAR(255) UNIQUE NOT NULL,
    パスワード VARCHAR(255) NOT NULL
);

商品テーブルの作成
次に、Todoリストの各項目を保存する商品テーブルを作成します。ここでは、所有者カラムを追加し、このカラムをユーザー認証テーブルのユーザー名カラムと連携させます。

CREATE TABLE 商品 (
    商品名 VARCHAR(255) NOT NULL,
    数量 INTEGER NOT NULL CHECK (数量 >= 0),
    所有者 TEXT,
    ID UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    カテゴリ VARCHAR(255)
);

テーブルの連携 (FOREIGN KEY 制約の設定)
商品テーブルの所有者カラムをユーザー認証テーブルのユーザー名カラムに連携させるため、FOREIGN KEY制約を設定します。

ALTER TABLE 商品
ADD CONSTRAINT fk_所有者
FOREIGN KEY (所有者) REFERENCES ユーザー認証(ユーザー名);

データの挿入と動作確認
次に、各テーブルにデータを挿入し、設定した外部キー制約が正しく機能するか確認します。

4.1 正常なデータの挿入
まず、ユーザー認証テーブルにユーザー情報を挿入します。

INSERT INTO ユーザー認証 (ユーザー名, パスワード) VALUES
('佐藤', 'secure_password_hash_for_sato'),
('田中', 'secure_password_hash_for_tanaka');

次に、商品テーブルに関連するデータを挿入します。

INSERT INTO 商品 (商品名, 数量, 所有者, ID, カテゴリ) 
VALUES ('サンプル商品', 10, '佐藤', gen_random_uuid(), '家電');

UUIDの設定

RLS（Row Level Security）の設定

外部キー設定

Discussion