2020-09-20

SSHのセッション確立フローをもう一回勉強するキャプチャで見てみる編

前回 SSHのフローを整理したということで実際の動きを見てみようと思います。
ssh -vとかで見てもいいんですがNWエンジニアらしくWire sharkを使います。

環境

クライアント
- サブ機Windows10
- IP: 192.168.0.12
サーバ
- Ubuntu 18.04LTS
- IP: 192.168.0.10

セッション確立のおさらい

TCPコネクション確立
サーバ使用できるバージョンを応答
サーバの公開ホストキーの提供
- 公開鍵認証でサーバのFinger printを確認して認証
各アルゴリズムのネゴシエーション
Diffie Hellman関数で鍵交換
- ここで暗号化、複合化用の共通鍵が交換される
共通鍵を使って以後の通信をやりとり
- 以降のコネクションで使われる共通鍵暗号はバイナリパケットプロトコルと呼ばれる
- パケットにMACを使ってデータの整合性を保っている

SSH接続して暗号化パケットが開始されるまでの流れを見ようと思います。

SSHのセッションサマリ

キャプチャを出力してみます。
テキストでログを出せるようtsharkを使います。
TCPも入れると見づらいのでsshに絞ります。

PS C:\Users\masashi\Documents> & 'C:\Program Files\Wireshark\tshark.exe'  -r .\sshcapture.pcapng -Y "ssh"
    4   0.010420 192.168.0.10 → 192.168.0.12 SSH 95 Server: Protocol (SSH-2.0-OpenSSH_7.6p1 Ubuntu-4ubuntu0.3)
    5   0.045942 192.168.0.12 → 192.168.0.10 SSH 87 Client: Protocol (SSH-2.0-OpenSSH_for_Windows_7.7)
    7   0.051690 192.168.0.10 → 192.168.0.12 SSHv2 1134 Server: Key Exchange Init
    8   0.074208 192.168.0.12 → 192.168.0.10 SSHv2 1374 Client: Key Exchange Init
   10   0.136611 192.168.0.12 → 192.168.0.10 SSHv2 102 Client: Diffie-Hellman Key Exchange Init
   12   0.149371 192.168.0.10 → 192.168.0.12 SSHv2 506 Server: Diffie-Hellman Key Exchange Reply, New Keys, Encrypted packet (len=172)
   13   0.179002 192.168.0.12 → 192.168.0.10 SSHv2 70 Client: New Keys
   15   0.221135 192.168.0.12 → 192.168.0.10 SSHv2 98 Client: Encrypted packet (len=44)
   17   0.223068 192.168.0.10 → 192.168.0.12 SSHv2 98 Server: Encrypted packet (len=44)

   以後暗号化フェーズなので割愛

鍵が作られるまではシンプルなフローですね。

キャプチャの詳細

さっきのコマンドに-Vでパケットの中身を出力できます。

& 'C:\Program Files\Wireshark\tshark.exe'  -r .\sshcapture.pcapng -Y "ssh" -V

全てのヘッダが出力されるのでSSHのみ抽出します。

Frame 4

server -> client

SSH Protocol
    Protocol: SSH-2.0-OpenSSH_7.6p1 Ubuntu-4ubuntu0.3
    [Direction: server-to-client]

サーバが使用できるバージョンを応答

Frame 5

client -> server

SSH Protocol
    Protocol: SSH-2.0-OpenSSH_for_Windows_7.7
    [Direction: client-to-server]

クライアントが使用できるバージョンを応答

Frame 7

server -> client

SSH Protocol
    SSH Version 2
        Packet Length: 1076
        Padding Length: 6
        Key Exchange
            Message Code: Key Exchange Init (20)
            Algorithms
                Cookie: 59ce5ed2756dcfa9fbdd28f4c53ae1ac
                kex_algorithms length: 258
                kex_algorithms string [truncated]: curve25519-sha256,curve25519-sha256@libssh.org,ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512,di
                server_host_key_algorithms length: 65
                server_host_key_algorithms string: ssh-rsa,rsa-sha2-512,rsa-sha2-256,ecdsa-sha2-nistp256,ssh-ed25519
                encryption_algorithms_client_to_server length: 108
                encryption_algorithms_client_to_server string: chacha20-poly1305@openssh.com,aes128-ctr,aes192-ctr,aes256-ctr,aes128-gcm@openssh.com,aes256-gcm@openssh.com
                encryption_algorithms_server_to_client length: 108
                encryption_algorithms_server_to_client string: chacha20-poly1305@openssh.com,aes128-ctr,aes192-ctr,aes256-ctr,aes128-gcm@openssh.com,aes256-gcm@openssh.com
                mac_algorithms_client_to_server length: 213
                mac_algorithms_client_to_server string [truncated]: umac-64-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512-etm@openssh.com,hmac-sha1-etm@openssh.com,umac-64@openssh.com,umac-128@openssh.com,hmac-sha2-
                mac_algorithms_server_to_client length: 213
                mac_algorithms_server_to_client string [truncated]: umac-64-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512-etm@openssh.com,hmac-sha1-etm@openssh.com,umac-64@openssh.com,umac-128@openssh.com,hmac-sha2-
                compression_algorithms_client_to_server length: 21
                compression_algorithms_client_to_server string: none,zlib@openssh.com
                compression_algorithms_server_to_client length: 21
                compression_algorithms_server_to_client string: none,zlib@openssh.com
                languages_client_to_server length: 0
                languages_client_to_server string: [Empty]
                languages_server_to_client length: 0
                languages_server_to_client string: [Empty]
                First KEX Packet Follows: 0
                Reserved: 00000000
        Padding String: 000000000000
    [Direction: server-to-client]

