Roles, Databases, and Least-Privilege Grants

The postgres superuser is for install day, not for application day. Every connection that reaches the cluster should prove who it is and should be able to do only the work that role exists for. PostgreSQL does not have users and groups as separate objects. It has roles. A role can log in, own objects, and be a member of other roles. Least privilege is the combination of those three facts: one login for the app, one owner for migrations, no PUBLIC grants left on the schema the app uses.

This walkthrough assumes PostgreSQL 16 on Ubuntu, bound to a private address as in the install guide. You will create a database, three roles, and a tight grant set you can re-run after each migration.

Superuser stays local

Remote application traffic should never use the postgres role. Leave it for peer auth on the unix socket. Create a login role for humans only if you must, with a password stored in .pgpass on the admin workstation, not in the app config. The application gets its own login, and migrations get a separate owner login that is not in the runtime connection string.

-- run as postgres on the unix socket
CREATE ROLE app_owner LOGIN PASSWORD 'replace_owner_pw' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE ROLE app_runtime LOGIN PASSWORD 'replace_runtime_pw' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE ROLE app_readonly LOGIN PASSWORD 'replace_ro_pw' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE DATABASE appdb OWNER app_owner ENCODING 'UTF8' TEMPLATE template0 LC_COLLATE 'C' LC_CTYPE 'C';
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app_owner, app_runtime, app_readonly;

template0 plus C locale keeps indexes and LIKE behaviour predictable across restores. REVOKE ALL FROM PUBLIC on the database is the first least-privilege step people skip. CONNECT is then an explicit grant, not an accident of the default template.

Schema grants, not table-by-table hope

Connect to appdb as app_owner and create a schema the runtime role can use. Do not put application tables in public. public still exists, and PUBLIC still has create rights on it in older clusters. Revoke that. Grant USAGE on the schema and the default privileges so tables created later inherit the same runtime grants.

-- connect to appdb as app_owner
REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_runtime, app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_runtime;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_runtime;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT ON SEQUENCES TO app_readonly;
CREATE TABLE app.orders (
  id bigserial PRIMARY KEY,
  customer_id bigint NOT NULL,
  total_cents integer NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Default privileges apply to objects created by the role that ran ALTER DEFAULT PRIVILEGES. If a human superuser creates a table later, the runtime role will not see it. That is why migrations should connect as app_owner, not as postgres. The owner owns the objects; the runtime role only DML; the readonly role only SELECT.

What the app must never be able to do

app_runtime should fail at CREATE TABLE, DROP TABLE, CREATE ROLE, and CREATE DATABASE. Test that on purpose. A connection string with extra rights will pass load tests and fail during an incident when a bug issues a migration at runtime.

Granting SELECT, INSERT, UPDATE, DELETE is still broad. For a service that only inserts orders, grant INSERT and SELECT on app.orders and skip UPDATE until a use case exists. Column-level grants are available when a table mixes public fields and secrets. Start with table-level grants that match the service, then tighten columns when a table grows a sensitive field.

Roles can be members of other roles. That is useful for a human admin who needs to SET ROLE app_owner during a migration window. It is not useful for the runtime login. Do not GRANT app_owner TO app_runtime. Membership would let the app become the owner.

pg_hba.conf should list the runtime role from the app host only, the owner role from the admin host only, and scram-sha-256 for both. A correct grant set with a wide pg_hba is still an open database. Pair this post with the pg_hba guide before you open 5432 even on the private NIC.

Search_path is part of privilege. Set it per role so unqualified names resolve to app and not to public.

ALTER ROLE app_runtime IN DATABASE appdb SET search_path = app, public;
ALTER ROLE app_readonly IN DATABASE appdb SET search_path = app, public;
ALTER ROLE app_owner IN DATABASE appdb SET search_path = app, public;
-- prove the runtime cannot migrate
SET ROLE app_runtime;
CREATE TABLE app.should_fail (id int);

The CREATE TABLE should error with permission denied. If it succeeds, the runtime role is still an owner or still has CREATE on the schema. GRANT CREATE ON SCHEMA is for the owner only.

Inventory the grants you actually have

Guessing is how PUBLIC creeps back. After every migration, list table grants in app and confirm PUBLIC has nothing.

SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'app'
ORDER BY table_name, grantee, privilege_type;
SELECT nspname, nspacl FROM pg_namespace WHERE nspname IN ('app', 'public');

Keep a SQL file in the repo that creates roles, database, schema, and default privileges. Apply it on a staging VPS the same way you apply it in Lucknow DC01. Role names and grant sets should be identical; passwords should not.

Takeaway

Three roles, one database, one application schema, PUBLIC revoked. Migrations use the owner; the app uses a DML login; reports use SELECT. That split is the whole model. Stand up a Lucknow DC01 Ubuntu instance on Netbay in under 60 seconds and apply this grant file before the first migration — netbayhosts.in.