Using HAWQ Native Authorization

HAWQ manages database access permissions using roles. The concept of roles subsumes the concepts of users and groups. A role can be a database user, a group, or both. Roles can own database objects (for example, tables) and can assign privileges on those objects to other roles to control access to the objects. Roles can be members of other roles, thus a member role can inherit the object privileges of its parent role.

Every HAWQ system contains a set of database roles (users and groups). Those roles are separate from the users and groups managed by the operating system on which the server runs. However, for convenience you may want to maintain a relationship between operating system user names and HAWQ role names, since many of the client applications use the current operating system user name as the default.

In HAWQ, users log in and connect through the master instance, which then verifies their role and access privileges. The master then issues commands to the segment instances behind the scenes as the currently logged in role.

Roles are defined at the system level, meaning they are valid for all databases in the system.

In order to bootstrap the HAWQ system, a freshly initialized system always contains one predefined superuser role (also referred to as the system user). This role will have the same name as the operating system user that initialized the HAWQ system. Customarily, this role is named . In order to create more roles you first have to connect as this initial role.

Secure the gpadmin system user. HAWQ requires a UNIX user id to install and initialize the HAWQ system. This system user is referred to as gpadmin in the HAWQ documentation. This gpadmin user is the default database superuser in HAWQ, as well as the file system owner of the HAWQ installation and its underlying data files. This default administrator account is fundamental to the design of HAWQ. The system cannot run without it, and there is no way to limit the access of this gpadmin user id. Use roles to manage who has access to the database for specific purposes. You should only use the gpadmin account for system maintenance tasks such as expansion and upgrade. Anyone who logs on to a HAWQ host as this user id can read, alter or delete any data; specifically system catalog data and database access rights. Therefore, it is very important to secure the gpadmin user id and only provide access to essential system administrators. Administrators should only log in to HAWQ as gpadmin when performing certain system maintenance tasks (such as upgrade or expansion). Database users should never log on as gpadmin, and ETL or production workloads should never run as gpadmin.
Assign a distinct role to each user that logs in. For logging and auditing purposes, each user that is allowed to log in to HAWQ should be given their own database role. For applications or web services, consider creating a distinct role for each application or service. See Creating New Roles (Users).
Use groups to manage access privileges. See .
Limit users who have the SUPERUSER role attribute. Roles that are superusers bypass all access privilege checks in HAWQ, as well as resource queuing. Only system administrators should be given superuser rights. See Altering Role Attributes.

Creating New Roles (Users)

A user-level role is considered to be a database role that can log in to the database and initiate a database session. Therefore, when you create a new user-level role using the CREATE ROLE command, you must specify the LOGIN privilege. For example:

A database role may have a number of attributes that define what sort of tasks that role can perform in the database. You can set these attributes when you create the role, or later using the ALTER ROLE command. See Table 1 for a description of the role attributes you can set.

A database role may have a number of attributes that define what sort of tasks that role can perform in the database.

You can set these attributes when you create the role, or later using the ALTER ROLE command. For example:

=# ALTER ROLE jsmith WITH PASSWORD 'passwd123';
=# ALTER ROLE admin VALID UNTIL 'infinity';
=# ALTER ROLE jsmith LOGIN;
=# ALTER ROLE jsmith RESOURCE QUEUE adhoc;
=# ALTER ROLE jsmith DENY DAY 'Sunday';

It is frequently convenient to group users together to ease management of object privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In HAWQ this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.

Use the CREATE ROLE SQL command to create a new group role. For example:

=# CREATE ROLE admin CREATEROLE CREATEDB;

Once the group role exists, you can add and remove members (user roles) using the GRANT and REVOKE commands. For example:

=# REVOKE admin FROM bob;

For managing object privileges, you would then grant the appropriate permissions to the group-level role only (see ). The member user roles then inherit the object privileges of the group role. For example:

=# GRANT ALL ON TABLE mytable TO admin;
=# GRANT ALL ON SCHEMA myschema TO admin;
=# GRANT ALL ON DATABASE mydb TO admin;

The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE are never inherited as ordinary privileges on database objects are. User members must actually SET ROLE to a specific role having one of these attributes in order to make use of the attribute. In the above example, we gave and CREATEROLE to the admin role. If sally is a member of admin, she could issue the following command to assume the role attributes of the parent role:

=> SET ROLE admin;

Managing Object Privileges

When an object (table, view, sequence, database, function, language, schema, or tablespace) is created, it is assigned an owner. The owner is normally the role that executed the creation statement. For most kinds of objects, the initial state is that only the owner (or a superuser) can do anything with the object. To allow other roles to use it, privileges must be granted. HAWQ supports the following privileges for each object type:

Object Type	Privileges
Tables, Views, Sequences	SELECT INSERT RULE ALL
External Tables	SELECT RULE ALL
Databases	CONNECT CREATE TEMPORARY \| TEMP ALL
Functions	EXECUTE
Procedural Languages	USAGE
Schemas	CREATE USAGE ALL
Custom Protocol	SELECT INSERT RULE ALL

