A database function is a named routine stored in the database. It accepts inputs and returns a result or produces a side effect, so its privileges matter as much as its SQL. section 8.4
The function that could drop production
A development-only function named reset_schema() dropped every table in the current schema. Because it was created by a superuser and executable by an unprivileged developer, the function ran with superuser privileges and destroyed production data. The safer lesson is to restrict both ownership and execution rights rather than treating function visibility as harmless. section 8.4
A function executes within the database’s permission model. If a privileged account creates and owns it, granting ordinary users permission to execute it can expose the creator’s effective privileges during execution. A buggy function can therefore cause damage even without malicious intent. section 8.4
Use database functions with these cautions:
- Grant only the permissions necessary for the intended task; avoid making regular database ownership and operations depend on superuser accounts. section 8.4
- Treat side-effecting routines as operationally dangerous, especially when they can drop tables or otherwise alter the database. section 8.4
- Check function behavior and data types during migrations; similarly named functions or types across DBMSs are not necessarily compatible. section 10.5
- Remember that ordinary functions such as count() have defined handling for NULL values: count() ignores NULL values in a nullable column. section 2.11
CREATE DATABASE frogge_next OWNER postgres;
Inspect the database ownership setup that makes privileged function execution especially dangerous. section 8.4
