10、PostgreSQL 问题解析

转载：下面链接和作者是第一作者，犇犇只是个学习搬运工
作者：高张远瞩(HiLoveS)
博客：http://www.cnblogs.com/hiloves/
最近在centos7 安装postgresql，window远程连接时候出现问题，总是连接不上，犇犇很头疼，拗了一天多，还是没找到原因，偶然的尝试，发现了问题所在，下面将其记录下来，留作以后的参考。

问题表现：
---在cnetos7.7 环境下，能够正常操作postgresql,且能创建postgis扩展，但是在window系统上navicte远程连接数据库时，总是出现下图问题，第一次遇到，实在很费解，网上资料并不能很好的解决该问题。server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request!

问题显示

问题最终分析
---最终的问题，发现是在配置文件中，登录连接认证方式的问题，因为原来默认的是ident，后来改成了trust，发现可以连接了（但是在生产环境尽量不要用trust,用md5），暂时锁定问题所在，但本犇犇并不是很了解这个配置文件，刚好查到高张远瞩(HiLoveS)大牛的博客，所以，本着偷懒的原则，就整个搬过来了，还望勿怪。ps【不要看格式不一样，那只是本犇犇写一遍记一遍的习惯，还是要尊重原作！】
如果想了解更多，可以继续读，如果是解决问题，已经可以解决了。

1、pg_hba.conf 文件简析

pg_hba.conf是客户端认证配置文件，定义如何认证客户端。常规常用的配置模式如下：

 TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
# "local" is for Unix domain socket connections only
local    all      all                 ident
# IPv4 local connections:
host     all      all   127.0.0.1/32  md5
# IPv6 local connections:
host     all      all   ::1/128       md5

TYPE定义了多种连接PostgreSQL的方式，分别是：
1 、“local”使用本地unix套接字
2、“host”使用TCP/IP连接（包括SSL和非SSL）
3、“host”结合“IPv4地址”使用IPv4方式，结合“IPv6地址”则使用IPv6方式
4、“hostssl”只能使用SSL TCP/IP连接
5、“hostnossl”不能使用SSL TCP/IP连接。

DATABASE指定哪个数据库，多个数据库，库名间以逗号分隔。“all”只有在没有其他的符合条目时才代表“所有”，如果有其他的符合条目则代表“除了该条之外的”，因为“all”的优先级最低。如下例：

local    db1    user1    reject
local    all      all        ident

这两条都是指定local访问方式，因为前一条指定了特定的数据库db1，所以后一条的all代表的是除了db1之外的数据库，同理用户的all也是这个道理。
1、USER指定哪个数据库用户（PostgreSQL正规的叫法是角色，role）。多个用户以逗号分隔。
2、CIDR-ADDRESS项local方式不必填写，该项可以是IPv4地址或IPv6地址，可以定义某台主机或某个网段。
3、METHOD指定如何处理客户端的认证。常用的有ident，md5，password，trust，reject
4、ident是Linux下PostgreSQL默认的local认证方式，凡是能正确登录服务器的操作系统用户（注：不是数据库用户）就能使用本用户映射的数据库用户不需密码登录数据库。用户映射文件为pg_ident.conf，这个文件记录着与操作系统用户匹配的数据库用户，如果某操作系统用户在本文件中没有映射用户，则默认的映射数据库用户与操作系统用户同名。
问题描述比如，服务器上有名为user1的操作系统用户，同时数据库上也有同名的数据库用户，user1登录操作系统后可以直接输入psql，以user1数据库用户身份登录数据库且不需密码。很多初学者都会遇到psql -U username登录数据库却出现“username ident 认证失败”的错误，明明数据库用户已经createuser。
原因就在于此，使用了ident认证方式，却没有同名的操作系统用户或没有相应的映射用户。
解决方案：1、在pg_ident.conf中添加映射用户；2、改变认证方式。
5、md5是常用的密码认证方式，如果你不使用ident，最好使用md5。密码是以md5形式传送给数据库，较安全，且不需建立同名的操作系统用户。
6、password是以明文密码传送给数据库，建议不要在生产环境中使用。
7、trust是只要知道数据库用户名就不需要密码或ident就能登录，建议不要在生产环境中使用。
8、reject是拒绝认证。

本地使用psql登录数据库，是以unix套接字的方式，附合local方式。
使用PGAdmin3或navicate登录数据库，不论是否本地均是以TCP/IP方式，附合host方式。如果是本地（数据库地址localhost），CIDR-ADDRESS则为127.0.0.1/32。如：

1 | 允许本地使用PGAdmin3登录数据库，数据库地址localhost，用户user1，数据库user1db：
host    user1db    user1    127.0.0.1/32    md5
2 | 允许10.1.1.0~10.1.1.255网段登录数据库
host    all    all    10.1.1.0/24    md5
3 | 信任192.168.1.10登录数据库：
host    all    all    192.168.1.10/32    trust

下面这一段，其实也不用这么麻烦，实际中，可以用vi保存修改，然后restart 数据库服务就可以了。也可能是本犇犇暂时没理解透彻。
{ pg_hba.conf修改后，使用pg_ctl reload重新读取pg_hba.conf文件，如果pg_ctl找不到数据库，则用-D /.../pgsql/data/　指定数据库目录，或export PGDATA=/.../pgsql/data/　导入环境变量。}

另：PostgreSQL默认只监听本地端口，用netstat -tuln只会看到“tcp 127.0.0.1:5432 LISTEN”。修改postgresql.conf中的listen_address=*，监听所有端口，这样远程才能通过TCP/IP登录数据库，用netstat -tuln会看到“tcp 0.0.0.0:5432 LISTEN”。

注意：当前犇犇使用的centos版本是7.7,实际使用中的相关命令请认真查看，可能会有一定的小差异。

2、pg_hba.conf 文件简析原文地址

在上面1中，里面讲到ident认证方式，需要建立映射用户或具备同名用户。
同名用户好办，各新建一个同名的操作系统用户和数据库用户，两个用户密码不必相同，但名字必须相同。用该用户登录到操作系统或su到该用户后，即可$ psql dbname。
如果不想新建同名用户，也可以配置pg_ident.conf文件。pg_ident.conf用来配置哪些操作系统用户可以映射为数据库用户。本文以PostgreSQL 9为例。

pg_ident.conf的格式如下：
# MAPNAME    SYSTEM-USERNAME    PG-USERNAME
usermap      username           dbuser

usermap为映射名，要在pg_hba.conf中用到，多个映射可以共用同一个映射名，username为操作系统用户名，dbuser为映射到的数据库用户。

例：操作系统用户userzy，使用数据库用户dbzy连接数据库，而操作系统用户userok，使用数据库用户dbok连接数据库。

pg_ident.conf的格式如下：
# MAPNAME    SYSTEM-USERNAME    PG-USERNAME
mapzy　　　　userzy　　　　　　　　dbzy
mapzy　　　　userok　　　　　　　　dbok

pg_hba.conf如下：
# TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
local    all      all                 ident  map=mapzy

map为pg_hba.conf的auth-options项，map=mapzy指示该认证条件使用mapzy映射。指定映射后原本的同名操作系统用户就不能连接数据库了。