Roles and privileges

Andrey Aksenov

Greengage DB enables flexible management of access to various database resources using SQL statements and command-line utilities. The main concepts of a Greengage DB access control system are the following:

A role is a user interacting with a Greengage DB cluster or a group of privileges that can be granted to a user.
An object is an entity to which access can be granted, for example, a database, a table, or a function.
A privilege allows a user to perform certain operations on specific objects, such as creating tables or reading or updating data.

To follow the examples in this topic, create a database, then create two tables, and populate them with the data as shown below:

script.sql

CREATE DATABASE crm;
\c crm

CREATE SCHEMA sales;

CREATE TABLE sales.orders
(
    order_id     SERIAL,
    customer_id  INTEGER,
    order_date   DATE,
    total_amount DECIMAL(6, 2),
    status       VARCHAR(15)
)
    WITH (appendoptimized = true, orientation = row)
    DISTRIBUTED BY (order_id);

CREATE TABLE sales.customers
(
    customer_id   SERIAL PRIMARY KEY,
    first_name    VARCHAR(25),
    last_name     VARCHAR(25),
    email         VARCHAR(25),
    customer_type VARCHAR(15)
)
    DISTRIBUTED REPLICATED;

INSERT INTO sales.customers (first_name, last_name, email, customer_type)
VALUES ('Andrew', 'Fuller', 'andrew@example.com', 'Regular'),
       ('Michael', 'Suyama', 'michael@testmail.com', 'VIP'),
       ('Robert', 'King', 'robert@demo.org', 'Business');

INSERT INTO sales.orders (customer_id, order_date, total_amount, status)
VALUES (1, '2025-02-01', 120.50, 'Pending'),
       (2, '2025-02-02', 89.99, 'Completed'),
       (3, '2025-02-03', 45.00, 'Shipped'),
       (1, '2025-02-04', 150.75, 'Pending'),
       (2, '2025-02-05', 200.00, 'Completed'),
       (3, '2025-02-06', 99.50, 'Cancelled'),
       (1, '2025-02-07', 75.80, 'Shipped'),
       (2, '2025-02-08', 250.00, 'Completed');

Greengage DB manages database access permissions using roles. The concept of roles encompasses the concepts of both users and groups:

A user-level role identifies a person or program interacting with a Greengage DB cluster. A user can log in to the database, initiate a database session, and execute commands allowed by privileges granted to this user.
A group-level role is a container for privileges that can be granted to user-level roles or other group-level roles.

A newly initialized Greengage DBMS contains one predefined superuser role. This role has the same name as the operating system user that initialized DBMS. Typically, this role is named gpadmin. To create other roles, you first need to connect as gpadmin.

Roles are defined at the Greengage DB cluster level. This means they are valid across all databases within the cluster.

NOTE

To allow the specified roles to establish remote connections, you need to edit the pg_hba.conf file accordingly.

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

LOGIN or NOLOGIN — specifies whether a role is allowed to log in. A role with the LOGIN attribute can be thought of as a user.
SUPERUSER or NOSUPERUSER — specifies if a role is a superuser.
PASSWORD <password> — specifies a role’s password.

The full list of role attributes is available in the Role attributes section.

An object is a securable entity to which access can be granted, for example:

schemas;
tables;
columns;
functions.

Different object types provide different sets of privileges that can be granted to them.

Privileges allow a user to perform certain operations on specific object types, for example:

The SELECT and UPDATE privileges granted on a table object allow a role to read or update data in this table.
The USAGE privilege granted on a schema object allows a role to access objects within this schema.
The CREATEROLE privilege allows a role to create and manage other roles.

The complete list of object privileges is available in the Object privileges section.

A role can own database objects, such as tables or views. The owner of all objects created initially is the gpadmin user.

An owner of new database objects is the user who created it. Owners automatically have privileges for objects they create. They can share these privileges with other roles using the GRANT command. If necessary, ownership of objects can be changed.

To create a new user, use the CREATE ROLE command:

CREATE ROLE alice WITH LOGIN;

The LOGIN attribute distinguishes user-level roles from group-level ones.

To change a user’s attribute, use the ALTER ROLE command. For example, to set the password of the existing user, use the PASSWORD attribute:

ALTER ROLE alice WITH PASSWORD '123456';

A user can have user-specific defaults for many of the server configuration settings. For example, you can set the default schema search path as follows:

ALTER ROLE alice SET search_path TO sales;

Once the user is created, you can grant them the necessary privileges using the GRANT command:

GRANT USAGE ON SCHEMA sales TO alice;
GRANT SELECT ON TABLE sales.orders TO alice;
GRANT SELECT (customer_type) ON TABLE sales.customers TO alice;

In this example, the alice user gets the following privileges:

Access objects within the sales schema.
Select any data from the orders table.
Select data only from the customer_type column in the customers table.

To revoke the user’s privileges, use the REVOKE command:

REVOKE USAGE ON SCHEMA sales FROM alice;
REVOKE SELECT ON TABLE sales.orders FROM alice;
REVOKE SELECT (customer_type) ON TABLE sales.customers FROM alice;

