Access Control List

What is access control list in Greenplum?

Access Control List (ACL) in Greenplum is a security feature that allows you to control access to Greenplum database objects such as tables, schemas, and databases. ACLs are used to specify which users or groups have access to which objects and what level of access they have (e.g., read-only or read-write access).

ACLs in Greenplum are based on the same concepts as those in PostgreSQL, which is the open-source database management system that Greenplum is based on. ACLs are defined on each object in the database, and they specify who has the right to perform which operations on the object.

Greenplum supports a range of access control options, including access control based on users, roles, and groups. You can use the Greenplum command-line tools or the graphical user interface (GUI) to manage access control lists for your database objects.

Overall, ACLs are an important tool for securing your Greenplum database and ensuring that only authorized users have access to sensitive data.


How to read access control list in Greenplum

You can read the access control list (ACL) for a specific object in Greenplum using the following SQL query:


SELECT relacl FROM pg_class WHERE relname = 'table_name';

Replace table_name with the name of the table you want to view the ACL for.

The relacl column in the pg_class system catalog table contains the ACL for the specified table. The ACL is represented as a string of comma-separated values, where each value corresponds to a specific user or group and the access level they have been granted. The syntax for the ACL string is as follows:


An ACL is a representation of a group of permissions with the following structure:

grantee=flags/grantor


Here, we see the following:

{user/group name}={permissions}/{grant type},{user/group name}={permissions}/{grant type},...

Whenever the granted and grantee results in the same name, the role is the owner of the database object.

The flags that can be used in an ACL are those reported in the following table. As you can see, not all the flags apply to all the objects: for example it does not make sense to have a "delete" permission on a function, and it does not make sense to have an "execute" permission on a table:

For example, the following is an example ACL string:

"user1=arwdDxt/user2=rw/"

This ACL grants the user user1 all permissions (read, write, delete, execute, and truncate) and the ability to change the access control list for the table. The user user2 is granted read and write permissions.

You can interpret the ACL string using the following key. The grant type can be either o (owner) or g (group). If the grant type is not specified, it defaults to o.


Flag Description Statements Applies to

a append, insert new data INSERT tables, columns

r read, get data SELECT tables, columns, and sequences

w write, update data UPDATE tables

d delete data DELETE tables

D delete all data TRUNCATE tables

C create a new object CREATE databases, schemas, and table spaces

c connect to a database database

t trigger, react to data changes CREATE TRIGGER tables

T crate temporary objects CREATE TEMP tables

x cross reference between data FOREIGN KEY tables

X execute runnable code CALL, SELECT functions, routines, and procedures

U use of various objects sequences, schemas, foreign objects, types, and languages