Note: Privileges must be granted for each object individually. For example, granting ALL on a database does not grant full access to the objects within that database. It only grants all of the database-level privileges (CONNECT, CREATE, TEMPORARY) to the database itself.

Use the GRANT SQL command to give a specified role privileges on an object. For example:

=# GRANT INSERT ON mytable TO jsmith;

=# REVOKE ALL PRIVILEGES ON mytable FROM jsmith;

You can also use the DROP OWNED and REASSIGN OWNED commands for managing objects owned by deprecated roles (Note: only an object’s owner or a superuser can drop an object or reassign ownership). For example:

Row-level or column-level access is not supported, nor is labeled security. Row-level and column-level access can be simulated using views to restrict the columns and/or rows that are selected. Row-level labels can be simulated by adding an extra column to the table to store sensitivity information, and then using views to control row-level access based on this column. Roles can then be granted access to the views rather than the base table.

PostgreSQL provides an optional package of encryption/decryption functions called pgcrypto, which you can enable in HAWQ.

If you are building HAWQ from source, then you should enable pgcrypto support as an option when compiling HAWQ.

The pgcrypto functions allow database administrators to store certain columns of data in encrypted form. This adds an extra layer of protection for sensitive data, as data stored in HAWQ in encrypted form cannot be read by users who do not have the encryption key, nor be read directly from the disks.

Note: The pgcrypto functions run inside the database server, which means that all the data and passwords move between pgcrypto and the client application in clear-text. For optimal security, consider also using SSL connections between the client and the HAWQ master server.

Encrypting Passwords

This technical note outlines how to use a server parameter to implement SHA-256 encrypted password storage. Note that in order to use SHA-256 encryption for storage, the client authentication method must be set to password rather than the default, MD5. (See Encrypting Client/Server Connections for more details.) This means that the password is transmitted in clear text over the network; to avoid this, set up SSL to encrypt the client server communication channel.

You can set your chosen encryption method system-wide or on a per-session basis. There are three encryption methods available: SHA-256, SHA-256-FIPS, and MD5 (for backward compatibility). The SHA-256-FIPS method requires that FIPS compliant libraries are used.

System-wide

You will perform different procedures to set the encryption method (password_hash_algorithm server parameter) system-wide depending upon whether you manage your cluster from the command line or use Ambari. If you use Ambari to manage your HAWQ cluster, you must ensure that you update encryption method configuration parameters only via the Ambari Web UI. If you manage your HAWQ cluster from the command line, you will use the hawq config command line utility to set encryption method configuration parameters.

If you use Ambari to manage your HAWQ cluster:

Set the password_hash_algorithm configuration property via the HAWQ service Configs > Advanced > Custom hawq-site drop down. Valid values include SHA-256 (or SHA-256-FIPS to use the FIPS-compliant libraries for SHA-256).
Select Service Actions > Restart All to load the updated configuration.

If you manage your HAWQ cluster from the command line:

Log in to the HAWQ master host as a HAWQ administrator and source the file /usr/local/hawq/greenplum_path.sh.
```
$ source /usr/local/hawq/greenplum_path.sh
```

Use the hawq config utility to set to SHA-256 (or SHA-256-FIPS to use the FIPS-compliant libraries for SHA-256):

$ hawq config -c password_hash_algorithm -v 'SHA-256'

Or:

$ hawq config -c password_hash_algorithm -v 'SHA-256-FIPS'

Reload the HAWQ configuration:
```
$ hawq stop cluster -u
```

Verify the setting:

$ hawq config -s password_hash_algorithm

Individual Session

To set the password_hash_algorithm server parameter for an individual database session:

=# SET password_hash_algorithm = 'SHA-256'
SET

or:

SET

Verify the setting:

You will see:
```
SHA-256
```
or:
```
SHA-256-FIPS
```
Example

Following is an example of how the new setting works:

=# SHOW password_hash_algorithm
password_hash_algorithm
-------------------------------
SHA-256-FIPS

Create a new role with password that has login privileges.

=# CREATE ROLE testdb WITH PASSWORD 'testdb12345#' LOGIN;

Change the client authentication method to allow for storage of SHA-256 encrypted passwords:

Open the pg_hba.conf file on the master and add the following line:
```
host all testdb 0.0.0.0/0 password
```
Restart the cluster.
Login to the database as user just created testdb.
```
$ psql -U testdb
```
Enter the correct password at the prompt.
Verify that the password is stored as a SHA-256 hash.

Note that password hashes are stored in pg_authid.rolpasswod
1. Login as the super user.
2. Execute the following:
```
=# SELECT rolpassword FROM pg_authid WHERE rolname = 'testdb';
Rolpassword
sha256<64 hexidecimal characters>
```

HAWQ enables the administrator to restrict access to certain times by role. Use the or ALTER ROLE commands to specify time-based constraints.