liveBook · Manning

Revoke statement

As explained in Just Use Postgres!

The REVOKE statement is an SQL command that removes privileges or role membership from a database principal.

REVOKE removes a database privilege from a role or group. In PostgreSQL, revoking CONNECT from PUBLIC restricts database access for roles that have not received an explicit grant. section 2.8

Block access to other databases

REVOKE CONNECT ON DATABASE brewery FROM PUBLIC; REVOKE CONNECT ON DATABASE postgres FROM PUBLIC; A role with access to coffee_chain can still connect there, but attempts to connect to brewery or postgres fail with “permission denied for database” and “User does not have CONNECT privilege.” section 2.8

Use REVOKE to:

  • Remove inherited access granted through PUBLIC. section 2.8
  • Limit a role’s connections to its intended database while retaining explicitly granted access. section 2.8
Listing 2.34 Revoking access on the brewery and postgres databases
REVOKE CONNECT ON DATABASE brewery FROM PUBLIC;
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;

View the complete commands that revoke CONNECT on the brewery and postgres databases. section 2.8

How Just Use Postgres! covers it

How deep it goes
Demonstrates database-level CONNECT revocation and verifies failures, but does not detail broader privilege-revocation syntax.
Examples
Uses coffee_chain_admin, brewery, postgres, and failed psql connection attempts as concrete access-control examples.
What it assumes
Assumes familiarity with PostgreSQL roles, databases, privileges, and GRANT.
What you can do after
Operate/configure PostgreSQL access by revoking CONNECT from PUBLIC and checking resulting connection errors.
How it connects
Connects REVOKE with roles, PUBLIC, GRANT, database connectivity, and privileges.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.