Allowing Connections to Greenplum Database

Greenplum database client access and authentication is controlled by a configuration file named pg_hba.conf.

In Greenplum Database, the pg_hba.conf file of the master instance controls client access and authentication to your Greenplum system. The segments also have pg_hba.conf files, but these are already correctly configured to only allow client connections from the master host. The segments never accept outside client connections, so there is no need to alter the pg_hba.conf file on your segments.

The general format of the pg_hba.conf file is a set of records, one per line. Blank lines are ignored, as is any text after the # comment character. A record is made up of a number of fields which are separated by spaces and/or tabs. Fields can contain white space if the field value is quoted. Records cannot be continued across lines. Each remote client access record is in the format of:

host database role CIDR-address authentication-method

Each UNIX-domain socket access record is in the format of:

local database role authentication-method

The meaning of the fields is as follows:

local

Matches connection attempts using UNIX-domain sockets. Without a record of this type, UNIX-domain socket connections are disallowed.

host

Matches connection attempts made using TCP/IP. Remote TCP/IP connections will not be possible unless the server is started with an appropriate value for the listen_addresses server configuration parameter.

hostssl

Matches connection attempts made using TCP/IP, but only when the connection is made with SSL encryption. SSL must be enabled at server start time by setting the ssl configuration parameter

hostnossl

Matches connection attempts made over TCP/IP that do not use SSL.

database

Specifies which database names this record matches. The value all specifies that it matches all databases. Multiple database names can be supplied by separating them with commas. A separate file containing database names can be specified by preceding the file name with @.

role

Specifies which database role names this record matches. The value all specifies that it matches all roles. If the specified role is a group and you want all members of that group to be included, precede the role name with a +. Multiple role names can be supplied by separating them with commas. A separate file containing role names can be specified by preceding the file name with @.

CIDR-address:

Specifies the client machine IP address range that this record matches. It contains an IP address in standard dotted decimal notation and a CIDR mask length. IP addresses can only be specified numerically, not as domain or host names. The mask length indicates the number of high-order bits of the client IP address that must match. Bits to the right of this must be zero in the given IP address. There must not be any white space between the IP address, the /, and the

CIDR mask length. Typical examples of a CIDR-address are 172.20.143.89/32 for a single host, or 172.20.143.0/24 for a small network, or 10.6.0.0/16 for a larger one. To specify a single host, use a CIDR mask of 32 for IPv4 or 128 for IPv6. In a network address, do not omit trailing zeroes.

IP-address

IP-mask

These fields can be used as an alternative to the CIDR-address notation. Instead of specifying the mask length, the actual mask is specified in a separate column. For example, 255.0.0.0 represents an IPv4 CIDR mask length of 8, and 255.255.255.255 represents a CIDR mask length of 32. These fields only apply to host, hostssl, and hostnossl records.

authentication-method

Specifies the authentication method to use when connecting.

Editing the pg_hba.conf File

This example shows how to edit the pg_hba.conf file of the master to allow remote client access to all databases from all roles using md5-encrypted password authentication.

Note: For a more secure system, consider removing all connections that use trust authentication from your master pg_hba.conf. Trust authentication means the role is granted access without any authentication, therefore bypassing all security. Replace trust entries with ident authentication if your system has an ident service available.

Editing pg_hba.conf

1.Open the file $MASTER_DATA_DIRECTORY/pg_hba.conf in a text editor.

2.Add a line to the file for each type of connection you want to allow. Records are read sequentially, so the order of the records is significant. Typically, earlier records will have tight connection match parameters and weaker authentication methods, while later records will have looser match parameters and stronger authentication methods.

For example:

Allow the gpadmin user local access to all databases using ident authentication

local all gpadmin ident sameuser

host all gpadmin 127.0.0.1/32 ident

host all gpadmin ::1/128 ident

Allow the 'dba' role access to any database from any host with IP address 192.168.x.x and use md5 encrypted passwords to authenticate the user

host all dba 192.168.0.0/32 md5

Allow all roles access to any database from any host and use ldap to authenticate the user. Greenplum role names must match the LDAP common name.

host all all 192.168.0.0/32 ldap ldapserver=usldap1 ldapport=1389 ldapprefix="cn=" ldapsuffix=",ou=People,dc=company,dc=com"

3.Save and close the file.

4.Reload the pg_hba.conf configuration file for your changes to take effect:

$ gpstop -u

Note: Note that you can also control database access by setting object privileges as described in “Managing Object Privileges” on page 33. The pg_hba.conf file just controls who can initiate a database session and how those connections are authenticated.