Open2023/10/23にコメント追加16

PostgreSQL関連

\dp は PostgreSQL の psql コマンドラインツールで使用されるコマンドで、データベースの中の特定のオブジェクトに対するアクセス権限を表示します。

このコマンドの出力は、以下の列で構成されています。

Schema: オブジェクトが属するスキーマ名。
Name: オブジェクトの名前。
Type: オブジェクトのタイプ（例：テーブル、ビューなど）。
Owner: オブジェクトの所有者。
Access privileges: これが最も重要な部分で、ユーザーとロールに対するアクセス権限を示します。
Column privileges: カラムレベルでの特定の権限があれば表示されます。
Policies: オブジェクトに対するRLS（Row-Level Security）ポリシーがあれば表示されます。

Access privileges 列に注目すると、以下のような内容が表示されることがあります。

username=arwdDxt/rolename

ここで各文字が意味する権限は次の通りです。

a: INSERT (追加)
r: SELECT (読み取り)
w: UPDATE (更新)
d: DELETE (削除)
D: TRUNCATE (テーブルの内容の切り捨て)
x: REFERENCES (外部キーの参照)
t: TRIGGER (トリガの使用)

最後の部分（この例では /rolename）は、その権限が指定されたロールにも適用されることを示します。

このように \dp コマンドは、データベース内のオブジェクトに対するアクセス権限の現在の状態を素早く確認するための強力なツールです。

pg_stat_tmpディレクトリとは
統計サブシステム用の一時ファイルを保有するサブディレクトリ
ディレクトリ内のデータは、データベースの動作に関する統計やデータベースのパフォーマンスをモニタリングするためのもの。一時的なデータのため、永続的なデータを保存するためのものではない。

postgres_fdwについて

contribモジュールとは

PostgreSQLにおいて、コア機能とは別に提供される追加モジュールのこと。

追加機能
様々な追加機能やユーティリティが含まれ、データベースの機能を拡張できます。
オプショナル
コア機能とは異なり、contribモジュールはオプショナルで、必要に応じてインストールや使用ができます。
コミュニティ支援
PostgreSQLコミュニティによって開発・支援され、新機能のテストやコミュニティからのフィードバックを通じて改善されます。

オプションについて

fetch_sizeは1回のフェッチで取得する行数を指定
batch_sizeは個々のINSERTで挿入する行数を指定

FILLFACTORについて
テーブルファイルのデフォルトのFILLFACTORは100%に設定されている
インデックスファイルのデフォルトのFILLFACTORは90%に設定されている

バックエンドプロセスについて
クライアントから接続要求を受けたときに生成されるプロセス。
SQLの実行はバックエンドプロセス内で行われる。

PostgreSQLにおけるバックエンドプロセスは、基本的にはクライアントの接続を処理するプロセスです。これには、クエリの実行、データの読み書きなどが含まれます。以下に、そのコンセプトを簡単に説明します。

### バックエンドプロセスの概要
1. **クライアント接続**: クライアント（例：アプリケーションやpsqlコマンドラインツール）がPostgreSQLデータベースに接続を試みる。
  
2. **バックエンドプロセス生成**: 接続が成功すると、PostgreSQLはその接続のために新しいバックエンドプロセスを生成します。このプロセスは、クライアントからの命令を処理し、データベースとクライアントの間でデータをやり取りします。

### バックエンドプロセスのタイプ
主に、以下の２つのタイプが存在します。
1. **User Backends**
   - クライアントからの接続ごとに生成されます。
   - クライアントからのクエリを受け取り、実行します。

2. **Background Worker Processes**
   - 自動的に生成され、特定のタスク（例：オートバキューム、レプリケーションなど）を背後で実行します。
   - システムの健全性やパフォーマンスを維持するために働きます。

### クエリの処理
クライアントがクエリを送信すると、対応するバックエンドプロセスがそれを受け取り、以下のステップを実行します。
1. **パース（解析）**: クエリ文字列がSQL文として正しい構文であるかを確認。
2. **プランニング**: 最適な実行計画を生成。
3. **実行**: 実行計画に基づいてクエリを実行。

### ハンズオン
バックエンドプロセスを観察する一つの方法は、`pg_stat_activity`ビューを利用することです。このビューは、現在のデータベースのアクティビティ、特にバックエンドプロセスに関する情報を提供します。

