Manage Roles and Privileges in Apache Cloudberry
The Apache Cloudberry authorization mechanism stores roles and privileges to access database objects in the database and is administered using SQL statements or command-line utilities.
Apache Cloudberry manages database access privileges 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 Apache Cloudberry 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 Apache Cloudberry role names, since many of the client applications use the current operating system user name as the default.
In Apache Cloudberry, users log in and connect through the coordinator instance, which then verifies their role and access privileges. The coordinator 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 Apache Cloudberry 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 Apache Cloudberry 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. Apache Cloudberry requires a UNIX user ID to install and initialize the Apache Cloudberry system. This system user is referred to as
gpadmin
in the Apache Cloudberry documentation. Thisgpadmin
user is the default database superuser in Apache Cloudberry, as well as the file system owner of the Apache Cloudberry installation and its underlying data files. This default administrator account is fundamental to the design of Apache Cloudberry. 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 thegpadmin
account for system maintenance tasks such as expansion and upgrade. Anyone who logs on to a Apache Cloudberry 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 Apache Cloudberry asgpadmin
when performing certain system maintenance tasks (such as upgrade or expansion). Database users should never log on asgpadmin
, and ETL or production workloads should never run asgpadmin
. - Assign a distinct role to each user that logs in. For logging and auditing purposes, each user that is allowed to log in to Apache Cloudberry should be given their own database role. For applications or web services, consider creating a distinct role for each application or service. See Create New Roles (Users).
- Use groups to manage access privileges. See Role membership.
- Limit users who have the SUPERUSER role attribute. Roles that are superusers bypass all access privilege checks in Apache Cloudberry, as well as resource queuing. Only system administrators should be given superuser rights. See Altering Role Attributes.
Create 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:
=# CREATE ROLE jsmith WITH LOGIN;