Security is always divided into two main actions of Authentication and Authorization. Authentication is proving you are who you say you are by usually providing a username and a password. Authorization is the process of approving or denying access to objects such as SELECT on a table. Greenplum always handles Authorization internally. You can’t delegate this to a third party but you can with Authentication.
Authentication is handled in Greenplum just like it is in PostgreSQL. Edit your pg_hba.conf file on the MASTER and provide information about how users should be authenticated. Typically, companies are using an LDAP solution for security and this is also usually the hub in a hub and spoke security model. Furthermore, Microsoft Active Directory is usually the LDAP solution picked because so many desktops are using Windows.
The security hub has a company’s password rules like failed attempts, password complexity, password expiration, etc. and you can leverage this very easily with Greenplum’s authentication model. Simply edit your pg_hba.conf file and at the very bottom of the file (it is read sequentially), add this:
host all all 0.0.0.0/0 ldap ldapserver=<ldap server> ldapprefix=”<domain>\” ldapsuffix=””
- LDAP server = ldap1
- Windows Domain = myco
So your entry at the bottom of the pg_hba.conf file would look like this:
host all all 0.0.0.0/0 ldap ldapserver=ldap1 ldapprefix=”myco\” ldapsuffix=””
During a login attempt, the pg_hba.conf file is read and the first matching criteria is used. So if you want to use basic password authentication for an ETL user or trust for local users, simply put these entries before the LDAP entry. Then the last line is the LDAP entry that is the “catch-all” which authenticates users to your Active Directory.
How does it work? It takes the username and password passed in and attempts to connect to the LDAP server with the ldapprefix (domain) specified. If the user can connect to the LDAP server, then the user is authenticated and then connects to Greenplum. It is that simple. Once in the database, Greenplum handles Authorization as normal.