PostgreSQL Roles and Privileges: An Oracle DBA Translation

One Object to Rule Them: the ROLE

Oracle separates users (who log in) from roles (bundles of privileges). PostgreSQL collapses both into a single object — a ROLE. A role with the LOGIN attribute is a user; a role without it behaves like an Oracle role. CREATE USER is literally just an alias for CREATE ROLE … LOGIN. Once that clicks, the rest of the model falls into place.

-- a "user"
CREATE ROLE app_user LOGIN PASSWORD '…';

-- a "role" (privilege bundle) — no LOGIN
CREATE ROLE readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO readonly;

-- membership replaces Oracle "GRANT role TO user"
GRANT readonly TO app_user;

Membership, INHERIT, and SET ROLE

A member role’s privileges apply automatically if the member has INHERIT (the default). Create a role NOINHERIT and it must explicitly SET ROLE to use a granted role’s rights — useful for privileged accounts that should operate least-privilege by default and elevate deliberately. SET ROLE is your new “become this identity for the session”.

There are no profiles

Oracle profiles (password lifetime, failed-login lockout, resource limits) don’t exist as a single object. Their pieces live elsewhere:

• Authentication & password policy → pg_hba.conf plus an extension like passwordcheck; password expiry via VALID UNTIL.
• Connection limits → ALTER ROLE app_user CONNECTION LIMIT 20;
• Resource control → set at the server / pooler level, not per profile.

The footgun: PUBLIC and the public schema

This is the one that bites migrated databases. Every role is implicitly a member of PUBLIC, and historically the public schema granted CREATE and USAGE to PUBLIC by default — meaning any user could create objects there and see it. Lock it down explicitly:

-- stop everyone from creating objects in public
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- and don't hand out data via PUBLIC by accident
REVOKE ALL ON DATABASE appdb FROM PUBLIC;

The piece everyone forgets: default privileges

Grants apply to objects that exist now. A table created tomorrow by another role isn’t covered by today’s GRANT. ALTER DEFAULT PRIVILEGES fixes the future so your readonly role keeps working as the schema grows — the single most common “why can’t my app see the new table” cause post-migration.

ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT ON TABLES TO readonly;

Finally: a PostgreSQL superuser bypasses all permission checks (broader than a single Oracle role) — grant it as sparingly as you would SYSDBA, and prefer specific privileges plus role membership for everything else.