liveBook · Manning

Principle of least privilege

As explained in PostgreSQL Mistakes and How to Avoid Them

The principle of least privilege is a security practice of granting each account only the permissions required for its tasks.

Grant each account only the permissions required for its tasks. In PostgreSQL, making databases and objects owned by a superuser encourages routine connections through an account that can bypass nearly every security check. section 8.4

The tempting ownership mistake

CREATE DATABASE frogge_next OWNER postgres; can turn the superuser into the practical owner and operator of an application database. A developer’s unprivileged account may then execute reset_schema(), a function created by the superuser with superuser execution rights. If that function drops every table, a production mistake can require restoration from backup, causing downtime and reputational damage. section 8.4

Apply the principle by:

  • Avoid using the default postgres superuser for regular application connections and operations. section 8.4
  • Separate development-only destructive capabilities from permissions available in production. section 8.4
CREATE DATABASE frogge_next OWNER postgres;

Inspect the superuser-owned database definition that creates the risky arrangement. section 8.4

How PostgreSQL Mistakes and How to Avoid Them covers it

How deep it goes
Explains the rationale and consequences through a production failure, but stops short of detailed privilege-revocation procedures.
Examples
Uses CREATE DATABASE frogge_next OWNER postgres and a reset_schema() function that drops tables.
What you can do after
Evaluate PostgreSQL account and ownership choices for unnecessary superuser exposure and production damage.
How it connects
Connects database ownership with superusers, privilege checks, stored functions, application accounts, and backups.
Cover of PostgreSQL Mistakes and How to Avoid Them
liveBookmanning.com
Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Also explained in

Compare how 17 books explain principle of least privilege →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.