Managing Roles and Privileges

Managing Roles and Privileges

The Greenplum Database authorization mechanism stores roles and permissions to access database objects in the database and is administered using SQL statements or command-line utilities.



In Greenplum Database, 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 Greenplum Database 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 Greenplum Database system. Customarily, this role is named gpadmin. In order to create more roles you first have to connect as this initial role.

Security Best Practices for Roles and Privileges

  • Secure the gpadmin system user. Greenplum requires a UNIX user id to install and initialize the Greenplum Database system. This system user is referred to as gpadmin in the Greenplum documentation. This gpadmin user is the default database superuser in Greenplum Database, as well as the file system owner of the Greenplum installation and its underlying data files. This default administrator account is fundamental to the design of Greenplum Database. 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 Greenplum host as this user id can read, alter or delete any data; including 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 Greenplum 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 Greenplum Database 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 Role Membership.

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.

Altering Role Attributes


Table 1. Role Attributes
Attributes Description
SUPERUSER | NOSUPERUSER Determines if the role is a superuser. You must yourself be a superuser to create a new superuser. NOSUPERUSER is the default.
CREATEDB | NOCREATEDB Determines if the role is allowed to create databases. NOCREATEDB is the default.
CREATEROLE | NOCREATEROLE Determines if the role is allowed to create and manage other roles. NOCREATEROLE is the default.
INHERIT | NOINHERIT Determines whether a role inherits the privileges of roles it is a member of. A role with the INHERIT attribute can automatically use whatever database privileges have been granted to all roles it is directly or indirectly a member of. INHERIT is the default.
LOGIN | NOLOGIN Determines whether a role is allowed to log in. A role having the LOGIN attribute can be thought of as a user. Roles without this attribute are useful for managing database privileges (groups). NOLOGIN is the default.
CREATEEXTTABLE | NOCREATEEXTTABLE Determines whether a role is allowed to create external tables. NOCREATEEXTTABLE is the default. For a role with the CREATEEXTTABLE attribute, the default external table type is readable and the default protocol is gpfdist. Note that external tables that use the file or execute protocols can only be created by superusers.
ENCRYPTED | UNENCRYPTED Controls whether a new password is stored as a hash string in the pg_authid system catalog. If neither ENCRYPTED nor UNENCRYPTED is specified, the default behavior is determined by the password_encryption configuration parameter, which is on by default.

If the supplied password string is already in hashed format, it is stored as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified.

See Protecting Passwords in Greenplum Database for additional information about protecting login passwords.

VALID UNTIL 'timestamp' Sets a date and time after which the role's password is no longer valid. If omitted the password will be valid for all time.
RESOURCE QUEUE queue_name Assigns the role to the named resource queue for workload management. Any statement that role issues is then subject to the resource queue's limits. Note that the RESOURCE QUEUE attribute is not inherited; it must be set on each user-level (LOGIN) role.
DENY {deny_interval | deny_point} Restricts access during an interval, specified by day or day and time. For more information see Time-based Authentication.

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 DENY DAY 'Sunday';

A role can also have role-specific defaults for many of the server configuration settings. For example, to set the default schema search path for a role:

=# ALTER ROLE admin SET search_path TO myschema, public;

Role Membership

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 Greenplum Database 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:


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

=# GRANT admin TO john, sally;
=# REVOKE admin FROM bob;

For managing object privileges, you would then grant the appropriate permissions to the group-level role only (see Table 2). 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;

The role attributes LOGIN, SUPERUSER, CREATEDB, CREATEROLE, CREATEEXTTABLE, and RESOURCE QUEUE 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 CREATEDB 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


Table 2. Object Privileges
Object Type Privileges
Tables, Views, Sequences SELECT






External Tables SELECT



Databases CONNECT




Functions EXECUTE
Procedural Languages USAGE
Schemas CREATE



Custom Protocol SELECT







