データベースフィルドの書き換え

【書き換え】
select Fxxxx,replace( Fxxxx,'D:','C:') from Schema.Table
where Fxxxx like 'D:\%'

update Schema.Table set Fxxx = replace(Fxxxx,'D:','C:')
where Fxxxx like 'D:\%'

【email マスク】

SELECT
email,
LOWER(
LEFT(account_part, 1) ||
REPEAT('*', GREATEST(LENGTH(account_part) - 2, 1)) ||
RIGHT(account_part, 1) ||'@' || domain_part
) AS masked_email
FROM (
SELECT
email,
SPLIT_PART(email, '@', 1) AS account_part,
SPLIT_PART(email, '@', 2) AS domain_part
FROM service."Users_user"
) AS sub;

 

 UPDATE "Users_user"
SET email = LOWER(
  LEFT(account_part, 1) ||
  REPEAT('*', GREATEST(LENGTH(account_part) - 2, 1)) ||
  RIGHT(account_part, 1) ||
  '@' || domain_part
)
FROM (
  SELECT
    uuid,    -- 主キー
    SPLIT_PART(email, '@', 1) AS account_part,
    SPLIT_PART(email, '@', 2) AS domain_part
  FROM ”Users_user"
) AS sub
WHERE "Users_User".uuid = sub.uuid;

 

  ① バックアップ列 email_original を作成
ALTER TABLE "Users_User"
ADD COLUMN email_original TEXT;

 ② 元の email を email_original にコピーしてからマスキング

UPDATE "Users_User"
SET
  email_original = email,
  email = LOWER(
    LEFT(account_part, 1) ||
    REPEAT('*', GREATEST(LENGTH(account_part) - 2, 1)) ||
    RIGHT(account_part, 1) ||
    '@' || domain_part
  )
FROM (
  SELECT
    uuid,
    SPLIT_PART(email, '@', 1) AS account_part,
    SPLIT_PART(email, '@', 2) AS domain_part
  FROM "Users_User"
) AS sub
WHERE "Users_User".id = sub.id;

【氏名】

SELECT
  first_name,
  CASE
    WHEN LENGTH(first_name) <= 2 THEN
      SUBSTRING(first_name, 1, 1) || REPEAT('＊', LENGTH(first_name) - 1)
    ELSE
      SUBSTRING(first_name, 1, 1) ||
      REPEAT('＊', LENGTH(first_name) - 2) ||
      SUBSTRING(first_name, LENGTH(first_name), 1)
  END AS masked_name
FROM service."Users_user";

UPDATE "Users_user"
SET first_name = CASE
  WHEN LENGTH(first_name) <= 2 THEN
    SUBSTRING(first_name, 1, 1) || REPEAT('＊', LENGTH(first_name) - 1)
  ELSE
    SUBSTRING(first_name, 1, 1) ||
    REPEAT('＊', LENGTH(first_name) - 2) ||
    SUBSTRING(full_name, LENGTH(first_name), 1)
END;

【電話】

SELECT
  tel,
  CASE
    WHEN LENGTH(tel) >= 11 THEN
      SUBSTRING(tel, 1, 3) ||
      REPEAT('*', LENGTH(tel) - 7) ||
      SUBSTRING(tel, LENGTH(tel) - 3, 4)
    ELSE
      tel
  END AS masked_tel
FROM "Users_user";

UPDATE "Users_user"
SET tel =
  CASE
    WHEN LENGTH(tel) >= 11 THEN
      SUBSTRING(tel, 1, 3) ||
      REPEAT('*', LENGTH(tel) - 7) ||
      SUBSTRING(tel, LENGTH(tel) - 3, 4)
    ELSE
      tel
  END;

 

（ハイフンなしをハイフォン付きで表示）

