PostgreSQL 遠端連結 Sever端配置

PostgreSQL Sever需要配置才可被使用者遠端連結

配置檔案：

pg_hba.conf 控制通路安全，管理用戶端通路PostgreSQL server的通路權限。

postgresql.conf 資料庫參數檔案，配置資料庫相關參數。

pg_ident.conf 用戶端通路server通過ident模式，會使用pg_ident.conf檔案，模拟作業系統使用者通路。

PG檢視配置檔案的位置

postgres=# select name, setting from pg_settings where category = 'File Locations';
       name        |             setting
-------------------+---------------------------------
 config_file       | /opt/pgsql/data/postgresql.conf
 data_directory    | /opt/pgsql/data
 external_pid_file |
 hba_file          | /opt/pgsql/data/pg_hba.conf
 ident_file        | /opt/pgsql/data/pg_ident.conf
(5 rows)
           

配置server可遠端通路

1.配置pg_hba.conf

# "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             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 trust
           

md5使用密碼登陸

2.配置監聽postgresql.conf

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

3.為初始使用者設定密碼

alter user postgres with password 'ikdhfel';           

4.配置檔案都配置好後重新開機pg server測試遠端連結

[postgres@postgresql data]$ pg_ctl restart -D /opt/pgsql/data
waiting for server to shut down.... done
server stopped
waiting for server to start....2018-12-10 16:40:29.828 CST [636] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-12-10 16:40:29.828 CST [636] LOG:  listening on IPv6 address "::", port 5432
2018-12-10 16:40:29.833 CST [636] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-12-10 16:40:29.857 CST [637] LOG:  database system was shut down at 2018-12-10 16:40:29 CST
2018-12-10 16:40:29.861 CST [636] LOG:  database system is ready to accept connections
 done
server started