各アルゴリズムのネゴシエーション

Frame 8

client -> server

SSH Protocol
    SSH Version 2
        Packet Length: 1316
        Padding Length: 9
        Key Exchange
            Message Code: Key Exchange Init (20)
            Algorithms
                Cookie: 0cf07f579036cbc7231de08c60d1d70c
                kex_algorithms length: 304
                kex_algorithms string [truncated]: curve25519-sha256,curve25519-sha256@libssh.org,ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512,di
                server_host_key_algorithms length: 290
                server_host_key_algorithms string [truncated]: ecdsa-sha2-nistp256-cert-v01@openssh.com,ecdsa-sha2-nistp384-cert-v01@openssh.com,ecdsa-sha2-nistp521-cert-v01@openssh.com,ecdsa-sha2-nistp256,ecdsa-sha2-nistp384,ecdsa-sha2-nistp521,ssh-ed25
                encryption_algorithms_client_to_server length: 108
                encryption_algorithms_client_to_server string: chacha20-poly1305@openssh.com,aes128-ctr,aes192-ctr,aes256-ctr,aes128-gcm@openssh.com,aes256-gcm@openssh.com
                encryption_algorithms_server_to_client length: 108
                encryption_algorithms_server_to_client string: chacha20-poly1305@openssh.com,aes128-ctr,aes192-ctr,aes256-ctr,aes128-gcm@openssh.com,aes256-gcm@openssh.com
                mac_algorithms_client_to_server length: 213
                mac_algorithms_client_to_server string [truncated]: umac-64-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512-etm@openssh.com,hmac-sha1-etm@openssh.com,umac-64@openssh.com,umac-128@openssh.com,hmac-sha2-
                mac_algorithms_server_to_client length: 213
                mac_algorithms_server_to_client string [truncated]: umac-64-etm@openssh.com,umac-128-etm@openssh.com,hmac-sha2-256-etm@openssh.com,hmac-sha2-512-etm@openssh.com,hmac-sha1-etm@openssh.com,umac-64@openssh.com,umac-128@openssh.com,hmac-sha2-
                compression_algorithms_client_to_server length: 4
                compression_algorithms_client_to_server string: none
                compression_algorithms_server_to_client length: 4
                compression_algorithms_server_to_client string: none
                languages_client_to_server length: 0
                languages_client_to_server string: [Empty]
                languages_server_to_client length: 0
                languages_server_to_client string: [Empty]
                First KEX Packet Follows: 0
                Reserved: 00000000
        Padding String: 000000000000000000
    [Direction: client-to-server]

各アルゴリズムのネゴシエーション

Frame 10

client -> server

SSH Protocol
    SSH Version 2
        Packet Length: 44
        Padding Length: 6
        Key Exchange
            Message Code: Diffie-Hellman Key Exchange Init (30)
            Multi Precision Integer Length: 32
            DH client e: 6f8e847cc717af634fc2a4a2f40f3e9c3da5589b96e6256e…
        Padding String: 000000000000
    [Direction: client-to-server]

Diffie Hellman関数で鍵交換

Frame 12

server -> client

SSH Protocol
    SSH Version 2
        Packet Length: 260
        Padding Length: 10
        Key Exchange
            Message Code: Diffie-Hellman Key Exchange Reply (31)
            KEX host key (type: ecdsa-sha2-nistp256)
                Host key length: 104
                Host key type length: 19
                Host key type: ecdsa-sha2-nistp256
                ECDSA elliptic curve identifier length: 8
                ECDSA elliptic curve identifier: nistp256
                ECDSA public key length: 65
                ECDSA public key (Q): 04862785baafcc04b427fc94afb8c218bc7cf3304bf46a8f…
            Multi Precision Integer Length: 32
            DH server f: b69a114f714a437eea10af55c24ffa3a6c1d7f82bde26e0c…
            KEX H signature length: 100
            KEX H signature: 0000001365636473612d736861322d6e6973747032353600…
        Padding String: 00000000000000000000
    SSH Version 2
        Packet Length: 12
        Padding Length: 10
        Key Exchange
            Message Code: New Keys (21)
        Padding String: 00000000000000000000
    SSH Version 2 (encryption:chacha20-poly1305@openssh.com mac:<implicit> compression:none)
        Packet Length (encrypted): b0a86384
        Encrypted Packet: a39e1c313eae920f96f8c3f899d1a6a8fbec57d03066ad42…
        MAC: c079d73892d1035d8c093ebee8d2d360
    [Direction: server-to-client]

Diffie Hellman関数で鍵交換

Frame 13

client -> server

SSH Protocol
    SSH Version 2
        Packet Length: 12
        Padding Length: 10
        Key Exchange
            Message Code: New Keys (21)
        Padding String: 00000000000000000000
    [Direction: client-to-server]

Diffie Hellman関数で鍵交換ここで暗号化の共通キーが作られてたようです

Frame 15

client -> server

SSH Protocol
    SSH Version 2 (encryption:chacha20-poly1305@openssh.com mac:<implicit> compression:none)
        Packet Length (encrypted): 34c31ecb
        Encrypted Packet: 3dd12d6bdb56ae5ed5a1e2e0582e010605e48952efb30374
        MAC: fae09f0688fcc0786a1fd2f974c2b657
    [Direction: client-to-server]

共通鍵を使って以後の通信をやりとり
ログインユーザ認証もここから

Frame 17

server -> client

SSH Protocol
    SSH Version 2 (encryption:chacha20-poly1305@openssh.com mac:<implicit> compression:none)
        Packet Length (encrypted): 15917057
        Encrypted Packet: c2d8d1fd1bbcd26183d94e91fd7b56ec98ca32f0e06c06e2
        MAC: 95b94d9b6d38d9d4daaeb1669b18baa1
    [Direction: server-to-client]

