雑記帳

■ [PostgreSQL] PostgreSQLでE-mailアドレスのドメインを追加する

PostgreSQLは、ドメイン（いわゆるデータ型）を定義することができる。よく使うデータの代表として、E-mailアドレスがある。これをドメインとして、定義したいと思う。

E-mailをチェックする方法として、いくつか方法があると思う。今回は下記のページを参考にしてやってみる。

http://stackoverflow.com/questions/5689718/how-can-i-create-a-constraint-to-check-if-an-email-is-valid-in-postgres

ここでは、PostgreSQLの拡張であるplperluからPerl ModuleであるEmail::Addressを使ってチェックしている。

今回利用する環境は、CentOS 7に http://yum.postgresql.org/ のyumリポジトリからPostgreSQL 9.4をインストールしている。

Perlモジュールをインストールする

PerlのEmail::Addressを使うため、先にインストールしておく。

$ sudo yum install -y perl-Email-Address

plperluをインストールする

PostgreSQLでPerlを使うための拡張である、plperluをインストールする。

$ sudo yum install postgresql94-plperl

インストールできたら、PostgreSQLを再起動する。

$ sudo systemctl restert postgresql-9.4

インストールできたのかを確認する。

$ psql -Upostgres
postgres=> select * from pg_available_extensions;
  name   | default_version | installed_version |                comment
---------+-----------------+-------------------+----------------------------------------
 plpgsql | 1.0             | 1.0               | PL/pgSQL procedural language
 plperl  | 1.0             | 1.0               | PL/Perl procedural language
 plperlu | 1.0             |                   | PL/PerlU untrusted procedural language
(3 行)

plperluが含まれているので、利用できるようだ。

データベースで拡張を使えるようにする

今回はexampleデータベースで使う。

example=# create extension plperlu;
CREATE EXTENSION

拡張の一覧を確認する。

example=# \dx
                        インストール済みの拡張の一覧
  名前   | バージョン |  スキーマ  |                  説明
---------+------------+------------+----------------------------------------
 plperl  | 1.0        | pg_catalog | PL/Perl procedural language
 plperlu | 1.0        | pg_catalog | PL/PerlU untrusted procedural language
 plpgsql | 1.0        | pg_catalog | PL/pgSQL procedural language
(3 行)

plperluの他にも、plperl, plpgsqlが利用できる状態であるようだ。

E-mailをチェックする関数を作成する

下記のSQLを実行する。

CREATE OR REPLACE FUNCTION check_email(email text) RETURNS bool LANGUAGE plperlu AS $$
  use Email::Address;
  my @addresses = Email::Address->parse($_[0]);
  return scalar(@addresses) > 0 ? 1 : 0;
$$;

問題なければ、関数として登録される。

example=# \df
                                 関数一覧
 スキーマ |    名前     | 結果のデータ型 | 引数のデータ型 |       型
----------+-------------+----------------+----------------+----------------
 public   | check_email | boolean        | email text     | normal（通常）
(1 行)

ドメインを作成する

ここまでで、やっとドメインを作る準備ができた。CREATE DOMAINを実行して、ドメインを作成する。

example=# create domain email_address AS text check (check_email(VALUE));
CREATE DOMAIN

問題ないか確認する。

example=# \dD
                                       ドメイン一覧
 スキーマ |        名前        |   型   |  修飾語  |                チェック
----------+--------------------+--------+----------+----------------------------------------
 public   | email_address      | text   |          | CHECK (check_email(VALUE))
(1 行)

試す

試しにテーブルを作成して、動作を確認する。

example=# create table emails (email_address email_address);
CREATE TABLE

紛らわしいSQLになってしまった。まぁいい。データを投入してみる。

example=# insert into emails (email_address) values ('test@example.com');
INSERT 0 1

入った。では、E-mailアドレスではないものを入れてみる。

adminpack_development=# insert into emails (email_address) values ('test.@example.com');
ERROR:  ドメインemail_addressの値が検査制約"email_address_check"に違反しています

ちゃんとエラーになった！ 素晴らしい。

これで、E-mailが必要な場面で、アプリによるE-mailを検証する実装が妥当でなくても、データベースを守ることができる。

■ [AWS] ap-northeast-2 リージョンのCloudTrailのログのPutを許可する

本日、ap-northeast-2リージョンが新しく利用可能になった。CloudTrailを有効にしようとしたらエラーになってしまった。

Incorrect S3 bucket policy is detected for bucket: iret.cloudtrail.logs (Aws::CloudTrail::Errors::InsufficientS3BucketPolicyException)

S3 Bucket Policyに、適切な権限の設定が必要だった。

CloudTrail Supported Regions を見ると、ap-northeast-2のAWS Account ID *1 が記載されている。Principalとして、下記のARNを指定する。

arn:aws:iam::492519147666:root

Policyは、以下のようになる。

{
   "Version": "2012-10-17",
   "Statement": [
       {
           "Effect": "Allow",
           "Principal": {
               "AWS": [
                   "arn:aws:iam::903692715234:root",
                   "arn:aws:iam::035351147821:root",
                   "arn:aws:iam::859597730677:root",
                   "arn:aws:iam::814480443879:root",
                   "arn:aws:iam::216624486486:root",
                   "arn:aws:iam::086441151436:root",
                   "arn:aws:iam::388731089494:root",
                   "arn:aws:iam::284668455005:root",
                   "arn:aws:iam::113285607260:root",
                   "arn:aws:iam::492519147666:root"
               ]
           },
           "Action": "s3:GetBucketAcl",
           "Resource": "arn:aws:s3:::your-bucket-name"
       },
       {
           "Effect": "Allow",
           "Principal": {
               "AWS": [
                   "arn:aws:iam::903692715234:root",
                   "arn:aws:iam::035351147821:root",
                   "arn:aws:iam::859597730677:root",
                   "arn:aws:iam::814480443879:root",
                   "arn:aws:iam::216624486486:root",
                   "arn:aws:iam::086441151436:root",
                   "arn:aws:iam::388731089494:root",
                   "arn:aws:iam::284668455005:root",
                   "arn:aws:iam::113285607260:root",
                   "arn:aws:iam::492519147666:root"
               ]
           },
           "Action": "s3:PutObject",
           "Resource": "arn:aws:s3:::your-bucket-name/AWSLogs/*/*",
           "Condition": {
               "StringEquals": {
                   "s3:x-amz-acl": "bucket-owner-full-control"
               }
           }
       }
   ]