Allowing Connections to Greenplum Database

posted Apr 28, 2017, 5:04 PM by Sachchida Ojha
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.

==================================================================================================================
Connecting Greenplum from Oracle using DBLink

All steps are on the gplinuxsachi server.

1. Instal postgres ODBC driver on gplinuxsachi
Remainder of steps assume that the install directory is /usr/lib64/psqlodbc.so

2. Locate odbc.ini file and add the following entry. Change value of password as needed.

[IPMGP]
Debug = 1
CommLog = 1
ReadOnly = yes
Driver = PostgreSQL
Servername = gplinuxsachi
FetchBufferSize = 99
Username = himanshu
Password = <password>
Port = 1587
Database = sachi

2a. Confirm contents of odbcinst.ini file. Ensure driver name matches ODBC entry.

[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/psqlodbc.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/lib64/psqlodbc.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1

3. Create the file $ORACLE_HOME/hs/admin/initIPMGP.ora with the following contents. Ensure that the path /usr/lib64/libodbc.so and odbc.ini is valid.
HS_FDS_CONNECT_INFO = IPMGP
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_LANGUAGE=american_america.we8mswin1252
HS_NLS_NCHAR=UCS2
set ODBCINI=/etc/odbc.ini

4. Add the following entry to $ORACLE_HOME/network/admin/tnsnames.ora
change host and port as needed.

IPMGP =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
(CONNECT_DATA=(SID=SACHIGP))
(HS=OK)
)


4a. Modify $ORACLE_HOME/network/admin/listener.ora
add to existing SID_LIST_<> entry if it already exists.
Change ORACLE_HOME= directory as needed.

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=SACHIGP)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe)
(PROGRAM=dg4odbc)
)
)

5. reload listener
lsnrctl reload LISTENER

6. Run the following SQL in the SACHI_DEV database as a user with create database link privalege. Get password for "himanshu" user on Greenplum.
create database link ora2gplink connect to "himanshu" identified by "<password>" using 'SACHIGP';
Comments