Use the GRANT SQL command to give a specified role privileges on an object. For example, to grant the role named jsmith insert privileges on the table named mytable:

=# GRANT INSERT ON mytable TO jsmith;
Similarly, to grant jsmith select privileges only to the column named col1 in the table named table2:
=# GRANT SELECT (col1) on TABLE table2 TO jsmith;

To revoke privileges, use the REVOKE command. For example:


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:

=# REASSIGN OWNED BY sally TO bob;
=# DROP OWNED BY visitor;

Simulating Row Level Access Control

Greenplum Database does not support row-level access or row-level, labeled security. You can simulate row-level access by using views to restrict the rows that are selected. You can simulate row-level labels 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. You can then grant roles access to the views rather than to the base table.

Encrypting Data

Greenplum Database is installed with an optional module of encryption/decryption functions called pgcrypto. 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 Greenplum Database in encrypted form cannot be read by anyone who does not have the encryption key, nor can it 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 Greenplum master server.
To use pgcrypto functions, register the pgcrypto extension in each database in which you want to use the functions. For example:
$ psql -d testdb -c "CREATE EXTENSION pgcrypto"

See pgcrypto in the PostgreSQL documentation for more information about individual functions.

Protecting Passwords in Greenplum Database

In its default configuration, Greenplum Database saves MD5 hashes of login users' passwords in the pg_authid system catalog rather than saving clear text passwords. Anyone who is able to view the pg_authid table can see hash strings, but no passwords. This also ensures that passwords are obscured when the database is dumped to backup files.

The hash function executes when the password is set by using any of the following commands:

The ENCRYPTED keyword may be omitted when the password_encryption system configuration parameter is on, which is the default value. The password_encryption configuration parameter determines whether clear text or hashed passwords are saved when the ENCRYPTED or UNENCRYPTED keyword is not present in the command.

Note: The SQL command syntax and password_encryption configuration variable include the term encrypt, but the passwords are not technically encrypted. They are hashed and therefore cannot be decrypted.

The hash is calculated on the concatenated clear text password and role name. The MD5 hash produces a 32-byte hexadecimal string prefixed with the characters md5. The hashed password is saved in the rolpassword column of the pg_authid system table.

Although it is not recommended, passwords may be saved in clear text in the database by including the UNENCRYPTED keyword in the command or by setting the password_encryption configuration variable to off. Note that changing the configuration value has no effect on existing passwords, only newly created or updated passwords.

To set password_encryption globally, execute these commands in a shell as the gpadmin user:
$ gpconfig -c password_encryption -v 'off'
$ gpstop -u
To set password_encryption in a session, use the SQL SET command:
=# SET password_encryption = 'on';

Passwords may be hashed using the SHA-256 hash algorithm instead of the default MD5 hash algorithm. The algorithm produces a 64-byte hexadecimal string prefixed with the characters sha256.


Although SHA-256 uses a stronger cryptographic algorithm and produces a longer hash string, it cannot be used with the MD5 authentication method. To use SHA-256 password hashing the authentication method must be set to password in the pg_hba.conf configuration file so that clear text passwords are sent to Greenplum Database. Because clear text passwords are sent over the network, it is very important to use SSL for client connections when you use SHA-256. The default md5 authentication method, on the other hand, hashes the password twice before sending it to Greenplum Database, once on the password and role name and then again with a salt value shared between the client and server, so the clear text password is never sent on the network.

To enable SHA-256 hashing, change the password_hash_algorithm configuration parameter from its default value, md5, to sha-256. The parameter can be set either globally or at the session level. To set password_hash_algorithm globally, execute these commands in a shell as the gpadmin user:
$ gpconfig -c password_hash_algorithm -v 'sha-256'
$ gpstop -u
To set password_hash_algorithm in a session, use the SQL SET command:
=# SET password_hash_algorithm = 'sha-256';

Time-based Authentication


For details, refer to the Greenplum Database Security Configuration Guide.