共通鍵を使って以後の通信をやりとり

まとめ

共通鍵の作成までは意外とシンプルなフローでしたね。
RFCにネゴシエーションは2～3ターンで完了します、とありましたが本当にその通りでした。

やはりといっては何ですが、暗号化用のプロトコルなのでキャプチャしても大した情報は得られませんでした。
公開ホストキーの情報も確認できると思いましたがキャプチャには載ってきませんでした。
本当はどこかに隠されているんでしょうか。こういうの見つけられたらハッカーぽくてかっこいいんですけどね。

鍵交換の部分も詳しく確認していきたいところですが、前回と今回でSSHセッションの大まかな流れが整理できたのでいったん完了にします。

しかしパケットの中身を見るのは楽しいですね。

2020-09-13

SSHのセッション確立フローをもう一回勉強する

業務の一環でNW機器のsshdのアルゴリズムを調べていたんですが、
そういえばSSHのセッション確立のフローについてちゃんと説明出来ないなと思ったのでおさらいします。

アルゴリズムの中身を書いていくときりがないので、どのタイミングで何をしているのかを整理したいと思います。

仕組み自体もそうですが、アルゴリズムの種類も多いので用語で混乱してしまいますね。

SSHで出てくる用語

アルゴリズム

暗号化アルゴリズム
- 共通鍵暗号のAESが一般的
鍵交換アルゴリズム
- Diffie-Hellmanが使われる
メッセージ認証コード(MAC)アルゴリズム
- hmacがよく使われる
ハッシュアルゴリズム
- mac用のハッシュ値を生成
- md5, sha1, sha2など

鍵

ホストキー
- SSHサーバを識別する値
共通鍵
- 暗号化に使われる鍵
公開鍵
秘密鍵
- それぞれログイン時のユーザ認証に使われる鍵

SSHの動作コネクション

大まかな流れ

TCPコネクション確立
サーバ使用できるバージョンを応答
サーバの公開ホストキーの提供
- 公開鍵認証でサーバのFinger printを確認して認証
各アルゴリズムのネゴシエーション
Diffie Hellman関数で鍵交換
- ここで暗号化、複合化用の共通鍵が交換される
共通鍵を使って以後の通信をやりとり
- 以降のコネクションで使われる共通鍵暗号はバイナリパケットプロトコルと呼ばれる
- パケットにMACを使ってデータの整合性を保っている

認証

以下のどちらかでユーザを認証する

パスワード認証
公開鍵認証
- 公開鍵、秘密鍵はここで使われる

なぜ説明できなかったのか、勘違いしていたところ

対象鍵暗号、非対称鍵暗号アルゴリズムがごっちゃになっていた

対象鍵暗号
- DES、AES等
- 鍵の交換は別のアルゴリズムを使用する(Diffie-Hellman等)
非対称鍵暗号
- RSA、ECDSA等

各アルゴリズムの用語は覚えていましたが、何の鍵暗号化の紐づけが忘れてしまっていました。

ホストキーと公開鍵がごっちゃになっていた

公開ホストキーと書かれている場合が多いので公開鍵と間違えていました。

公開ホストキーはSSHコネクション時に確認される
- known_hostsファイルに保存される。情報が無い場合はユーザ認証前に確認される
公開鍵はユーザ認証のみで使う
- 認証キーペアはクライアント側で作成してサーバに配置する

これですっきり。

ファイル、コマンド系

サーバ側

公開ホストキー

ホストキはssh_host_XXX_key.pubとアルゴリズムごとにありますが、私の経験ではecdsaがよく使われています。

/etc/ssh/ssh_host_ecdsa_key.pub # 接続するとclientのknown_hostsに書かれる

ホストキー確認コマンド

ssh-keygen -lv -f /etc/ssh/ssh_host_ecdsa_key.pub

クライアント側

Windowsだとソフトによりけりなので一般的なLinuxで書きます。

各sshサーバのホストキー

$HOME/.ssh/known_hosts

認証鍵ペア

$HOME/.ssh/id_rsa
$HOME/.ssh/id_rsa.pub # こっちをサーバのauthrized_keyに書く。またはssh-copy-id

残課題ホストキーについて

ホストの認証とは何なのか?どう行っているのか?

SSHコネクションを行う際に公開ホストキーでサーバの識別を行いますが、ecdsaの鍵が使われておりこれも公開鍵認証を行っていそうです。

~~ユーザではなくサーバ認証用のキーペアがありそうですが、この周りの資料が出てこなかったのでこの分は今回保留とします。~~

サーバ側でホストキーファイルを確認したら公開鍵と秘密鍵のペアっぽいものがありました。

masashi@PC-ubuntu:~$ ls -l /etc/ssh/ssh_host_*
-rw------- 1 root root  227  8月 17  2019 /etc/ssh/ssh_host_ecdsa_key
-rw-r--r-- 1 root root  176  8月 17  2019 /etc/ssh/ssh_host_ecdsa_key.pub
-rw------- 1 root root  411  8月 17  2019 /etc/ssh/ssh_host_ed25519_key
-rw-r--r-- 1 root root   96  8月 17  2019 /etc/ssh/ssh_host_ed25519_key.pub
-rw------- 1 root root 1675  8月 17  2019 /etc/ssh/ssh_host_rsa_key
-rw-r--r-- 1 root root  396  8月 17  2019 /etc/ssh/ssh_host_rsa_key.pub

公開鍵を確認。

