2022-07-30

PostgreSQLで自分が作成したテーブルの数を表示する

SELECT count(relid) FROM pg_stat_user_tables;

参考

https://www.postgresql.jp/document/13/html/monitoring-stats.html

2022-07-30

認証・認可関連の言葉の整理

WEBシステム開発における認証・認可関連の言葉の整理をする。

●識別子（Identifier）

集合の中から要素を識別するための情報。
識別子の例は、UUIDやメールアドレス。

●アカウント

システム内で行動を行なった事を記録する際に、その行動を行なった者を示すもの。

●認証（Authentication）

認証される側がアカウントの集合のうちの特定のアカウントである事を示す証を示して、認証する側が、特定のアカウントである事を認めること。
証の例は、IDとPassword。

●権限（Authority）

アカウントが、目的物（オブジェクト）に対して、ある行動を出来る権利。
ある行動の例は、作成、削除など。

●所有者

目的物を所有しているアカウント。
所有者は、目的物に対する全ての権限を持つことが多い。
一方で所有者以外のアカウントは、目的物に対する権限が無ければ何も出来ないことが多い。

●ロール

権限の集合に名前をつけたもの。

●認可（Authorization）

認可する側が認可される側に、権限またはロールを付与（Grant）すること。

●ログイン

認証される側が認証済みである事を認証する側に対して簡略化して示す機能。
例えば、Cookieを利用して実現される。

2022-07-24

PostgreSQLの内部結合（INNER JOIN）と外部結合（OUTER JOIN）の動きについて

1. 前提テーブル

1-1. weatherテーブル

city	temp_lo	temp_hi	prcp	date
San Francisco	46	50	0.25	1994-11-27
San Francisco	43	57	0	1994-11-29
Hayward	37	54		1994-11-29

1-2. citiesテーブル

name	location
San Francisco	(-194, 53)

2. 定義

2-1. 内部結合（INNER JOIN）のSQL

SELECT
    weather.city,
    weather.temp_lo,
    weather.temp_hi,
    weather.prcp,
    weather.date,
    cities.location
FROM
    weather
      INNER JOIN cities ON weather.city = cities.name;

2-2. 左外部結合（LEFT OUTER JOIN）のSQL

SELECT
    weather.city,
    weather.temp_lo,
    weather.temp_hi,
    weather.prcp,
    weather.date,
    cities.location
FROM
    weather
        LEFT OUTER JOIN cities ON weather.city = cities.name;

2-3. 右外部結合（RIGHT OUTER JOIN）のSQL

SELECT
    weather.city,
    weather.temp_lo,
    weather.temp_hi,
    weather.prcp,
    weather.date,
    cities.location
FROM
    weather
        LEFT OUTER JOIN cities ON weather.city = cities.name;

3. 定義

結合元テーブル

FROM句で指定したテーブルを結合元テーブルという。
上記2のSQLでは、weather がそれにあたる。

結合先テーブル

INNER JOIN句で指定したテーブルを結合先テーブルという。
上記2のSQLでは、cities がそれにあたる。

4. 内部結合（INNER JOIN）と外部結合（OUTER JOIN）のうごき

4-1. 上記2-1の内部結合（INNER JOIN）のSQLを投げた場合

結合元テーブルの全ての行を読み取る。
結合先テーブルの行を1つずつ読み取り、ON句で指定した条件に一致した結合先テーブルの行のみを結合元テーブルに結合する。
つまり、条件に一致しない結合先テーブルの行は捨てられる。

4-2. 上記2-2の左外部結合（LEFT OUTER JOIN）のSQLを投げた場合

結合元テーブルの全ての行を読み取る。
結合先テーブルの行を1つずつ読み取り、ON句で指定した条件に一致した結合先テーブルの行を結合元テーブルに結合する。
条件に一致しない結合先テーブルの行はNULLとして結合する。

4-3. 上記2-3の右外部結合（RIGHT OUTER JOIN）のSQLを投げた場合

結合元テーブルの全ての行を読み取る。
結合先テーブルの行を1つずつ読み取り、ON句で指定した条件に一致した結合先テーブルの行を結合元テーブルに結合する。
条件に一致しない結合先テーブルの行は、結合元テーブルの行がNULLとして結合先テーブルの行と結合する。

2022-07-24

PostgreSQLのモデル

https://github.com/sakuoden/PostgreSQL-model

2022-07-17

公開鍵（public key）と秘密鍵（private key）の仕組みについて

公開鍵と秘密鍵の仕組みの面白さがようやく分かったので抽象度を上げてメモする。

前提定義

公開鍵と秘密鍵...公開鍵と秘密鍵は、1対1の関係で紐づけられる。
公開鍵（public key）...公開鍵で暗号化したモノは、紐づけられた秘密鍵でのみ復号することが出来る。また紐づけられた1つの公開鍵は複数の人が持つことを前提としている。
秘密鍵（private key）...秘密鍵で暗号化したモノは、紐づけられた公開鍵でのみ復号することが出来る。また秘密鍵は1人だけが持つことを前提としている。

例

前提

さくらさん、おいちゃん、おばちゃんは、共通の公開鍵を持つ。
寅さんは、1つの秘密鍵を持つ。
ひろしさん、タコ社長は、鍵を持たない。

公開鍵を使った暗号化

さくらさんが、公開鍵で暗号化した手紙の文章は、寅さんしか読むことが出来ない。
おいちゃん、おばちゃん、ひろしさん、タコ社長は手紙を受け取っても文章を読むことが出来ない。

一方で、寅さんは手紙の文章を読むことが出来ても、さくらさんが公開鍵で暗号化したものなのか、おいちゃん、おばちゃんが公開鍵で暗号化したものなのかは分からない。
なぜなら、さくらさん、おいちゃん、おばちゃんは共通の公開鍵を持つからである。

つまり、公開鍵による暗号化は文章の内容を1人だけに読ませることが出来ることに特化していると言える。
しかし、誰が暗号化したのかまでは分からない。つまり書いた人を特定することは出来ない。

秘密鍵を使った暗号化

寅さんが、秘密鍵で暗号化した手紙の文章は、さくらさん、おいちゃん、おばちゃんしか読むことが出来ない。
ひろしさん、タコ社長は手紙を受け取っても文章を読むことが出来ない。

さくらさん、おいちゃん、おばちゃんは寅さんが秘密鍵で暗号化した手紙の文章を公開鍵で復号して読むことが出来れば、それは寅さんが暗号化したもの = 寅さんが書いた手紙であるということが分かる。
なぜなら、秘密鍵で暗号化したモノは、1対1で紐づけられた公開鍵でのみ復号すること出来るが、その秘密鍵は1人しか持っていないからである。

つまり、秘密鍵による暗号化は、文章を誰が書いたのかを特定することに特化していると言える。
しかし、文章の内容は公開鍵を持っている人であれば読むことが出来るため、文章の内容の開示を1人だけに特定することは出来ない。

誰が書いたのかを特定するという事は、誰であるかを証明する事であり、これを 認証 と言う。
よって、秘密鍵により暗号化して、公開鍵で復号することにより誰であるかを特定するという方法で 認証 されるユースケースがある。
その例が SSHの公開鍵認証 である。

参考

https://milestone-of-se.nesuke.com/sv-advanced/digicert/public-private-key/

作記録

記憶代わり