🚨

セキュリティポリシーの定義や複合ユニーク制約を強制したりするテストコードの書き方

まったん

2024/12/03に公開

※この記事は「COUNTERWORKS Advent Calendar」の3日目の記事です。

はじめに

株式会社COUNTERWORKSでソフトウェアエンジニアをしているまったんです。
最近弊社ではシングルテナントアーキテクチャのアプリをマルチテナントアーキテクチャへと変更しています。

マルチテナント化するにあたり、全テーブルにテナントの識別値(以降tenant_idと表記します)を追加したり、PostgreSQLのRow Level Security(以降RLSと表記します)を使い他テナントのレコードを操作できないようにするためのセキュリティポリシーを全てのテーブルに定義しRLSを有効化したり、全てのユニーク制約をtenant_idとの複合ユニーク制約にしたりしました。

現状の実装方法だと、今後追加される全てのテーブルにRLSセキュリティポリシーを定義することが必須となり、その対応が漏れた場合は他テナントのデータを操作できてしまう可能性があるというリスクがあったり、今後追加される全てのユニーク制約はtenant_idとの複合ユニーク制約であることが必須となり、その対応が漏れた場合はユーザーには解決不可能な値の重複エラーを起こしてしまうリスクがあります。

そのリスクを小さくするために試行錯誤した結果、全てのテーブルにRLSセキュリティポリシーが存在することや、全てのユニーク制約がtenant_idとの複合ユニーク制約であることを強制するテストコードを書くことができたのでシェアしたいと思います。
以下はRSpecを使ったテストコードの例ですが、SQLが実行できればどのテスティングフレームワークでも検証可能だと思います。

全てのテーブルにセキュリティポリシーが存在することを検証する

※セキュリティポリシーの内容とポリシー名は全てのテーブルで同じであるということを前提としており、このテストではセキュリティポリシーの内容までは検証しません

セキュリティポリシーの定義の有無は以下のようなSQLで確認可能です。

SELECT *
FROM pg_policies
WHERE tablename = 'テーブル名' AND policyname = 'セキュリティポリシー名'

RSpecでは次のようなコードで全てのテーブルにセキュリティポリシーが定義されていることを検証可能です。

it do
  connection = ActiveRecord::Base.connection
  tables = connection.tables - %w[schema_migrations ar_internal_metadata]

  tables.each do |table|
    existing_policy = connection.select_all(
      <<~SQL
        SELECT EXISTS (
          SELECT *
          FROM pg_policies
          WHERE tablename = '#{table}' AND policyname = 'セキュリティポリシー名'
        )
      SQL
    )
    expect(existing_policy.to_a[0]['exists']).to eq true
  end
end

このテストがパスしない場合は、セキュリティポリシーが定義されているテーブルが存在するということになります。

pg_policiesはRLSセキュリティポリシーに関する情報を提供しているビューです。
pg_policiesはrolesやwith_checkといったカラムがあり、上記の例より詳細な検証も可能となっています。
参考:

全てのテーブルでセキュリティポリシーが有効化されていることを検証する

セキュリティポリシーが有効になっているかは以下のようなSQLで確認可能です。

SELECT relrowsecurity
FROM pg_class
WHERE relname = 'テーブル名'

RSpecでは次のようなコードで全てのテーブルでセキュリティポリシーが有効になっていることを検証可能です。

it do
  connection = ActiveRecord::Base.connection
  tables = connection.tables - %w[schema_migrations ar_internal_metadata]

  tables.each do |table|
    enabled_rls = connection.select_all(
      <<~SQL
        SELECT relrowsecurity
        FROM pg_class
        WHERE relname = '#{table}'
      SQL
    )
    expect(enabled_rls.to_a[0]['relrowsecurity']).to eq true
  end
end

このテストがパスしない場合は、セキュリティポリシーが有効化されていないテーブルが存在するということになります。

