PostgreSQL Access Control List

In today article, we will learn an access control list, or ACL, is a list of user permissions that controls access to a system resource.

An ACL specifies which users or system processes are allowed to have access to the system resource. Each ACL entry specifies a subject and an allowed operation.

When we connect from Psql and run the “\z” parameter, it gives the output as follows.

Above, there are objects and the permissions related to them in the public schema.

The Access privileges section lists which user has what privileges.

If a column-based authority or policy was defined, the relevant information would also appear in this table.

Let’s explain what is in the ACL table and what is in the Access privileges section.

SELECT(r): Allows the user to retrieve data from a table, view, or array (the nextval() function may not be called with SELECT-only rights). Also known as “read” rights.

INSERT(a): Allows a user to insert new rows into a table. Also known as “append” rights.

UPDATE, DELETE(w): Allows a user to modify or remove rows of data in a table.

If the right to UPDATE or DELETE is granted, the other is also granted implicitly. Also known as “write” rights.

RULE(R): Allows a user to create a rewrite rule on a table or view.

ALL(arwR): Represents a shortcut to grant or revoke all rights at once.

ALL is not a right in itself. Giving ALL results by issuing SELECT, INSERT, UPDATE, DELETE and RULE.