HOWTO · Postgres

Postgres 連線字串

本文展示了 Postgres 中連線字串的正確格式。

本頁內容

PostgreSQL 用戶端會使用連線字串來定位資料庫,並提供驗證身分所需的資訊。格式正確的 URI 可讓應用程式連線至本機伺服器或遠端主機;伺服器也必須可連線,並允許指定的使用者連線。

PostgreSQL 連線字串格式

PostgreSQL URI 的格式如下:postgresql://[user[:password]@][host][:port][/dbname][?param=value&…]。通訊協定名稱也可以寫成 postgres://。使用者名稱與密碼用來識別資料庫角色,主機名稱指定伺服器,埠號指定伺服器的監聽端點,路徑則指定資料庫。查詢參數可加入連線設定,例如 sslmode。

使用本機連線時,libpq 用戶端會將省略的主機視為本機通訊端;若使用 TCP 連線,常見的主機值是 localhost。TCP 的預設埠號為 5432。伺服器或用戶端採用不同設定時,預設值也可能不同。資料庫名稱不一定與使用者名稱相同,而且該角色必須獲准連線至該資料庫。

建立並使用連線字串

建立本機 TCP 連線時,請將各欄位替換成伺服器上設定的值:postgresql://app_user:your_password@localhost:5432/inventory。連線至遠端資料庫時,請將 localhost 換成管理員或服務提供者提供的主機名稱。請使用指派給你的埠號、資料庫和角色,不要假設本機預設值也適用。

URI 語法會將 @、/ 和 : 等字元視為分隔符號,因此憑證若含有這些字元,插入前必須先進行百分比編碼。請分別建立並編碼 URI 的各個元件,不要直接串接未處理的憑證。

執行以下範例,查看憑證中的保留字元如何在 URI 中編碼:

本節中的程式碼以及顯示的標籤或輸出仍為英文。

from urllib.parse import quote

user = quote("app_user", safe="")
password = quote("my@pass/word", safe="")
host = "localhost"
port = "5432"
database = quote("inventory", safe="")
uri = f"postgresql://{user}:{password}@{host}:{port}/{database}"
print(uri)
postgresql://app_user:my%40pass%2Fword@localhost:5432/inventory

這個獨立範例只會建立並印出 URI,不會連線至 PostgreSQL。若應用程式需要建立即時連線,請從環境變數讀取值、編碼 URI 元件,再將 URI 傳給已安裝的 psycopg2 套件:

import os
from urllib.parse import quote

required = ("DB_USER", "DB_PASSWORD", "DB_HOST", "DB_PORT", "DB_NAME")
missing = [name for name in required if not os.environ.get(name)]
if missing:
    print("Set the database environment variables before connecting.")
else:
    try:
        import psycopg2

        import ipaddress

        user = quote(os.environ["DB_USER"], safe="")
        password = quote(os.environ["DB_PASSWORD"], safe="")
        raw_host = os.environ["DB_HOST"]
        try:
            address = ipaddress.ip_address(raw_host)
        except ValueError:
            host = raw_host
        else:
            host = f"[{raw_host}]" if address.version == 6 else raw_host
        port = os.environ["DB_PORT"]
        database = quote(os.environ["DB_NAME"], safe="")
        uri = f"postgresql://{user}:{password}@{host}:{port}/{database}"
        connection = psycopg2.connect(uri)
        connection.close()
        print("Connected and closed the PostgreSQL connection.")
    except Exception as error:
        print(f"Connection was not completed ({type(error).__name__}).")

只有在五個變數都已設定,且已安裝 psycopg2 套件時,第二個範例才會嘗試連線至資料庫。將 DB_HOST 設為 DNS 名稱、IPv4 位址或未加方括號的 IPv6 常值;程式碼會在 URI authority 部分中的 IPv6 常值外加方括號,DNS 名稱和 IPv4 位址則維持原樣。確認連線已開啟後,範例會將其關閉。例外處理程式會以文字顯示連線或設定錯誤;請在應用程式執行的環境中設定變數,不要將憑證寫入原始碼。

勿將憑證寫入原始碼

請將密碼存放在環境變數或機密管理工具中,避免將密碼提交至版本庫、印出完整 URI,或將 URI 寫入日誌與錯誤報告。例如,可使用 Python 的 os.environ 讀取 DB_USER、DB_PASSWORD、DB_HOST、DB_PORT 和 DB_NAME;使用 urllib.parse.quote(..., safe="") 編碼使用者名稱、密碼和資料庫元件,再組合 URI 並傳給 psycopg2.connect(uri)。請在應用程式執行的環境中設定所需變數。只有當 localhost 或 5432 等預設值符合該環境的伺服器設定時,才適合使用。

環境變數能讓機密資料遠離原始碼檔案,但仍須設定適當的存取控制。不要用原始碼中的常數取代機密儲存:常數並不能隱藏資料。若憑證已外洩,請更換該憑證。

設定遠端連線的 SSL

伺服器要求加密連線時,請將 sslmode=require 加入 URI 查詢參數,例如 postgresql://app_user:ENCODED_PASSWORD@db.example.test:5432/inventory?sslmode=require。如果 URI 已有查詢參數,請用 & 加入這個參數,不要再加第二個 ?。

sslmode=require 會要求加密,但單靠這項設定無法驗證連線對象是否為預期的伺服器。若要驗證伺服器身分,請為用戶端設定受信任的憑證授權單位,並使用 sslmode=verify-full;這也會檢查伺服器名稱。請依照資料庫管理者的憑證與連線要求設定。若遇到憑證錯誤,請先了解停用驗證的後果,不要直接關閉驗證來迴避問題。

排查連線失敗

請依照下列順序檢查連線資訊與伺服器設定:

  • **主機或埠號:**確認應用程式所在的機器能解析主機名稱、伺服器正在該位址與埠號上監聽,而且網路規則允許連線。localhost 指的是應用程式所在的環境;該環境可能是容器或另一台主機,而不是你的工作站。
  • **資料庫或角色:**確認資料庫名稱和使用者名稱拼寫正確,再檢查角色是否存在、密碼是否仍有效,以及該角色是否有 CONNECT 權限。網路連通性與身分驗證、授權是不同問題。
  • **URI 編碼:**對使用者名稱、密碼和資料庫路徑中的保留字元進行百分比編碼。例如,未編碼的 @ 可能會被解析為主機之前的分隔符號。
  • **SSL 設定:**確認要求的 sslmode 符合伺服器政策。若要驗證憑證,請檢查設定的 CA 檔案,並確認主機名稱與憑證相符。
  • **用戶端設定:**確認應用程式讀取的是預期的環境變數,並使用預期的 PostgreSQL 驅動程式。Python 的 psycopg2 可透過 psycopg2.connect(uri) 接受 PostgreSQL URI;上方印出 URI 的範例只會建立字串;另一個以環境變數為基礎的範例則會嘗試建立即時連線。

請根據錯誤類型和伺服器日誌,判斷問題是網路逾時、連線遭拒、身分驗證失敗、資料庫不存在,還是 TLS 憑證驗證問題。診斷資訊中不要分享完整連線字串,因為其中可能含有憑證。