Aurora(Postgres)にRDS Proxy経由(IAM認証)でLambdaから接続する設定をcdk v2で行ったメモ

## 概要
LambdaからRDS Proxy経由でDB設定を行う手順のメモ。
RDSは[前々回](https://qiita.com/hibohiboo/items/9a6f6cf4b01927f670d0)利用したものを使う。
また、RDSへの接続は[前回](https://qiita.com/hibohiboo/items/e043ff7ab4e343e786fc)の手順でポートフォワーディングをして行う。
コード本文にパスワードを含めないほうがかっこいいと思ってRDSProxyへの接続はIAM認証を使用している。
また、Adminユーザ以外でのユーザの接続もここで試す。
cdk,lambdaともにTypescriptを使用。

[ソースコード](https://github.com/hibohiboo/aws-cdk-v2/tree/id/3)


## アーキテクチャ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99876/a386ce0d-1cb9-4e08-446d-80d527e8d446.png)

## 環境
- Windows 10
- aws-cli/2.4.7 Python/3.8.8 Windows/10 exe/AMD64 prompt/off
- aws-cdk: 2.4.0

## CDK
### 環境変数
今回利用するDB設定用の環境変数（最終系）。
ARNやエンドポイントは、RDSProxy作成後にコンソールから確認して設定しているので（Lambda用のStackを実行する直前に設定）。
Stackをまとめたり、一つのAppで複数のStackを管理したらこのあたりは不要になるかも。

プロキシARNは`arn:aws:rds-db`で指定すること。コンソールからコピーすると`arn:aws:rds`になるので注意。
○`arn:aws:rds-db:ap-northeast-1:000000:db-proxy:prx-xxx`
×`arn:aws:rds:ap-northeast-1:000000:db-proxy:prx-xxx`

<details><div>

`proxy.grantConnect(role, props.dbAdminName);`では以下のようなポリシーを持ったロールが作成される。

```json
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": "rds-db:connect",
            "Resource": "arn:aws:rds-db:ap-northeast-1:000000:dbuser:prx-xxx/rdsadmin",
            "Effect": "Allow"
        },
        {
            "Action": "rds-db:connect",
            "Resource": "arn:aws:rds-db:ap-northeast-1:000000:dbuser:prx-xxx/rdsuser",
            "Effect": "Allow"
        }
    ]
}
```


</div></details>

```diff:cdk/.env
VPC_ID=vpc-hoge
PRIVATE_SG_ID=sg-fuga
PUBLIC_SG_ID=sg-hoge
SUBNET_GROUP_NAME=SubnetGroupForAurora
PUBLIC_SUBNET_ID=subnet-hoge
DB_SECRET_NAME=db-secrets
DB_ADMIN_NAME=rdsadmin
+ DB_USER_SECRET_NAME=db-rdsuser-secrets
+ DB_USER_NAME=rdsuser
+ DB_USER_RESOURCE_ARN=プロキシARN（arn:aws:rds-db:ap-northeast-1:000000:db-proxy:prx-xxx）
+ DB_PROXY_ENDPOINT=proxy.proxy-hoge.ap-northeast-1.rds.amazonaws.com
```


### VPC設定
Lambda用のセキュリティグループを追加

```diff:cdk/lib/vpc-stack.ts
import { Aspects, Stack, StackProps, Tag, Tags } from 'aws-cdk-lib';
import { Construct } from 'constructs';
import { Peer, Port, PrivateSubnet, PrivateSubnetProps, SecurityGroup, SubnetType, Vpc } from 'aws-cdk-lib/aws-ec2';
import { SubnetGroup } from 'aws-cdk-lib/aws-rds';

const DB_PORT = 5432;

interface VpcStackProps extends StackProps {
  subnetGroupName: string
}
export class VpcStack extends Stack {
  constructor(scope: Construct, id: string, props: VpcStackProps) {
    super(scope, id, { ...props, subnetGroupName: undefined } as StackProps);
    const cidr = '10.0.0.0/16';
    const vpc = new Vpc(this, 'VPC', {
      cidr,
      natGateways: 0, // デフォルトは1。
      subnetConfiguration: [
        {
          cidrMask: 24,
          name: "public-subnet",
          subnetType: SubnetType.PUBLIC
        },
      ], // デフォルトはAZごとに１つのパブリックサブネットと１つのプライベートサブネット
      maxAzs: 3 // 3を指定すると、 public: 10.0.0.0 - 10.0.0.2 の3つ分のcidrBlockが使われる。
    })
    // Tags.of(vpc).add('Stack', id);
    Tags.of(vpc).add('Name', 'vpc');

    // プライベートSubnet（RDS用）
    const privateSubnetProps: PrivateSubnetProps[] = [
      { availabilityZone: 'ap-northeast-1a', vpcId: vpc.vpcId, cidrBlock: '10.0.3.0/24' },
      { availabilityZone: 'ap-northeast-1c', vpcId: vpc.vpcId, cidrBlock: '10.0.4.0/24' },
      { availabilityZone: 'ap-northeast-1d', vpcId: vpc.vpcId, cidrBlock: '10.0.5.0/24' },
    ]

    const subnets = privateSubnetProps.map((prop, i) => {
      const subnet = new PrivateSubnet(this, `MyPrivateSubnet${i}`, prop);
      Tags.of(subnet).add('Name', `private-subnet-${i}`);
      Tags.of(subnet).add('aws-cdk:subnet-type', SubnetType.PRIVATE_ISOLATED);
      return subnet
    });

    //------------------ Aurora用の設定 ----------------------------------
    const dbConnectionGroup = new SecurityGroup(this, 'SecurityGroupForPrivateSubnets', {
      vpc,
      description: 'seburity group for Aurora'
    })
    Tags.of(dbConnectionGroup).add('Name', 'SecurityGroupForPrivateSubnets');

    const subnetGroupForAurora = new SubnetGroup(this, 'SubnetGroupForAurora', {
      vpc,
      vpcSubnets: { subnets },
      description: 'subnet group for Aurora db',
      subnetGroupName: props.subnetGroupName
    });
    Tags.of(subnetGroupForAurora).add('Name', 'SubnetGroupForAurora');

    //------------------ 踏み台用の設定 ----------------------------------
    const securityGroupPublic = new SecurityGroup(this, 'SecurityGroupForPublicSubnets', {
      vpc,
      description: 'seburity group for bastion'
    })
    Tags.of(securityGroupPublic).add('Name', 'SecurityGroupForPublicSubnets');
    dbConnectionGroup.addIngressRule(securityGroupPublic, Port.tcp(DB_PORT));


+    //------------------ Lambda用の設定 ----------------------------------
+    const lambdaToRDSProxyGroup = new SecurityGroup(this, 'Lambda to RDS Proxy Connection', vpc });
+    dbConnectionGroup.addIngressRule(lambdaToRDSProxyGroup, Port.tcp(DB_PORT), 'allow lambda connection');
    //------------------ 共通設定 ----------------------------------
    // 作成したリソース全てにタグをつける
    Aspects.of(this).add(new Tag('Stack', id));
  }
}

```

### RDS Proxy設定
IAM認証とTLS必須の設定を行ってProxyを作成。
ProxyのセキュリティグループはRDSと同様とする。
ProxyにSecretManager経由でのRDS接続用の管理者・ユーザの接続権限を与える。

```diff:cdk/lib/aurora-stack.ts
import { Aspects, RemovalPolicy, Stack, StackProps, Tag, Tags } from 'aws-cdk-lib';
import { Construct } from 'constructs';
import { InstanceClass, InstanceSize, InstanceType, SecurityGroup, Vpc } from 'aws-cdk-lib/aws-ec2';
import { AuroraPostgresEngineVersion, Credentials, DatabaseCluster, DatabaseClusterEngine, DatabaseProxy, ProxyTarget, SubnetGroup } from 'aws-cdk-lib/aws-rds';
import { Secret } from 'aws-cdk-lib/aws-secretsmanager';
import { AccountPrincipal, Effect, PolicyStatement, Role } from 'aws-cdk-lib/aws-iam';

interface AuroraStackProps extends StackProps {
  vpcId: string
  sgId: string
  subnetGroupName: string
  dbAdminName: string
  dbAdminSecretName: string
  dbReadOnlyUserName: string
  dbReadOnlyUserSecretName: string
}

export class AuroraStack extends Stack {
  constructor(scope: Construct, id: string, props: AuroraStackProps) {
    const superProps = {
      ...props, vpcId: undefined, sgId: undefined, subnetName: undefined
      , dbSecretName: undefined, dbAdminName: undefined, dbUserPassword: undefined

    } as StackProps
    super(scope, id, superProps);

    const vpc = Vpc.fromLookup(this, 'Vpc', { vpcId: props.vpcId })
    const securityGroup = SecurityGroup.fromLookupById(this, 'SecurityGroup', props.sgId);
    const subnetGroup = SubnetGroup.fromSubnetGroupName(this, 'SubnetGroup', props.subnetGroupName.toLowerCase());

    const secret = this.createSecret({ secretName: props.dbAdminSecretName, rdsName: props.dbAdminName });

    const cluster = new DatabaseCluster(this, 'clusterForAurora', {
      // LTSのバージョンを選択.RDSProxyは10と11のみのサポート 2021.12.10
      engine: DatabaseClusterEngine.auroraPostgres({ version: AuroraPostgresEngineVersion.VER_11_9 }),
      removalPolicy: RemovalPolicy.DESTROY, // 本番運用だと消しちゃだめだと思う
      defaultDatabaseName: 'postgres',
      instanceProps: {
        vpc,
        securityGroups: [securityGroup],
        instanceType: InstanceType.of(InstanceClass.T4G, InstanceSize.MEDIUM),
      },
      instances: 1,
      subnetGroup,
      credentials: Credentials.fromSecret(secret)
    });


+    // RDSで読み取り用に作成する予定のユーザをシークレットに登録
+    const secretForDBUser = this.createSecret({ secretName: props.dbReadOnlyUserSecretName, rdsName: props.dbReadOnlyUserName });


+    const proxy = new DatabaseProxy(this, 'Proxy', {
+      proxyTarget: ProxyTarget.fromCluster(cluster),
+      secrets: [secret, secretForDBUser],
+      vpc,
+      securityGroups: [securityGroup],
+      requireTLS: true,
+      iamAuth: true
+    });
+    Tags.of(proxy).add('Name', 'AuroraProxy');


+    const role = new Role(this, 'DBProxyRole', { assumedBy: new AccountPrincipal(this.account) });
+    Tags.of(role).add('Name', 'AuroraProxyRole');


+    proxy.grantConnect(role, props.dbAdminName);
+    proxy.grantConnect(role, props.dbReadOnlyUserName);

    // 作成したリソース全てにタグをつける
    Aspects.of(this).add(new Tag('Stack', id));

  }
  private createSecret(props: { secretName: string, rdsName: string }) {
    const secret = new Secret(this, props.secretName, {
      secretName: props.secretName,
      generateSecretString: {
        secretStringTemplate: JSON.stringify({ username: props.rdsName }),
        excludePunctuation: true, // '、/、"、@、スペースはpostgresのパスワードに利用できないので除外
        includeSpace: false,
        generateStringKey: 'password'
      }
    })
    Tags.of(secret).add('Name', props.secretName);
    return secret;
  }
}
```

### Lambda設定
lambdaのcdkを動かす前に、RDSを作成し、読取専用のユーザを作成しておく。（後述）
lambdaはapi gatewayを通してアクセスするものとする。読取・書込をそれぞれのユーザで検証できるように設定。
lambda layerにnode_modulesを配置し、同階層にProxyのTLS用のpemファイルをコピーすることとする。
(`src/common/data/AmazonRootCA1.pem`に[ルート証明書](https://www.amazontrust.com/repository/AmazonRootCA1.pem)が配置されているとする。 [*](https://aws.amazon.com/jp/premiumsupport/knowledge-center/rds-aurora-mysql-connect-proxy/))
lambda layerは `/opt/nodejs/`のパスでアクセスできるので、ソース上からのアクセスは`/opt/nodejs/data/AmazonRootCA1.pem`となる。

```json:cdk/package.json
{
  "scripts": {
    "createLayer": "ts-node lib/process/pre.ts",
    "copyData": "cd .. && npm run bundleData",
    "prelambda-deploy": "npm run createLayer && npm run copyData",
    "lambda-deploy": "cdk -a \"npx ts-node --prefer-ts-exts bin/lambda.ts\" destroy PrivateLambdaStack -c @aws-cdk/core:newStyleStackSynthesis=true --profile produser"
  }
}
```

```json:package.json
{
  "scripts": {
    "bundleData": "cp -r src/common/data cdk/bundle-node_modules/nodejs/data"
  }
}
```


```ts:cdk/bin/lambda.ts
#!/usr/bin/env node
import 'source-map-support/register';
import * as cdk from 'aws-cdk-lib';
import { PrivateLambdaStack } from '../lib/private-lambda-stack';
import { config } from 'dotenv'
config();

const app = new cdk.App();
const env = {
  account: process.env.CDK_DEFAULT_ACCOUNT,
  region: process.env.CDK_DEFAULT_REGION
}

const envNames = ['VPC_ID', 'PRIVATE_LAMBDA_SG_ID', 'DB_USER_RESOURCE_ARN', 'DB_ADMIN_NAME', 'DB_PROXY_ENDPOINT', 'DB_USER_NAME'] as const
const checkEnvs = (e: any): e is Record<(typeof envNames)[number], string> => {
  for (const a of envNames) {
    if (!e[a]) throw new Error(`please set environment variable ${a}`)
  }
  return true
}
if (!checkEnvs(process.env)) throw new Error('到達しない')

new PrivateLambdaStack(app, 'PrivateLambdaStack', {
  env,
  vpcId: process.env.VPC_ID,
  sgId: process.env.PRIVATE_LAMBDA_SG_ID,
  rdsProxyArn: process.env.DB_USER_RESOURCE_ARN,
  dbAdminName: process.env.DB_ADMIN_NAME,
  dbProxyEndpont: process.env.DB_PROXY_ENDPOINT,
  dbReadOnlyUserName: process.env.DB_USER_NAME
});
```

```ts:cdk/lib/private-lambda-stack.ts
import { Aspects, Duration, Stack, StackProps, Tag, Tags } from 'aws-cdk-lib';
import { NodejsFunction, BundlingOptions } from 'aws-cdk-lib/aws-lambda-nodejs';
import * as lambda from 'aws-cdk-lib/aws-lambda';
import { Construct } from 'constructs';
import { NODE_LAMBDA_LAYER_DIR } from './process/setup';
import { ISecurityGroup, IVpc, SecurityGroup, SelectedSubnets, SubnetType, Vpc } from 'aws-cdk-lib/aws-ec2';
import { LambdaIntegration, RestApi } from 'aws-cdk-lib/aws-apigateway';
import { Effect, PolicyStatement } from 'aws-cdk-lib/aws-iam';

interface PrivateLambdaStackProps extends StackProps {
  vpcId: string
  sgId: string
  rdsProxyArn: string
  dbAdminName: string
  dbProxyEndpont: string
  dbReadOnlyUserName: string
}

export class PrivateLambdaStack extends Stack {
  constructor(scope: Construct, id: string, props: PrivateLambdaStackProps) {
    super(scope, id, props);
    const vpc = Vpc.fromLookup(this, 'Vpc', { vpcId: props.vpcId })
    const securityGroup = SecurityGroup.fromLookupById(this, 'SecurityGroup', props.sgId);
    const vpcSubnets = vpc.selectSubnets({ subnetType: SubnetType.PRIVATE_ISOLATED })

    const nodeModulesLayer = new lambda.LayerVersion(this, 'NodeModulesLayer',
      {
        code: lambda.AssetCode.fromAsset(NODE_LAMBDA_LAYER_DIR),
        compatibleRuntimes: [lambda.Runtime.NODEJS_14_X]
      }
    );
    const bundling = {
      externalModules: [
        'aws-sdk', // Use the 'aws-sdk' available in the Lambda runtime
        'date-fns', // Layrerに入れておきたいモジュール
        'pg'
      ],
    }
    const lambdaParamsDefault = {
      layers: [nodeModulesLayer],
      vpc,
      vpcSubnets,
      securityGroups: [securityGroup],
      bundling
    }

    const helloLambda = this.createLambda({
      ...lambdaParamsDefault,
      entry: `../src/handler/api/hello.ts`,
      name: 'hellorLambda',
      descritption: 'サンプル用メッセージ表示'
    })

    const rdsEnv = {
      DB_PORT: '5432',
      DB_HOST: props.dbProxyEndpont,
      DB_USER: props.dbAdminName,
      DB_DBNAME: 'postgres',
    }
    const rdsEnvReadOnly = {
      ...rdsEnv,
      DB_USER: props.dbReadOnlyUserName,
    }
    const rdsAdminResource = `${props.rdsProxyArn}/${props.dbAdminName}`;
    const rdsReadOnlyUserResource = `${props.rdsProxyArn}/${props.dbReadOnlyUserName}`;
    const adminPolicy = new PolicyStatement({
      effect: Effect.ALLOW,
      actions: ['rds-db:connect'],
      resources: [rdsAdminResource],
    })
    const readOnlyUserPolicy = new PolicyStatement({
      effect: Effect.ALLOW,
      actions: ['rds-db:connect'],
      resources: [rdsReadOnlyUserResource],
    })


    const electricLambda = this.createLambda({
      ...lambdaParamsDefault,
      entry: `../src/handler/api/getElectric.ts`,
      name: 'electricLambda',
      descritption: 'RDSAdminでテーブル参照',
      environment: { ...rdsEnv },
      initialPolicy: [adminPolicy],
      timeoutSec: 10, // DBへの再接続を1秒置き、3回まで行うため、デフォルトの3秒より延ばしておく
    })

    const api = new RestApi(this, 'ServerlessRestApi', { cloudWatchRole: false });
    api.root.addResource('hello').addMethod('GET', new LambdaIntegration(helloLambda));
    const electricResource = api.root.addResource('electric')
    electricResource.addMethod('GET', new LambdaIntegration(electricLambda));

    const electricLambda2 = this.createLambda({
      ...lambdaParamsDefault,
      entry: `../src/handler/api/getElectric.ts`,
      name: 'electricLambda2',
      descritption: 'RDS 読取専用ユーザでテーブル参照',
      environment: { ...rdsEnvReadOnly },
      initialPolicy: [readOnlyUserPolicy],
      timeoutSec: 10,
    })

    const electricReadonlyResource = api.root.addResource('electric-readonly');
    electricReadonlyResource.addMethod('GET', new LambdaIntegration(electricLambda2));

    const electricLambda3 = this.createLambda({
      ...lambdaParamsDefault,
      entry: `../src/handler/api/postElectric.ts`,
      name: 'electricLambda3',
      descritption: 'RDS 読取専用ユーザでテーブル挿入',
      environment: { ...rdsEnvReadOnly },
      initialPolicy: [readOnlyUserPolicy],
      timeoutSec: 10,
    })

    electricReadonlyResource.addMethod('POST', new LambdaIntegration(electricLambda3));

    const electricLambda4 = this.createLambda({
      ...lambdaParamsDefault,
      entry: `../src/handler/api/postElectric.ts`,
      name: 'electricLambda4',
      descritption: 'RDSAdminでテーブル挿入',
      environment: { ...rdsEnv },
      initialPolicy: [adminPolicy],
      timeoutSec: 10,
    })
    electricResource.addMethod('POST', new LambdaIntegration(electricLambda4));

    Aspects.of(this).add(new Tag('Stack', id));
  }

  private createLambda(props: {
    layers: lambda.LayerVersion[]
    vpc: IVpc
    vpcSubnets: SelectedSubnets
    securityGroups: ISecurityGroup[]
    bundling: BundlingOptions
    name: string
    descritption: string
    entry: string
    environment?: Record<string, string>
    initialPolicy?: PolicyStatement[]
    timeoutSec?: number
  }) {
    const func = new NodejsFunction(this, props.name, {
      runtime: lambda.Runtime.NODEJS_14_X,
      entry: props.entry,
      functionName: props.name,
      description: props.descritption,
      layers: props.layers,
      vpc: props.vpc,
      vpcSubnets: props.vpcSubnets,
      securityGroups: props.securityGroups,
      bundling: props.bundling,
      environment: props.environment,
      initialPolicy: props.initialPolicy,
      timeout: props.timeoutSec ? Duration.seconds(props.timeoutSec) : undefined,
    });
    Tags.of(func).add('Name', props.name);
    return func;
  }
}
```

## Postgresへ接続するLambdaのソース
IAM認証で接続する。
接続できなかった場合、最大5回リトライ。
ローカルで実行した場合には、dockerで起動されたDBに接続するようにしている。
（ローカル実行時には環境変数`process.env.AWS_SAM_LOCAL`が`true`に設定される。）
また、[lambdaのライフサイクル](https://docs.aws.amazon.com/ja_jp/lambda/latest/dg/runtimes-context.html)的に、lambda内でのプールの使いまわしは難しい。
[確実にコネクションをクローズ](https://node-postgres.com/features/pooling)できるようにラッパーメソッドを用意しておく。

```ts:src/common/persistants/postgres.ts
import { Pool } from 'pg';
import { RDS, SecretsManager } from 'aws-sdk'
import * as fs from 'fs'
import type { PoolClient } from 'pg';

const RETRY_COUNT = 5;
const RETRY_INTERVAL_MILLI_SECOND = 100;
const signer = new RDS.Signer()
let pool: Pool | null = null;

class Postgres {
  #client: PoolClient

  async init() {
    if (!pool) throw new Error('pool is undefined')

    for (let i = 0; i < RETRY_COUNT; i++) {
      try {
        this.#client = await pool.connect();
        return;
      } catch (e) {
        console.warn(`error try ${i}`, e);
        await new Promise(resolve => setTimeout(resolve, RETRY_INTERVAL_MILLI_SECOND));
      }
    }
    throw new Error('connect failed')
  }

  async execute(query: string, params = []) {
    return (await this.#client.query(query, params)).rows;
  }

  async release() {
    await this.#client.release(true);
  }

  async begin() {
    await this.#client.query('BEGIN');
  }

  async commit() {
    await this.#client.query('COMMIT');
  }

  async rollback() {
    await this.#client.query('ROLLBACK');
  }
}

const getPool = () => {
  if (process.env.AWS_SAM_LOCAL === 'true') {
    // ローカル実行用。admin。 docker/.envで設定したPostgresへの接続内容。host.docker.internalはdockerコンテナ内からホスト上のサービスに対して接続するときのDNS名。
    const connectionString = 'postgresql://admin:secret@host.docker.internal:5432/postgres';
    return new Pool({ connectionString });
  }

  const { DB_PORT, DB_HOST, DB_USER, DB_DBNAME, AWS_REGION } = process.env;
  if (!DB_PORT) throw new Error(`DB_PORT is undefined`)
  if (!DB_HOST) throw new Error(`DB_HOST is undefined`)
  if (!DB_USER) throw new Error(`DB_USER is undefined`)
  if (!DB_DBNAME) throw new Error(`DB_DBNAME is undefined`)
  if (!AWS_REGION) throw new Error('AWS_REGION is undefined') // RDSとLambdaが同一のリージョンに存在する想定

  const signerOptions = {
    region: AWS_REGION,
    hostname: DB_HOST, 
    port: Number(DB_PORT),
    username: DB_USER,
  }

  return new Pool({
    host: signerOptions.hostname,
    port: signerOptions.port,
    user: signerOptions.username,
    database: DB_DBNAME,
    // IAM認証のため、パスワードの代わりにトークンを使用する。
    password: () => signer.getAuthToken(signerOptions),
    ssl: {
      ca: fs.readFileSync('/opt/nodejs/data/AmazonRootCA1.pem')
    }
  });
}

const getClient = async () => {
  if (!pool) pool = getPool();
  const postgres = new Postgres();
  await postgres.init();
  return postgres;
};

export const executeSingleQuery = async (query: string, params: any[] = []) => {
  if (!pool) pool = getPool();
  for (let i = 0; i < RETRY_COUNT; i++) {
    try {
      const ret = await pool.query(query, params);
      return ret.rows;
    } catch (e) {
      console.warn(`error try ${i}`, e);
      await new Promise(resolve => setTimeout(resolve, RETRY_INTERVAL_MILLI_SECOND));
    }
  }
  throw new Error('connect failed')
}

export const executeTransaction = async (executeQuery: (client: Postgres) => Promise<any>) => {
  const client = await getClient();

  try {
    await client.begin();
    const ret = await executeQuery(client);
    await client.commit();
    return ret
  } catch (e: any) {
    console.warn('transaction rollback. error ->', e);
    await client.rollback();
    throw e;
  } finally {
    await client.release();
  }
}
```

```ts:src/handler/api/getElectric.ts
import { APIGatewayProxyHandler } from 'aws-lambda';
import { executeSingleQuery } from '@/common/persistants/postgres';

export const handler: APIGatewayProxyHandler = async (event) => {
  const result = await executeSingleQuery('select * from electric');
  return {
    statusCode: 200,
    body: JSON.stringify(result)
  };
};
```

```ts:src/handler/api/postElectric.ts
import { APIGatewayProxyHandler } from 'aws-lambda';
import { getPostgresClient } from '@/common/persistants/postgres';

export const handler: APIGatewayProxyHandler = async (event) => {
  const client = await getPostgresClient();
  const result = await client.execute(`insert into electric(id,name,measuredtime,value) values(99,'test',now(),1111);`);
  return {
    statusCode: 200,
    body: JSON.stringify(result)
  };
};
```

## 動作確認

まずは、RDSのデプロイを行う。

```
cd cdk
npm run vpc-deploy
npm run aurora-deploy
npm run bastion-deploy
```

次に、ポートフォワーディングを有効にする。

```
./bin/create-document.sh
./bin/start-port-forwarding.sh
```

ここで、シークレットマネージャで作成した読取用ユーザのパスワードを確認しておく。

```
SECRET_NAME=db-rdsuser-secrets
SECRET=$(aws secretsmanager get-secret-value --region ap-northeast-1 --secret-id $SECRET_NAME --profile produser | jq .SecretString | jq fromjson)
echo $SECRET | jq -r .password
```


データベースで読取用のユーザを作成する。
テスト用のテーブルとデータも作成しておく。

```sql
CREATE USER rdsuserWITH PASSWORD '確認したパスワード';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rdsuser;

create table electric (
  id integer,
  name varchar,
  measuredtime timestamp,
  value numeric(19,1)
);
insert into electric(id,name,measuredtime,value) values(1,'test1',now(),100);
insert into electric(id,name,measuredtime,value) values(2,'test2',now(),10.11);
select * from electric;
```

コンソールからデプロイされたRDSProxyを確認し、`cdk/.env`の`DB_USER_RESOURCE_ARN`と`DB_PROXY_ENDPOINT`の値を記述する。
lambdaをデプロイする。

```
cd cdk
npm run lambda-deploy
```

REST で結果を確認する。

```bash
###
get https://hoge.execute-api.ap-northeast-1.amazonaws.com/prod/hello

###
get  https://hoge.execute-api.ap-northeast-1.amazonaws.com/prod/electric

###
get  https://hoge.execute-api.ap-northeast-1.amazonaws.com/prod/electric-readonly

###
post  https://hoge.execute-api.ap-northeast-1.amazonaws.com/prod/electric

###
post  https://hoge.execute-api.ap-northeast-1.amazonaws.com/prod/electric-readonly
# エラー発生。permission denied for table electric
```


## 参考

### 公式
[RDS Proxy の開始方法](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/rds-proxy-setup.html)
[IAMデータベースアクセス用のIAMポリシーの作成と使用](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.IAMPolicy.html)
[Troubleshooting for RDS Proxy](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.troubleshooting.html)

[Amazon RDS Proxy のご紹介 ... ピン留め問題など](https://pages.awscloud.com/rs/112-TZM-766/images/EV_amazon-rds-aws-lambda-update_Jul28-2020_RDS_Proxy.pdf)
[ピン留めを回避する](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/rds-proxy-managing.html#rds-proxy-pinning)
[AWSのポリシーとアクセス許可](https://docs.aws.amazon.com/ja_jp/IAM/latest/UserGuide/access_policies.html)

[CDK v2 - RDS](https://docs.aws.amazon.com/cdk/api/v2//docs/aws-cdk-lib.aws_rds-readme.html)

### 野良
[CDK Patternsを参考にしてRDS Proxyを利用したAPIをデプロイしてみた](https://dev.classmethod.jp/articles/cdk-rds-proxy-api-go/)
[SAMでローカル実行したLambdaからMongodbへのアクセスを試してみたメモ](https://qiita.com/hibohiboo/items/15db5256624c7a8e8b8c)
[aws-cdk v2でlambda layers を扱ったメモ](https://qiita.com/hibohiboo/items/b33c530a5a3e8a9c7411)
[docker - コンテナからホスト上のサービスに対して接続したい](%3CmxGraphModel%3E%3Croot%3E%3CmxCell%20id%3D%220%22%2F%3E%3CmxCell%20id%3D%221%22%20parent%3D%220%22%2F%3E%3CmxCell%20id%3D%222%22%20value%3D%22Private%20subnet%22%20style%3D%22points%3D%5B%5B0%2C0%5D%2C%5B0.25%2C0%5D%2C%5B0.5%2C0%5D%2C%5B0.75%2C0%5D%2C%5B1%2C0%5D%2C%5B1%2C0.25%5D%2C%5B1%2C0.5%5D%2C%5B1%2C0.75%5D%2C%5B1%2C1%5D%2C%5B0.75%2C1%5D%2C%5B0.5%2C1%5D%2C%5B0.25%2C1%5D%2C%5B0%2C1%5D%2C%5B0%2C0.75%5D%2C%5B0%2C0.5%5D%2C%5B0%2C0.25%5D%5D%3BoutlineConnect%3D0%3BgradientColor%3Dnone%3Bhtml%3D1%3BwhiteSpace%3Dwrap%3BfontSize%3D12%3BfontStyle%3D0%3Bcontainer%3D1%3BpointerEvents%3D0%3Bcollapsible%3D0%3BrecursiveResize%3D0%3Bshape%3Dmxgraph.aws4.group%3BgrIcon%3Dmxgraph.aws4.group_security_group%3BgrStroke%3D0%3BstrokeColor%3D%23147EBA%3BfillColor%3D%23E6F2F8%3BverticalAlign%3Dtop%3Balign%3Dleft%3BspacingLeft%3D30%3BfontColor%3D%23147EBA%3Bdashed%3D0%3B%22%20vertex%3D%221%22%20parent%3D%221%22%3E%3CmxGeometry%20x%3D%22-280%22%20y%3D%22140%22%20width%3D%22880%22%20height%3D%22170%22%20as%3D%22geometry%22%2F%3E%3C%2FmxCell%3E%3C%2Froot%3E%3C%2FmxGraphModel%3E)
[IAM ロールの PassRole と AssumeRole をもう二度と忘れないために絵を描いてみた](https://dev.classmethod.jp/articles/iam-role-passrole-assumerole/)
[[AWS SAM] Amazon RDS Proxy をつかって Amazon Aurora MySQL に接続するサーバーレスアプリケーションを構築する](https://www.seeds-std.co.jp/blog/creators/2021-08-13-002824/)
[LambdaでRDS Proxy経由でAurora(MySQL)に接続する（IAM認証）](https://qiita.com/takeshi_hirosue/items/a76d974a8bca36db42b6)
[TypeORMからIAMデータベース認証でRDSへ接続する](https://tech.bitbank.cc/typeorm-iam-rds/)