ablog

不器用で落着きのない技術者のメモ

Redshift でテーブルにカラムを追加できる位置

Redshift でテーブルの末尾にしかカラムを追加できない。ドキュメントを見る限りカラム位置を指定するような構文はない。

  • 接続する
$ psql -a "host=redshift-cluster-poc-central.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=dev port=5439"
  • テーブルを作成
dev=# create table test(
dev(#   name varchar(10),
dev(#   updated timestamp
dev(# );
CREATE TABLE
  • カラム追加
dev=# alter table test add column added_column int;
ALTER TABLE
  • 追加したカラムを確認
dev=# show table test;
               Show Table DDL statement
------------------------------------------------------
 CREATE TABLE public.test (                          +
     name character varying(10) ENCODE lzo,          +
     updated timestamp without time zone ENCODE az64,+
     added_column integer ENCODE az64                +
 )                                                   +
 DISTSTYLE AUTO;
(1 row)

dev=#
dev=# SHOW COLUMNS FROM TABLE dev.public.test;
 database_name | schema_name | table_name | column_name  | ordinal_position | column_default | is_nullable |          data_type          | character_maximum_length | numeric_precision | remarks
---------------+-------------+------------+--------------+------------------+----------------+-------------+-----------------------------+--------------------------+-------------------+---------
 dev           | public      | test       | added_column |                3 |                | YES         | integer                     |                          |                32 |
 dev           | public      | test       | name         |                1 |                | YES         | character varying           |                       10 |                   |
 dev           | public      | test       | updated      |                2 |                | YES         | timestamp without time zone |                          |                   |
(3 rows)

Azure Data Factory から AWS Transfer Family の SFTP への接続時に "no matching host key type found" エラー

事象

  • Azure Data Factory から AWS Transfer Family の SFTP への接続時に "no matching host key type found" エラーが発生する。
Failed to connect to Sftp server 's-********.server.transfer.ap-northeast-1.amazonaws.com'.
An established connection was aborted by the server.


解決策

  • AWS Transfer Family の SFTP の暗号化アルゴリズムのオプションを TransferSecurityPolicy-2020-06 以前に設定する。
  • マネジメントコンソールで [AWS tansfer Family] - [サーバー] - [対象のサーバー] を選択し、[その他の詳細] の [編集] をクリック、[暗号化アルゴリズムのオプション] - [セキュリティポリシー] で TransferSecurityPolicy-2020-06 を選択して保存する。

Azure Data Factory から AWS Transfer Faimily の SFTP に公開鍵認証で接続する

AWS Transfer Family の設定

IAM ロールの作成
  • 以下の IAM ポリシーを作成して、IAM ロールにアタッチする。
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowListingOfUserFolder",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Effect": "Allow",
            "Resource": [
                "arn:aws:s3:::<S3 バケット名>"
            ]
        },
        {
            "Sid": "HomeDirObjectAccess",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObjectVersion",
                "s3:GetObjectACL",
                "s3:PutObjectACL"
            ],
            "Resource": "arn:aws:s3:::<S3 バケット名>/*"
        }
    ]
}
  • 信頼ポリシー
{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Effect": "Allow",
			"Principal": {
				"Service": "transfer.amazonaws.com"
			},
			"Action": "sts:AssumeRole"
		}
	]
}
S3 バケットの作成
  • バケット名: azure-aws-sync
    • オブジェクト所有者: ACL無効
    • パブリックアクセスをすべて ブロック: チェック
    • バケットのバージョニング: 有効
    • 暗号化タイプ: SSE-S3
    • バケットキー: 有効にする
AWS Transfer Family の設定
  • サーバーを作成
    • プロトコル: SFTP
    • ID プロバイダーのタイプ: サービスマネージド
    • エンドポイントの設定
      • エンドポイントのタイプ: パブリックアクセス可能
      • カスタムホスト名: なし
    • ドメイン: Amazon S3
    • ログ記録
      • ロググループ: 新しいロググループを作成
    • 暗号化アルゴリズムのオプション: TransferSecurityPolicy-2022-03
  • ユーザーを作成
$ ssh-keygen -f ~/.ssh/ssh-key-sftp -t rsa -m PEM
Azure Data Factory の設定
  • Azure Data Factory のデータ転送先の設定

Azure Data Factory から AWS Transfer Family の SFTP へ接続しようとすると "Failed to connect to Sftp server '...'. Server HMAC algorithm not found" エラーが発生する

事象

  • Azure Data Factory から AWS Transfer Family の SFTP へ接続しようとすると以下のエラーが発生する。
Error code:
SftpFailedToConnectToSftpServer

Details:
Failed to connect to Sftp server 's-********.server.transfer.ap-northeast-1.amazonaws.com'. Server HMAC algorithm not found


原因

解決策

  • AWS Transfer Family の SFTP の暗号化アルゴリズムのオプションを TransferSecurityPolicy-2022-03 以前に設定する。
  • マネジメントコンソールで [AWS tansfer Family] - [サーバー] - [対象のサーバー] を選択し、[その他の詳細] の [編集] をクリック、[暗号化アルゴリズムのオプション] - [セキュリティポリシー] で TransferSecurityPolicy-2022-03 を選択して保存する。

AWS Transfer Family の SFTP でパスワード認証を使う

AWS Transfer Family の SFTP でパスワード認証を行いたい場合、こちらの CloudFormation Template を実行し、Transfer Family、API Gateway、Lambda、Secrets Manager のリソースを作成する。あとは、Secrets Manager で以下を設定してやれば OK。

  • シークレットの名前: aws/transfer/<サーバ名>/<ユーザー名>
  • シークレットの値:

