Ubuntu20.04 PostgreSQL 14 安裝配置記錄

• 2022 年 2 月 15 日
• 筆記
• database, linux, PostgreSQL, Ubuntu

PostgreSQL 名稱來源

It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley.

In 1996, the project was renamed to PostgreSQL to reflect its support for SQL.

PostgreSQL 的發音為 [ˈpəʊsɡreˈsɪkl], 中間部分類似於 progress 的發音

安裝

參考官方安裝說明 //www.postgresql.org/download/linux/ubuntu/

# 創建軟體源
sudo sh -c 'echo "deb //apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# 添加key
wget --quiet -O - //www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# 安裝
sudo apt update
sudo apt install postgresql
# 檢查
sudo systemctl status postgresql
# 查看埠
sudo netstat -lnp

安裝後, 系統中會增加一個 postgres 用戶, 注意哦, 這個用戶是可以登錄的. 建議給這個用戶設個密碼

$ more /etc/passwd
...
postgres:x:113:121:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

這個用戶可以直接訪問 postgresql

$ sudo su postgres
[sudo] password for milton: 
postgres@ubuntu:/home/milton$ psql
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type "help" for help.

postgres=# 
postgres-# \q
postgres@ubuntu:/home/milton$

配置

查看資料庫列表

sudo -u postgres psql -l 

創建用戶

# 使用 postgres 用戶
createuser --interactive
# 或者 sudo
sudo -u postgres createuser --interactive

默認情況下, postgresql 會使用與linux用戶同名的用戶登錄, 所以如果創建了用戶為 milton, 還需要再創建一個名為 milton 的 linux 用戶.

創建資料庫

# 使用 postgres 用戶
createdb milton
# 或者 sudo
sudo -u postgres createdb milton
# 指定用戶
sudo -u postgres createdb testdb -O postgres

主配置

對應的配置文件在 /etc/postgresql//main, 當前的版本是14, 路徑是 /etc/postgresql/14/main/postgresql.conf

sudo vi /etc/postgresql/14/main/postgresql.conf 

主配置文件說明 //www.postgresql.org/docs/14/runtime-config-connection.html

服務IP listen_addresses

# 監聽所有地址
listen_addresses = '*'
# 監聽指定地址
listen_addresses = '192.168.10.20'

服務埠 port

port = 5432

密碼加密方式 password_encryption

password_encryption = scram-sha-256    # scram-sha-256 or md5

用戶名命名空間 db_user_namespace, 如果設置為on, 用戶創建時可以使用 username@dbname 這樣的格式, 用於與資料庫綁定. 這時候依然可以創建全局用戶, 但是連接時客戶端必須加上 @

db_user_namespace = off

基於主機的認證配置

配置文件 pg_hba.conf, 配置說明 //www.postgresql.org/docs/14/auth-pg-hba-conf.html

客戶端認證由配置文件控制, 通常為名為 pg_hba.conf 的文件, 存儲在集群的數據目錄(HBA 代表 host-based authentication 的縮寫). 當數據目錄初始化時, 會生成一個默認的 pg_hba.conf 文件. 可以通過修改主配置文件, 將文件放到其他路徑.

pg_hba.conf 文件通常的格式是按行組織的文本記錄

• 使用#號標識注釋
• 如果一行未結束需要換行, 使用\符號.
• 每行記錄由一些空格或tab分隔的欄位組成. 如果欄位包含空格, 需要用雙引號包圍.
• 每行記錄指定了: 連接類型, 客戶端IP範圍, 資料庫名, 用戶名, 驗證方式.
• 匹配的第一個記錄(匹配連接類型+客戶端地址+資料庫+用戶名)將用於驗證
• 沒有預設或再次驗證, 只要一個記錄被選中, 那麼驗證就只用這個記錄處理, 如果沒有命中的記錄, 就返回拒絕.

初始配置示例

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               trust
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

記錄格式說明

記錄可以是這些格式

local         database  user  auth-method [auth-options]
host          database  user  address     auth-method  [auth-options]
hostssl       database  user  address     auth-method  [auth-options]
hostnossl     database  user  address     auth-method  [auth-options]
hostgssenc    database  user  address     auth-method  [auth-options]
hostnogssenc  database  user  address     auth-method  [auth-options]
host          database  user  IP-address  IP-mask      auth-method  [auth-options]
hostssl       database  user  IP-address  IP-mask      auth-method  [auth-options]
hostnossl     database  user  IP-address  IP-mask      auth-method  [auth-options]
hostgssenc    database  user  IP-address  IP-mask      auth-method  [auth-options]
hostnogssenc  database  user  IP-address  IP-mask      auth-method  [auth-options]

連接方式

• local 使用本機Unix-domain sockets, 如果沒有local開頭的記錄, 則不允許用Unix-domain sockets連接
• host 使用TCP/IP連接, 包含SSL和GSSAPI方式
• hostssl TCP/IP + 使用SSL
• hostnossl TCP/IP + 不使用SSL
• hostgssenc TCP/IP + GSSAPI 加密
• hostnogssenc TCP/IP + 不使用 GSSAPI 加密

資料庫, 指定匹配的資料庫

• 資料庫名 指定資料庫, 多個資料庫使用逗號連接
• all 匹配所有
• sameuser 與此資料庫同名的用戶, 必須是這個用戶
• samerole 與此資料庫同名的role, 用戶必須屬於這個role
• samegroup 以廢棄
• replication
• @ 可以用@號指定文件

用戶, 指定匹配的用戶

• 用戶名 指定的用戶, 多個用戶用+號連接
• all 所有用戶
• @ 可以用@號指定文件

客戶端地址

• 172.20.143.89/32 IPv4地址或範圍
• 172.20.1.1/255.0.0.0 IPv4地址範圍的另一種格式
• fe80::7a31:c1ff:0000:0000/96 IPv6地址或範圍
• all 所有地址
• samenet 同一子網的地址
• samehost 當前主機的所有地址
• .example.com 域名通配

驗證方式

• trust 無條件通過
• reject 直接拒絕
• scram-sha-256 使用SCRAM-SHA-256驗證
• md5 使用 Perform SCRAM-SHA-256 或 MD5 驗證
• password 使用未加密的密碼驗證, 注意這種方式下, 密碼在網路中是明文傳輸
• gss 使用 GSSAPI 驗證, 僅適用於 TCP/IP 連接.
• sspi 使用 SSPI 驗證, 僅適用於 Windows
• ident 通過ident伺服器, 獲取當前客戶端作業系統用戶名, 並與請求的資料庫用戶名進行校驗, 僅適用於 TCP/IP 連接.
• peer 從作業系統獲取用戶名, 僅適用於 local 方式的連接
• ldap Authenticate using an LDAP server.
• radius Authenticate using a RADIUS server
• cert 使用 SSL 客戶端證書進行驗證
• pam 使用作業系統提供的 Pluggable Authentication Modules (PAM) 服務進行驗證
• bsd 使用作業系統提供的 BSD Authentication service 進行驗證

驗證選項

• 根據不同的驗證方式提供的選項

常用命令

修改用戶口令

postgres=# ALTER USER postgres WITH PASSWORD 'postgres';

待更新補充