ACL data type for Postgres

The default ACL type in Postgres (aclitem) produces rather cryptic output that is hard to understand.

The ACL type simplifies that.

An ACL is a composite type comprised of 3 parts: any array of human-readable rights, the grantee (what role these rights are granted to), and the grantor (what role granted the rights). This ACL can then be used strictly as output (perhaps via the pretty-print function`aclpp()`), or for comparison purposes (to see if a particular object has certain ACLs).

How is this different from the other acl extension?

The two big differences are that extension has to be compiled (frequently a problem in production), and it’s still very terse. This extension uses a compound type to make it very easy to interface with ACLs.

I do plan to add casts to and from the ACE type in the future.

Installation

• Install pgxnclient

• pgxn load pg_acl -d dbname --schema acl (See pgxn load documentation.)

Alternatively, you can pgxn install pg_acl and then CREATE EXTENSION pg_acl SCHEMA acl; from within the database.

Note: you can install pg_acl into any schema you want, but once it’s installed you can’t move it.

Provided types

There are two main types for ACLs. Each of those has several variations.

acl_right

acl_right is an ENUM (enumerator) type that lists all grantable rights in their english form. It comes with two functions for listing all the available values. _allacl_right returns an array of all values and _allacl_right_srf returns a set.

_all__acl_right_srf()

 INSERT
 INSERT WITH GRANT OPTION
 SELECT
...
 TEMPORARY WITH GRANT OPTION
 CONNECT
 CONNECT WITH GRANT OPTION
(24 rows)

acl_right_no_grant

This is the same as acl_right but does not include any rights with the grant option.

_all__acl_right_no_grant_srf()

 INSERT
 SELECT
 UPDATE
 DELETE
 TRUNCATE
 REFERENCES
 TRIGGER
 EXECUTE
 USAGE
 CREATE
 TEMPORARY
 CONNECT
(12 rows)

acl_right_only_grant

Contains only the with grant variation of rights.

Object-specific Rights

TODO: These have not been created yet.

These types follow the same pattern as acl_right, but allowed rights are limited by the type of object involved. These types are:

• aclcolumn

• aclrelation

• aclsequence

• acldatabase

• aclfdw

• aclforeign_server

• aclfunction

• acllanguage

• acllargeobject

• aclnamespace

• acltablespace

• acltype

_rights_to_enum()

This function converts the rights portion of an aclitem string to an array of acl_right. There is also _rights_to_enum_no_grant(), which does what you’d expect..

acl

acl is a composite type, with elements grantee (regrole), rights (acl_right[]), and grantor (regrole).

The proper way to create an acl is with the acl(aclitem) function. (Unfortunately, you can’t cast to a composite type.)

Comparison Use Cases

Note	Comparison operators don’t exist yet, but these are the targeted use cases.

"Match" in these cases means ordering of items is not important.

• Verify any of the acl*_right*[] arrays "match" another acl right array. If one of the arrays is a no_grant version then matching will ignore the WITH GRANT portion of the right (ie: the full type will be downcast to the no_grant type). There will not be comparasons between only_grant and no_grant rights arrays.

• Verify an aclitem[] array "matches" an acl[] array.

• Verify a specific right does/doesn’t exist anywhere in an aclitem[] array.

• Support acl[] to acl[] versions of all aclitem[] to acl[] comparisons.

• Support acl comparisons that ignore the grantor.

Copyright and License

Copyright (c) 2016 Jim Nasby <Jim.Nasby@BlueTreble.com>.