你使用过Proxmox吗？

标题不一样，但Proxmox很方便。当进行这种应用的试错时，可以轻松地完全删除或还原环境，非常好用。而且，使用LXC（Linux容器）可以节省磁盘容量和内存，对机器友好。

最终的目标

安装完PostgreSQL就结束的话，在很多文章中都可能会有这样的描述，所以我决定稍微进行调优来实现正式的运营。
题目设定如下：
– 安装PostgreSQL16
– 将通信路径连接改为SSL连接
– 从A5:SQL Mk-2进行连接
– 从ODBC驱动程序进行连接
– 禁止连接到public架构
– 修改日志轮换配置

引入 PostgreSQL16

由于「PostgreSQL 16」的正式版已经发布，我们将尝试安装最新版本！

我們將使用yum軟件源在AlmaLinux 9上安裝PostgreSQL 16。根據官方網站提供的步驟進行操作。

按照步骤安装yum存储库。

$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

禁用PostgreSQL模块并安装主体。

$ sudo dnf -qy module disable postgresql
$ sudo dnf install -y postgresql16-server

我們將以UTF-8作為默認字符編碼，以C作為默認區域設置，創建數據庫集群。由於根據PostgreSQL的著名規範，根據區域設置進行漢字排序可能與預期不符，因此我們將禁用區域設置。

$ sudo PGSETUP_INITDB_OPTIONS='--encoding=UTF-8 --no-locale' /usr/pgsql-16/bin/postgresql-16-setup initdb

创建连接用户

我們將在後續章節中使用的PostgreSQL創建一個連接用戶（並非操作系統帳戶）。

$ su - postgres
Last login: Thu Sep 21 01:43:59 2023
$ psql
psql (16.0)
Type "help" for help.

postgres=#

我们先创建一个名为hogeuser的用户，用于测试连接。

postgres=# create user hogeuser password 'hogeuser';
CREATE ROLE
postgres=# create database hogedb owner hogeuser;
CREATE DATABASE

暂时先尝试用本地主机登录

[root@postgres ~]# psql -h localhost -U hogeuser hogedb
Password for user hogeuser: 
psql (16.0)
Type "help" for help.

hogedb=> 

密码保存的加密格式

如果连接到PostgreSQL，可以使用密码认证、LDAP和SSL证书等认证方式，但在这里我们选择比较简单的密码认证进行操作。密码认证有以下3种方式，但在业务中使用的话可能会选择scram-sha-256作为唯一选项吧？
顺便提一下，在PostgreSQL 16中，保存密码的默认方式似乎是scram-sha-256。

试着从另一个设备登录

PostgreSQL具备可以进行访问来源限制的功能，并且默认设置下，无法从其他终端连接。

listen_addresses = '*'          # what IP address(es) to listen on;
#listen_addresses = 'localhost'         # what IP address(es) to listen on;

指定连接网络范围或特定IP地址。

host    all             all             100.100.0.0/24        scram-sha-256

启用安全的 SSL 连接

將 PostgreSQL 的通信路徑進行 SSL 加密。我個人覺得如果在非武裝地帶（DMZ）上有資料庫，通信路徑就不需要 SSL 加密，但對方高層對此不滿呢（笑）。因此，這次我會積極地進行相應的處理。
首先，我會準備自設憑證，先把有效期限設置為3650天，然後進行創建。

审查地点和权限以放置证书副本。

$ sudo openssl req -new -x509 -days 3650 -nodes -text -out /var/lib/pgsql/16/data/server.crt -keyout /var/lib/pgsql/16/data/server.key
$ sudo chown postgres:postgres /var/lib/pgsql/16/data/server.{crt,key}
$ sudo chmod og-rwx /var/lib/pgsql/16/data/server.key

启用安全的 SSL 连接

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

在连接到网络或特定IP时进行SSL指定。
请注意，官方网站的说明可能不太清楚，我们将使用hostssl指定。

hostssl    all             all             100.100.0.0/24        scram-sha-256

确认是否可以进行SSL通信

