Apply Principle of Least Privilege

Why This Matters

The principle of least privilege means granting users and roles only the minimum permissions necessary to perform their tasks. In Postgres, this reduces the attack surface and makes security audits easier.

By default, Postgres grants broad permissions to the public role, which all users inherit. This can lead to:

• Unnecessary access: Users can access tables, functions, or schemas they don't need
• Larger attack surface: More permissions mean more potential vulnerabilities
• Poor audit trail: Hard to track who has access to what
• Accidental misuse: Developers might use permissions they shouldn't have

The Problem

Default Postgres permissions grant too much access:

-- New tables grant SELECT, INSERT, UPDATE, DELETE to public by default
CREATE TABLE sensitive_data (
  id UUID PRIMARY KEY,
  user_id UUID,
  secret TEXT
);

-- All users can SELECT, INSERT, UPDATE, DELETE
-- Even if RLS restricts rows, users can still attempt operations
-- Functions and sequences also grant to public by default

-- Users can see all function definitions
SELECT proname, prosrc FROM pg_proc;

Even with RLS enabled, users have unnecessary permissions. They can attempt operations, see function source code, and access sequences they don't need.

The Solution

Revoke public defaults and grant specific permissions only where needed:

-- Create table
CREATE TABLE sensitive_data (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  secret TEXT
);

-- Revoke all permissions from public
REVOKE ALL ON TABLE sensitive_data FROM PUBLIC;

-- Grant only SELECT to authenticated users (RLS will filter rows)
GRANT SELECT ON TABLE sensitive_data TO authenticated;

-- Grant INSERT, UPDATE, DELETE only if needed
-- Most apps only need SELECT with RLS
GRANT INSERT, UPDATE, DELETE ON TABLE sensitive_data TO authenticated;

-- Enable RLS for row-level filtering
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

For functions, revoke execute from public and grant only to specific roles:

-- Create function
CREATE OR REPLACE FUNCTION calculate_total(amount NUMERIC)
RETURNS NUMERIC LANGUAGE SQL STABLE
AS $$ SELECT amount * 1.1; $$;

-- Revoke execute from public
REVOKE EXECUTE ON FUNCTION calculate_total(NUMERIC) FROM PUBLIC;

-- Grant execute only to authenticated users
GRANT EXECUTE ON FUNCTION calculate_total(NUMERIC) TO authenticated;

Revoke Public Defaults on New Objects

Set default privileges to automatically revoke public access on new objects:

-- Revoke default privileges on tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE ALL ON TABLES FROM PUBLIC;

-- Revoke default privileges on functions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

-- Revoke default privileges on sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  REVOKE ALL ON SEQUENCES FROM PUBLIC;

Common Mistakes

• Granting to PUBLIC: Never grant permissions to PUBLIC unless absolutely necessary. Use authenticated or anon roles instead.
• Granting unnecessary operations: If users only need to read data, don't grant INSERT, UPDATE, or DELETE. RLS handles row-level access.
• Forgetting to revoke defaults: Use ALTER DEFAULT PRIVILEGES to prevent future objects from inheriting public permissions.
• Granting schema usage: Be careful with GRANT USAGE ON SCHEMA. Users need this to access objects, but don't grant it unnecessarily.
• Not auditing existing grants: Regularly check what permissions exist using pg_catalog views to ensure least privilege is maintained.

How to Audit Permissions

Use these queries to audit permissions in your database:

-- Find tables with public grants
SELECT 
  schemaname,
  tablename,
  grantee,
  privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'PUBLIC'
  AND schemaname = 'public'
ORDER BY tablename, privilege_type;

-- Find functions with public execute grants
SELECT 
  n.nspname AS schema_name,
  p.proname AS function_name,
  pg_get_userbyid(d.defaclrole) AS grantee
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_default_acl d ON d.defaclnamespace = n.oid
WHERE n.nspname = 'public'
  AND d.defaclobjtype = 'f';

Review the results and revoke any unnecessary public grants.