acl

Overview

+cast pg_uuid_t

Version

Build

You can build the RPM / DEB packages for pg_acl using pig build:

pig build pkg pg_acl         # build RPM / DEB packages

Install

You can install pg_acl directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_acl;          # Install for current active PG version

pig ext install -y pg_acl -v 18  # PG 18
pig ext install -y pg_acl -v 17  # PG 17
pig ext install -y pg_acl -v 16  # PG 16
pig ext install -y pg_acl -v 15  # PG 15
pig ext install -y pg_acl -v 14  # PG 14

dnf install -y acl_18       # PG 18
dnf install -y acl_17       # PG 17
dnf install -y acl_16       # PG 16
dnf install -y acl_15       # PG 15
dnf install -y acl_14       # PG 14

apt install -y postgresql-18-acl   # PG 18
apt install -y postgresql-17-acl   # PG 17
apt install -y postgresql-16-acl   # PG 16
apt install -y postgresql-15-acl   # PG 15
apt install -y postgresql-14-acl   # PG 14

Create Extension:

CREATE EXTENSION acl;

Usage

acl: access control list data type for PostgreSQL

The acl extension provides Access Control List types for row-level security based on application users, without requiring separate database accounts.

CREATE EXTENSION acl;

Data Types

• ace: Standard role-based ACE using PostgreSQL OIDs
• ace_int4: ACE with 32-bit integer identifiers
• ace_int8: ACE with 64-bit integer identifiers
• ace_uuid: ACE with UUID identifiers

ACLs are stored as PostgreSQL arrays of ACE types (e.g., ace[]).

ACE Format

[type]/[flags]/[who]=[mask]

• Type: a (allow) or d (deny)
• Flags: i (inherit only), o (object inherit), c (container inherit), p (no propagate), h (inherited)
• Who: Role name, OID, integer, UUID, or "" (everyone)
• Permissions: r (read), w (write), d (delete), c (read ACL), s (write ACL), plus 16 custom permissions (0-F)

Checking Permissions

-- Check current user's access
SELECT acl_check_access(acl_column, 'rw', false) FROM my_table;

-- Check specific role
SELECT acl_check_access(acl_column, 'r', 'username'::name, false);

-- Check custom int4 roles
SELECT acl_check_access(acl_column, 'rw', ARRAY[1001, 1002]::int4[], false);

ACL Inheritance

-- Compute child ACL from parent
SELECT acl_merge(parent_acl, child_acl, true, true);

Row-Level Security Example

CREATE TABLE file_system (
    id   int PRIMARY KEY,
    name text,
    acl  ace[]
);

ALTER TABLE file_system ENABLE ROW LEVEL SECURITY;

CREATE POLICY read_policy ON file_system FOR SELECT TO PUBLIC
    USING (acl_check_access(acl, 'r', false) = 'r');

CREATE POLICY write_policy ON file_system FOR UPDATE TO PUBLIC
    USING (acl_check_access(acl, 'w', false) = 'w');