Users and roles in Managed Service for Greenplum®

Written by

Updated at September 9, 2024

Attributes
Group roles
Privileges
The mdb_admin role instead of a superuser

Greenplum® manages database access rights using roles. Roles can own database objects, such as tables, and have attributes and privileges. You can assign privileges to other roles on behalf of a particular role.

In Greenplum®, a user is a role that can log in to the database. To do this, it is granted the LOGIN attribute.

An admin user with the mdb_admin role has the maximum privileges among all users.

Attributes

The role has attributes that define which jobs it can run in the database.

Attributes	Description
`SUPERUSER` or `NOSUPERUSER`	Defines whether the role is a superuser. In Managed Service for Greenplum®, the `SUPERUSER` attribute is assigned to the `gpadmin` and `monitor` service roles and is not available to service users.
`CREATEDB` or `NOCREATEDB`	Determines whether a database may be created. The default attribute is `NOCREATEDB`.
`CREATEROLE` or `NOCREATEROLE`	Determines whether other roles may be created and managed. The default attribute is `NOCREATEROLE`.
`INHERIT` or `NOINHERIT`	Determines whether the role inherits the privileges of the roles it is a part of. The default attribute is `INHERIT`.
`LOGIN` or `NOLOGIN`	Determines whether the role may log in to the system, i.e., whether it is a user. The default attribute is `NOLOGIN`.
`CONNECTION LIMIT <value>`	Number of concurrent connections for the role with the `LOGIN` attribute. The default value is `-1` (unlimited).
`CREATEEXTTABLE` or `NOCREATEEXTTABLE`	Determines whether external tables may be created. The default attribute is `NOCREATEEXTTABLE`.
`PASSWORD '<password>'`	Setting a password for the role. If no authentication is required for the role, you can skip this attribute.
`ENCRYPTED` or `UNENCRYPTED`	Save the password as a hash string or plain text. The default attribute is `ENCRYPTED`. For more information about protecting authorization passwords, see the Greenplum® documentation.

Group roles

Some roles can become a part of other roles and inherit their privileges. When privileges of the parent role are changed, privileges of all roles within it are changed as well. For more information about group roles, see the Greenplum® documentation.

Privileges

Privileges determine what a role can do with database objects.

Do not use the mdb_admin role for routine tasks, because an incorrect command sent on its behalf may cause the cluster to fail. For these tasks, create separate roles with the minimum required privileges:

Object type	Privileges
Tables, external tables, view	`SELECT` `INSERT` `UPDATE` `DELETE` `REFERENCES` `TRIGGER` `TRUNCATE` `ALL`
Columns	`SELECT` `INSERT` `UPDATE` `REFERENCES` `ALL`
Sequences	`USAGE` `SELECT` `UPDATE` `ALL`
Databases	`CREATE` `CONNECT` `TEMPORARY` `TEMP` `ALL`
Domains	`USAGE` `ALL`
External data shells	`USAGE` `ALL`
External servers	`USAGE` `ALL`
Functions	`EXECUTE` `ALL`
Procedural languages	`USAGE` `ALL`
Schemas	`CREATE` `USAGE` `ALL`
Tablespaces	`CREATE` `ALL`
Types	`USAGE` `ALL`
Protocols	`SELECT` `INSERT` `ALL`

For more information about privileges and how to manage them, see the Greenplum® documentation.

The mdb_admin role instead of a superuser

In a Managed Service for Greenplum® cluster, superuser permissions are not granted. Instead, you can work with databases via an admin user with the mdb_admin role. Such user is created along with the cluster and allows you the following operations:

Managing roles and users.
Managing resource groups.
Managing client resources and user sessions (a user with the mdb_admin role can access mdb_toolkit objects).
Accessing the pg_stat_activity view and getting information from it.
Managing database objects of other users, such as tables. A user with the mdb_admin role owns objects of all users.
Using the connection slot reserved for mdb_admin.

If too many connections to a database are established, one more connection may cause an error. A user with the mdb_admin role avoids that situation, because it has a reserved connection slot.
Creating a database.
Working with the PXF protocol:
- Creating external data sources.
- Creating external tables.
- Accessing user credentials. This data is transmitted over PXF in an open format.
Expanding the cluster: Adding segment hosts to it and redistributing data between them via the gp_expand utility.
Connecting to the gpperfmon database and diagnose cluster performance.

The admin's username and password are created along with the cluster.

If you want to grant the admin user privileges to another user, assign the mdb_admin role to that user:

GRANT mdb_admin TO <username>;

Greenplum® and Greenplum Database® are registered trademarks or trademarks of VMware, Inc. in the United States and/or other countries.

Users and roles in Managed Service for Greenplum®

AttributesAttributes

Group rolesGroup roles

PrivilegesPrivileges

The mdb_admin role instead of a superuserThe mdb_admin role instead of a superuser

Was the article helpful?

Attributes

Group roles

Privileges

The mdb_admin role instead of a superuser