The Strange Behavior of The Empty Default Privileges on PostgreSQL

PostgreSQL grants privileges on some types of objects to PUBLIC by default
when the objects are created.

• No privileges are granted to PUBLIC by default on tables, table columns, sequences, foreign data wrappers, foreign servers, large objects, schemas, tablespaces, or configuration parameters.

For other types of objects, the default privileges granted to PUBLIC are as
follows:

• CONNECT and TEMPORARY (create temporary tables) privileges for databases;
• EXECUTE privilege for functions and procedures;
• and USAGE privilege for languages and data types (including domains).

The object owner can, of course, REVOKE both default and expressly granted
privileges. Also, these default privilege settings can be overridden using the
ALTER DEFAULT PRIVILEGES command.

PostgreSQL - Privileges Documentation

---

This document illustrates a strange behavior where even after revoking all
default privileges for functions, newly created functions still inherit the
built-in EXECUTE access to PUBLIC (and to the function owner), contrary as
the expectation.

Background

The catalog pg_catalog.pg_proc stores information about functions, procedures,
aggregate functions, and window functions (collectively also known as routines).
This catalog contains the proacl aclitem[] column that stores the access
privileges of the object.

Each aclitem lists all the permissions of one grantee that have been
granted by a particular grantor. Specific privileges are represented by
one-letter abbreviations from Privileges - Table 5.1, with * appended if the
privilege was granted with grant option. For example, calvin=r*w/hobbes,
specifies that the role calvin has the privilege
SELECT (r) with grant option (*) as well as the non-grantable
privilege UPDATE (w), both granted by the role hobbes. If calvin also
has some privileges on the same object granted by a different grantor, those
would appear as a separate aclitem entry. An empty grantee field in an
aclitem stands for PUBLIC.

The value of the proacl aclitem[] column is set when the object is created,
based on default access privileges set to the database or schema. That column
value can be in one of three possible states:

• null: The object's ACL was not defined. In this case, the built-in default privileges are applied to the object. For functions, the built-in default privileges are to grant EXECUTE access to PUBLIC and to the object owner.
• an empty value: The object's privileges entry is non-null but empty. This means that no privileges are granted at all, even to the object's owner — a rare situation. The owner still has implicit grant options in this case, and so could re-grant her own privileges.
• the list of entries: The object's ACL is defined with the entries of grantees, the privileges and the grantor information.

ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied
to objects created in the future. (It does not affect privileges assigned to
already-existing objects.) Privileges can be set globally (i.e., for all objects
created in the current database), or just for objects created in specified
schemas.

As before explained, the default privileges for any object type normally grant
all grantable permissions to the object owner, and may grant some privileges
to **PUBLIC** as well. However, this behavior can be changed by altering the
global default privileges with ALTER DEFAULT PRIVILEGES.

Don't confuse global/per-schema default privileges with built-in default
privileges.

• built-in default privileges: The base rules of the default privileges.
• global/per-schema default privileges: The rules of the default privileges after configured by ALTER DEFAULT PRIVILEGES.

The Strange Behavior of The Empty Default Privileges

Built-in default privileges of PostgreSQL automatically apply some grants
privileges on some types of objects to PUBLIC . In short, this default
behavior does not have much security concerns, because the types of objects and
the grants involved on this condition does not reprents a direct security risk,
as they are protected by other layers of objects that encapsulate their usage.

Must also be clear that PUBLIC does not mean any unkonown access, but rather
access from registered roles in the system. And, perhaps the intention of the
PostgreSQL's engineering of this design is to simplify the model of access of
the database resources.

For example, the efficacy of an EXECUTE grant to permits the operation of
some function by a role, depends of the grantee to have the USAGE privilege
to access the function's scheme too. As well, the grantee must have privileges
to operate other resources related to the function purpose during the function
operation.

Some architectures may need to address the principle of least privilege, so
revoking default privileges to gradually provide these access to appropriate
roles one by one. But even after revoking all default privileges for functions
using ALTER DEFAULT PRIVILEGES, newly created functions still inherit the
built-in EXECUTE privilege. This behavior goes against the expectation that
revoking all default privileges should completely remove any access granted by
default.

