liveBook · Manning

Create role statement

As explained in Just Use Postgres!

The CREATE ROLE statement is a PostgreSQL SQL command that creates a database role and can assign role attributes.

CREATE ROLE creates a PostgreSQL database role and can assign connection attributes such as LOGIN. A role can represent an application backend that connects on a user’s behalf while remaining unable to change database structure. section 2.8

Create an application role

CREATE ROLE coffee_chain_admin WITH LOGIN PASSWORD 'password'; GRANT CONNECT ON DATABASE coffee_chain TO coffee_chain_admin; REVOKE CONNECT ON DATABASE coffee_chain FROM PUBLIC; CREATE ROLE establishes the identity; GRANT and REVOKE control database connectivity. Additional grants can provide schema, table, and sequence access without granting ownership or structural control. section 2.8

Listing 2.32 Creating an admin-level role for the coffee chain
CREATE ROLE coffee_chain_admin WITH LOGIN PASSWORD 'password';

GRANT CONNECT ON DATABASE coffee_chain TO coffee_chain_admin;

REVOKE CONNECT ON DATABASE coffee_chain FROM PUBLIC;

Use the complete SQL listing to create the coffee-chain administrator role and restrict database connectivity. 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;

Follow the role creation with schema, table, and sequence privileges needed for catalog and business-data operations. section 2.8

How Just Use Postgres! covers it

How deep it goes
Explains role creation, LOGIN, connectivity grants, and delegated data permissions, but stops short of broader role administration.
Examples
Uses coffee_chain_admin to query and update catalog data while rejecting table drops, table creation, and unrelated database connections.
What it assumes
Assumes basic PostgreSQL database access and SQL familiarity while demonstrating role connectivity and object privileges concretely.
What you can do after
Operate/configure a PostgreSQL role and verify its connection, data, and structural permissions with psql.
How it connects
Connects CREATE ROLE with PostgreSQL roles, LOGIN attributes, GRANT, REVOKE, schemas, tables, and sequences.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Also explained in

Compare how 2 books explain create role 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.