On: Paranoid SQL Execution on Postgres

[mattashii]

No, just that an adversary character might create a type `date` in a schema that's on your path, so that any data goes through that type's input and output functions / the semantics of the query change.

It's quite similar to why you fully qualify table names and column names.

[hashimotonomora → mattashii]

Can you give a short example? So you don’t trust your own administrator?

[mattashii → hashimotonomora]

> So you don’t trust your own administrator?

Not per se, but it might just as well be that you share the database with several other applications, which might do DDL. Fully qualifying all identifiers is the easiest way to guarantee that you don't have negative dependencies to worry about (i.e. depending on the non-existance of some identifier in a certain schema); the issue will most likely happen only when a) an adversary gets CREATE TYPES access to the database, or when you use custom types and a name that's in use starts to be shadowed.

Examples:

CREATE DOMAIN "bigint" AS pg_catalog.text;

CREATE TYPE "text" AS ENUM ();

Though, in all earnesty, this is also an issue with custom operators:

CREATE OPERATOR = (function = always_false, left_arg = int, right_arg = int);

You can schema-qualify operators ( Col1 OPERATOR(pg_catalog.=) Col2 ), but in doing that you lose operator precedence.

[ttymck → mattashii]

A good corollary might be: "use very specific names for custom types."

[tpetry → mattashii]

I am not sure at the moment but don‘t you need superuser access to create operators? In that case not „any application“ could make your application faulty.

[mattashii → tpetry]

Nope:

> To be able to create an operator, you must have USAGE privilege on the argument types and the return type, as well as EXECUTE privilege on the underlying function. If a commutator or negator operator is specified, you must own these operators.

(https://www.postgresql.org/docs/14/sql-createoperator.html)