masashi@PC-ubuntu:~$ cat /etc/ssh/ssh_host_ecdsa_key.pub
ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBIYnhbqvzAS0J/yUr7jCGLx88zBL9GqPlrZZLobdBmdkKdLEDIQ2VUCcMdfsZ1IZA0kqStnq61o5s9OGQ0sbzN0= root@PC-ubuntu

クライアント側と比較してみます。
環境の都合でWindowsでの確認ですが、同じですね。
最近のビルドではPowershellにsshが付いているので便利です。

PS C:\Users\masashi> cat .\.ssh\known_hosts
192.168.0.10 ecdsa-sha2-nistp256 AAAAE2VjZHNhLXNoYTItbmlzdHAyNTYAAAAIbmlzdHAyNTYAAABBBIYnhbqvzAS0J/yUr7jCGLx88zBL9GqPlrZZLobdBmdkKdLEDIQ2VUCcMdfsZ1IZA0kqStnq61o5s9OGQ0sbzN0=

ということはサーバ接続時も公開鍵認証でクライアントがサーバを認証していてアルゴリズムはecdsaを使用しているということになりますね。
で、サーバの公開鍵がクライアントのknown_hostsに書かれると。
パスワード認証の場合でも一回は公開鍵認証をしているということですね。

まとめ

普段使っているSSHですが、意外と説明に困ったので今回まとめてみました。
技術的な説明は何もできていませんが、とりあえずはフローの整理ができたので良しとします。

SSHが詳しく書かれている本って意外と無いんですよね。
O'Reilly本を読むのとRFCを読まないとですね。

参考サイト

Understanding the SSH Encryption and Connection Process | DigitalOcean

RFC 4716 - The Secure Shell (SSH) Public Key File Format

RFC 4253 - The Secure Shell (SSH) Transport Layer Protocol

2020-09-08

SQLでマイ出費確認ツールの項目別の件数を出力する

本件について本当は先月やりたかったのですが、いつも使っているノートPCの電源が付かなくなったのもあり先週やっと上半期の支出を計算しました。
(原因はわかりませんが、しばらくACアダプタ繋いでいたら復旧しました。)

以前カード会社の月次CSVをINSERT分に作ったのですが、これのお陰でものの数分で完了してしまいました。
作っといてよかった!

環境

サブ機ノートWindows10のWSL上で動かしています。

PS C:\Users\masashi> Get-WmiObject Win32_OperatingSystem


SystemDirectory : C:\WINDOWS\system32
Organization    :
BuildNumber     : 18363
RegisteredUser  : user
SerialNumber    : 00330-80000-00000-AA868
Version         : 10.0.18363

PS C:\Users\masashi> wsl uname -a ; sqlite3 --version
Linux DESKTOP-N095TND 4.4.0-18362-Microsoft #836-Microsoft Mon May 05 16:04:00 PST 2020 x86_64 x86_64 x86_64 GNU/Linux
3.28.0 2019-04-16 19:49:53 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50

PS C:\Users\masashi> wsl cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.2 LTS"

INSERT部分は割愛しまして、
半期毎の集計スクリプトがあるので早速出してみましょう。

集計スクリプト

生活圏がばれますが、まあ気にしないですw

masashi@DESKTOP-N095TND:~/expenses$ cat syukei2020\ first\ half.sql
.headers on
.mode column

select 分類 as 決済内容,sum(合計) as 合計 from
  (select case contents
    when (select contents from expenses where contents like '%ａｕ電話利用料%') then '通信費'
    when (select contents from expenses where contents like '%Ｊ：ＣＯＭ%') then '通信費'
    when 'インターネットイニシアティブ' then '通信費'
    when 'ＮＥＴＦＬＩＸ．ＣＯＭ' then '通信費'
    when 'ヤフージャパン' then '通信費'
    when 'DIGITALOCEAN.COM (DIGITALOCEAN.)' then '通信費'
    when 'ミーナ津田沼' then '食費'
    when 'イオンリテール' then '食費'
    when 'イトーヨーカ堂　（サインレス' then '食費'
    when 'ＪＲ東日本モバイルＳｕｉｃａ' then 'SUICA'
    when '家賃' then '住宅費'
    when '光熱費' then '住宅費'
    ELSE 'その他' END AS '分類',
    sum(payment) AS '合計'
    from expenses
    where date BETWEEN '2020/01/01' and '2020/06/30'
    group by contents)
 group by 分類
 UNION ALL
 select '-',sum(payment) as 合計 from expenses
 where date BETWEEN '2020/01/01' and '2020/06/30';

sqliteの仕様で日本語でのカラムがずれてしまうのはご了承ください。

masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2020.db < syukei2020\ first\ half.sql
決済内容        合計
----------  ----------
SUICA       356250
その他         280821
住宅費         568000
通信費         69114
食費          104905
-           1379090

計137万...!?
いくらなんでもこんなに使った覚えはありません。
特に今はコロナ禍で外食等の出費は減っているはずです。
ここは去年のデータと比較してみましょう。

集計スクリプトは上記のものと日付が違うだけです。

masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2019.db  < syukei2019\ first\ half.sql
決済内容        合計
----------  ----------
SUICA       185000
その他         211674
住宅費         426000
通信費         59273
食費          70291
-           952238

95万ちょいですね。
今年は40万近く増えてます。やはり何かおかしい...

サマリだと分類をざっくりに出しすぎなので項目ごとにもう一度集計してみましょう。

COUNTとGROUP BY

項目毎の集計は難しい事なくてCOUNTとGROUP BYで出来ます。
COUNTのカラム作って項目でGROUP BYすればOKです。
ついでに金額で降順にソートすればいい感じになるかな。

件数集計スクリプト

masashi@DESKTOP-N095TND:~/expenses$ cat count2019\ half.sql
.headers on
.mode column

