[Learn] PostgreSQL 01：macOS 使用 Homebrew 安裝與連線設定 • BlogZzz

最近重新學習 SQL，距離上次認真使用 SQL 已經過了七年

這幾年雖然偶爾還是會接觸資料庫，但大多只停留在基本查詢或者基本的 CRUD，因此決定從安裝 PostgreSQL、使用 psql，以及設定應用程式連線開始重新整理

這篇以 macOS、Homebrew 與 PostgreSQL 18 為例

安裝 PostgreSQL 18

先透過 Homebrew 安裝 PostgreSQL 18

brew install postgresql@18

postgresql@18 是 keg-only formula，如果安裝後找不到 psql，需要把它的 bin 目錄加入 PATH

echo 'export PATH="$(brew --prefix postgresql@18)/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

安裝過程會建立預設的 database cluster，資料目錄位於：

$HOMEBREW_PREFIX/var/postgresql@18

Apple Silicon 的 $HOMEBREW_PREFIX 通常是 /opt/homebrew，Intel Mac 通常是 /usr/local，可用以下指令確認實際路徑

brew --prefix

啟動並確認 PostgreSQL

使用 Homebrew Services 啟動 PostgreSQL，並設定為登入 macOS 後自動啟動

brew services start postgresql@18

先確認 psql client 是否可用

psql --version

輸出會類似：

psql (PostgreSQL) 18.x

psql --version 只會顯示 client 版本，不能證明 database server 已經啟動

要檢查 server 是否接受連線，可以執行：

pg_isready

成功時會看到類似結果：

/tmp:5432 - accepting connections

第一次連線與建立資料庫

PostgreSQL cluster 初始化時會建立 postgres、template1 與 template0 三個 database

建立實際要使用的專案 database，例如 spring_test

createdb spring_test
psql -d spring_test

createdb 是 CREATE DATABASE 的 command-line wrapper，執行者必須擁有建立 database 的權限

如果還沒有要建立專案 database，也可以連到 postgres

psql -d postgres

postgres 是供使用者、工具與第三方應用程式連線的預設 database，-d 則是 --dbname 的縮寫，用來指定 database 名稱

如果直接執行 psql 而沒有提供參數，預設會：

使用目前的作業系統帳號作為 PostgreSQL role 名稱
使用該 role 名稱作為 database 名稱
在 macOS 等 Unix 系統透過 Unix-domain socket 連線

因此，只有存在同名 role 與 database 時，以下指令才會成功

# 建立與目前作業系統帳號同名的 database
createdb

# 連到與目前 role 同名的 database
psql

剛初始化的 Homebrew cluster 會以執行 initdb 的作業系統帳號建立初始 superuser role，所以本機安裝時通常已經有同名 role

psql 基本操作

進入 psql 後，可以使用以下 meta-command 查看連線與資料庫資訊

-- 查看目前的連線資訊
\conninfo

-- 查看目前 server 中的 database
\l

-- 查看 PostgreSQL role
\du

-- 切換到另一個 database
\c spring_test

-- 查看目前 schema 中可見的 table
\dt

-- 離開 psql
\q

以反斜線開頭的是 psql meta-command，由 psql client 處理，不是 SQL

如果想用 SQL 查詢目前連線的 database，可以執行：

SELECT current_database();

也可以在 psql 中使用 SQL 建立另一個 database

CREATE DATABASE another_database;

應用程式連線不一定需要密碼

Spring Boot、Express 或 ASP.NET Core 等應用程式都需要提供 database 連線資訊，但不代表 PostgreSQL 一定會要求密碼

是否需要驗證密碼，是由 database cluster 的 pg_hba.conf 決定

兩種常見連線方式的差異如下：

psql -d spring_test
  → 未指定 host
  → 使用 Unix-domain socket
  → 比對 pg_hba.conf 的 local 規則

jdbc:postgresql://localhost:5432/spring_test
  → 指定 localhost
  → 使用 TCP/IP
  → 比對 pg_hba.conf 的 host 規則

pg_hba.conf 會使用第一條符合連線類型、位址、database 與 role 的規則，驗證方式可能是 trust、peer 或 scram-sha-256 等

全新 Homebrew cluster 使用的 initdb 指令沒有另外指定驗證方式，而 initdb 預設使用 trust，所以即使透過 localhost 連線，也可能不會要求密碼

為 TCP 連線啟用密碼驗證

如果要練習應用程式使用帳號與密碼連線，先在 psql 中確認 role 名稱

\du

假設 role 名稱是 alice，可以使用 \password 設定密碼

\password alice

\password 會互動式要求輸入新密碼，避免明文密碼出現在 psql command history 或 server log

接著找出目前使用的 pg_hba.conf

SHOW hba_file;

編輯該檔案，找到既有的 localhost TCP 規則，將驗證方式改成 scram-sha-256

# TYPE  DATABASE  USER  ADDRESS         METHOD
host    all       all   127.0.0.1/32    scram-sha-256
host    all       all   ::1/128         scram-sha-256

不要只把新規則附加在既有的 trust 規則後面，因為 pg_hba.conf 只會套用第一條符合的規則

修改後重新載入設定

SELECT pg_reload_conf();

再從 terminal 測試 TCP 與密碼登入

psql -h localhost -U alice -d spring_test

-h localhost 指定使用 TCP/IP 連線
-U alice 指定 PostgreSQL role
-d spring_test 指定 database

如果沒有從其他來源提供密碼，psql 會在 server 要求密碼驗證時自動顯示輸入提示

如果只設定 role 密碼，卻沒有把符合的 pg_hba.conf 規則改成密碼驗證，連線仍可能因為 trust 而不詢問密碼

Spring Boot 連線範例

確認 TCP 密碼登入成功後，就能把相同的連線資訊交給 Spring Boot

spring.datasource.url=jdbc:postgresql://localhost:5432/spring_test
spring.datasource.username=alice
spring.datasource.password=${DB_PASSWORD}

實際密碼適合放在環境變數或 secret manager，不要直接提交到版本控制

結論

psql --version 只能確認 client，pg_isready 才是檢查 server 連線狀態的工具
psql 未指定 host 時預設使用 Unix-domain socket，指定 localhost 時則使用 TCP/IP
PostgreSQL 是否要求密碼取決於 pg_hba.conf，不是由 framework 或連線方式單獨決定
\password 只負責設定 role 密碼，還要搭配 scram-sha-256 等驗證規則才會要求密碼
專案應使用獨立 database，並避免把明文密碼寫進程式碼或版本控制