skip to content

What does it mean to own a database object, what does the owner implicitly get that a grantee does not, and how would you decide who or what should own a production schema?

level: seniorimportance: should knowfreq 38%

answer

  1. owner = creator; ownership is a property, not a grant row
  2. implicit all privileges + ALTER/DROP + grant authority
  3. owner bypasses RLS unless FORCE ROW LEVEL SECURITY
  4. own with a NOLOGIN role; humans get membership
  5. set role at top of migration to avoid ownership drift

basics

~20 s

The owner is the role that created the object or was later assigned it. Owners implicitly hold every privilege on it, can ALTER and DROP it, can grant rights to others, and in most engines are exempt from row-level policies on it. Production objects should be owned by a dedicated NOLOGIN role that people and pipelines are granted membership in - never by an individual.

solid answer

~50 s

Ownership is an attribute stored with the object, initially the creating role. The owner implicitly has all privileges on it - no `GRANT` appears in the catalog - plus rights no grant can convey: `ALTER`, `DROP`, changing ownership, and granting privileges to others. In engines with row-level security the owner is normally exempt from policies on its own tables unless the table is explicitly set to force them. That makes ownership a much bigger deal than any single privilege, and it is why an application's runtime account should never own the tables it reads. For production I want ownership held by a dedicated `NOLOGIN` role, say `app_owner`. Humans and deploy pipelines are granted membership in it and act as it when doing DDL. That keeps ownership stable when people leave, avoids objects scattering across whoever ran a migration, and means offboarding is a membership revoke rather than a mass ownership reassignment. Ownership assignment belongs in the migration scripts so every environment matches.

code

sql · 8 lines
sql
CREATE ROLE app_owner NOLOGIN;
GRANT app_owner TO deploy_bot;    -- pipeline may act as owner

-- at the top of every migration, so objects land with a stable owner
SET ROLE app_owner;
CREATE SCHEMA IF NOT EXISTS app;
CREATE TABLE app.orders (id BIGINT PRIMARY KEY);
RESET ROLE;

go deeper

for a junior

State that the creator owns the object and that owners can do everything to it, including DROP, without needing grants.

for a middle

Add that ownership is implicit rather than granted, that owners can grant to others, and that the application account should therefore not own its tables.

for a senior

Cover the RLS exemption and FORCE, ownership by a NOLOGIN role with membership for pipelines and humans, setting the role inside migrations, and definer's-rights functions as controlled escalation.

for a principal

Define the ownership policy across services and environments, including drift detection, reassignment procedure, and the constraint that definer's-rights code escalates only to a purpose-built, non-superuser owner.

## What ownership is Every schema-level object - table, view, sequence, function, schema itself - carries an owner: the role that created it, unless ownership was reassigned. It is not a privilege in the grant table; it is a property of the object, which is why it does not show up when you list grants and is so often missed in access reviews. ## What the owner gets implicitly 1. **All object privileges**, without any GRANT existing. Revoking `SELECT` from the owner is meaningless - it is not held by grant. 2. **DDL rights**: `ALTER`, `DROP`, rename, add constraints, change ownership. 3. **Grant authority**: the owner can grant any privilege on the object to anyone, so ownership implies control of who else gets access. 4. **Policy exemption**: in engines with row-level security, the table owner bypasses policies on that table by default. PostgreSQL offers `ALTER TABLE ... FORCE ROW LEVEL SECURITY` to close this, which is essential if the owner is ever used for data access. 5. **Dependent-object authority**: dropping the owner role is blocked while it owns objects, so ownership also constrains role lifecycle. A grantee, by contrast, has exactly the verbs someone granted, cannot alter or drop the object, and (unless granted with grant option) cannot pass access on. ## Why this shapes design The practical implications follow directly: - **The runtime application account must not own its tables.** If it does, 'least privilege' is nominal: the account can drop the schema and bypasses RLS. This is the single most common finding in a database access review. - **Ownership by an individual is a liability.** People leave; the role gets dropped or disabled and either fails because it owns objects, or ownership is hastily reassigned during an outage. Worse, whichever human happened to run a migration becomes the owner of that table, so ownership is inconsistent across the schema and later ALTERs fail unpredictably in some environments but not others. - **Definer's-rights routines inherit the owner's power.** A function that executes with the privileges of its owner (SQL standard `SQL SECURITY DEFINER`, PostgreSQL `SECURITY DEFINER`) is a deliberate, controlled escalation path: callers need only `EXECUTE`. That is a useful pattern - and a dangerous one if the owner is over-privileged or if the function's search path is not pinned, since an attacker who can create objects earlier in the resolution path could hijack what the function calls. ## Choosing an owner The pattern that holds up: - Create a dedicated, `NOLOGIN`, non-superuser role - `app_owner` - per application or per schema. - The schema and every object in it are owned by that role. Migrations run as it, either by a pipeline account that is a member of it, or by explicitly setting the role at the start of the migration so objects land with the right owner rather than the runner's identity. - Humans who need to perform DDL are granted membership; ideally with non-inheriting membership so they must deliberately assume the role, giving an explicit elevation record. - Runtime and reporting roles are grantees only, never members of the owner role. Owning with a non-superuser role also limits definer's-rights functions defined in that schema: they escalate to `app_owner`, not to the whole cluster. ## Keeping it consistent Ownership drift is real and quiet. Guard against it: - Set the role explicitly at the top of migrations so ownership does not depend on who ran them. - Add a check (in CI or a periodic job) that every object in the schema has the expected owner; deviations mean someone created something out of band. - When ownership must change - a reorganisation, a role rename - engines provide bulk reassignment commands; use them deliberately rather than object by object. ## Interview framing The crisp statement is: *a grant says what a role may do to an object; ownership says the role controls the object.* Everything else - implicit privileges, DDL, grant authority, RLS exemption - follows from that. And the design conclusion is that ownership should be held by a stable, non-login, purpose-built role that people and pipelines borrow, not by any identity that serves traffic or belongs to a person.

  • A team enabled row-level security on a table but the application still sees every row. What is the first thing you check?
    Whether the application connects as the table's owner. Owners are exempt from row-level policies by default, so the policies exist but never apply. The fix is to run the app as a non-owner role with explicit grants, and additionally to FORCE row-level security on the table so even the owner is subject to the policies.
  • Why is a NOLOGIN role a better owner than the deploy pipeline's own login account?
    Because the pipeline account will be rotated, renamed or replaced, and ownership would have to be reassigned each time - during which some objects inevitably get missed. A NOLOGIN owner role is stable for the life of the schema; the pipeline is simply granted membership in it, so credentials can change freely without touching ownership. It also makes definer's-rights functions escalate to a purpose-built identity rather than to a pipeline credential.

saying these in an interview costs you the question

  • Thinking the owner's access can be removed with REVOKE - the owner's privileges are implicit, not granted.
  • Enabling row-level security while the application connects as the table owner, so policies silently do not apply.
  • Letting whichever human or CI account ran the migration become the owner, producing inconsistent ownership across environments.
  • Creating definer's-rights functions owned by a superuser or by an over-privileged role without pinning the search path.

context

resolving links…