pg_classはデータベース内のテーブル、ビュー、インデックス、シーケンスなどのメタデータが入ったテーブルです。
relrowsecurityはRLSが有効なテーブルではtrue、無効なテーブルではfalseが入っています。
参考:

全てのユニーク制約がtenant_idとの複合ユニーク制約であることを検証する

tenant_idとの複合ユニーク制約になっていないユニーク制約の有無は以下のようなSQLで確認可能です。

SELECT
    index_class.relname AS index_name
FROM
    pg_index
JOIN
    pg_class table_class ON table_class.oid = pg_index.indrelid
JOIN
    pg_namespace ON pg_namespace.oid = table_class.relnamespace
JOIN
    pg_class index_class ON index_class.oid = pg_index.indexrelid
JOIN
    pg_attribute ON pg_attribute.attnum = ANY(pg_index.indkey) AND pg_attribute.attrelid = table_class.oid
WHERE
    pg_index.indisunique = TRUE
    AND pg_index.indisprimary = FALSE
    AND pg_namespace.nspname = 'public'
GROUP BY
    index_class.relname
HAVING
    bool_or(pg_attribute.attname = 'tenant_id') = FALSE;

RSpecでは次のようなコードでtenant_idとの複合ユニーク制約になっていないユニーク制約が存在しないことを検証可能です。

it do
  index_names = ActiveRecord::Base.connection.select_all(
    <<~SQL
      SELECT
          index_class.relname AS index_name
      FROM
          pg_index
      JOIN
          pg_class table_class ON table_class.oid = pg_index.indrelid
      JOIN
          pg_namespace ON pg_namespace.oid = table_class.relnamespace
      JOIN
          pg_class index_class ON index_class.oid = pg_index.indexrelid
      JOIN
          pg_attribute ON pg_attribute.attnum = ANY(pg_index.indkey) AND pg_attribute.attrelid = table_class.oid
      WHERE
          pg_index.indisunique = TRUE
          AND pg_index.indisprimary = FALSE
          AND pg_namespace.nspname = 'public'
      GROUP BY
          index_class.relname
      HAVING
          bool_or(pg_attribute.attname = 'tenant_id') = FALSE;
    SQL
  )
  expect(index_names.count).to eq 0
end

このテストがパスしない場合は、tenant_idとの複合ユニーク制約になっていないユニーク制約が存在するということになります。

pg_indexはデータベース内のインデックスに関する情報を提供しているシステムカタログテーブルで、pg_indexはindisuniqueやindisprimaryといったカラムを持ち、一意制約やプライマリキーの特性を判定できます。
pg_namespaceはデータベース内のスキーマ情報を管理するシステムカタログで、nspnameカラムを利用して、対象をpublicスキーマ内のインデックスに限定しています。これにより、アプリケーションで使用するテーブルやインデックスに絞った検証を行うことができます。
pg_attributeはデータベース内の列情報を管理するシステムカタログテーブルで、attnameカラムを利用してインデックスにtenant_id列が含まれているかどうかを判定しています。
参考:

終わりに

PostgreSQLのカタログテーブル等を使うと制約やセキュリティポリシーなどのテストコードが書けることをお伝えしました。
ここで書いたことを応用し、インデックスを作成させることを強制させたり、インデックスや制約の命名規則なんかを強制させたりすることも可能です。読者の方が死守したいルールを守るためのヒントになれば幸いです。

株式会社カウンターワークスでは、様々なルールを守り続けるための工夫をしながら事業を前進させるメンバーを募集しています！
興味のある方はぜひ以下のリンクからご応募ください！

COUNTERWORKS テックブログPublication

ポップアップストアや催事イベント向けの商業スペースを簡単に予約できる「SHOPCOUNTER」と商業施設向けリーシングDXシステム「SHOPCOUNTER Enterprise」を運営しています。エンジニア採用強化中ですので、興味ある方はお気軽にご連絡ください！ counterworks.co.jp/