You need this extension if you want to restrict access to rows in tables based on an authenticated user. Access Control List (ACL) has become the de-facto standard for implementing a security model in modern applications. Complex scenarios can be handled by using ACLs without having a complex permission model at the application level.
This document is not intended to be a tutorial on ACLs and security and describes the Access Control List Extension only. You can find more information on ACLs on the sites below.
- Wikipedia: Access Control List
- MSDN: How AccessCheck Works
- MSDN: ACL Technology Overview
- Network File System (NFS) version 4 Protocol
- POSIX Access Control Lists on Linux
The extension provides you with a number of special types representing ACL entries (ACEs) and a set of functions that check a user against an ACL.
The aclitem is an internal type. Its behaviour may change in a future
release without notice, so do not rely on it unless you like living
dangerously. Besides, in a typical mid-tier/server environment, applications
rarely have separate database accounts for each user, which means that the
aclitem type will do little to help you secure your application.
If you are running Linux, the easiest way to install the extension is to to use the PGXN client.
$ pgxn install acl
Or if you prefer to stick with the good old Make, you can set up the extension like this:
$ make
$ make install
$ make installcheck
If you are running Windows, you need to run the MSBuild command in the Visual Studio command prompt.
> msbuild /p:pgversion=9.4 /p:configuration=Release /p:platform=x64
The platforms available are x64 and x86 and the configuration are 9.1, 9.2, 9.3, 9.4, and 9.5.
Or you can download the latest released zip here.
Then you must copy the DLL from the project into the PostgreSQL's lib
directory and the .sql and .control files into the directory
share\extension.
> copy x64\9.4\acl.dll "C:\Program Files\PostgreSQL\9.4\lib"
> copy *.control "C:\Program Files\PostgreSQL\9.4\share\extension"
> copy *.sql "C:\Program Files\PostgreSQL\9.4\share\extension"
Once the extension is installed, you can add it to a database.
$ CREATE EXTENSION acl;
Since an ACL is a simple one-dimensional PostgreSQL array, the easiest way to create it is from a literal constant.
SELECT '{a/i/postgres=rwd, d//user=r, a//=r}'::ace[];ACL can have zero or more access control entries (ACE). Every ACE has the following textual representation.
[type]/[flags]/[who]=[mask]
There are two types of ACEs: allow and deny (a and d in a textual
representation). The type part is mandatory.
The who part defines for which role this ACE is in effect. There is a
special identifier "" (empty string) representing everyone.
Mask is a string value that specifies the permissions that are allowed or denied in an ACE.
For more information on ACLs see ACL Structure section below.
ACLs are often applied to hierarchical structures. In order to compute a new ACL based on a parent ACL, use the following function.
acl_merge(ace[] parent_acl, ace[] acl, bool container, bool deny_first)
The container parameter determines if the ACL is computing for a container
object or a leaf object.
The deny_first parameter defines if the deny ACEs must be placed first (this
is a default behaviour for all Microsoft products).
For more information on ACL flags see ACL Structure section below.
Once you created an ACL, you may want to determine if it grants a current or any particular user a specified set of permissions. In order to do this, use one of the following functions.
acl_check_access(ace[] acl, text mask, bool implicit_allow)acl_check_access(ace[] acl, int4 mask, bool implicit_allow)acl_check_access(ace[] acl, text mask, oid role, bool implicit_allow)acl_check_access(ace[] acl, int4 mask, oid role, bool implicit_allow)acl_check_access(ace[] acl, text mask, name role, bool implicit_allow)acl_check_access(ace[] acl, int4 mask, name role, bool implicit_allow)
The first two check the PostgreSQL current user against the specified ACL while the four others take the role to check in its third parameters.
There are two types of acl_check_ functions which take text or int4 as
the mask. Production environments should always use the latter form as it
is much more efficient.
The functions return the granted permissions (either as a text or int4,
depending on the type of their mask parameters). If no permissions are granted,
an empty string is returned.
There is also an additional parameter implicit_allow which controls whether
permission is granted if it was not explicitly granted or denied in the ACL.
There are 16 custom flags and 16 custom permission (see ACE Structure section below). How you use them is up to you. All the extension functions ignore custom flags.
Yes! The Access Control List Extension integrates flawlessly with PostgreSQL row-level security. All you need is to add an ACL column to your data table and define security policies.
CREATE TABLE file_system (id int PRIMARY KEY NOT NULL, parent_id int, is_directory bool NOT NULL, name text, acl ace[]);
ALTER TABLE file_system ADD CONSTRAINT file_system_parent_fk FOREIGN KEY (parent_id) REFERENCES file_system(id);
GRANT SELECT, INSERT, UPDATE, DELETE ON file_system TO PUBLIC;
ALTER TABLE file_system ENABLE ROW LEVEL SECURITY;
CREATE POLICY file_system_read_policy ON file_system FOR SELECT TO PUBLIC
USING (acl_check_access(acl, 'r', false) = 'r');
CREATE POLICY file_system_update_policy ON file_system FOR UPDATE TO PUBLIC
USING (acl_check_access(acl, 'w', false) = 'w');
CREATE POLICY file_system_delete_policy ON file_system FOR DELETE TO PUBLIC
USING (acl_check_access(acl, 'd', false) = 'd');
CREATE POLICY file_system_insert_policy ON file_system FOR INSERT TO PUBLIC
WITH CHECK (acl_check_access((SELECT p.acl FROM file_system p WHERE p.id = file_system.parent_id), 'w', false) = 'w');
CREATE FUNCTION file_system_modify()
RETURNS TRIGGER AS $$
DECLARE
v_parent_acl ace[];
BEGIN
v_parent_acl = (SELECT p.acl FROM file_system p WHERE p.id = NEW.parent_id);
IF NOT FOUND THEN
IF NEW.parent_id IS NULL THEN
-- Only a superuser can add a root directory
IF current_user <> 'postgres' THEN
RAISE EXCEPTION 'Access denied';
END IF;
RETURN NEW;
END IF;
END IF;
IF v_parent_acl IS NULL THEN
NEW.acl = NULL;
ELSIF NEW.acl IS NULL THEN
NEW.acl = v_parent_acl;
ELSE
NEW.acl = acl_merge(v_parent_acl, NEW.acl, NEW.is_directory, true);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER file_system_insert.
BEFORE INSERT OR UPDATE ON file_system
FOR EACH ROW EXECUTE PROCEDURE file_system_modify();
INSERT INTO file_system(id, parent_id, name, is_directory, acl)
VALUES (1, NULL, '/', TRUE, '{a/c/=r}'),
(2, 1, '/home', TRUE, '{a//=rdw}'),
(3, 1, '/bin', TRUE, '{a//postgres=rdw,d//=rdw}');
SELECT * FROM file_system; id | parent_id | is_directory | name | acl
----+-----------+--------------+-------+-----------------------------------
1 | | t | / | {a/c/=r}
2 | 1 | t | /home | {a//=dwr,a/hc/=r}
3 | 1 | t | /bin | {d//=dwr,a//postgres=dwr,a/hc/=r}
Then connect as another user and check how it works.
SET ROLE test;
SELECT * FROM file_system; id | parent_id | is_directory | name | acl
----+-----------+--------------+-------+-------------------
1 | | t | / | {a/c/=r}
2 | 1 | t | /home | {a//=dwr,a/hc/=r}
INSERT INTO file_system (id, parent_id, name, is_directory, acl)
VALUES(10, 1, '/test', TRUE, '{a//=rdw}');ERROR: new row violates row level security policy for "file_system"
INSERT INTO file_system (id, parent_id, name, is_directory, acl)
VALUES(10, 2, '/home/test', TRUE, '{a//=rdw}');
SELECT * FROM file_system; id | parent_id | is_directory | name | acl
----+-----------+--------------+------------+-------------------
1 | | t | / | {a/c/=r}
2 | 1 | t | /home | {a//=dwr,a/hc/=r}
10 | 2 | t | /home/test | {a//=dwr,a/hc/=r}
DELETE FROM file_system WHERE id = 1;DELETE 0
DELETE FROM file_system WHERE id = 10;
SELECT * FROM file_system; id | parent_id | is_directory | name | acl
----+-----------+--------------+-------+-------------------
1 | | t | / | {a/c/=r}
2 | 1 | t | /home | {a//=dwr,a/hc/=r}
You can still use the Access Control List Extension even if your application does not rely on the PostgreSQL roles system. There are three additional ACE types supported.
ace_int4(e.g.a/h/1985=rdw,d//-2015=s)ace_int8(e.g.a/h/0=w,d/oic/1234567890=AB)ace_uuid(e.g.a//00001101-0000-1000-8000-00805F9B34FB=r)
And a set of acl_check_ functions that are slightly different from the ones
you have seen so far.
acl_check_access(ace_int4 acl, text mask, int4[] roles, bool implicit_allow)acl_check_access(ace_int4 acl, int4 mask, int4[] roles, bool implicit_allow)acl_check_access(ace_int8 acl, text mask, int8[] roles, bool implicit_allow)acl_check_access(ace_int8 acl, int4 mask, int8[] roles, bool implicit_allow)acl_check_access(ace_uuid acl, text mask, uuid[] roles, bool implicit_allow)acl_check_access(ace_uuid acl, int4 mask, uuid[] roles, bool implicit_allow)
The third parameter of these functions is an array of roles that your application considers the user has.
It introduces some overhead in the data reading, depending on the type and size of the ACLs (see Performance Benchmarks below), averaging 25%. It is way faster than any existing mid-tier security solution could offer.
Sometimes you will see a number sign (#) followed by a number instead of a role name.
d/h/#42=w
Or an entry with a special 'x' (INVALID) flag.
a/ox/=rd
It happens when the OID specified in the ACL entry cannot be resolved to a role name. One of the most likely causes is that the role was deleted. Since ACLs are usually set on a large number of objects, it would be unwise to try to remove invalid entries every time a role is being removed from the system, so these invalid ACEs remain there until you remove them manually. Another imporant reason that makes us provide a reasonable textual representation of every ACE is that PostgreSQL uses them to make backups and restores.
Unfortunately, no, but feel free to write one :)
To build it, just do this:
$ make
$ make install
$ make installcheck
If you encounter an error such as:
"Makefile", line 8: Need an operator
You need to use GNU make, which may well be installed on your system as gmake:
$ gmake
$ gmake install
$ gmake installcheck
If you encounter an error such as:
make: pg_config: Command not found
Be sure that you have pg_config installed and in your path. If you used a package management system such as RPM to install PostgreSQL, be sure that the -devel package is also installed. If necessary tell the build process where to find it:
$ env PG_CONFIG=/path/to/pg_config make && make install && make installcheck
If you encounter an error such as:
ERROR: must be owner of database regression
You need to run the test suite using a super user, such as the default "postgres" super user:
$ make installcheck PGUSER=postgres
Once the extension is installed, you can add it to a database. Connect to a database as a super user and do this:
$ CREATE EXTENSION acl;
[type]/[flags]/[who]=[mask]
Examples
a/ihpc/acl_test1=wdd/ox/acl_test1=sa//"acl test2"=dw0a//"test""blah"=AB1d//=
| Type | Description |
|---|---|
| ALLOW | Explicitly grants the access to the object. |
| DENY | Explicitly denies the access to the object. |
| Flag | Mask | Description |
|---|---|---|
| INHERIT_ONLY | 0x80000000 (i) | Indicates that this ACE does not apply to the current object. |
| OBJECT_INHERIT | 0x40000000 (o) | Indicates that child objects will inherit this ACE. |
| CONTAINER_INHERIT | 0x20000000 (c) | Indicates that child containers will inherit this ACE. |
| NO_PROPAGATE_INHERIT | 0x10000000 (p) | Indicates that child containers will not propagate this ACE any further. |
| INHERITED | 0x08000000 (h) | Indicates that this ACE was inherited from another container. |
| INVALID | 0x04000000 (x) | Indicates that this ACE must not used while checking permissions. |
| | Flags from 0x02000000 to 0x00010000 are reserved for the future use.
| 0x00008000 (F) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00004000 (E) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00002000 (D) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00001000 (C) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00000800 (B) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00000400 (A) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00000200 (9) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00000100 (8) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00000080 (7) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00000040 (6) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00000020 (5) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00000010 (4) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00000008 (3) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00000004 (2) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00000002 (1) | Flags from 0x00008000 to 0x00000001 are application-specific.
| 0x00000001 (0) | Flags from 0x00008000 to 0x00000001 are application-specific.
| Permission | Mask | Description |
|---|---|---|
| READ | 0x80000000 (r) | Permission to read the object. |
| WRITE | 0x40000000 (w) | Permission to write the object. |
| DELETE | 0x20000000 (d) | Permission to delete the object. |
| READ_ACL | 0x10000000 (c) | Permission to read the ACL of the object. |
| WRITE_ACL | 0x08000000 (s) | Permission to modify the ACL of the object. |
| | Permissions from 0x04000000 to 0x00010000 are reserved for the future use.
| 0x00008000 (F) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00004000 (E) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00002000 (D) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00001000 (C) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00000800 (B) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00000400 (A) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00000200 (9) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00000100 (8) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00000080 (7) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00000040 (6) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00000020 (5) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00000010 (4) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00000008 (3) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00000004 (2) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00000002 (1) | Permissions from 0x00008000 to 0x00000001 are application-specific.
| 0x00000001 (0) | Permissions from 0x00008000 to 0x00000001 are application-specific.
There is a special identifier "" (empty string) representing everyone.
Intel(R) Core(TM) i5-3470 CPU @ 3.20GHz (fam: 06, model: 3a, stepping: 09)
Linux 3.13.0-generic Ubuntu SMP x86_64, PostgreSQL 9.4.4
~ 20 entries in each ACL
| ACE type | Checks per sec | Read rate, records per sec | Read overhead |
|---|---|---|---|
| OID | 3.2 million | 0.48 million | 18% |
| int4 | 2.4 million | 0.55 million | 29% |
| int8 | 2.9 million | 0.48 million | 20% |
| UUID | 1.6 million | 0.42 million | 36% |
Access Control List Extension is distributed under the terms of BSD 2-clause license. See LICENSE or http://www.opensource.org/licenses/bsd-license.php for more details.