SELECT
  tel,
  CASE
    WHEN tel ~ '^\+81\d{9,10}$' THEN
      -- +81xxx-xxxx-xxxx の形式に変換（+81から始まる場合）
      '+81-' || SUBSTRING(tel, 4, 1) || '-' ||
      SUBSTRING(tel, 5, 4) || '-' ||
      SUBSTRING(tel, 9, 4)
    WHEN LENGTH(tel) = 11 THEN
      -- 09012345678 を 090-1234-5678 の形式に変換
      SUBSTRING(tel, 1, 3) || '-' ||
      SUBSTRING(tel, 4, 4) || '-' ||
      SUBSTRING(tel, 8, 4)
    WHEN LENGTH(tel) = 10 THEN
      -- 03-1234-5678 のような10桁の番号に対応
      SUBSTRING(tel, 1, 2) || '-' ||
      SUBSTRING(tel, 3, 4) || '-' ||
      SUBSTRING(tel, 7, 4)
    ELSE
      tel -- それ以外はそのまま表示
  END AS formatted_tel
FROM "Users_User";

【住所】

SELECT
  address,
  CASE
    WHEN address ~ '^.{3,}' THEN
      SUBSTRING(address, 1, 3) || REPEAT('＊', LENGTH(address) - 3)
    ELSE
      address
  END AS masked_address
FROM "Users_User";
 

UPDATE "Users_User"
SET address = CASE
  WHEN address ~ '^.{3,}' THEN
    SUBSTRING(address, 1, 3) || REPEAT('＊', LENGTH(address) - 3)
  ELSE
    address
END;

【秘密情報らしいフィールドの検索】
SELECT
  table_schema,
  table_name,
  column_name,
  data_type
FROM
  information_schema.columns
WHERE
  column_name ILIKE ANY (
    ARRAY[
      '%name%',     -- 氏名, full_name, last_name, etc
      '%email%',    -- email, e_mail
      '%tel%',      -- tel, telephone, tel_no
      '%phone%',    -- phone_number
      '%address%'   -- address, home_address
    ]
  )
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY
  table_schema, table_name;

 

 

-- 例: Users_User テーブルの full_name をマスクするSQLを出力
SELECT
  'UPDATE "' || table_name || '" SET "' || column_name || '" = ' ||
  CASE
    WHEN column_name ILIKE '%name%' THEN
      'CASE WHEN LENGTH("' || column_name || '") >= 3 THEN ' ||
      'SUBSTRING("' || column_name || '", 1, 1) || REPEAT(''＊'', LENGTH("' || column_name || '") - 2) || ' ||
      'SUBSTRING("' || column_name || '", LENGTH("' || column_name || '"), 1) ELSE "' || column_name || '" END'

    WHEN column_name ILIKE '%email%' THEN
      'CASE WHEN POSITION(''@'' IN "' || column_name || '") > 2 THEN ' ||
      'SUBSTRING("' || column_name || '", 1, 1) || REPEAT(''＊'', POSITION(''@'' IN "' || column_name || '") - 2) || ' ||
      'SUBSTRING("' || column_name || '", POSITION(''@'' IN "' || column_name || '") - 1) ELSE "' || column_name || '" END'

    WHEN column_name ILIKE '%tel%' OR column_name ILIKE '%phone%' THEN
      'CASE WHEN LENGTH("' || column_name || '") >= 11 THEN ' ||
      'SUBSTRING("' || column_name || '", 1, 3) || ''-＊＊＊＊-'' || SUBSTRING("' || column_name || '", 8, 4) ELSE "' || column_name || '" END'

    WHEN column_name ILIKE '%address%' THEN
      'CASE WHEN LENGTH("' || column_name || '") > 3 THEN ' ||
      'SUBSTRING("' || column_name || '", 1, 3) || REPEAT(''＊'', LENGTH("' || column_name || '") - 3) ELSE "' || column_name || '" END'
      
    ELSE
      '"' || column_name || '"'
  END || ';'
AS mask_sql
FROM information_schema.columns
WHERE
  column_name ILIKE ANY (
    ARRAY['%name%', '%email%', '%tel%', '%phone%', '%address%']
  )
  AND table_schema = 'public' -- 必要なら変更
ORDER BY table_name, column_name;