Contents

• pg_set_acl
• Installation
  • Compiling
  • PostgreSQL setup
• Usage
• Example
  • grant
  • work_mem

pg_set_acl

PostgreSQL extension that implements a SET command access control list.

Installation

Compiling

This module can be built using the standard PGXS infrastructure. For this to work, the pg_config program must be available in your $PATH:

git clone https://github.com/pierreforstmann/pg_set_acl.git cd pg_set_acl make make install

This extension has been validated with PostgreSQL 10, 11, 12, 13, 14 and 15.

PostgreSQL setup

Extension should be loaded at server level with shared_preload_libraries parameter: shared_preload_libraries='pg_set_acl' Extension must be created in each database with: create extension pg_set_acl;

Usage

pg_set_acl has no specific GUC setting. Note that installing pg_set_acl removes execution privilege on set_config function from PUBLIC.

pg_set_acl checks that all SET commands are in the access control list materialized by set_acl.privs table (for all users who are not superusers):

``` \d set_acl.privs; Table "set_acl.privs" Column | Type | Collation | Nullable | Default
----------------+------+-----------+----------+------------- privilege | text | | | 'SET'::text parameter_name | text | | not null | user_name | text | | not null |

If this table is empty, only superusers can use the SET command. In other words, user must have been explicitly granted privilege to use SET command with a given setting using `set_acl.grant` function: select set_acl.grant(setting, user); To revoke privilege to use SET command with a given setting use: select set_acl.revoke(setting, user); `` Only superusers can executeset_acl.grantandset_acl.revoke`.

Example

``` testdb=# \c testdb postgres You are now connected to database "testdb" as user "postgres". testdb=# create extension pg_set_acl; CREATE EXTENSION testdb=# \c testdb testuser; You are now connected to database "testdb" as user "testuser". testdb=> select * from set_acl.privs; privilege | parameter_name | user_name -----------+----------------+----------- (0 rows)

testdb=> set work_mem='1GB'; ERROR: pg_set_acl: permission denied for (work_mem,testuser) testdb=> \c testdb postgres You are now connected to database "testdb" as user "postgres". testdb=# select set_acl.grant('work_mem','testuser');

grant

t (1 row)

testdb=# \c testdb testuser; You are now connected to database "testdb" as user "testuser". testdb=> select * from set_acl.privs; privilege | parameter_name | user_name -----------+----------------+----------- SET | work_mem | testuser (1 row)

testdb=> set work_mem='1GB'; SET testdb=> show work_mem;

work_mem

1GB (1 row)

testdb=> ```