To drop the user, use DROP ROLE:

DROP ROLE alice;

If the user owns database objects, such as tables, schemas, or functions, it cannot be dropped until ownership of those objects is reassigned. You also need to revoke any privileges the user has on other objects.

To create a group for granting the same privileges to several users, use CREATE ROLE:

CREATE ROLE sales_team;

To create regular users, use CREATE ROLE with the LOGIN privilege:

CREATE ROLE alice WITH LOGIN;
CREATE ROLE bob WITH LOGIN;

Grant the privileges to the sales_team role using the GRANT command:

GRANT USAGE ON SCHEMA sales TO sales_team;
GRANT ALL PRIVILEGES ON sales.orders TO sales_team;

Assign the sales_team role to users using GRANT:

GRANT sales_team TO alice, bob;

To revoke the sales_team role from users, use the REVOKE command:

REVOKE sales_team FROM alice, bob;

This section shows how to implement the following hierarchy of roles:

sales_team
      ├─── sales_team_manager
      │               └─── alice
      └─── sales_team_reader
                      └─── bob

In this example, the alice and bob users inherit privileges granted to the sales_team_manager and sales_team_reader roles, respectively. Both users also get privileges of the parent sales_team role.

To create all the roles, use CREATE ROLE:

CREATE ROLE sales_team;

CREATE ROLE sales_team_manager;
CREATE ROLE sales_team_reader;

CREATE ROLE alice WITH LOGIN;
CREATE ROLE bob WITH LOGIN;

Grant the specified privileges to group-level roles:

GRANT USAGE ON SCHEMA sales TO sales_team;

GRANT ALL PRIVILEGES ON sales.orders TO sales_team_manager;
GRANT SELECT ON TABLE sales.orders TO sales_team_reader;

Then, assign the sales_team role to the sales_team_manager and sales_team_reader roles:

GRANT sales_team TO sales_team_manager, sales_team_reader;

Finally, assign the sales_team_manager and sales_team_reader roles to users:

GRANT sales_team_manager TO alice;
GRANT sales_team_reader TO bob;

To get information about all the roles, use the \du meta-command:

\du

The result might look like this:

                            List of roles
     Role name      |                   Attributes
--------------------+------------------------------------------------
 alice              |
 bob                |
 gpadmin            | Superuser, Create role, Create DB, Replication
 sales_team         | Cannot login
 sales_team_manager | Cannot login
 sales_team_reader  | Cannot login

You can get information about the privileges granted to the specific role in the information_schema system catalog. For example, the SQL query below retrieves information about the table privileges granted to the sales_team_manager role:

SELECT *
FROM information_schema.role_table_grants
WHERE grantee = 'sales_team_manager';

The result might look as follows:

 grantor |      grantee       | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+--------------------+---------------+--------------+------------+----------------+--------------+----------------
 gpadmin | sales_team_manager | crm           | sales        | orders     | INSERT         | NO           | NO
 gpadmin | sales_team_manager | crm           | sales        | orders     | SELECT         | NO           | YES
 gpadmin | sales_team_manager | crm           | sales        | orders     | UPDATE         | NO           | NO
 gpadmin | sales_team_manager | crm           | sales        | orders     | DELETE         | NO           | NO
 gpadmin | sales_team_manager | crm           | sales        | orders     | TRUNCATE       | NO           | NO
 gpadmin | sales_team_manager | crm           | sales        | orders     | REFERENCES     | NO           | NO
 gpadmin | sales_team_manager | crm           | sales        | orders     | TRIGGER        | NO           | NO

A role can own database objects, such as tables or views. The example below shows how to set the owner of the sales.orders_temp table to alice:

ALTER TABLE sales.orders_temp
    OWNER TO alice;

To change ownership of objects in the current database from alice to bob (including the sales.orders_temp table owner), use the REASSIGN OWNED command:

REASSIGN OWNED BY alice TO bob;

To drop objects owned by the specified role, use the DROP OWNED command:

DROP OWNED BY bob;

Not all the privileges of a parent role might be available to a child role in the current session. A child role doesn’t inherit a parent role’s privileges in the following cases:

If a child role is created with the NOINHERIT attribute.
Even if a child role is created with the INHERIT attribute, the following attributes of a parent role are never inherited: LOGIN, SUPERUSER, CREATEDB, CREATEROLE, CREATEEXTTABLE, and RESOURCE QUEUE.

In such cases, a user needs to execute the SET ROLE command to perform actions allowed by its parent role.

In the example below, two roles are created:

CREATE ROLE dba CREATEROLE;
CREATE ROLE alice WITH LOGIN;
GRANT dba TO alice;

A parent dba role. The dba role is created with the CREATEROLE attribute, which is not inherited as ordinary privileges.
A child alice role.

By default, alice cannot create other roles in the current session. To be able to do this, alice needs to execute SET ROLE to get all the privileges of dba:

SET ROLE 'dba';

After this, alice can create other roles:

CREATE ROLE bob WITH LOGIN;

Attribute Description Default

