Pebellslearn by building
0%
RoadmapsMatrixCoursesSandboxSign in
Security  ›  Lesson

Granting and Revoking Privileges

Grant / Revoke 14 minAccess needs a privilege at every layer — CONNECT on the database, USAGE on the schema and the right verb on the object — and default privileges cover objects created later.

Granting and Revoking Privileges

Ravi joined the analysts group yesterday, yet his first query fails with permission denied for schema billing. The group was granted SELECT on the tables. What is missing? PostgreSQL checks privileges at every layer of the object tree, and a grant at one layer does nothing if an outer door is shut.

The rule

Open every door on the path: database, schema, then object.

To read billing.invoices, a role needs:

  1. CONNECT on the database pedal.
  2. USAGE on the schema billing (to find anything inside it).
  3. SELECT on the table billing.invoices.
GRANT CONNECT ON DATABASE pedal TO analysts;
GRANT USAGE ON SCHEMA billing TO analysts;
GRANT SELECT ON ALL TABLES IN SCHEMA billing TO analysts;

The table privileges are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES and TRIGGER (plus MAINTAIN from version 17). Schemas have USAGE and CREATE. REVOKE takes any of them away:

REVOKE DELETE ON public.rentals FROM pedal_app;

Four rules explain most surprises:

  • The owner of an object has every privilege on it and is the only one (apart from superusers) who can ALTER or DROP it.
  • PUBLIC means every role. By default, PUBLIC may connect to any new database. Since PostgreSQL 15 it can no longer create objects in the public schema.
  • ON ALL TABLES IN SCHEMA covers only tables that exist now. Tomorrow's new table gets nothing.
  • Default privileges fix that. They say what future objects created by a given role will grant:
ALTER DEFAULT PRIVILEGES FOR ROLE pedal_owner IN SCHEMA billing
  GRANT SELECT ON TABLES TO analysts;

\dp billing.* in psql shows each table's access list, such as analysts=r/pedal_owner: analysts can read (r), granted by pedal_owner.

The shortcut

When you meet permission denied for X, read what X is. The message names the door that is shut: a database, a schema or a table. Then walk the path from the outside in.

Fixing Ravi's accessStep 1 of 4

  1. Read the error

    "permission denied for schema billing": the schema door, not the table.

The trap is fixing access with GRANT ALL. It hands over TRUNCATE and DELETE along with reading. Grant exactly the verbs the job needs.

Drills

Answer fast — trust the rule.

quick drill

A role has SELECT on billing.invoices but gets permission denied for schema billing. What is missing?

quick drill

You ran GRANT SELECT ON ALL TABLES IN SCHEMA public TO analysts; last week. A table created today is unreadable to them. Why?

quick drill

Who can run DROP TABLE rentals without a superuser?

quick drill

The app must add and read rentals but never remove them. Which grant fits?

Remember

  • Open every door on the path: database, schema, then object.
  • The error names the shut door; walk from the outside in.
  • ON ALL TABLES is for today; default privileges are for tomorrow.

Practice — level up

0/1 solved

Write your answer, then compare it with a rubric and a model answer.

ExplainGive read-only access that lasts

Write the statements that give the analysts group read-only access to every current and future table in the billing schema of the pedal database, and explain what each statement opens and why GRANT ALL would be wrong.