IAMロールの設定は以下の通り。

  • IAMポリシー
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowListingOfUserFolder",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Effect": "Allow",
            "Resource": [
                "arn:aws:s3:::<S3 バケット名>"
            ]
        },
        {
            "Sid": "HomeDirObjectAccess",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:DeleteObject",
                "s3:DeleteObjectVersion",
                "s3:GetObjectVersion",
                "s3:GetObjectACL",
                "s3:PutObjectACL"
            ],
            "Resource": "arn:aws:s3:::<S3 バケット名>/*"
        }
    ]
}
  • 信頼ポリシー
{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Effect": "Allow",
			"Principal": {
				"Service": "transfer.amazonaws.com"
			},
			"Action": "sts:AssumeRole"
		}
	]
}

Amazon Linux 2023 で sshd のログを確認する

$ sudo journalctl -u sshd
Apr 01 00:51:17 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2155]: Server listening on 0.0.0.0 port 22.
Apr 01 00:51:17 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2155]: Server listening on :: port 22.
Apr 01 00:51:17 ip-172-31-29-44.ap-northeast-1.compute.internal systemd[1]: Starting sshd.service - OpenSSH server daemon...
Apr 01 00:51:17 ip-172-31-29-44.ap-northeast-1.compute.internal systemd[1]: Started sshd.service - OpenSSH server daemon.
Apr 01 00:51:47 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2295]: Accepted publickey for ec2-user from 27.0.3.153 port 9559 ssh2: RSA SHA256:Cs3EDupiG0ybMNJemrbwDgT>
Apr 01 00:51:48 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2295]: pam_unix(sshd:session): session opened for user ec2-user(uid=1000) by (uid=0)
Apr 01 00:52:54 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2563]: Accepted publickey for ec2-user from 27.0.3.153 port 29383 ssh2: RSA SHA256:Cs3EDupiG0ybMNJemrbwDg>
Apr 01 00:52:55 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2563]: pam_unix(sshd:session): session opened for user ec2-user(uid=1000) by (uid=0)
Apr 01 00:52:55 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2563]: pam_unix(sshd:session): session closed for user ec2-user
Apr 01 00:54:02 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2687]: Accepted publickey for ec2-user from 27.0.3.153 port 2323 ssh2: RSA SHA256:Cs3EDupiG0ybMNJemrbwDgT>
Apr 01 00:54:02 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2687]: pam_unix(sshd:session): session opened for user ec2-user(uid=1000) by (uid=0)
Apr 01 00:55:21 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2766]: Accepted publickey for ec2-user from 27.0.3.153 port 4847 ssh2: RSA SHA256:Cs3EDupiG0ybMNJemrbwDgT>
Apr 01 00:55:22 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2766]: pam_unix(sshd:session): session opened for user ec2-user(uid=1000) by (uid=0)
Apr 01 00:59:38 ip-172-31-29-44.ap-northeast-1.compute.internal systemd[1]: Stopping sshd.service - OpenSSH server daemon...
Apr 01 00:59:38 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2155]: Received signal 15; terminating.
Apr 01 00:59:38 ip-172-31-29-44.ap-northeast-1.compute.internal systemd[1]: sshd.service: Deactivated successfully.
Apr 01 00:59:38 ip-172-31-29-44.ap-northeast-1.compute.internal systemd[1]: Stopped sshd.service - OpenSSH server daemon.
Apr 01 00:59:38 ip-172-31-29-44.ap-northeast-1.compute.internal systemd[1]: Starting sshd.service - OpenSSH server daemon...
Apr 01 00:59:38 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2986]: Server listening on 0.0.0.0 port 22.
Apr 01 00:59:38 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[2986]: Server listening on :: port 22.
Apr 01 00:59:38 ip-172-31-29-44.ap-northeast-1.compute.internal systemd[1]: Started sshd.service - OpenSSH server daemon.
Apr 01 01:00:13 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[3032]: userauth_pubkey: certificate signature algorithm ssh-rsa: signature algorithm not supported [preau>
Apr 01 01:00:21 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[3032]: Accepted password for ec2-user from 27.0.3.153 port 21587 ssh2
Apr 01 01:00:21 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[3032]: pam_unix(sshd:session): session opened for user ec2-user(uid=1000) by (uid=0)
Apr 01 01:03:50 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[3230]: userauth_pubkey: certificate signature algorithm ssh-rsa: signature algorithm not supported [preau>
Apr 01 01:03:58 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[3230]: Accepted password for ec2-user from 27.0.3.153 port 21957 ssh2
Apr 01 01:03:58 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[3230]: pam_unix(sshd:session): session opened for user ec2-user(uid=1000) by (uid=0)
Apr 01 01:15:13 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[3571]: Accepted password for ec2-user from 20.43.64.145 port 11264 ssh2 ★ Azure Data Factory からの接続
Apr 01 01:15:13 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[3571]: pam_unix(sshd:session): session opened for user ec2-user(uid=1000) by (uid=0)
Apr 01 01:15:13 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[3571]: pam_unix(sshd:session): session closed for user ec2-user
Apr 01 01:16:13 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[3641]: userauth_pubkey: certificate signature algorithm ssh-rsa: signature algorithm not supported [preau>
Apr 01 01:16:24 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[3641]: Accepted password for ec2-user from 27.0.3.153 port 3813 ssh2
Apr 01 01:16:24 ip-172-31-29-44.ap-northeast-1.compute.internal sshd[3641]: pam_unix(sshd:session): session opened for user ec2-user(uid=1000) by (uid=0)