Attribute	Description	Default
SUPERUSER or NOSUPERUSER	Specifies if a role is a superuser. Only a superuser can create a new superuser	NOSUPERUSER
CREATEDB or NOCREATEDB	Specifies if a role can create databases	NOCREATEDB
CREATEROLE or NOCREATEROLE	Specifies if a role can create and manage other roles	NOCREATEROLE
INHERIT or NOINHERIT	Specifies if a role inherits the privileges of roles it is a member of. A role with the `INHERIT` attribute can use any privileges granted to roles it is directly or indirectly a member of	INHERIT
LOGIN or NOLOGIN	Specifies whether a role is allowed to log in. A role with the `LOGIN` attribute can be considered a user. Roles with the `NOLOGIN` attribute can be considered groups that are useful for managing privileges	NOLOGIN
CONNECTION LIMIT <limit>	Specifies the maximum number of concurrent connections a role can establish. This attribute applies to `LOGIN` roles only	-1 (no limit)
CREATEEXTTABLE or NOCREATEEXTTABLE	Specifies if a role is allowed to create external tables. 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	NOCREATEEXTTABLE
PASSWORD <password>	Specifies a role’s password. A password should be set only for users that have password authentication enabled. For other authentication methods, setting a password is not required	NULL
ENCRYPTED or UNENCRYPTED	Controls whether a new password is stored as a hash string. If the provided password is already hashed, it is stored as-is, regardless of whether `ENCRYPTED` or `UNENCRYPTED` is specified. Learn more in the Password hashing topic	Determined by the `password_encryption` configuration parameter
VALID UNTIL <timestamp>	Sets a date and time after which a role’s password is no longer valid	Never expires
RESOURCE QUEUE <queue_name>	Assigns a role to the named resource queue for workload management. Keep in mind that the `RESOURCE QUEUE` attribute is not inherited and must be explicitly set for each user-level (`LOGIN`) role	pg_default
DENY <deny_point_or_interval>	Sets a day or a day and time to deny access. Learn more in the Restrict user access by time topic	—

SUPERUSER or NOSUPERUSER

Specifies if a role is a superuser. Only a superuser can create a new superuser

NOSUPERUSER

CREATEDB or NOCREATEDB

Specifies if a role can create databases

NOCREATEDB

CREATEROLE or NOCREATEROLE

Specifies if a role can create and manage other roles

NOCREATEROLE

INHERIT or NOINHERIT

Specifies if a role inherits the privileges of roles it is a member of. A role with the INHERIT attribute can use any privileges granted to roles it is directly or indirectly a member of

INHERIT

Specifies whether a role is allowed to log in. A role with the LOGIN attribute can be considered a user. Roles with the NOLOGIN attribute can be considered groups that are useful for managing privileges

NOLOGIN

CONNECTION LIMIT <limit>

Specifies the maximum number of concurrent connections a role can establish. This attribute applies to LOGIN roles only

-1 (no limit)

CREATEEXTTABLE or NOCREATEEXTTABLE

Specifies if a role is allowed to create external tables. 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

NOCREATEEXTTABLE

PASSWORD <password>

Specifies a role’s password. A password should be set only for users that have password authentication enabled. For other authentication methods, setting a password is not required

NULL

ENCRYPTED or UNENCRYPTED

Controls whether a new password is stored as a hash string. If the provided password is already hashed, it is stored as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified. Learn more in the Password hashing topic

Determined by the password_encryption configuration parameter

VALID UNTIL <timestamp>

Sets a date and time after which a role’s password is no longer valid

Never expires

RESOURCE QUEUE <queue_name>

Assigns a role to the named resource queue for workload management. Keep in mind that the RESOURCE QUEUE attribute is not inherited and must be explicitly set for each user-level (LOGIN) role

pg_default

DENY <deny_point_or_interval>

Sets a day or a day and time to deny access. Learn more in the Restrict user access by time topic

—

Object type Privileges

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

Tables, external tables, views

SELECT
INSERT
UPDATE
DELETE
REFERENCES
TRIGGER
TRUNCATE
ALL

Columns

SELECT
INSERT
UPDATE
REFERENCES
ALL

Sequences

USAGE
SELECT
UPDATE
ALL

Databases

CREATE
CONNECT
TEMPORARY or TEMP
ALL

Domains

USAGE
ALL

Foreign data wrappers

USAGE
ALL

Foreign servers

USAGE
ALL

Functions

EXECUTE
ALL

Procedural languages

USAGE
ALL

Schemas

CREATE
USAGE
ALL

Tablespaces

CREATE
ALL

Types

USAGE
ALL

Protocols

SELECT
INSERT
ALL

Encryption of database connections

Restrict user access by time

Roles and privileges

Prerequisites

Overview

Roles: users and groups

Role attributes

Object types

Object privileges

Object owners

Manage users

Create a user

Change attributes

Set user-specific defaults

Grant privileges to a user

Revoke a user’s privileges

Drop users

Grant privileges using groups

Group as a container for user privileges

Group hierarchies

Get information about roles

Change object owners

Change the current session role

Reference

Role attributes

Object privileges