Surprising Behavior Roles Privileges

Introduction

This is *not* about security bugs. All of the issues here are known, and for the most part documented at least indirectly.

This document is about behaviors that are unintuitive and very hard to document properly, and therefore may give users a false sense of security.

Surprises

A user doesn't control what code is executed with their privileges.

Ordinary access (e.g. INSERT, SELECT) to ordinary objects like tables and views can execute arbitrary code, and there's no practical way to prevent that or to even know what code will be executed. The code written by the object owner is executed with the privileges of the user accessing the object.

The only way to be safe is to only access objects owned by users you *absolutely* trust, such as a superuser, or a user that is a member of your role. If the object owner is not explicitly a member of your role, and you access the object, they might as well be a member of your role.

Effectively, this implies that our privilege system is hierarchical, but it's not documented as such. Object owners pass out fine-grained privileges with GRANT, and the ones accessing the objects must trust the object owners entirely.

A function author doesn't know what their function will do.

A PL/pgSQL function, or any function that uses SPI, relies on search_path to find even very basic functions and operators like >. Thus, the caller can easily manipulate the search path so that the function or operator resolves to a function/operator of the same name in a different schema, created by the caller, and containing arbitrary code.

This creates two potential problems:

1. The most obvious problem is for SECURITY DEFINER functions, where the author must absolutely control the function's behavior, because it executes with the privileges of the author.

2. A more subtle problem is when the object owner depends on the function to return the correct results, such as in a constraint definition. The caller can manipulate the behavior of the function to return the wrong results and incorrectly pass a constraint check, thereby violating the constraint.

These problems can be resolved by specifying a SET search_path clause on the function definition. To be safe, any function that might be called directly or indirectly by another user should be declared with a SET search_path clause, but the importance is under-documented. Furthermore, the behavior is unsafe-by-default (functions have public EXECUTE privileges and no SET search_path), and in general the practice is not encouraged or widespread.

There's no way to drop privileges.

The most intuitive ways to drop privileges, SET SESSION AUTHORIZATION and SET ROLE, can both be undone in the same session.

Hacks and special cases.

There are some cases where more privileged users need to access objects owned by less-privileged users. Examples include table maintenance (e.g. REINDEX) and logical replication. Rather than generally address the problem that a user doesn't control what code gets executed with their privileges, Postgres simply carved out special cases for maintenance commands. That means it's safe for a superuser to issue a REINDEX on a table owned by an unprivileged user, but highly dangerous to execute an INSERT or SELECT on that same table.