GreenPlum 介绍 - client认证、限制并发、SSL连接

【设置client认证】

要从远端连接GP，修改配置文件 pg_hba.conf (标准PostgreSQL host-basedauthentication文件)
虽然在master和segment都存在pg_hba.conf，但是只要修改master就可以了。client只能连接master，从来不需要直连segment。

pg_hba.conf的内容远端访问格式如下：
local             database  user auth-method  [auth-options]
host              database  user CIDR-address  auth-method [auth-options]
hostssl       database  user CIDR-address  auth-method [auth-options]
hostnossl   database user  CIDR-address auth-method  [auth-options]
host             database  user IP-address  IP-mask auth-method  [auth-options]
hostssl       database  user IP-address  IP-mask auth-method  [auth-options]
hostnossl  database user  IP-address IP-mask  auth-method [auth-options]

 

解释:
[local] - 使用unix-domain socket连接
[host] - 使用TCP/IP连接，host包含SSL和non-SSL连接
[hostssl] - 使用TCP/IP连接, 只接受SSL加密连接
[hostnossl] - 使用TCP/IP连接, 接受non-ssl连接
[database] - 数据库名称，all表示全部数据库，多个数据库用逗号分隔
[user] -数据库用户，all表示全部数据库，多个数据用逗号分隔，+表示role或group的成员，@表示来源于外部文件
[CIDR-address] - CIDR地址，如:172.20.143.89/32 ,"/"前面是ip地址，后面是子网掩码 (仅用于host, hostssl, and hostnossl)
[IP-address]/[IP-mask] - 跟CIDR-address是一样，只是2种表示方式 (仅用于 host,hostssl, and hostnossl)
[auth-method] - 包含选项有:trust/reject/md5/password/gss/sspi/krb5/ident/ldap/radius/cert/pam
  使用较多的: trust(不需要任何验证)

              reject(拒绝任何请求)

              md5(需要提供MD5加密的密码)

              password(非加密的密码)

              ident(OS用户本地连接)
(注:线下可以使用trust,线上必须把trust设置去掉)

 

修改完配置文件，需要执行: $ gpstop -u

 

官方示例如下:

# Allow any user on the local system to connect to any databasewith
# any database user name using Unix-domain sockets (the default forlocal
# connections).
#
# TYPE DATABASE       USER           CIDR-ADDRESS           METHOD
local  all            all                                    trust

# The same using local loopback TCP/IP connections.
#
# TYPE DATABASE       USER           CIDR-ADDRESS           METHOD
host   all            all            127.0.0.1/32           trust

# The same as the previous line, but using a separate netmaskcolumn
#
# TYPE DATABASE       USER           IP-ADDRESS     IP-MASK            METHOD
host   all            all            127.0.0.1      255.255.255.255    trust

# Allow any user from any host with IP address 192.168.93.x toconnect
# to database "postgres" as the same user name that ident reportsfor
# the connection (typically the operating system user name).
#
# TYPE DATABASE       USER           CIDR-ADDRESS           METHOD
host   postgres       all            192.168.93.0/24        ident

# Allow any user from host 192.168.12.10 to connect todatabase
# "postgres" if the user's password is correctly supplied.
#
# TYPE DATABASE       USER           CIDR-ADDRESS           METHOD
host   postgres       all            192.168.12.10/32       md5

# In the absence of preceding "host" lines, these two lineswill
# reject all connections from 192.168.54.1 (since that entry willbe
# matched first), but allow Kerberos 5 connections from anywhereelse
# on the Internet.  The zero mask causes no bitsof the host IP
# address to be considered, so it matches any host.
#
# TYPE DATABASE       USER           CIDR-ADDRESS           METHOD
host   all            all            192.168.54.1/32        reject
host   all            all            0.0.0.0/0              krb5

# Allow users from 192.168.x.x hosts to connect to any database,if
# they pass the ident check.  If, for example,ident says the user is
# "bryanh" and he requests to connect as PostgreSQL user "guest1",the
# connection is allowed if there is an entry in pg_ident.conf formap
# "omicron" that says "bryanh" is allowed to connect as"guest1".
#
# TYPE DATABASE       USER           CIDR-ADDRESS           METHOD
host   all            all            192.168.0.0/16         ident map=omicron

# If these are the only three lines for local connections, theywill
# allow local users to connect only to their own databases(databases
# with the same name as their database user name) except foradministrators
# and members of role "support", who can connect to alldatabases.  The file
# $PGDATA/admins contains a list of names ofadministrators.  Passwords
# are required in all cases.
#
# TYPE DATABASE       USER           CIDR-ADDRESS           METHOD
local  sameuser       all                                    md5
local  all            @admins                                md5
local  all            +support                               md5

# The last two lines above can be combined into a singleline:
local  all            @admins,+support                       md5

# The database column can also use lists and file names:
local  db1,db2,@demodbs all                                  md5

【限制并发】
postgresql.conf文件参数 ===> max_connection -最大连接数 
要变更的话，master和segment都要修改。 segment的值必须是master的5-10倍。
和max_connection相关参数： max_prepared_transactions - 最大预备事务数
master必须设置>=max_connection,segment应该设置和master一样。

例如:
在$MASTER_DATA_DIRECTORY/postgresql.conf(含standby master)中:
max_connection = 100
max_prepared_transactions = 100
在$SEGMENT_DATA_DIRECTORY/postgresql.conf中:
max_connection = 500
max_prepared_transactions = 100

修改步骤如下：
1. 停库 $ gpstop
2. 修改master参数
3. 修改每个segment参数
4. 重启 $ gpstart

 

【加密的C/S连接】
GP支持SSL连接。
条件如下：
1.client和master server需要安装openSSL
2.设置master中postgresql.conf参数： ssl=on

当ssl模式开启时候，将会搜索master的数据目录包含的2个文件: server.key(server privatekey)和server.crt (server certificate)
如何生成这2个文件，参看openSSL document