select contents, COUNT(*) from expenses
where date BETWEEN '2019/01/01' and '2019/06/30'
GROUP BY contents
ORDER BY COUNT(*) DESC;

昨年の件数集計

masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2019.db  < count2019\ half.sql
contents        COUNT(*)
--------------  ----------
ＪＲ東日本モバイルＳｕｉｃａ  86
イトーヨーカ堂　（サインレス  14
ミーナ津田沼          10
イトーヨーカドー　食品     9
イオンリテール         7
DIGITALOCEAN.C  6
ヤフージャパン         6
光熱費             6
家賃              6
Ｊ：ＣＯＭ　２サービスパック  6
ＮＥＴＦＬＩＸ．ＣＯＭ     6
インターネットイニシアティブ  5
スウイ―ト　カヴアカヴア    5
ENT-INN-CORP (  3
イトーヨーカ堂         2
ツダヌマ　パルコ        2
MOJANG.COM (ST  1
ONEDREAM (0368  1
くまざわ書店　津田沼店     1
つり具ランド　館山総合館    1
イトーヨーカドー        1
イトーヨーカドー専門店     1
ノジマ　津田沼店        1
ラクテンペイ　ハリケ―ン    1
ラクテンペイ　レプマ―ト    1
０１月分　ａｕ電話利用料    1
０２月分　ａｕ電話利用料    1
０３月分　ａｕ電話利用料    1
０４月分　ａｕ電話利用料    1
０５月分　ａｕ電話利用料    1
１２月分　ａｕ電話利用料    1
ＪＲ東日本モバイルＳｕｉｃａ  1
ＪＲ西日本　５４８９サービス  1
ＮＨＫ　放送受信料       1

まあこっちがベースになるので特にコメントなしです。
そういえば去年マイクラ買いましたねw

今年の集計

同じスクリプトで今年の日付に変更してます。

masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2020.db < count2020\ half.sql
contents        COUNT(*)
--------------  ----------
ＪＲ東日本モバイルＳｕｉｃａ  109
イトーヨーカドー　食品     25
ミーナ津田沼          12
光熱費             8
家賃              8
ＮＥＴＦＬＩＸ．ＣＯＭ     8
Ｊ：ＣＯＭ　サービス利用料   7
DIGITALOCEAN.C  6
イトーヨーカドー        6
ヤフージャパン         6
自遊空間　津田沼北口店     5
スウイ—ト　カヴアカヴア    4
オナマエドツトコムドメイン   3
ヒマラヤ　モレラ岐阜店     3
１２月分　ａｕ電話利用料    3
ＪＲ西日本　５４８９サービス  3
ＴＳＵＴＡＹＡ　ＢＯＯＫ　Ｓ  3
ONEDREAM (714-  2
イオンリテール         2
インターネットイニシアティブ  2
ＡＭＡＺＯＮ．ＣＯ．ＪＰ    2
CROP (05700018  1
ニトリ             1
ニトリネット          1
ノジマ　津田沼店        1
ヤフーかんたん決済       1
０１月分　ａｕ電話利用料    1
０２月分　ａｕ電話利用料    1
０３月分　ａｕ電話利用料    1
０４月分　ａｕ電話利用料    1
０５月分　ａｕ電話利用料    1
ＡＸＥＳ　ＨＭ         1
ＮＨＫ　放送受信料       1
ＷＩＬＤ—１幕張店       1

Suicaの件数が増えたのはリモートワークでコンビニに行く回数が増えたからかな?
しかし、ちょっと怪しいところ発見しました。

光熱費             8
家賃              8
ＮＥＴＦＬＩＸ．ＣＯＭ     8
Ｊ：ＣＯＭ　サービス利用料   7
オナマエドツトコムドメイン   3
１２月分　ａｕ電話利用料    3

この辺は月次の固定費なので6回しかないはずですが多いですね...

犯人を探せ

犯人は私で、INSERT文変換スクリプトのミスでした。
ミスというか変換ツールを複数回実行してしまったようでダブったレコードがありました。

DBを修正します。(手動で)
幸いにも1月日付分だけでした。

重複のハンドリングも検知する仕組みを作らないとですね。

再集計

OK。
とりあえず固定費のカウントは戻りました。
Suicaも重複が無くなって少し減りました。

masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2020.db < count2020\ half.sql
contents        COUNT(*)
--------------  ----------
ＪＲ東日本モバイルＳｕｉｃａ  82
イトーヨーカドー　食品     20
ミーナ津田沼          10
DIGITALOCEAN.C  6
ヤフージャパン         6
光熱費             6
家賃              6

では再度集計してみます。

masashi@DESKTOP-N095TND:~/expenses$ sqlite3 Credit_expenses_2020.db < syukei2020\ first\ half.sql
決済内容        合計
----------  ----------
SUICA       164000
その他         206920
住宅費         426000
通信費         54729
食費          99049
-           950698

いいですね。
ほぼ昨年と同じくらいの出費です。
悲しいかな、昨年とまったく同じ生活をしているということですねw

むすび

この集計ツールを作ったお陰で何年もやってこなかった収支の計算が非常に楽になりました。
キャッシュレス支払いが主体の生活ではありますが、月次の明細CSVをDBにインポートするだけでトラッキング、集計ができるのでこれにかける時間はほとんどありません。
(明細が全角なのはアレですが)

ただ、ツールの重複チェックが無かったのでインポートをミスってしまい本記事を書くことになりました。
発見して修正出来たので結果オーライですはあります。

本件で半年ぶりにSQLいじりました。
極小規模DBですがいろいろな集計ができるので触ってて面白いですね。

あとは現金払いをうまくトラッキングできればこのツールも完ぺきになるんですがね。
どう取り入れるか悩みモノです。

ちなみに収入を含めた収支は安月給のため恥ずかしくて公開できません…
ローカルでひっそり更新します。

本シリーズ

2020-08-28

python exceptの超簡単なエラーハンドリングあれこれ

pythonで例外処理をするとき

try:
  <code>
except:
  <code>

を使いますよね。
私が勉強した本にもこう書いてあって例外ケースを飛ばして進めたいときは使っています。

また、チュートリアル的なサイトでは

except XXXError as e:
  print(e)

みたいな文がよく出てきてこれなんだろうと思っていたんですが
この書き方を使う時があったのでメモしておきます。

エラーの種類を分類して処理するときに使うものなんですね。

pythonのエラー文

pythonに限らずですが、構文がミスってたりするとエラーが出ますよね。

試しに適当に打ってみると、

>>> x = x + 1
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
NameError: name 'x' is not defined
>>> with open('test.txt', 'r') as f:
...   a = f.read()
...
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
FileNotFoundError: [Errno 2] No such file or directory: 'test.txt'
>>>
>>> 1 / 0
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ZeroDivisionError: division by zero

と上記の例で

NameError
FileNotFoundError
ZeroDivisionError

と3種類のエラーメッセージが出ました。

exceptの後にこのエラーの種類を書くことで特定のエラーのみをハンドリングする、ということだったんですね。

try文で試し

先ほどのエラー例をtryを使って書いてみます。

指定なし

>>> try:
...   x = x + 1
... except:
...   print('Error')
...
Error

エラーなのでexceptの処理をしていますが、とりあえず"Error"を出力するだけなので詳細がわかりませんね。
また、マニュアルにはexceptだけだとワイルドカード扱いで全てのエラーを隠してしまうので注意せよと書いてありました。

NameError(想定のエラー)

>>> try:
...   x = x + 1
... except NameError as e:
...   print(e)
...
name 'x' is not defined

そのままですが、変数未定義を想定したエラーメッセージが出てますね。

FileNotFoundError(想定外のエラー)

>>> try:
...   x = x + 1
... except FileNotFoundError as e:
...   print(e)
...
Traceback (most recent call last):
  File "<stdin>", line 2, in <module>
NameError: name 'x' is not defined

変数未定義に対しFileNotFoundの処理しか書いていなかったのでスタックトレースも出てます。(別に問題なさそうだけど)

Exception

ちなみにこちらを書いておくとエラーの種類を問わず出力してくれます。

>>> try:
...   x = x + 1
... except Exception as e:
...   print(e)
...
name 'x' is not defined


>>> try:
...   with open('test.txt', 'r') as f:
...      a = f.read()
... except Exception as e:
...   print(e)
...
[Errno 2] No such file or directory: 'test.txt'

変数未定義、ファイルが存在しない、とどちらも出力できてますね。

まとめ

自分が作るツールは自分で使うものかつ小規模なものなのであまりエラーハンドリングをする必要がなく、機会が無かったのですが今回少し整理できました。

とはいえどう使うのがいいのかまではイマイチわかっていません。
入力させる処理とセットで入力ミスの戻しとかに使うんでしょうか。
はたまたシステム的なエラーを見つけるためでしょうか。

私が今回発見したのは設計時のExcelのインプット資料をpythonでテキストファイルに落とす時でした。
社外秘の資料なのでコードもお見せすることはできないのですが、新発見だったので本エントリに残しておきます。

初歩的な処理になりますが、まだまだ使えてない機能がありますね。

また、タイトルにあれこれと書きましたが、例外の種類がめちゃくちゃあるのでほんの一部だけです…

参考サイト

8. エラーと例外 — Python 3.8.5 ドキュメント

2020-08-05

｢エンジニアためのマネジメントキャリアパス｣を読んだ

今週久しぶりに大阪へ出張に行って来ました。

タイトルの本は1年くらい前?にtwitterで出回っていて、私も購入したものの寝かせてしまっていたのですが、今回の出張の空き時間でようやく読めました。

www.oreilly.co.jp

簡単に印象に残った点を残しておきます。

よかった点

各管理者の定義がある

メンターから始まり終盤の章ではCTOになった場合の在り方を書いています。
各管理者のステップアップごとに書いてあるので、自分が昇進した才に辞書のように引いて指針と出来そうです。
(私にはその予定はありませんが)

「できる上司」とは何かという文が冒頭にありますが、その定義も記述しています。

部下と定期的に(1対1で)ミーティングを行い、現状を把握する
部下のスキルアップ、キャリアアップの支援を行う

というのが主な内容です。

キャリアアップごとに仕事の規模は増えていきますが、基本的にこの2つを大切にすることがキモの様です。

もちろんこの本だけで断定できるものでないかもしれませんが、
キャリアアップ系の本を読むのは初めてなので勉強になりました。

具体例がある

筆者の経験と共に書かれているので、各管理者に求められるスキルと具体的な事例があります。

この管理者になったAさんはこういう進め方で失敗したけどBさんはこういう方法で上手くいきました、というのがキャリアごとにありますのでイメージがしやすいです。

べし、べからず集がある

各管理者にキャリアアップしてこういう仕事をする際のべし、べからず集がでてきます。
こういうのも迷ったときに読み返したくなりますね。

技術系管理職に特化している

タイトルの通り技術系管理者主体で書かれているので、
他の指南書にありそうなものは割愛されたり、引用として文章中にでてくるだけです。

他の本でありそうなものは書かずに趣旨に沿ったものだけ書いてある本は
他と内容が重複しないので、この本を読んでよかったなと思いますね。

まとめ

簡単な感想文になってしまいましたが、久しぶりに技術本以外の本を読みました。
とても全部の内容は書ききれないので記述しませんが、面白かったです。

私は現在、人売りの会社で現場に出向の身なので、本で出てくる様な自社プロダクト在りきの昇進はできませんが、書内の根底にある

自分に求められている役割を把握し果たせるよう努める
好奇心を持ち続ける

ということは忘れずに日々を過ごしていこうと思います。

私もNW機器をゴリゴリ触っているエンジニアなのでまだ管理者をやりたいという思いは無いのですが、本を読んでいると管理者も技術とは別の視点で楽しそうな仕事だなと思いますね。

管理者は技術力のない人がやる仕事だとか、かつてあったけど忘れてしまうみたいな噂やイメージがありましたが、この本で実情を学べてよかったです。
前線を離れて別の仕事をしているので当然ですよね。
キャリアアップしたら部下に委任するのも仕事だとも書いてありました。

しかし、キャリアアップするごとに企業の戦略やチームを導く上で、自分で手を動かすことが無くても新しい技術は常に学んでいかなければならないとも書かれています。
自分は管理者になれるかわかりませんが、この気構えは忘れないでいたいものです。

2020-07-31

Privoxy + Tor 環境で特定のURLをTorから除外する

最近pythonのコードしかブログに書いていなかったので、久しぶりにインフラ屋らしいこと書きます。

昔メイン機にPrivoxyというプロキシサーバを立てたのですが、Torも被せられるという事でハッカーを目指す私はTorを導入して通信経路の隠蔽を行おうと思います。

専用ブラウザ入れなくてもプロキシ指定すればTorが効くので凄く便利ですね。

環境

メインwindows機のwsl上にPrivoxyを立てているので同じ環境にインストールします。

paloma69.hatenablog.com

Torのインストールは割愛します。

Torとの連携方法

Privoxyのconfigファイルに1行追加するだけです。

forward-socks5 / 127.0.0.1:9050 .

これで全てのHTTPリクエストはプロキシ経由でTorトンネルをリレーします。
仕方ないことですが、有効にするとレスポンスの遅延が感じられますね。

特定のURLを除外したい

Torトンネル経由になったのはいいのですが、Torの特性上レスポンス時間や一定の期間でグローバルIPが変わってしまいます。
これでは動画サイトがスムーズに見れなかったり、通販等で不正アクセスと見なされて注文が通らないかもしれません。
そこで特定のドメインはTorトンネルを使用しないようにしたいと思います。

方法は簡単で、トンネルから除外したいドメインをconfigファイルに記載するだけです。
マニュアル通りなのですが、Tor用の設定より下に書かないと動きませんでした。

forward-socks5 / 127.0.0.1:9050 . # この設定より下に除外したいドメインを書く
forward         .youtube.com    .
forward         .netflix.com    .
forward         .amazon.co.jp   .

試しにという感じですがこれでyoutube、netflix、amazonはTorのトンネルの対象外になります。

ちなみに行末の“.”は他のプロキシに転送せずPrivoxyからそのままリクエストを投げる、という設定です。

確認方法

上記の設定の確認をしましょう。
Privoxyのデバッグ機能等使ってもいいんですが、NW屋らしくcurlを使ってIPアドレス確認サイトから確認してみます。
私はMy Global IPをよく使います。

プロキシ無し

IPを全部書くと住所付近が特定されてしまうので一部マスクします。
セキュリティ上Torを導入したのに自分から晒したら世話無いですからねw

masashi@DESKTOP-986MNSO:/etc/Privoxy$ curl -s http://www.myglobalip.com/ | grep -P "(\d{1,3}\.){3}\d{1,3}"
        <h3 id="default"><span class="ip">113.xx2.xx4.20</span></h3>

これが自宅のグローバルIPです。

プロキシあり

IPが変わってます。トンネル経由になっている様ですね。

masashi@DESKTOP-986MNSO:/etc/Privoxy$ curl -s -x localhost:8118 http://www.myglobalip.com/ | grep -P "(\d{1,3}\.){3}\d{1
,3}"
        <h3 id="default"><span class="ip">162.247.74.74</span></h3>

トンネル対象の検証のためMy Global IPのドメインを追加する

検証のためconfigファイルにMy Global IPのドメインを追加します。

forward-socks5 / 127.0.0.1:9050 .
forward         .youtube.com    .
forward         .netflix.com    .
forward         .amazon.co.jp   .
forward         www.myglobalip.com      .

Privoxyを再起動。

masashi@DESKTOP-986MNSO:/etc/Privoxy$ sudo /etc/init.d/Privoxy restart
 * Restarting filtering proxy server Privoxy                                                                     [ OK ]

プロキシ無しの時のIPになっていれば成功で、トンネルの対象外となっているわけですね。

masashi@DESKTOP-986MNSO:/etc/Privoxy$ curl -s -x localhost:8118 http://www.myglobalip.com/ | grep -P "(\d{1,3}\.){3}\d{1,3}"
        <h3 id="default"><span class="ip">113.xx2.xx4.20</span></h3>

OK!
元のグローバルIPが見えてます。
これで他のドメインもTor無しで動きそうですね。

むすび

昔の話になりますが、自宅のプロキシサーバにPrivoxyを使ったのはTorと連携できるからでした。
諸々の事情があってTorの設定をするのが遅れてしまいましたが、ようやく実現できました。

この業界に入ったばかりの頃はVPN系のトンネリングって仕組みもよくわからない夢のような技術の話でしたが、
なんてことはない、ただのパケットヘッダの付け替えなんですよね。

今回はPrivoxyの設定をチューニングしましたが、機会があればTorの方の設定も見てみたいと思います。

おまけ

IPアドレスのgrepで使ったperlの正規表現は以下を使いました。

(\d{1,3}\.){3}\d{1,3}

ターミナル操作でIPアドレスを抜きたいときによく使ってます。
プライベートアドレスのクラスA、B、Cとも対応できます。

masashi@DESKTOP-986MNSO:~$ cat ipaddress
10.0.0.1
172.16.0.1
192.168.0.1
masashi@DESKTOP-986MNSO:~$ cat ipaddress | grep -P "(\d{1,3}\.){3}\d{1,3}"
10.0.0.1
172.16.0.1
192.168.0.1

参考サイト

Privoxy

http://www.Privoxy.org/user-manual/config.html

https://wiki.archlinux.jp/index.php/Tor

2020-07-16

pythonのPillowライブラリで画像の切り貼り貼り付け編

貼り付け編

画像ファイルの一部を切り取って貼り付けたいという話ですが、前回無事切り取ることができました。
今回は貼り付け編です。

貼り付け用イメージファイルの作成

画像を張り付ける大元のファイルを作ります。
背景色は何でもいいんですが白にしました。
この時点では真っ白な空の画像ファイルです。

>>> newimfile = Image.new('RGBA', (1920, 1080), 'white')

切り取ったファイルを日付順にソート

前回と同じ方法で更新日順にソートします。

resultlist = sorted(glob.glob('result*.png'), key=lambda f: os.stat(f).st_mtime)

ループで全ファイル貼り付け

各画像ファイルの貼り付けですが、1920 * 1080でファイルを作成しているのでサイズを超えないようにします。
また、見やすいように左端から右下へとZ型に貼り付けたいと思います。

こんな順番で張り付けたい

f:id:paloma69:20200716223007p:plain

水平に張り付行けていって端までいったら次の段に行きます。

HDサイズだとx座標1920までいったらy座標を移動すればいいですね。
1920を越えないようにx座標始点を1520超えたらy軸移動するようにしました。
1720だとなぜかはみ出たので1520で指定しています。

コードの書き方としてはx座標が1520を超えるまではx座標に貼り付け、
超えたらx座標リセット、y座標をプラスして再スタートという形になりました。

本件のループで初めてcontinueを使いました。
continueはこう使うんですねえ。

貼り付けるコード

画像を張り付けるにはpaste関数を使います。
開いた画像をコピーして張りたいファイルと座標を記載します。

流れは前回と同じなので、これを流します。

>>> x2, y2 = 0, 0
>>> for a in resultlist:
...     imfile = Image.open(a)
...     pastefile = imfile.copy()
...     newimfile.paste(pastefile, (x2, y2))
...     if x2 <= 1520:
...         x2 += 200 # x座標が1520を超えるまでは200置きに貼り付け
...         continue
...     x2 = 0  # x座標が1520を超えたらx座標リセット
...     y2 += 120 # x座標が1520を超えたらy座標を追加
...

ループ処理が通ったので確認

>>> newimfile.show()

f:id:paloma69:20200716223255p:plain

いいですね。イメージ通りです。

セーブします。

>>> newimfile.save('result_summary.png')

あとはこれを手動でトリミングして余白を削除します。

同じ手順で勝敗の結果もまとめました。

f:id:paloma69:20200716223340p:plain

以上がこの記事で使ったサマリ画像の作成裏話です。

使った関数のメモ

画像のコピー

imfile = Image.open(a)
pastefile = imfile.copy() # 元ファイルに影響無い様にコピーする

貼り付け

newimfile.paste(pastefile, (x2, y2)) #newimfileイメージにpastefileをx2, y2の位置に貼り付け

画像の表示

newimfile.show()

むすび

プログラミングでGUI処理をしようとすると大変ですが、
今回の画像処理も難しいかなと思いきや、前後編合わせて3時間ほどで出来ました。

プログラミング処理の便利なところは微修正がありつつも基本の処理はそのままなので、何回でもやり直せるのがいいですね。

今回のような作業では手動コピペだとサイズを揃えるのも大変だし、
やり直そうとするとはじめから、となってしまうと思います。

その点今回は何度か失敗したものの、サイズを調整するだけでイメージした画像の作成まで行けてしまいました。

画像処理系のタスクはやったこと無いですが、こんなに便利で楽しいライブラリがあるので
今回の経験をどこかに活かしたいものです。

環境

セッション確立のおさらい

SSHのセッションサマリ

キャプチャの詳細

まとめ

SSHで出てくる用語

鍵

SSHの動作 コネクション

認証

なぜ説明できなかったのか、勘違いしていたところ

対象鍵暗号、非対称鍵暗号アルゴリズムがごっちゃになっていた

ホストキーと公開鍵がごっちゃになっていた

ファイル、コマンド系

サーバ側

クライアント側

残課題 ホストキーについて

まとめ

参考サイト

環境

COUNTとGROUP BY

昨年の件数集計

今年の集計

犯人を探せ

再集計

むすび

本シリーズ

pythonのエラー文

try文で試し

まとめ

参考サイト

よかった点

各管理者の定義がある

具体例がある

べし、べからず集がある

技術系管理職に特化している

まとめ

環境

Torとの連携方法

特定のURLを除外したい

確認方法

プロキシ無し

プロキシあり

トンネル対象の検証のためMy Global IPのドメインを追加する

むすび

おまけ

参考サイト

貼り付け編

貼り付け用イメージファイルの作成

切り取ったファイルを日付順にソート

ループで全ファイル貼り付け

貼り付けるコード

使った関数のメモ

むすび

SSHの動作コネクション

残課題ホストキーについて