psql での PostgreSQL サーバーへのログインと接続

<!--

設定値が自動的にフォールバックされたり、
サーバである PostgreSQL とクライアントである psql の関係がごっちゃになったりして、
混乱したためまとめました。　

```
psql を使って PostgreSQL にログインする方法を列挙してください。
OS は macOS です。
```

-->








## 方法 1. コマンドの引数

`-U`, `-d` は省略して書けます。

```
psql

psql ユーザ名 データベース名

psql -U ユーザ名 -d データベース名

psql -h ホスト -p ポート ユーザ名 データベース名

psql -h ホスト -p ポート -U ユーザ名 -d データベース名
```




### ◯ デフォルト値

| 項目 | 値 |
|:-:|:-:|
|ユーザ名|ログインしている OS のユーザ名|
|データベース名|ログインしている OS のユーザ名|
|ホスト|localhost, 127.0.0.1|
|ポート番号|5432|

* [psql - PostgreSQL: Documentation](https://www.postgresql.org/docs/current/app-psql.html)

<!-- プロンプトから入力する場合、パスワードをあとから入力するのでワンライナーでは書けません。ワンライナーでやりたい--> 

<!--

### ◯ ログアウト・切断方法

バックスラッシュ `\` と `q`

```
\q
```

-->


### ◯ Docker を使用している場合

<details><summary>詳細</summary>


:::note warn

接続先のホストがローカルの場合は、デフォルト値であっても `-h localhost` または `-h 127.0.0.1` を明示してログインしてください。そうしないと以下のようなエラーに遭遇します。理由も記載しました。

* ["/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory - Qiita](https://qiita.com/domodomodomo/items/99ce7ca0dcff41e3d838)

:::

</details>









### ◯ パスワードを忘れた場合

<details><summary>詳細</summary>

:::note alert

OS の root 権限を持っている場合だけの対処法です。パスワードは忘れないようにしてください。

:::


`pg_hba.conf` を開き `peer` または `md5` から `trust` に書き換えます。パスワードが無効になります。

```:pg_hba.conf
# 変更前
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5

# 変更後
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust
```

詳細はリンク先をご確認ください。

* [PostgreSQL パスワード 忘れた - Google 検索結果](https://www.google.com/search?q=postgresql+%E3%83%91%E3%82%B9%E3%83%AF%E3%83%BC%E3%83%89+%E5%BF%98%E3%82%8C%E3%81%9F)

<!--
PostgreSQL の Administrator 権限を持ったユーザ（正確にはロールというらしいですが）の

Windows 以外の場合は、以下のコマンドで `pg_hba.conf` ファイルの場所を時間がかかりますが確認できます。

```
find / -name 'pg_hba.conf' 2>/dev/null
```


* [Reset Forgotten Password For postgres User - POSTGRES TUTORIAL](https://www.dbrnd.com/2018/04/postgresql-how-to-recover-postgres-user-password/)
* [Getting error: Peer authentication failed for user “postgres”, when trying to get pgsql working with rails - Stackoverflow](https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge)

-->

</details>




## 方法 2. 環境変数



```bash:macOS, Ubuntu - bash
export PGHOST=ホスト
export PGPORT=ポート番号
export PGDATABASE=データベース名
export PGUSER=ユーザ名
export PGPASSWORD=パスワード
psql
```
```:Windows - コマンドプロンプト
set PGHOST=ホスト
set PGPORT=ポート番号
set PGUSER=ユーザ名
set PGDATABASE=データベース名
set PGPASSWORD=パスワード
psql
```
```powershell:Windows - PowerShell
$env:PGHOST="ホスト"
$env:PGPORT="ポート番号"
$env:PGUSER="ユーザ名"
$env:PGDATABASE="データベース名"
$env:PGPASSWORD="パスワード"
psql
```


* [33.14. Environment Variables - PostgreSQL: Documentation](https://www.postgresql.org/docs/current/libpq-envars.html)





### ◯ ワンライナーでログインしたい場合

<details><summary>詳細</summary>

矢印キー `↑` を使って履歴からコマンドを再利用するときにパスワードを打たなくてよくなるので便利かなと思います。

```bash:macOS, Ubuntu - bash
PGPASSWORD=パスワード psql -U ユーザ名 データベース名
```
```:Windows - コマンドプロンプト
# ワンライナーでは書けないので２行で書いてください...orz
set PGPASSWORD=パスワード
psql -U ユーザ名 データベース名
```
```powershell:Windows - PowerShell
$env:PGPASSWORD="パスワード"; psql -U ユーザ名 データベース名
```

* [How do I specify a password to 'psql' non-interactively? - Stackoverflow](https://stackoverflow.com/questions/6405127/how-do-i-specify-a-password-to-psql-non-interactively/6405162)


<!--

### ◯ 安全にパスワードを保存したい...

ChatGPT 先生に相談してください... 回答結果は保証しませんが...

> PostgreSQL のパスワードを環境変数に安全に保存したいです。しかし例えば `~/.bashrc` に書き込む方法だとパスワードがほかのユーザに見えてしまう恐れがあります。なにか良い方法はありませんか？

  -->



</details>




### ◯ 注意

<details><summary>詳細</summary>

この「方法 2. 環境変数」も便利な気がしますが...

:::note alert

psql の対話シェルではなく直接 OS の対話シェルにパスワードを書いた場合、コマンドの履歴にパスワードが残ります。例えば bash の場合 `~/.bash_history` にコマンドが保存されます。もしかしたらほかにもどこかで保存されているかもしれず詳しいことは存じておりません。ほかの人がそのコマンドの履歴を見られる可能性があることに留意してください。これを避けたい場合

1. 以下のように OS の対話シェルにはパスワードを書き込まない、または
2. システムレベルで提供されているキーストア（例: macOS の [Keychain](https://qiita.com/kudojp/items/b027aa492f1d17f24960)、Linux の Secret Service API）を利用する

ことなどを検討してください。

:::

</details>







## 方法 3. パスワードファイル

コロン `:` で区切ります。

```:ファイルの書式
ホスト:ポート:データベース名:ユーザ名:パスワード
```



```:ファイルの場所
1. macOS, Ubuntu の場合
  ~/.pgfile

2. Windows の場合
  %APPDATA%\postgresql\pgpass.conf

3. あるいは環境変数 PGPASSFILE でファイルパスを指定できます。
```
* [33.15. The Password File - PostgreSQL: Documentation](https://www.postgresql.jp/document/11/html/libpq-pgpass.html)


### ◯ 注意

<details><summary>詳細</summary>

この「方法 3. パスワードファイル」はログインするたびにユーザ名などを入力するのが面倒な場合は便利な気がしますが...

:::note alert

パスワードをファイルに直接書き込む場合、ほかの人がそのファイルを見られる可能性があることに留意してください。これを避けたい場合

1. ファイルにパスワードを保存しない、または
2. ファイルに閲覧の制限をかける（例: bash で `chmod 600 ~/.pgpass`）

ことなどを検討してください。

> Unixシステムにおいて、パスワードファイルの権限はグループ、他者へのアクセスをすべて拒否しなければなりません。 これは `chmod 0600 ~/.pgpass` といったコマンドによって行います。 **権限をこれよりも緩くすると、このファイルは無視されます。**
> Microsoft Windowsにおいては、このファイルが安全なディレクトリに格納されていることを前提としていますので、特別に行われる権限の検査はありません。
> * [33.15. The Password File - PostgreSQL: Documentation](https://www.postgresql.jp/document/11/html/libpq-pgpass.html)

:::

</details>




## おわりに

### ◯ まとめ


|指定方法|ホスト名|ポート番号|データベース名|ユーザ名|パスワード|
|:--:|:--:|:--:|:--:|:--:|:--:|
|引数|-h, --host|-p, --port|-d, --dbname|-U, --username|-|
|デフォルト値|127.0.0.1|5432|OS のユーザ名|OS のユーザ名|-|
|環境変数|PGHOST|PGPORT|PGDATABASE|PGUSER|PGPASSWORD|
|パスワードファイル|1|2|3|4|5|











### ◯ psql ってなんだ？

![PostgreSQL サーバー(1).jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254825/526f2863-5fd6-27d3-27c3-2c3ab1e0dffa.jpeg)

* 登場人物
    1. PostgreSQL サーバー
        * 説明省略
    2. psql クライアント
        * PostgreSQL サーバーを操作するアプリ
    3. `psql` コマンド
        * psql クライアントを起動し PostgreSQL サーバにログインするコマンド
        * 上図には書かれていません。
* 実行順序
    1. `psql` コマンドで psql クライアントが起動します
    2. psql クライアントで PostgreSQL サーバーにログインします 
    3. psql クライアントで PostgreSQL サーバーを操作します（上図はここを表しています）


> psql は PostgreSQL の [ターミナル](https://wa3.i-3-i.info/word12049.html) ベースのフロントエンドです。[対話](https://wa3.i-3-i.info/word12550.html) 的にクエリを入力し、PostgreSQL に発行し、そのクエリ結果を見ることができます。
> psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results.
> * [psql - PostgreSQL interactive terminal](https://www.postgresql.org/docs/12/app-psql.html)


<details><summary>詳細</summary>

psql クライアントは `psql` コマンドで起動することができます。psql クライアントは自分のパソコンのなかで動いている PostgreSQL サーバだけではなく、ネットワーク上にいるほかのパソコンで動いている PostgreSQL サーバも操作できます。

このことは `psql` コマンドでホスト名やポート番号を指定できることからもわかります。自分は初めて PostgreSQL を触っていたときにサーバとクライアントは別物であることがわからず混乱していました。その原因は `psql` コマンドが何も指定しないと自動的に `localhost`, `127.0.0.1` にある PostgreSQL サーバに接続されるからではないかと思っています。

psql はクライアントです。PostgreSQL はサーバです。ややこしいのですがサーバとクライアントを区別すると良いような気がします。以下のリンク "PostgreSQL Client Applications" の一覧の中に `psql` コマンドがあり、 psql はクライアントに分類されていることがわかります。
* [PostgreSQL Client Applications - PostgreSQL: Documentation](https://www.postgresql.org/docs/current/reference-client.html)




</details>





<br>


### ◯ Python での PostgreSQL 接続方法

<details><summary>詳細</summary>



もはや psql 関係ないですが...

**psycopg2 の書式**

```python
import psycopg2

connection = psycopg2.connect(
    user="postgres",
    password="secret",
    host="localhost",
    dbname="postgres"
)
```

<br />


**SQLAlchemy の書式**

```python
from sqlalchemy import create_engine

# dialect[+driver]://user:password@host/dbname[?key=value..]
URL = "postgresql+psycopg2://ユーザ名:パスワード@ホスト名/データベース名"
engine = create_engine(URL)
```

`URL` は RFC1738 に定められた書式だそうです（PEP 249 で定められてはいない、Python に限定して言えば SQLAlchemy の中だけで使われていそうな気配を感じます）。

> The string form of the URL is dialect[+driver]://user:password@host/dbname[?key=value..], where dialect is a database name such as mysql, oracle, postgresql, etc., and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle, etc. Alternatively, the URL can be an instance of URL.

* https://docs.sqlalchemy.org/en/13/core/engines.html#database-urls



<!--
II. PostgreSQL Client Applications に分類されます。もう一つは、です。クライアントなので別のサーバにもアクセスできます。psql コマンドでサーバに接続できますが、なにも指定しないと自動的に接続されるため、このあたりの意識が希薄になっていました。
-->


<!--
〇 疑問
こんなことしていいのかなと思ったのですが、ホストにログインしていて
 -->


</details>




















<!--


### ◯ 意見広告

2023年11月13日のマイナビ転職が運営するメディアの広告です。

![img.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/254825/4894954e-f3e9-3357-e5d5-a076c96879c4.jpeg)

* ttps://meetscareer.tenshoku.mynavi.jp/entry/data_01/vo

<details><summary>意見広告 本文</summary>

上記の記事では

:::note alert
「どういうときに働きがいを感じるか」は「働きに見合う報酬が得られている」よりも「成長を感じる」ときである
:::

と主張されています。

賃金が低くても転職してほしいという思いがあるのかどうかわからないのですが、そうした意図が透けて見えるようで嫌だなと思いました。Qiita の皆様にはこういうのにハマって欲しくないという思いのもと、以下に意見広告を記載させていただきます。

わたしは企業が労働者に明文化して提供できる価値は、「待遇」と「キャリア」の２つに大別できると個人的に思っています。

```
企業が労働者に提供できる価値

1. 待遇
1.1. 年収
1.2. 福利厚生
2. キャリア
2.1. 成長
2.1.1. 働きがい、やりがい
2.1.2. 裁量
```


* やりがい
    * キャリア、明確に、数年後の転職時の給料を明示
    * 裁量 ... 残業時間, 自分がやりたいことに対しても残業代をくれるか？
    * リモートの時間

上記が伴っていないのにやりがいを主張する企業は搾取のかのうせい

https://x.com/12345tetsu12/status/1813953265148862939

「キャリア」を提供できない企業は「待遇」が良くてもブラック企業です。

* [「こんな仕事のために…」新入社員が1年で退社、ホワイト企業が“ゆるいブラック”と化す理由](https://diamond.jp/articles/-/325003?page=2)

その上で記事を読んでいて怖いなと感じたのは次の ２ 点です。

まず第一に「働きがい」は転職する判断基準において「キャリア」に従属する物であり、「待遇（年収）」と対等に比較することができないにも関わらず、あたかも「働きがい」と「待遇（年収）」が比較できかつ「働きがい」が「待遇（年収）」よりも優先順位が高いかのように記述されていることです。

また第二に「キャリア」を「成長」という下位の曖昧な言葉で濁していることです。どんなスキルだってやれば「成長」します。その「成長」が「キャリア」に寄与するかどうかは別問題です。この記事からわかる態度あるいは姿勢は「成長」という言葉で濁して「キャリア」の提供にコミットするということを回避していることです。

<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">これは確かにそうだなぁ。<br><br>メンバーのほとんどは今の仕事がどのようなキャリアに繋がっていくかすら理解してないから、そこは説明しつつ、それが自分のなりたいキャリアか？ってのを選んでもらう感じになる。 <a href="https://t.co/1Wmp9vorln">https://t.co/1Wmp9vorln</a></p>&mdash; フリーランスエンジニアQ太郎 (@q42_engineer) <a href="https://twitter.com/q42_engineer/status/1724951181209010219?ref_src=twsrc%5Etfw">November 16, 2023</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

「成長」や「働きがい」はもちろん大事な要素のひとつですが、「キャリア」に繋がらない「成長」や「働きがい」は、いわゆる「やりがい搾取」をするための道具として活用されていると私は思っています。だから私は自身の市場価値を高めていく「キャリア」という言葉が抜けていることが怖いのです。
* [「自腹で色んなリキュール類を買って並べてました」ワタミメンバーの軌跡がいろいろヤバいと話題に](https://news.livedoor.com/article/detail/7801371/)

「待遇」と「キャリア（働きがい）」は一般に相関していているのではないでしょうか。

「待遇（年収）」を提供できない企業が「キャリア（働きがい）」を提供することは難しいことだと思っています。なぜならいわゆる「キャリア」を提供できる上流工程の企業あるいは社格の高い企業は収益も高く、必然的に「待遇（年収）」も良くなると思うからです。

そのことを考えると「キャリア（働きがい）」がいいから「待遇」は我慢しようと思っているときは、求職者は転職エージェントにのせられてしまっている可能性があるのではないかと思います。

「待遇」が「キャリア（働きがい）」に相関しない例を２つ示します。

まず第一に、今後成長するが今はまだ未成熟な産業の組織の場合です。2000年代 ~ 2010年代のウェブ業界のようにまだ成熟していない産業では必ずしも相関していないこともあります。市場が成長するかどうかを読み逆張りして行動することは我々一般人には非常に困難なことのように思います。

「成長できる」、「働きがいがある」という不明確な言葉、基準ではなく、市場の成長の有無を見据え「キャリア」という明確な言葉、基準で判断していくことが大事ではないかなと感じました。

また第二に、官公庁のように圧倒的に権力を握っている組織の場合です。

<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">金融庁はこれをやって成功させています<br><br>外銀マンが公務員の給与水準で転職しないと思いがちですが、ある程度の年齢になるとクビになって転職先も無くなるので、外銀に残って高給をもらっている元同僚への嫉妬から規制当局に転職し、古巣を焼き払うことに生きがいを見出す方もいます😊 <a href="https://t.co/SptKe7tXrc">https://t.co/SptKe7tXrc</a></p>&mdash; Tamaちゃん (@aoyamazabu) <a href="https://twitter.com/aoyamazabu/status/1716307069131985274?ref_src=twsrc%5Etfw">October 23, 2023</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

上記二例ともにレアケースであることには変わりないように感じます。

「やりがい」、「アットホームな職場」なんていまどき流行らないだろと思っていたのですが、こうやって隙あらばご丁寧に広告まで打って人の思考を染めようとしてくるのは本当に怖いこどだなと感じました。



</details>
-->

<!--
年収に相関するのではないのか？
対人関係は密より疎にできるが重要

アンケートをこの３つに集約したい

1. 待遇
2. キャリア
3. 対人関係
-->