4. postgresql数据库配置

PostgreSQL有两个重要的全局配置文件，都位于初始化数据目录中

    postgresql.conf ：负责配置文件位置、资源限制、集群复制等

    pg_hba.conf ：负责客户端的连接和认证

 

[root@instance-795y2mib data]# ls -l /pgdata/10/data |grep conf
-rw------- 1 postgres postgres  4513 Mar 28 00:11 pg_hba.conf            --数据库实例的“防火墙”
-rw------- 1 postgres postgres  1636 Mar 28 00:11 pg_ident.conf
-rw------- 1 postgres postgres    88 Mar 28 00:11 postgresql.auto.conf
-rw------- 1 postgres postgres 22828 Mar 28 00:11 postgresql.conf

 

0，查看配置

    查询pg_settings系统表

SELECT name,setting FROM pg_settings where name ~ ‘xxx’;\
ELECT current_setting(name);

show all 命令查看

 

一，pg_hba.conf    数据库实例的“防火墙”

 

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

实际上简单来说每一行的作用就是：

    允许哪些主机可以通过什么连接方式和认证方式通过哪个数据库用户连接哪个数据库。

    也就是允许ADDRESS列的主机通过TYPE方式以METHOD认证方式通过USER用户连接DATABASE数据库。

 

    1. TYPE(连接方式)列标识允许的连接方式，可用的值有：local、host、hostssl、hostnossl

 ·local 匹配使用Unix域套接字的连接。如果没有TYPE为local的条目则不允许通过Unix域套接字连接。

 ·host 匹配使用TCP/IP建立的连接，同时匹配SSL和非SSL连接。默认安装只监听本地环回地址localhost的连接，不允许使用TCP/IP远程连接，启用远程连接需要修改postgresql.conf中的listen_addresses参数。

·hostssl 匹配必须是使用SSL的TCP/IP连接。配置hostssl有三个前提条件：

    1.客户端和服务端都安装OpenSSL；

    2.编译PostgreSQL的时候指定configure参数--with-openssl打开SSL支持；

    3.在postgresql.conf中配置ssl=on。

 ·hostnossl和hostssl相反，它只匹配使用非SSL的TCP/IP连接。

 

    2.DATABASE(目标数据库)

        DATABASE列标识该行设置对哪个数据库生效

    3. USER(目标用户)

        USER列标识该行设置对哪个数据库用户生效

    4. ADDRESS(访问来源)

        ADDRESS列标识该行设置对哪个IP地址或IP地址段生效；

    5.METHOD(认证方法)

        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 pghost1 -p 1921 -U postgres psql: SCRAM authentication requires libpq version 10 or above

 

二，postgresql.conf

postgresql.conf配置文件的文件结构很简单，由多个configparameter=value形式

在配置项末尾标记了“#（change requires restart）”的配置项是需要重启数据库实例才可以生效的，其他没有标记的配置项只需要reload即可生效。

    1.全局配置的修改方法

       ALTER SYSTEM 修改的全局参数，自动编辑postgresql.auto.conf文件，在数据库启动时会加载postgresql.auto.conf文件，并用它的配置覆盖postgresql.conf中已有的配置

mydb=# ALTER SYSTEM SET listen_addresses = '*';

    2.非全局配置的修改方法

       设置和重置Database级别的配置

ALTER DATABASE name SET configparameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET configuration

        设置和重置Session级别的配置

SET configparameter { TO | = } { value | 'value' | DEFAULT }
SET configparameter TO DEFAULT;

        更新pg_settings视图

UPDATE `pg_settings` SET setting = new_value WHERE name = 'configparameter';
UPDATE `pg_settings` SET setting = reset_val WHERE name = 'configparameter';

         set_config函数更新会话配置

SELECT set_config('configparameter',new_value,false);

         设置和重置Role级别的配置

ALTER ROLE name IN DATABASE database_name SET configparameter { TO |= } 
ALTER ROLE name IN DATABASE database_name RESET configparameter

三，参数生效

   如果是不需要重启的参数，reload一次就可以生效    

mydb=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t (
1
row)

 or  使用pg_ctl命令reload配置

[postgres@pghost1 ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/10/data reload

四，允许远程访问数据库

默认情况下，PostgreSQL实例是不允许通过远程访问数据库

-bash-4.2$ netstat -nlt |grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN    
tcp6       0      0 ::1:5432                :::*                    LISTEN  

    1.修改监听地址

    postgresql.conf文件中的listen_addresses。默认安装只监听本地环回地址localhost的连接，不允许使用TCP/IP建立远程连接

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)

·what IP address（es）to listen on—监听什么IP地址？也就是允许用哪些IP地址访问，可以是一个IP，也可以是多个IP地址。

·comma-separated list of addresses；—以逗号分隔地址列表。

·defaults to'localhost'；use'*'for all—默认是“localhost”，使用“*”允许所有地址；大多数的高可用架构都使用VIP的方式访问数据库，所以我们一般设置为“*”。

·（change requires restart）—修改这个参数需要重新启动数据库。

修改为如下：

listen_addresses = '*'

重启数据库使配置生效

pg_ctl -D /pgdata/10/data restart

 

--check 检查

-bash-4.2$ netstat -nlt |grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN    

 

2. 要修改pg_hba.conf

假设我们允许所有主机通过TCP/IP建立的连接，同时匹配SSL和非SSL连接，通过md5口令认证，使用pguser用户，连接mydb数据库，那么我们只需要在pg_hba.conf文件中增加一行

echo "host mydb pguser 0.0.0.0/0 md5" >> /pgdata/10/data/pg_hba.conf

reload生效

pg_ctl -D /pgdata/10/data reload