Managing roles and users
Greenplum® manages database access rights using two types of roles:
- User: Role that can log in to the database.
- Group: Role that includes other roles.
For more information, see Users and roles.
The admin user is created with the Managed Service for Greenplum® cluster and is automatically given the mdb_admin admin role. Connect to the database on its behalf to:
- View a list of roles.
- Create a role.
- Configure group roles.
- Change role attributes.
- Configure role privileges.
- Delete a role.
For more information about role interaction commands, see the Greenplum® documentation.
View a list of roles
Run this command:
SELECT rolname FROM pg_roles;
To see a list of roles with their privileges, run the command:
SELECT
rolname,
rolsuper,
rolcreatedb,
rolcreaterole,
rolinherit,
rolcanlogin,
rolconnlimit,
rolcreaterextgpfd
FROM pg_roles;
Result:
rolname | rolsuper | rolcreatedb | rolcreaterole | rolinherit | rolcanlogin | rolconnlimit | rolcreaterextgpfd
----------------+----------+-------------+---------------+------------+-------------+--------------+-------------------
mdb_admin | f | f | f | t | f | -1 | f
gpadmin | t | t | t | t | t | -1 | t
monitor | t | f | f | t | t | -1 | f
user1 | f | t | t | t | t | -1 | t
Where:
- t: Privilege is available.
- f: No privilege.
Create a role
Run this command:
CREATE ROLE <role_name> <list_of_attributes>;
For a list of available attributes, see Attributes.
Configure group roles
To add a role to a group role, run the command:
GRANT <group_role_name> TO <comma-separated_list_of_roles>;
You can obtain role names with a list of roles in the cluster.
The LOGIN, SUPERUSER, CREATEDB, CREATEROLE, CREATEEXTTABLE, and RESOURCE QUEUE attributes are not inherited. To use all attributes of the group role, run the following command on behalf of the role:
SET ROLE <group_role_name>;
To remove a role from a group role, run the command:
REVOKE <group_role_name> FROM <comma-separated_list_of_roles>;
Change role attributes
Run this command:
ALTER ROLE <role_name> <list_of_attributes>;
For a list of available attributes, see Attributes.
Configure role privileges
To grant privileges to a role, run the command:
GRANT <comma-separated_list_of_privileges> ON <object_name> TO <role_name>;
For a list of available privileges, see Privileges.
To revoke privileges from a role, run the command:
REVOKE <comma-separated_list_of_privileges> ON <object_name> FROM <role_name>;
Instead of listing all object privileges, use ALL PRIVILEGES.
To revoke all privileges of all role objects, run the command:
DROP OWNED BY <role_name>;
Delete a role
Before deleting a role, delete all objects it owns or reassign their ownership rights and revoke all privileges for other objects.
Run this command:
DROP ROLE <role_name>;
Examples
Creating a role
Create a role with these test characteristics:
- Name:
greenplum_user. - Attributes:
CREATEDBandCREATEROLE. - Option of logging in to the system using a secure password:
password123.
Run this command:
CREATE ROLE greenplum_user CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'password123';
Check that the new role with the specified attributes appeared in the list:
SELECT
rolname,
rolsuper,
rolcreatedb,
rolcreaterole,
rolinherit,
rolcanlogin,
rolconnlimit,
rolcreaterextgpfd
FROM pg_roles;
Greenplum® and Greenplum Database® are registered trademarks or trademarks of VMware, Inc. in the United States and/or other countries.