Table of Contents
1 Postgres访问接口
在一个完整的应用架构中，PG数据库工作在操作系统与应用程序之间，可以运行在各种操作系统上（Linux,Windows，AIX，FreeBSD，Solaris）和其他的类UNIX操作系统上。
不同的编程语言使用不同的驱动程序连接到PG数据库，总的来说这些驱动分为两类：
① 使用纯语言实现的PG驱动，例如JDBC、.NET等方式。这种连接方式不需要libpq库。
② 通过包装PG的C语言接口库lobpq实现的驱动，例如：Python下的psycopg库、Perl的DBD::pg模块、ODBC等。所以在安装这些驱动之前需要先安装PG的libpg库。
一般情况下，各种应用程序都是通过TCP/IP连接数据库的，如果应用程序与数据库在同一服务器上，也可以使用UNIX domain sockets连接数据库。
2 pg_hba.conf 配置文件说明
pg_hba.conf文件的一般格式是一组记录，每行一个。空行和任何注释字符#之后的文本会被忽略。第一个匹配行会被用来认证。在第一个匹配之后，后续的记录不会被考虑。如果客户端不能用第一个匹配记录中指定的方法认证，该连接会被拒绝。一个记录由多个被空格或者制表符分隔的域组成。如果域值被加上引号，域可以包含空格。记录不能跨越多行。
每一个远程客户端访问记录具有如下的格式：
host database role CIDR-address authentication-method
每一个UNIX-域套接字记录具有如下的格式：
local database role authentication-method
下面的表格描述了每个域的含义。
| 域 | 描述 |
|---|---|
| local | 匹配尝试使用UNIX-域套接字的连接。如果没有这种类型的记录，则UNIX-域套接字连接不被允许。 |
| host | 匹配尝试使用TCP/IP的连接。除非用合适的listen_addresses服务器配置参数值启动，就不能进行远程TCP/IP连接。 |
| hostssl | 匹配尝试使用TCP/IP建立的连接，但只有用SSL加密建立该连接时才允许。必须通过设置ssl配置参数在服务器启动时启用SSL |
| hostnossl | 匹配在不使用SSL的TCP/IP上建立的连接尝试。 |
| database | 指定这一记录匹配的数据库名。值all指定它匹配所有数据库。可以提供多个数据库名，用逗号分隔它们。在文件名前面放一个@，可以指定一个含有数据库名的单独的文件。 |
| role | 指定这一记录匹配的数据库角色名。值all指定它匹配所有角色。如果指定的角色是一个组并且希望该组中的所有成员都被包括在内，在该角色名前面放一个+。可以提供多个角色名，用逗号分隔它们。在文件名前面放一个@，可以指定一个含有角色名的单独的文件。 |
| CIDR-address | 指定这一记录匹配的客户端机器的IP地址范围。它包含一个标准点分十进制表示的IP地址和一个CIDR掩码长度。IP地址只能用数字指定，不能写成域或者主机名。掩码长度指示客户端IP地址必须匹配的高位位数。给定IP地址中，在这些位的右边必须是零。IP地址、/和CIDR掩码长度之间不能有任何空格。典型的CIDR地址例子是：192.0.2.89/32是一个单一主机，192.0.2.0/24是一个小网络，10.6.0.0/16是一个大网络。要指定一个单一主机，对IPv4使用一个CIDR掩码32，对IPv6使用128。在一个网络地址中，不要省略拖尾的零。 |
| IP-addressIP-mask | 这些域可以被用作CIDR地址记号的一种替代。实际的掩码在一个单独的列中指定，而不是指定掩码长度。例如，255.255.255.255表示CIDR掩码长度32。这些域只适用于host、hostssl和hostnossl记录。 |
| authentication-method | 指定连接时使用的认证方法。Greenplum支持PostgreSQL 9.0支持的认证方法。 |
认证方法
METHOD ：列标识 客户端 的认证方法，常见的认证方法有 trust reject md5 和password
reject ：认证方式主要应用在这样的场景中：允许某一网段的大多数主机访问数据库，但拒绝这 网段的少数特定主
md5 password ：认证方式的区别在于 md5 认证方式为双 md5 加密， password 指明文密码，所以不要在非信任网络使用 password 认证方式
scram-sha-256 ：PostgreSQL 10 中新 的基于 SASL 的认证方式 PostgreSQL 目前提供的最安全的认证方式 使用 scram-sha-256 认证方式不支持旧版本的客户端库 如果使PostgreSQL 10 以前的 户端库连接数据库，会有如下错误：
[postgres@pghost2 ~]$ /usr/pgsql- 9.6/bin/psql - h pghostl -p 1921 - U postgres mydb
psql : SCRAM authentication requ res l 工 bpq version 10 or above
更多认证方式的详细说明参考官方文档： https 😕/www.postgresql.org/ docs/ current/static/ auth-methods.html
3 相关参数
3.1 连接和认证
listen_addresses(string)
在默认情况下， PostgreSQL 实例是不允许通过远程访问数据库的，从其他主机访问数据库端口将被拒绝，因此需要调整监听地址配置，主要修改的参数是listen_adddress;
listen_addresses = '*' # what IP address(es) to listen on;-监听什么IP地址，可以是一个IP或者 多个IP地址
# comma-separated list of addresses;
用，逗号隔开
# defaults to 'localhost'; use '*' for all
默认是localhost，*表示全部放开
# (change requires restart)
需要重启生效
-
port(integer)服务器监听的 TCP 端口；默认是 5432 。请注意服务器会同一个端口号监听所有的 IP 地址。这个参数只能在服务器启动时设置。
-
max_connections(integer)决定数据库的最大并发连接数。默认值通常是 100 个连接，但是如果内核设置不支持（initdb时决定），可能会比这个 数少。这个参数只能在服务器启动时设置。当运行一个后备服务器时，你必须设置这个参数等于或大于主服务器上的参数。否则，后备服务器上可能无法允许查询。
-
superuser_reserved_connections(integer)决定为PostgreSQL超级用户连接而保留的连接“槽”数。 同时活跃的并发连接最多max_connections个。任何时候，活跃的并发连接数最多为
max_connections减去superuser_reserved_connections，新连接就只能由超级用户发起了，并且不会有新的复制连接被接受。默认值是 3 连接 。这个值必须小于max_connections。 这个参数只能在服务器启动时设置。 -
unix_socket_directories(string)指定服务器用于监听来自客户端应用的连接的 Unix 域套接字目录。通过列出用逗号分隔的多个目录可以建立多个套接字。 项之间的空白被忽略，如果你需要在名字中包括空白或逗号，在目录名周围放上双引号。 一个空值指定在任何 Unix 域套接字上都不监听，在这种情况中只能使用 TCP/IP 套接字来连接到服务器。 默认值通常是
/tmp，但是在编译时可以被改变。在windows上，默认值为空，意味着默认不建立UNIX-域嵌套。这个参数只能在服务器启动时设置。除了套接字文件本身（名为.s.PGSQL.*nnnn*，其中*nnnn*是服务器的端口号），一个名为.s.PGSQL.*nnnn*.lock的普通文件会在每一个unix_socket_directories目录中被创建。任何一个都不应该被手工移除。 -
unix_socket_group(string)设置 Unix 域套接字的所属组（套接字的所属用户总是启动服务器的用户）。可以与选项
unix_socket_permissions一起用于对 Unix域连接进行访问控制。默认是一个空字符串，表示服务器用户的默认组。这个参数只能在服务器启动时设置。Windows 上不支持这个参数，所有设置会被忽略。 -
unix_socket_permissions(integer)设置 Unix 域套接字的访问权限。Unix 域套接字使用普通的 Unix 文件系统权限集。这个参数值应该是数字的形式，也就是系统调用
chmod和umask接受的 形式（如果使用自定义的八进制格式，数字必须以一个0（零）开头）。默认的权限是0777，意思是任何人都可以连接。合理的候选是0770（只有用户和同组的人可以访问， 又见unix_socket_group）和0700（只有用户自己可以访问）（请注意，对于 Unix 域套接字，只有写权限有麻烦，因此没有对读取和执行权限的设置和收回）。这个访问控制机制与第 20 章中的用户认证没有关系。这个参数只能在服务器启动时设置。这个参数与完全忽略套接字权限的系统无关，尤其是自版本10以上的Solaris。 在那些系统上，可以通过把unix_socket_directories指向一个把搜索权限 限制给指定用户的目录来实现相似的效果。 -
bonjour(boolean)通过Bonjour广告服务器的存在。默认值是关闭。 这个参数只能在服务器启动时设置。
-
bonjour_name(string)指定Bonjour服务名称。空字符串
''（默认值）表示使用计算机名。 如果编译时没有打开Bonjour支持那么将忽略这个参数。这个参数只能在服务器启动时设置。 -
tcp_keepalives_idle(integer)规定在操作系统向客户端发送一个TCP keepalive消息后无网络活动的时间总量。 如果指定值时没有单位，则以秒为单位。值0(默认值)表示选择操作系统默认值。 指定不活动多少秒之后通过 TCP 向客户端发送一个 keepalive 消息。 0 值表示使用默认值。 这个参数只有在支持
TCP_KEEPIDLE或等效套接字选项的系统或 Windows 上才可以使用。在其他系统上，它必须为零。在通过 Unix 域套接字连接的会话中，这个参数被忽略并且总是读作零。注意在 Windows 上，设定值为0将设置这个参数为 2 小时，因为 Windows 不支持读取系统默认值。 -
tcp_keepalives_interval(integer)规定未被客户端确认收到的TCP keepalive消息应重新传输的时间长度。 如果指定值时没有单位，则以秒为单位。值0(默认值)表示选择操作系统默认值。 这个参数只有在支持
TCP_KEEPINTVL或等效套接字选项的系统或 Windows 上才可以使用。在其他系统上，必须为零。在通过 Unix域套接字连接的会话中，这个参数被忽略并总被读作零。注意在 Windows 上，设定值为0将设置这个参数为 1 秒，因为 Windows 不支持读取系统默认值。 -
tcp_keepalives_count(integer)指定服务器到客户端的连接被认为中断之前可以丢失的TCP keepalive消息的数量。值0(默认值)表示选择操作系统默认值。 这个参数只有在支持
TCP_KEEPCNT或等效套接字选项的系统上才可以使用。在其他系统上，必须为零。在通过 Unix 域套接字连接的会话中，这个参数被忽略并总被读作零。注意Windows 不支持该参数，且必须为零。 -
tcp_user_timeout(integer)指定传输的数据在TCP连接被强制关闭之前可以保持未确认状态的时间量。 如果指定值时没有单位，则以毫秒为单位。值0(默认值)表示选择操作系统默认值。 这个参数只有在支持
TCP_USER_TIMEOUT的系统上才被支持；在其他系统上，它必须为零。 在通过Unix-domain 套接字连接的会话中，此参数将被忽略并且始终读取为零。注意在Windows上不支持该参数，并且必须为零。
3.2 安全和认证
-
authentication_timeout(integer)允许完成客户端认证的最长时间。如果一个客户端没有在这段时间里完成认证协议，服务器将关闭连接。 这样就避免了出问题的客户端无限制地占有一个连接。如果指定值时没有单位，则以秒为单位。 默认值是 1分钟（
1m）。这个参数只能在服务器命令行上或者在postgresql.conf文件中设置。 -
password_encryption(enum)当在CREATE ROLE或者ALTER ROLE中指定了口令时，这个参数决定用于加密该口令的算法。默认值是
md5，它会将口令存为一个MD5哈希（on也会被接受，它是md5的别名）。将这个参数设置为scram-sha-256将使用SCRAM-SHA-256来加密口令。注意老的客户端可能缺少对SCRAM认证机制的支持，因此无法使用用SCRAM-SHA-256加密的口令。详情请参考第 20.5 节。 -
krb_server_keyfile(string)设置Kerberos服务器密钥文件的位置。详情请参考第 20.6 节。这个参数只能在
postgresql.conf文件中或者服务器命令行上设置。 -
krb_caseins_users(boolean)设置是否应该以大小写不敏感的方式对待GSSAPI用户名。默认值是
off（大小写敏感）。这个参数只能在postgresql.conf文件中或者服务器命令行上设置。 -
db_user_namespace(boolean)这个参数启用针对每个数据库的用户名。这个参数默认是关掉的。这个参数只能在
postgresql.conf文件中或者服务器命令行上设置。如果这个参数为打开，应该把用户创建成*username@dbname的形式。当一个连接客户端传来username*时，@和数据库名会被追加到用户名并且服务器会查找这个与数据库相关的用户名。注意在SQL环境中用含有@的名称创建用户时，需要把用户名放在引号内。在这个参数被启用时，仍然可以创建平常的全局用户。而在客户端中指定这种用户时只需要简单地追加@，例如joe@。在服务器查找该用户名之前，@会被剥离掉。db_user_namespace会导致客户端和服务器的用户名表达形式不同。认证检查总是会以服务器的用户名表达形式来完成，因此认证方法必须针对服务器用户名而不是客户端用户名来配置。由于md5方法在客户端和服务器两端都使用用户名作为salt，md5不能与db_user_namespace同时使用。注意这种特性的目的是在找到完整的解决方案之前提供一种临时的措施。在找到完整解决方案时，这个选项将被去除。
3.3 SSH相关参数
-
ssl(boolean)启用SSL连接。这个参数只能在
postgresql.conf文件中或者服务器命令行上设置。默认值是off。 -
ssl_ca_file(string)指定包含 SSL 服务器证书颁发机构（CA）的文件名。相对路径是相对于数据目录的。这个参数只能在
postgresql.conf文件中或者服务器命令行上设置。默认值为空，表示没有载入CA文件，并且客户端证书验证没有被执行。 -
ssl_cert_file(string)指定包含 SSL 服务器证书的文件名。相对路径是相对于数据目录的。这个参数只能在
postgresql.conf文件中或者服务器命令行上设置。默认值是server.crt。 -
ssl_crl_file(string)指定包含 SSL 服务器证书撤销列表（CRL）的文件名。其中的相对路径是相对于数据目录的。这个参数只能在
postgresql.conf文件中或者服务器命令行上设置。默认值是空，表示没有载入CRL文件。 -
ssl_key_file(string)指定包含 SSL 服务器私钥的文件名。相对路径是相对于数据目录。这个参数只能在
postgresql.conf文件中或者服务器命令行上设置。默认值是server.key。 -
ssl_ciphers(string)指定一个允许用于SSL连接的SSL密码套件列表。 这个设置的语法和所支持的值列表可以参见OpenSSL包中的ciphers手册页。 仅在使用 TLS 版本 1.2 及更低版本的连接才受影响。目前没有控制 TLS 版本 1.3 连接使用的密码选择的设置。 默认值是
HIGH:MEDIUM:+3DES:!aNULL。默认值通常是一种合理的选择，除非用户有特定的安全性需求。这个参数只能在postgresql.conf文件中或者服务器命令行上设置。默认值的解释：HIGH使用来自HIGH组的密码的密码组（例如 AES, Camellia, 3DES）MEDIUM使用来自MEDIUM组的密码的密码组（例如 RC4, SEED）+3DESOpenSSL 对HIGH的默认排序是有问题的，因为它认为 3DES 比 AES128 更高。这是错误的，因为 3DES 提供的安全性比 AES128 低，并且它也更加慢。+3DES把它重新排序在所有其他HIGH和MEDIUM密码之后。!aNULL禁用不做认证的匿名密码组。这类密码组容易收到中间人攻击，因此不应被使用。可用的密码组细节可能会随着 OpenSSL 版本变化。可使用命令openssl ciphers -v 'HIGH:MEDIUM:+3DES:!aNULL'来查看 当前安装的OpenSSL版本的实际细节。注意这个列表是根据服务器密钥类型 在运行时过滤过的。 -
ssl_prefer_server_ciphers(boolean)指定是否使用服务器的 SSL 密码首选项，而不是用客户端的。这个参数只能在
postgresql.conf文件中或者服务器命令行上设置。 默认值是on。老的PostgreSQL版本没有这个设置并且总是使用客户端的首选项。这个设置主要用于与那些版本 的向后兼容性。使用服务器的首选项通常会更好，因为服务器更可能会被合适地配置。 -
ssl_ecdh_curve(string)指定用在ECDH密钥交换中的曲线名称。它需要被所有连接的客户端支持。 它不需要与服务器椭圆曲线密钥使用的曲线相同。这个参数只能在
postgresql.conf文件中或者服务器命令行上设置。默认值是prime256v1。OpenSSL 命名了最常见的曲线：prime256v1(NIST P-256)、secp384r1(NIST P-384)、secp521r1(NIST P-521)。openssl ecparam -list_curves命令可以显示可用曲线的完 整列表。不过并不是所有的都在TLS中可用。 -
ssl_min_protocol_version(enum)设置要使用的最小SSL/TLS协议版本。当前的可用版本包括：
TLSv1,TLSv1.1,TLSv1.2,TLSv1.3. 旧版本的 OpenSSL 库不支持所有值;如果选择了不支持的设置将会引发错误。 TLS 1.0之前的协议版本，也就是SSL 版本 2 and 3，总是禁用的。默认为TLSv1.2, 在本文撰写时的行业最佳实践。 -
ssl_max_protocol_version(enum)设定要使用的最大SSL/TLS协议版本。 有效的版本为 ssl_min_protocol_version, 添加一个空字符串，允许任何协议版本。 默认为允许任何版本。设置最大协议版本主要用于测试，或者某个组件在与较新的协议配合工作时出现了问题。
-
ssl_dh_params_file(string)指定含有用于SSL密码的所谓临时DH家族的Diffie-Hellman参数的文件名。默认值为空，这种情况下将使用内置的默认DH参数。使用自定义的DH参数可以降低攻击者破解众所周知的内置DH参数的风险。可以用命令
openssl dhparam -out dhparams.pem 2048创建自己的DH参数文件。这个参数只能在postgresql.conf文件中或服务器命令行上进行设置。 -
ssl_passphrase_command(string)设置当需要一个密码（例如一个私钥）来解密SSL文件时会调用的一个外部命令。默认情况下，这个参数为空，表示使用内建的提示机制。该命令必须将密码打印到标准输出并且以代码0退出。在该参数值中，
%p被替换为一个提示字符串（要得到文字%，应该写成%%）。注意该提示字符串将可能含有空格，因此要确保加上适当的引号。如果输出的末尾有单一的新行，它会被剥离掉。该命令实际上并不一定要提示用户输入一个密码。它可以从文件中读取密码、从钥匙链得到密码等等。确保选中的机制足够安全是用户的责任。这个参数只能在postgresql.conf文件中或服务器命令行上进行设置。 -
ssl_passphrase_command_supports_reload(boolean)这个参数决定在配置重载期间如果一个密钥文件需要口令时，是否也调用
ssl_passphrase_command设置的密码命令。 如果这个参数为off（默认），那么在重载期间将忽略ssl_passphrase_command，如果在此期间需要密码则SSL配置将不会被重载。 对于要求一个TTY（当服务器正在运行时可能是不可用的）来进行提示的命令，这种设置是合适的。 例如，如果密码是从一个文件中得到的，将这个参数设置为on可能是合适的。这个参数只能在postgresql.conf文件中或者服务器命令行上设置。
3 连接访问测试
3.1 unix套接字连接
unix 相关参数：默认的 sockets 文件位于 /tmp 目录下，可以对unix套接字目录和所属组的权限控制
postgres@[local]:5432=#SELECT name,setting FROM pg_settings WHERE name ~ 'unix';
name | setting
-------------------------+---------
unix_socket_directories | /tmp
unix_socket_group |
unix_socket_permissions | 0777
(3 rows)
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 192.168.0.0/16 md5
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128
其中# "local" is for Unix domain socket connections only
local all all trust
就是本地socket连接的认证方式
socket文件
srwxrwxrwx. 1 postgres postgres 0 Aug 23 22:05 .s.PGSQL.5432
-rw-------. 1 postgres postgres 47 Aug 23 22:05 .s.PGSQL.5432.lock
[root@pgserver12 tmp]# pwd
/tmp
这里的socket文件就是 777权限，.s.PGSQL.5432 5432是端口号
连接测试
[postgres@pgserver12 pgdata]$ psql
psql (12.5)
Type "help" for help.
postgres@[local]:5432=#
postgres@[local]:5432=#select inet_client_port(),inet_client_addr();
inet_client_port | inet_client_addr
------------------+------------------
|
(1 row)
postgres@[local]:5432=#
客户端端口和地址都是空值
禁用测试
禁用使用unix套接字访问（注释 local行）
#local all all trust
[postgres@pgserver12 pgdata]$ pg_ctl reload
server signaled
[postgres@pgserver12 pgdata]$ psql
psql: error: FATAL: no pg_hba.conf entry for host "[local]", user "postgres", database "postgres", SSL off
[postgres@pgserver12 pgdata]$
3.2 基于主机的IPv4和IPV6回环地址(loopback)访问
pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
测试连接
IPV4 回环测试
[postgres@pgserver12 ~]$ psql -h localhost
psql (12.5)
Type "help" for help.
postgres@localhost:5432=#
IPV6 回环测试
[postgres@pgserver12 ~]$ psql -h ::1
psql (12.5)
Type "help" for help.
postgres@::1:5432=#
禁用连接测试
# IPv4 local connections:
#host all all 127.0.0.1/32 trust
# IPv6 local connections:
#host all all ::1/128 trust
[postgres@pgserver12 ~]$ psql -h ::1
psql: error: FATAL: no pg_hba.conf entry for host "::1", user "postgres", database "postgres", SSL off
3.3 基于host 的IPv4的地址访问
3.3.1 基于网段的访问
host all all 192.168.129/24 trust
表明只允许 192.168.129.1~192.168.129.255之间的主机访问
[postgres@sungsasong ~]$ psql -h 192.168.129.60
psql (12.5)
Type "help" for help.
postgres=#
C:\Users\Desktop>psql -h 192.168.129.60 -U postgres -d postgres -p5532
psql (13.0, server 12.5)
Type "help" for help.
postgres=#
3.3.2.基于具体主机访问
host all all 192.168.129.26/32 trust
$ ps -ef | grep 192.168.129.25 | egrep -v "grep"
postgres 13302 13173 0 11:12 ? 00:00:00 postgres: postgres postgres 192.168.129.25(5432) idle
3.3.3 访问指定的数据库
#host all all 192.168.0.0/16 md5
host yanwei all 192.168.0.0/16 trust
通过yanwei用户连接yanwei数据库可以
PS C:\WINDOWS\system32> psql -h 192.168.245.119 -d yanwei -p 5432 -U yanwei
psql (12.6, 服务器 12.5)
输入 "help" 来获取帮助信息.
PS C:\WINDOWS\system32> psql -h 192.168.245.119 -d yanwei -p 5432 -U postgres
psql (12.6, 服务器 12.5)
输入 "help" 来获取帮助信息.
访问postgres数据库不可以
PS C:\WINDOWS\system32> psql -h 192.168.245.119 -d postgres -p 5432
psql: 错误: FATAL: no pg_hba.conf entry for host "192.168.245.110", user "YanWei", database "postgres", SSL off
3.3.4 指定的用户访问
# IPv4 local connections:
host all all 127.0.0.1/32 trust
#host all all 192.168.0.0/16 md5
host yanwei yanwei 192.168.0.0/16 trust
[postgres@pgserver12 pgdata]$ pg_ctl reload
server signaled
用户postgres用户连接报错
PS C:\WINDOWS\system32> psql -h 192.168.245.119 -d yanwei -p 5432 -U postgres
psql: 错误: FATAL: no pg_hba.conf entry for host "192.168.245.110", user "postgres", database "yanwei", SSL off
用yanwei用户连接是可以的
PS C:\WINDOWS\system32> psql -h 192.168.245.119 -d yanwei -p 5432 -U yanwei
psql (12.6, 服务器 12.5)
输入 "help" 来获取帮助信息.
yanwei=#
3.4 ssl连接
本文参考官方手册：18.9. 用 SSL 进行安全的 TCP/IP 连接
- 修改pg_hba文件
# IPv4 local connections:
host all all 127.0.0.1/32 trust
#host all all 192.168.0.0/16 md5
hostssl yanwei yanwei 192.168.0.0/16 trust
-
开启ssl功能
ALTER SYSTEM SET ssl = on; $pg_ctl reload -D $PGDATA 或者 SELECT pg_reload_conf(); [postgres@pgserver12 pgdata]$ pg_ctl reload -D $PGDATA server signaled [postgres@pgserver12 pgdata]$ psql psql (12.5) Type "help" for help. postgres@[local]:5432=#show ssl postgres-# ; ssl ----- on (1 row) -
创建 ssl的存放目录
$mkdir ~/ssl
cd ssl
- 要为服务器创建一个有效期为365天的简单自签名证书，可以使用下面的OpenSSL命令，将dbhost.yourdomain.com替换为服务器的主机名,并修改key的权限
openssl req -new -x509 -days 365 -nodes -text -out server.crt \
-keyout server.key -subj "/CN=pgserver12"
Generating a 2048 bit RSA private key
......................................+++
.....................................................................................................................+++
writing new private key to 'server.key
[postgres@pgserver12 ssl]$ ls
server.crt server.key
chmod og-rwx server.key：如果文件的权限比这个更自由，服务器将拒绝该文件
[postgres@pgserver12 ssl]$ chmod og-rwx server.key
[postgres@pgserver12 ssl]$ ls -ltr
total 8
-rw-------. 1 postgres postgres 1704 Aug 23 23:42 server.key
-rw-rw-r--. 1 postgres postgres 4071 Aug 23 23:42 server.crt
ps：尽管可以使用自签名证书进行测试，但是在生产中应该使用由证书颁发机构（CA）（通常是企业范围的根CA）签名的证书。
- 要创建其身份可以被客户端验证的服务器证书，请首先创建一个证书签名请求（CSR）和一个公共/专用密钥文件：
openssl req -new -nodes -text -out root.csr \
-keyout root.key -subj "/CN=pgserver12"
chmod og-rwx root.key
[postgres@pgserver12 ssl]$ openssl req -new -nodes -text -out root.csr \
> -keyout root.key -subj "/CN=pgserver12"
Generating a 2048 bit RSA private key
.........+++
...............................+++
writing new private key to 'root.key'
-----
[postgres@pgserver12 ssl]$ chmod og-rwx root.key
[postgres@pgserver12 ssl]$ ll
total 16
-rw-rw-r--. 1 postgres postgres 3317 Aug 23 23:46 root.csr
-rw-------. 1 postgres postgres 1704 Aug 23 23:46 root.key
-rw-rw-r--. 1 postgres postgres 4071 Aug 23 23:42 server.crt
-rw-------. 1 postgres postgres 1704 Aug 23 23:42 server.key
- 使用密钥对请求进行签名以创建根证书颁发机构（使用Linux上的默认OpenSSL配置文件位置）：
openssl x509 -req -in root.csr -text -days 3650 \
-extfile /etc/ssl/openssl.cnf -extensions v3_ca \
-signkey root.key -out root.crt
这里文档给的openssl.cnf为/etc/ssl/openssl.cnf；但是实际上是/etc/pki/tls/openssl.cnf
[postgres@pgserver12 ssl]$ ll /etc/pki/tls/openssl.cnf
-rw-r--r--. 1 root root 10923 Aug 6 2019 /etc/pki/tls/openssl.cnf
因此命令为
openssl x509 -req -in root.csr -text -days 3650 \
-extfile /etc/pki/tls/openssl.cnf -extensions v3_ca \
-signkey root.key -out root.crt
[postgres@pgserver12 ssl]$ openssl x509 -req -in root.csr -text -days 3650 \
> -extfile /etc/pki/tls/openssl.cnf -extensions v3_ca \
> -signkey root.key -out root.crt
Signature ok
subject=/CN=pgserver12
Certificate:
Data:
Version: 1 (0x0)
Serial Number:
b4:e6:67:dd:98:08:29:4d
Signature Algorithm: NULL
Issuer: CN=pgserver12
Validity
Not Before: Aug 23 15:49:42 2021 GMT
Not After : Aug 21 15:49:42 2031 GMT
Subject: CN=pgserver12
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
Public-Key: (2048 bit)
Modulus:
00:c2:ef:21:70:97:a4:1c:ce:7e:4c:d8:70:8e:44:
5c:a9:55:5c:03:f7:bf:f6:9b:a0:21:b0:35:3c:ad:
4b:e7:7c:87:32:08:22:21:7f:00:10:b8:7b:1d:fa:
60:47:38:c1:b8:68:55:a0:49:cb:9c:c2:67:28:72:
f6:38:74:10:bf:01:2e:37:8f:df:1e:9e:d8:74:2f:
04:7d:c4:09:bb:47:16:d2:28:65:16:b3:26:b4:6c:
e8:7b:25:18:f2:7a:02:b5:2a:17:12:04:84:2a:b5:
11:40:56:91:3f:9b:7d:cf:11:8d:1e:ed:dd:d9:46:
71:c2:2a:bf:60:8c:da:94:b0:05:f3:73:d0:fc:fa:
56:76:de:30:17:78:fa:3b:40:77:0c:17:21:2c:1e:
f0:fb:86:17:99:58:f4:32:85:00:a0:82:95:ea:11:
3c:4d:5b:4e:f0:4a:84:47:ca:46:fb:12:17:5c:12:
c4:32:45:7a:36:d9:37:a7:86:a6:8b:8d:52:38:ff:
56:02:4e:20:2c:7d:45:cb:2a:d7:28:c0:dd:ca:e9:
52:c8:72:56:f5:2a:ad:63:59:46:1c:cb:3b:7e:8a:
45:32:fe:fa:c9:e0:71:be:29:b5:d9:70:4f:35:f8:
a9:49:96:15:08:6f:1d:6c:fa:99:99:c0:81:c3:c7:
d8:73
Exponent: 65537 (0x10001)
Signature Algorithm: NULL
Getting Private key
[postgres@pgserver12 ssl]$
[postgres@pgserver12 ssl]$ ll
total 20
-rw-rw-r--. 1 postgres postgres 1099 Aug 23 23:52 root.crt
-rw-rw-r--. 1 postgres postgres 3317 Aug 23 23:46 root.csr
-rw-------. 1 postgres postgres 1704 Aug 23 23:46 root.key
-rw-rw-r--. 1 postgres postgres 4071 Aug 23 23:42 server.crt
-rw-------. 1 postgres postgres 1704 Aug 23 23:42 server.ke
- 创建由新的根证书颁发机构签名的服务器证书：
openssl req -new -nodes -text -out server.csr \
-keyout server.key -subj "/CN=pgserver12"
chmod og-rwx server.key
openssl x509 -req -in server.csr -text -days 365 \
-CA root.crt -CAkey root.key -CAcreateserial \
-out server.crt
postgres@pgserver12 ssl]$ openssl x509 -req -in server.csr -text -days 365 \
> -CA root.crt -CAkey root.key -CAcreateserial \
> -out server.crt
Signature ok
subject=/CN=pgserver12
Certificate:
Data:
Version: 1 (0x0)
Serial Number:
fc:a7:8a:70:93:8d:97:c4
Signature Algorithm: NULL
Issuer: CN=pgserver12
Validity
Not Before: Aug 23 15:53:21 2021 GMT
Not After : Aug 23 15:53:21 2022 GMT
Subject: CN=pgserver12
Subject Public Key Info:
Public Key Algorithm: rsaEncryption
Public-Key: (2048 bit)
Modulus:
00:ba:66:9a:f6:ec:15:68:51:5c:df:9d:57:48:d8:
56:e3:55:2f:d5:0b:d9:9f:2f:b7:02:17:20:5b:5b:
f0:7e:af:1d:09:b0:8a:e2:23:12:4a:a8:6f:97:94:
3b:73:2e:df:88:79:fc:e5:99:17:32:3b:ce:04:42:
91:1b:eb:51:4b:8f:13:b9:70:86:88:ef:1f:c2:51:
ad:12:5b:8c:29:21:0d:72:39:a9:57:87:db:25:fd:
03:23:72:46:cd:7c:ec:8e:20:da:76:80:13:bd:37:
8b:e2:09:be:11:86:b6:a4:60:ba:e3:05:d7:92:b3:
da:6e:fb:3a:85:9f:39:87:0c:d4:19:86:d4:2d:af:
96:0f:d5:f6:4c:51:0f:d1:69:70:21:eb:43:a2:51:
1e:58:f2:4a:c2:81:d5:e5:70:fe:71:1f:e5:3e:a1:
91:fb:7b:b7:ce:72:64:4e:57:5a:5d:c4:1b:7f:5d:
0a:ec:d4:18:c5:50:5a:f2:9b:c5:d8:89:79:d6:8b:
b3:9f:94:ba:e4:d6:2c:c2:64:8c:20:22:9e:99:b1:
60:88:89:eb:cd:60:5b:7a:a1:8d:c7:9c:f6:8c:0c:
06:c4:ce:bb:90:9b:f2:73:ff:56:7b:ba:d4:57:55:
c4:82:ed:ff:5d:67:41:0c:43:b1:e4:79:86:f3:f0:
91:a7
Exponent: 65537 (0x10001)
Signature Algorithm: NULL
Getting CA Private Key
[postgres@pgserver12 ssl]$ ll
total 28
-rw-rw-r--. 1 postgres postgres 1099 Aug 23 23:52 root.crt
-rw-rw-r--. 1 postgres postgres 3317 Aug 23 23:46 root.csr
-rw-------. 1 postgres postgres 1704 Aug 23 23:46 root.key
-rw-rw-r--. 1 postgres postgres 17 Aug 23 23:53 root.srl
-rw-rw-r--. 1 postgres postgres 981 Aug 23 23:53 server.crt
-rw-rw-r--. 1 postgres postgres 3317 Aug 23 23:52 server.csr
-rw-------. 1 postgres postgres 1704 Aug 23 23:52 server.key
[postgres@pgserver12 ssl]$
-
server.crt和server.key应该存储在服务器上，并且root.crt应该存储在客户端上，以便客户端可以验证服务器的叶证书已由其受信任的根证书签名server.crt 文件和 server.key 文件到 $PGDATA cp server.crt server.key $PGDATA -
重新启动数据库
pg_ctl restart -D $PGDATA -l /tmp/logfile [postgres@pgserver12 pgdata]$ pg_ctl restart -D $PGDATA -l /tmp/logfile waiting for server to shut down.... done server stopped waiting for server to start.... done server started [postgres@pgserver12 pgdata]$ -
测试连接
PS C:\WINDOWS\system32> psql -h 192.168.245.119 -d yanwei -p 5432 -U yanwei psql (12.6, 服务器 12.5) SSL 连接（协议：TLSv1.2，密码：ECDHE-RSA-AES256-GCM-SHA384，密钥位：256，压缩：关闭) 输入 "help" 来获取帮助信息. yanwei=# 说明ssl配置成功了如果启用了 ssl方式连接，那么如果在 $PGDATA/pg_hba.conf中采用 host配置时，ssl 连接的优先级要高于 非 ssl 连接
特别注意:
无论对于 PGDATA/pg_hba.conf 在何时修改，已连接到数据库的客户端都不会受其影响。直到用户自行断开并尝试 下一次连接时,PGDATA/pg_hba.conf 才会生效。
3.5 scram-sha-256密码
从10版本开始，sha-256已经被更改为 scram-sha-256 ，
trust:可以接收任何网段范围内的主机的信任连接
md5:采用 md5加密的方式进行连接
scram-sha-256:采用 sha-256加密的方式进行连接
reject:拒绝所有的连接
peer|ident:对等认证方式连接
3.5.1 检查密码策略
postgres@[local]:5432=#show password_encryption
postgres-# ;
password_encryption
---------------------
md5
(1 row)
postgres@[local]:5432=#
数据库创建用户指定密码默认为 md5的方式进行加密
3.5.2 检查用户的密码方式
SELECT rolname,rolpassword FROM pg_authid WHERE rolname !~ '^pg';
postgres@[local]:5432=#SELECT rolname,rolpassword FROM pg_authid WHERE rolname !~ '^pg';
rolname | rolpassword
----------+-------------------------------------
postgres | md53175bce1d3201d16594cebf9d7eb3f9d
yanwei | md5bfe7b344bf40674a74058e8f37ab5d47
(2 rows)
postgres@[local]:5432=#
3.5.3 配置 md5 的方式进行连接
[postgres@pgserver12 pgdata]$ egrep "192.168.0.0" $PGDATA/pg_hba.conf | egrep -v "^#"
hostssl yanwei yanwei 192.168.0.0/16 md5
[postgres@pgserver12 pgdata]$
yanwei=# \q
PS C:\WINDOWS\system32> psql -h 192.168.245.119 -d yanwei -p 5432 -U yanwei
用户 yanwei 的口令：
psql (12.6, 服务器 12.5)
SSL 连接（协议：TLSv1.2，密码：ECDHE-RSA-AES256-GCM-SHA384，密钥位：256，压缩：关闭)
输入 "help" 来获取帮助信息.
yanwei=#
需要输入密码
3.5.4 配置 scram-sha-256的方式进行连接
1 调整用户密码
postgres@[local]:5432=#ALTER SYSTEM SET password_encryption = 'scram-sha-256';
ALTER SYSTEM
postgres@[local]:5432=#SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres@[local]:5432=#SHOW password_encryption ;
password_encryption
---------------------
scram-sha-256
(1 row)
postgres@[local]:5432=#ALTER USER yanwei WITH PASSWORD 'yanwei';
ALTER ROLE
postgres@[local]:5432=#SELECT rolname,rolpassword FROM pg_authid WHERE rolname !~ '^pg';
rolname | rolpassword
----------+---------------------------------------------------------------------------------------------------
------------------------------------
postgres | md53175bce1d3201d16594cebf9d7eb3f9d
yanwei | SCRAM-SHA-256$4096:sAS+aWEQAEOfVvZHCTIutw==$pY4vHMhb1V1/0dYIEWC6dHWBLW4ILL4xZs7ISShh6qU=:0RGke73vM
39fz5xAg/Bj7S9Ha53SNFmBEu2RWgYg9tE=
(2 rows)
postgres@[local]:5432=#
2 调整pg_hba.conf 策略
[postgres@pgserver12 pgdata]$ egrep "192.168.0.0" $PGDATA/pg_hba.conf | egrep -v "^#"
hostssl yanwei yanwei 192.168.0.0/16 scram-sha-256
[postgres@pgserver12 pgdata]$ pg_ctl reload
server signaled
[postgres@pgserver12 pgdata]$
3 测试
PS C:\WINDOWS\system32> psql -h 192.168.245.119 -d yanwei -p 5432 -U yanwei
用户 yanwei 的口令：
psql (12.6, 服务器 12.5)
SSL 连接（协议：TLSv1.2，密码：ECDHE-RSA-AES256-GCM-SHA384，密钥位：256，压缩：关闭)
输入 "help" 来获取帮助信息.
yanwei=#
3.6 免密登录
.pgpass 是 连接 postgresql 时使用的密码文件，通常位置为 ~/.pgpass。
在使用某些组件时还真的必须使用。具体的格式为：hostname:port:database:username:password
localhost:5432:postgres:postgres:postgres
[postgres@pgserver12 ~]$ psql -h localhost -d postgres -U postgres
psql (12.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres@localhost:5432=#
3.7 对等认证
peer(采用非TCP/IP的方式进行连接)
ident（支持TCP/IP的方式进行连接）
涉及到两个文件
$PGDATA/pg_hba.conf
$PGDATA/pg_ident.conf
依赖要求:
需要安装 oidentd软件包
yum install -y oidentd
3.7.1 文件格式
pg_ident.conf的格式如下：
# MAPNAME SYSTEM-USERNAME PG-USERNAME
usermap username dbuser
3.7.2 软件包
需要安装 oidentd软件包
yum install -y oidentd
[root@pgserver12 yum.repos.d]# yum -y install oidentd
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package oidentd.x86_64 0:2.0.8-20.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=================================================================================================================================================
Package Arch Version Repository Size
=================================================================================================================================================
Installing:
oidentd x86_64 2.0.8-20.el7 huaweiepel 65 k
Transaction Summary
=================================================================================================================================================
Install 1 Package
Total download size: 65 k
Installed size: 132 k
Downloading packages:
oidentd-2.0.8-20.el7.x86_64.rpm | 65 kB 00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : oidentd-2.0.8-20.el7.x86_64 1/1
Verifying : oidentd-2.0.8-20.el7.x86_64 1/1
Installed:
oidentd.x86_64 0:2.0.8-20.el7
Complete!
启动
systemctl start oidentd
查看状态
systemclt stauts oidentd
[root@pgserver12 yum.repos.d]# systemctl status oidentd
● oidentd.service - RFC 1413 identification server
Loaded: loaded (/usr/lib/systemd/system/oidentd.service; disabled; vendor preset: disabled)
Active: active (running) since Tue 2021-08-24 21:46:48 CST; 4s ago
Docs: man:oidentd(8)
man:oidentd.conf(5)
man:oidentd_masq.conf(5)
Main PID: 79032 (oidentd)
CGroup: /system.slice/oidentd.service
└─79032 /usr/sbin/oidentd --foreground --nosyslog -q -u nobody -g nobody
Aug 24 21:46:48 pgserver12 systemd[1]: Started RFC 1413 identification server.
[root@pgserver12 yum.repos.d]#
采用的端口默认使用 113 端口
[root@pgserver12 yum.repos.d]# netstat -antulp|grep 113
tcp 0 0 0.0.0.0:113 0.0.0.0:* LISTEN 79032/oidentd
3.7.3 对等测试
postgres用户中配置 $PGDATA/pg_hba.conf 和 $PGDATA/pg_ident.conf 文件
#测试目的，本地操作系统用户yanwei，需要访问postgres数据库。
1 建立yanwei用户
useradd yanwei
echo yanwei|passwd --stdin yanwei
2 yanwei用户配置PostgreSQL的环境变量
[yanwei@pgserver12 ~]$ cat .bashrc
# .bashrc
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases and functions
export PGHOME=/data/pg12.5
export PGDATA=${PGHOME}/pgdata
export PATH=${PGHOME}/bin:$PATH
export LD_LIBRARY_PATH=${PGHOME}/lib:$LD_LIBRARY_PATH
[yanwei@pgserver12 ~]$ . .bashrc
[yanwei@pgserver12 ~]$
3 配置pg_hba.conf
host all all 192.168.0.0/16 ident map=yanwei
[postgres@pgserver12 pgdata]$ pg_ctl reload
server signaled
4 配置$PGDATA/pg_ident.conf 文件
# MAPNAME SYSTEM-USERNAME PG-USERNAME
yanwei yanwei postgres
5 数据库创建yanwei相关用户
CREATE USER yanwei WITH PASSWORD 'yanwei';
6 登陆yanwei用户进行测试
[yanwei@pgserver12 ~]$ psql -h 192.168.245.119 -d postgres -U yanwei
psql (12.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#
测试2：使用操作系统用户访问非数据库用户下的数据库，用yanwei用户连接ostest用户的数据库
1 vi $PGDATA/pg_hba.conf
host all all 192.168.0.0/16 ident map=yanwei
2 tail -1 $PGDATA/pg_ident.conf
[postgres@pgserver12 pgdata]$ pg_ctl reload
server signaled
[postgres@pgserver12 pgdata]$ tail -1 pg_ident.conf
yanwei yanwei ostest
3 数据库创建ostest用户
CREATE USER ostest WITH PASSWORD 'ostest';
4 用yanwei数据库用户连接
[yanwei@pgserver12 ~]$ psql -h 192.168.245.119 -d postgres -U yanwei
psql: error: FATAL: Ident authentication failed for user "yanwei"
FATAL: Ident authentication failed for user "yanwei"
但是用对应的ostest用户连接即可；【 】
[yanwei@pgserver12 ~]$ psql -h 192.168.245.119 -d postgres -U ostest
psql (12.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=>
总结：对等认证方式连接，只能适用于数据库主机与操作系统用户都在同一台主机上。