Suggestion to mitigate the strange behavior

To address the issue where revoking default privileges does not completely
remove the unintended access, the following solution provides a way to regain
control over default privilege inheritance and prevent unwanted access:

1. Create a nobody role (if it doesn't exists).

   This role is designed to act as a placeholder for access management,
   ensuring that no one receives unintended privileges automatically.
   

   CREATE ROLE nobody WITH NOLOGIN NOINHERIT;
   

2. Revoke all default privileges.

   By revoking all default privileges on functions, you ensure that no default
   access is granted to the PUBLIC role or the postgres superuser role.
   

   ALTER DEFAULT PRIVILEGES
       REVOKE ALL
       ON FUNCTIONS
       FROM PUBLIC, postgres;
   

3. Grant all default privileges to nobody.

   Finally, you explicitly grant the default privileges for functions to the
   nobody role. This step ensures that any new functions created will have
   their default privileges directed toward the this role, but does not have
   any practical effect, as this role does not have login or inheritance
   rights.

   This step effectively neutralizes the strange behavior of the empty default
   privileges by confining it to a role that has no impact on actual database
   operations.
   

   ALTER DEFAULT PRIVILEGES
     GRANT ALL
     ON FUNCTIONS
     TO nobody;
   

By implementing these steps, you can regain control over the default privilege
behavior in PostgreSQL and prevent the automatic inheritance of privileges,
which is important for maintaining a security-conscious environment.

This solution can also be applied to other types of objects and privileges that
are automatically granted by built-in default privileges.

How to reproduce the strange behavior

PostgreSQL 10+.

Create the database to test.

CREATE DATABASE example_db;

After the database was created, reconnect on it example_db database.

Showing the initial state of the session.

SELECT current_database(), current_user, current_role, session_user;

current_database	current_user	current_role	session_user
example_db	postgres	postgres	postgres

Result: 1 row

Check the initial state of default privileges.

SELECT * FROM pg_catalog.pg_default_acl;

oid	defaclrole	defaclnamespace	defaclobjtype	defaclacl

Result: 0 rows

Setup

Revoke all default privileges for functions.

ALTER DEFAULT PRIVILEGES
  REVOKE ALL
  ON FUNCTIONS
  FROM PUBLIC, postgres;

Check default privileges definition.

SELECT * FROM pg_catalog.pg_default_acl;

oid	defaclrole	defaclnamespace	defaclobjtype	defaclacl
43772	10	0	f

Result: 1 row

Objects creation.

At this example, we'll create a function to show as current default privileges
will be appliend.

-- An unprivileged role (not owner).
CREATE ROLE example_role WITH NOLOGIN NOINHERIT;

-- An scheme to isolate the function.
CREATE SCHEMA example_schema;
-- Grants the USAGE privilege on the schema to allow example_role to access it.
GRANT USAGE ON SCHEMA example_schema TO example_role;

-- The target function.
CREATE FUNCTION example_schema.example_function()
  RETURNS TEXT
  LANGUAGE SQL
  AS $$
    SELECT 'Hello!';
  $$

Check the initial access privileges of the function.

It's initially null, meaning the system default (grants EXECUTE access to
PUBLIC and to the owner).

SELECT oid::regproc as name, proacl FROM pg_catalog.pg_proc
  WHERE proname = 'example_function';

THE STRANGE BEHAVIOR IS HERE: At this moment the function's ACL value
must be inherited from defined default privileges, empty (no one entry),
but it's null (built-in default privileges, that grants EXECUTE access to
PUBLIC and to the function's owner).

name	proacl
example_schema.example_function	(NULL)

Result: 1 row.

Callint to test

Changes the current user.

SET SESSION AUTHORIZATION example_role;

Check the current state of the session.

SELECT current_user, current_role, session_user;

current_user	current_role	session_user
example_role	example_role	example_role

Result: 1 row

Execute the function (expects to fail).

SELECT example_schema.example_function()

But not fail.

example_function
Hello!

Result: 1 row