```sql
SELECT * FROM pg_stat_activity;

上記のクエリを実行することで、現在活動中の各バックエンドプロセスの状態、実行中のクエリなどを観察できます。

チェックサムについて

PostgreSQLのデータブロックの中身が万が一壊れた時に、早期に検出できるようにするための仕組み

データベースクラスタの初期化時（initdbコマンドの実行時）に設定する必要がある
チェックサムを有効にするためのコマンドは以下の通り

initdb -k /path/to/data/directory

「checksums」のchec「k」sumsなのかな？
一度データベースクラスタが作成されてしまうと、既存のデータベースに対して後からチェックサムを有効・無効に切り替えることはできない

チェックサムの機能

チェックサムが有効化されたデータベースクラスタでは、各ページの最後にチェックサム値が格納されます。データベースがページを読み込む際、ページの内容から計算したチェックサムが、格納されているチェックサム値と一致するかどうかが確認されます。

一致する場合
ページのデータは正しいとされ、通常通りの処理が続行されます。
一致しない場合
ページのデータが何らかの理由で破損しているとみなされ、エラーが報告されます。

利点と制約

利点
データ破損を早期に検出できる。
システムやストレージの問題によるデータの不整合や破損を特定できる。
制約
I/Oのオーバーヘッドが生じる可能性がある。
既存のデータベースに対しては適用できない。

チェックサムの失敗が報告されるビューはpg_stat_databaseである

通信経路暗号化（SSL）について

PostgreSQLは標準でSSLを用いた通信経路の暗号化をサポートしている

利用するためには

OpenSSLがPostgreSQLサーバだけでなくクライアントの両方にインストールされている
PostgreSQLのビルド時に、SSL接続を有効にするオプションを付与する
---with-opensslオプションを利用する
RPMでインストールした場合はデフォルトでonになっている
インストール後、postgresql.confファイルでsslパラメータの設定値を「on」に指定してPostgreSQLを起動する

参考

データベースの複製について

IS_TEMPLATEをtrueにすることでテンプレートとして設定される

ALTER DATABASE mytemplate SET is_template TO TRUE;

テンプレートを利用するには下記のように実行する

CREATE DATABASE new_database_name TEMPLATE mytemplate;

参考

pgcryptについて

pgcrypto は PostgreSQL の contrib モジュールの一部で、多様な暗号関数を提供します。このモジュールを利用することで、ハッシュ、暗号化、復号化などの多くの暗号化関連の操作を行うことができます。

gen_salt()関数について

gen_salt() 関数は、パスワードを安全にハッシュ化する際に使用するソルトを生成するための関数です。ソルトはランダムなデータを取り、パスワードのハッシュ生成プロセスに組み入れることで、ハッシュの一貫性を破るのを助け、レインボーテーブルといった一般的な攻撃手法の効果を低減します。

gen_salt() の使用方法:
基本的な使用方法は次のとおりです：

sql
Copy code
SELECT gen_salt('bf');
上記の例では 'bf' は Blowfish を意味します。gen_salt() はさまざまなハッシュアルゴリズムをサポートしています。引数に使用できるアルゴリズムの一部は以下のとおりです：

'bf': Blowfish
'md5': MD5 (この場合、ソルトは実際には使用されません)
'xdes': Extended DES
その他...
ソルトを生成した後、このソルトを使用してパスワードをハッシュ化するには、crypt() 関数を使用します：

sql
Copy code
SELECT crypt('yourpassword', gen_salt('bf'));
上記のコマンドは、'yourpassword' を Blowfish アルゴリズムを使用してハッシュ化した結果を返します。

注意：
実際にパスワードをハッシュ化するときは、同じソルトを再度使用しないように注意することが重要です。このため、新しいパスワードをハッシュ化するたびに gen_salt() を呼び出して新しいソルトを生成することが推奨されます。
pgcrypto モジュールを使用する前に、CREATE EXTENSION pgcrypto; というコマンドを実行して、該当のデータベースでモジュールを有効にする必要があります。

参考

log_statementについて
postgresql.confのパラメータ
どの種類のSQLの内容を出力するか指定する(ddl, mod, all, none(デフォルト値))

設定値について

none(off)：どのメッセージも記録しない
ddl：CREATE、ALTER、及びDROP文といった、データ定義文をすべてログに記録する
mod：すべてのDDL文に加え、INSERT、UPDATE、DELETE、TRUNCATE、及びCOPY FROMといった、データ変更分をログに記録する。PREPARE、EXECUTE及びEXPLAIN ANALYZEコマンドも、そこに含まれるコマンドが適切な種類であれば記録される。拡張問い合わせプロトコルを使用するクライアントではExecuteメッセージを受け取ったときにBindパラメータの値（すべての単一引用符が二重にされた状態で）含まれていた場合、ログに記録される
all：すべてのメッセージを記録する

track_activitiesについて

このパラメータはデフォルトで有効。
サーバーが実行中のコマンドに関する統計情報を収集するかどうかをコントロールする
各セッションで実行中のコマンドに関する情報と、そのコマンドの識別子及び実行開始時刻の収集を有効にする。
スーパーユーザーとセッションの所有者のみから確認できる。
スーパーユーザーのみが設定を変更できる。

pg_tblspcについて

PostgreSQLでは、テーブルやインデックスなどのデータベースオブジェクトをbaseディレクトリ以外の任意のディレクトリ（テーブル空間）に格納できる。テーブル空間として作成されたディレクトリへのシンボリックリンクをpg_tblspcディレクトリに格納する。

なぜテーブルスペースは重要なのか？

パフォーマンス: 異なるストレージタイプ（例：HDD vs. SSD）や、異なる性能特性を持つストレージを用いて、I/O パフォーマンスを最適化できます。
管理の便利性: データベースの拡張や、バックアップを効率的に実施することが可能です。例えば、頻繁にアクセスされるテーブルを高速なストレージに、アーカイブ用のテーブルを低速なストレージに分けるなどの運用が可能です。
ストレージ容量: 特定のテーブルやインデックスを別のディスクに移動させ、ディスク容量を柔軟に管理できます。

具体的な使用例

テーブルスペースの作成

SSD にテーブルを配置して高速なI/O性能を得たいとします。SSDがマウントされた /mnt/fastssd ディレクトリを利用してテーブルスペースを作成します。

CREATE TABLESPACE fastssd LOCATION '/mnt/fastssd';

テーブルスペースの使用

作成した fastssd テーブルスペースにテーブルを作成します。

CREATE TABLE mytable (...) TABLESPACE fastssd;

このテーブルは物理的に /mnt/fastssd ディレクトリに保存され、SSDの高速なI/O性能を利用できるようになります。

テーブルスペース情報の確認

pg_tablespace ビューまたは pg_tblspc ディレクトリをクエリして、テーブルスペースの情報を確認できます。

SELECT * FROM pg_tablespace;

注意点

権限: テーブルスペースを作成するディレクトリは、PostgreSQLが実行されているOSユーザーに対して書き込み可能でなければなりません。

ポータビリティ: 物理パスがハードコードされるため、バックアップや復元、移行時に注意が必要です。

pg_hba.confに設定するパスワード認証について

設定値としては以下の通り

md5：md5暗号化によるパスワード認証を行う
password：平文によるパスワード認証を行う
scram-sha-256：scram-sha-256暗号化によるパスワード認証を行う

passwordは非推奨
md5はscram-sha-256が使用できない環境で使用
scram-sha-256はパスワード認証方式の中で推奨認証方式

track_functionsについて

関数の呼び出し数と費やされた時間の追跡を有効にする

手続き言語関数のみを追跡するにはplと指定する
SQL関数、C言語関数も追跡するためにはallを指定する

デフォルトでは、統計情報追跡機能を無効にするnone
スーパーユーザーのみがこの設定を変更できる。

crypt()関数について

crypt(3)形式でパスワードのハッシュ処理をおこなう

新しいパスワードを保管するときには、gen_salt()を使用して新しいsaltを生成する必要がある。
パスワードを検査する時は既存のハッシュ値をsaltとして渡し、結果が格納された値と一致するかどうかを確認する。

新しいパスワードの設定例

UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));

認証の例
入力パスワードが正しければtrueを返す

SELECT pswhash = crypt('entered password', pswhash) FROM ...;

定義済みロール

PostgreSQLでは、ある種の共通に必要で、特権のある機能や情報にアクセスできるよう、いくつかのあらかじめ定義されたロールを提供しています。 (CREATEROLE権限を持つロールを含む)管理者は自分の環境のユーザあるいはロールに対し、これらのロールをGRANT(付与)することで、それらのユーザに、その機能や情報を提供することができます

参考