$ psql -h 100.100.0.100 -U hogeuser -d hogedb
Password for user hogeuser: 
psql (16.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

hogedb=> 

A5: 从SQL Mk-2 进行连接。

我认为，pgAdmin这个工具可以通过图形界面操作表定义和数据库周边，非常方便。不过，个人使用作为SQL客户端的话，我觉得A5:SQL Mk-2更方便。因此，我们要安装它。

然而，当使用A5:SQL Mk-2向PostgreSQL进行安全SSL连接时，可能会遇到以下错误。正如A5:SQL MK-2官方论坛上所述，这可能是因为使用的SSH/SSL库规范存在问题。

从ODBC/JDBC驱动程序的连接

如果从 ODBC/JDBC 驱动程序连接到安全 SSL，请使用 SSLMode= 指定。我自己尝试过的是，从 Access 通过 ODBC 到 Postgres 安全 SSL 连接时，参数为 SSLMode=require。顺便说一下，在使用JAVA的情况下，好像使用 MyBatis 时，默认情况下会进行安全 SSL 连接或者挑战响应，无需指定。

顺便提一下，要安装单独的 Postgres ODBC 驱动器。

禁止使用public schema进行连接

据说在PostgreSQL14之前，所有用户默认属于Public角色。或者可以用以下稍微拗口但更容易理解的方式来解释：
– 在数据库中必须存在Public模式。
– 默认情况下，被授予了Public模式角色权限。

当涉及用户权限和数据库关联时，如果给出以下权限设置，hoge1用户将可以访问hoge2db模式。

如果你使用过Oracle和SQLServer，那么向PostgreSQL数据库工程师问「在这个项目中我们使用了公共模式吗？」可能会被嘲讽，被认为是一个完全不了解数据库的人（我自己也有过几次经验）。

再深入一点。

在中国，这个“默认情况下允许的Public模式角色权限”是在PostgreSQL14之前的情况，在15及以后的版本中已经解决了。那么在PostgreSQL14之前的情况下，应该怎么办呢？很简单。
– 禁用Public模式角色权限。
– 禁用所有用户对数据库的连接权限(CONNECT)。
– 对应用户的数据库连接权限(CONNECT)设置为允许。

如果通过命令来表示，如下所示。

postgres=# revoke connect on database postgres from public;
postgres=# revoke connect on database hoge1db from public;
postgres=# revoke connect on database hoge2db from public;

postgres=# grant connect on database hoge1db to hoge1;
postgres=# grant connect on database hoge2db to hoge2;

当然的是，PostgreSQL 的 admin 用户postgres 可以访问任何数据库和模式。

顺便提一下，自从PostgreSQL15以后，这种想法就不再需要了。

用户权限

假设我们已经简单了解了数据库和用户之间的关系。现在，让我们考虑一种情况：我们想要创建一个新用户并连接到现有的数据库。

从答案来看，只需将hoge1用户权限继承并授予hoge1app用户。

postgres=# create user hoge1app with login password 'hoge1app';
postgres=# grant connect on database hoge1db to hoge1app;

postgres=# grant hoge1app to hoge1;

如果你有 SQL Server 经验，你会觉得：“哎呀？不是应该将用户与数据库权限关联起来吗？”这样想想没关系（请以相对模糊的方式思考）。

日志输出的设置和日志轮转的更改

PostgreSQL生成的日志文件会按照周单位（由月到日的首字母）进行覆盖，这样感觉挺好的（因为按周覆盖，日志不会溢出），但还是有一些推荐的设置，至少需要将下面的内容作为参考放置。

这个设置会按照年月日单位输出日志，但不能设置轮换文件的数量，所以需要注意。

log_filename = 'postgresql.log'
log_line_prefix = '[%t]%u %d %p[%l]'
log_min_duration_statement = 5000

那么对于日志文件如何处理呢？有两种选择：
– 创建自定义的日志轮转sh脚本。
– 将PostgreSQL日志保存为固定名称，并设置logrotate来进行管理。
– 输出到syslog以记录日志，并设置logrotate来进行管理。

由于考虑到运用，这些都是根据设计的。但我个人认为自己制作会更好一些。

最后

PostgreSQL是一个免费的开源关系数据库管理系统（RDBMS），很容易以便捷的方式进行部署。我也是以这样的形式使用它。
然而，正如任何应用一样，在作为业务工具使用时，我们还需要额外考虑安全性和备份等方面的问题。

我参考了你们的网站。

・日常管理PostgreSQL数据库
・通过logrotate来管理PostgreSQL日志的设置