Right, let’s talk about the Principle of Least Privilege (PoLP). It’s not some abstract academic concept; it’s the single most effective thing you can do to secure your database. The core idea is laughably simple: a user (or a process) should only have the permissions absolutely necessary to do its job, and not a single byte more. In PostgreSQL, this isn’t just a good idea—it’s the entire point of the roles and privileges system. We’re going to build a fortress, not a wide-open field with a “Please Don’t Hack Me” sign.

Think about your application. Does the user that handles user logins really need to be able to drop tables? Of course not. That’s like giving the mailroom guy the keys to the CEO’s office and the corporate bank account. We’re going to stop doing that.

The Default State of Affairs is a Travesty

Here’s the first thing that will make you cringe: when you create a new database, a whole set of default privileges are granted to the PUBLIC role. The PUBLIC role is exactly what it sounds like—every single user in your system. Some of these defaults are…questionable.

-- Let's see what PUBLIC can do in a fresh database
SELECT * FROM information_schema.usage_privileges WHERE grantee = 'PUBLIC';

You’ll likely find that PUBLIC has USAGE on types like pg_trgm and languages like plpgsql. This means any user can create functions in these languages. Let that sink in. A random app user with basic connect privileges could, in theory, write and execute a plpgsql function to do all sorts of nasty things. We fix this by revoking these defaults and being intentional.

-- This is step one of securing any new database. Do it now.
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE myapp FROM PUBLIC;

-- Now, no one can do anything except superusers. It's a clean slate.

Building from the Ground Up: Application Roles

Now we start building back up, intentionally. We create roles for specific purposes. I like to think in terms of “access tiers”.

-- A role for our application to connect with. It can't log in itself; it's a group.
CREATE ROLE app_authenticator WITH NOLOGIN;

-- A role that has the right to read/write data in the app's schema
CREATE ROLE app_user WITH NOLOGIN;
GRANT CONNECT ON DATABASE myapp TO app_authenticator;
GRANT USAGE ON SCHEMA app TO app_authenticator, app_user;

-- Grant specific, minimal privileges to the app_user role
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO app_user;

-- Now, make our authenticator role able to assume the app_user role
GRANT app_user TO app_authenticator;

Now, your application connection pool connects using a specific login role that is a member of app_authenticator.

CREATE ROLE web_app_login WITH LOGIN PASSWORD 'supersecretpassword';
GRANT app_authenticator TO web_app_login;

Why the indirection? Because web_app_login can’t do anything but connect and then switch to the app_user role (via SET ROLE app_user;). Its permissions are limited to that single action. If your application’s connection string is ever compromised, the attacker can only become app_user—they can’t become a superuser or modify schema. You’ve contained the blast radius.

The Devil is in the Details: Column-Level Privileges

Sometimes SELECT on a whole table is too much. The users table might have a password_hash column that should only be accessible to a dedicated authentication function, not to general application queries.

-- First, grant SELECT on the table, but then...
GRANT SELECT ON TABLE app.users TO app_user;

-- ...revoke access to the sensitive column for that role.
REVOKE SELECT (password_hash) ON TABLE app.users FROM app_user;

-- Now, create a function that can access it, and grant execute to app_user
CREATE FUNCTION app_private.authenticate(/* ... */) RETURNS jwt_token SECURITY DEFINER AS $$
BEGIN
  -- ... function logic that uses password_hash ...
END;
$$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION app_private.authenticate TO app_user;

The SECURITY DEFINER clause is key here. It means the function runs with the privileges of the user who defined it, not the user who executed it. So you can define it as a superuser or a highly privileged role, but allow your low-privilege app_user to call it. This is the correct way to gatekeep sensitive operations.

The Pitfalls: Sequences, Future Objects, and the PUBLIC Schema

  1. Sequences: Remember to grant USAGE on sequences. An INSERT that uses nextval() will fail without it, even if you have INSERT on the table. It’s a common “why won’t this work?!” moment.

  2. Future Objects: The GRANT ... ON ALL TABLES ... syntax only affects existing tables. If you create a new table tomorrow, app_user won’t have access. You need to alter the default privileges for the role that creates the tables (usually your migration user).

    ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT USAGE ON SEQUENCES TO app_user;

  3. The public schema: Just don’t. Seriously. It’s a dumping ground. Create your own schemas (app, app_private, reporting) and be explicit about privileges. Revoke create on public from everyone if you can.

This might feel like a lot of ceremony, and it is. But it’s the difference between having a security strategy and just hoping nothing bad happens. The initial setup is a one-time cost. The peace of mind is permanent.