liveBook · Manning

Grant statement

As explained in Just Use Postgres!

The GRANT statement is an SQL command that assigns privileges or role membership to a database principal.

The GRANT statement assigns database privileges to a role. In PostgreSQL, it can permit database connections, schema access, table operations, and sequence use without granting permission to change database structure. section 2.8

Configure an application role

Create a login role, permit access to its database, and grant data privileges: CREATE ROLE coffee_chain_admin WITH LOGIN PASSWORD 'password'; GRANT CONNECT ON DATABASE coffee_chain TO coffee_chain_admin; GRANT USAGE ON SCHEMA products TO coffee_chain_admin; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA products TO coffee_chain_admin; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA products TO coffee_chain_admin; The role can query and update catalog data, generate sequence-based IDs, and still cannot create tables or drop tables it does not own. section 2.8

Listing 2.33 Setting up permissions for the role
GRANT USAGE ON SCHEMA public TO coffee_chain_admin;
GRANT USAGE ON SCHEMA products TO coffee_chain_admin;
GRANT USAGE ON SCHEMA customers TO coffee_chain_admin;
GRANT USAGE ON SCHEMA sales TO coffee_chain_admin;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES
  IN SCHEMA public TO coffee_chain_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES
  IN SCHEMA products TO coffee_chain_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES
  IN SCHEMA customers TO coffee_chain_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES
  IN SCHEMA sales TO coffee_chain_admin;

GRANT USAGE, SELECT ON ALL SEQUENCES
  IN SCHEMA products TO coffee_chain_admin;
GRANT USAGE, SELECT ON ALL SEQUENCES
  IN SCHEMA customers TO coffee_chain_admin;
GRANT USAGE, SELECT ON ALL SEQUENCES
  IN SCHEMA sales TO coffee_chain_admin;

Inspect the complete permission setup across public, products, customers, and sales schemas. section 2.8

How Just Use Postgres! covers it

How deep it goes
Demonstrates runnable grants and resulting access, stopping short of broader administration or privilege theory.
Examples
Uses coffee_chain_admin, product-catalog queries and updates, sequence IDs, and denied connections to brewery and postgres.
What it assumes
Assumes basic SQL and PostgreSQL role familiarity while demonstrating the permission model through concrete commands.
What you can do after
Operate/configure PostgreSQL roles with database, schema, table, and sequence privileges.
How it connects
Connects roles, database privileges, schemas, tables, sequences, and access control.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Also explained in

Compare how 3 books explain grant statement →

Related concepts

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.