PostgreSQL Roles, Permissions and Your First Application Schema

Before writing application code, the most important database design step is creating the initial schema for your specific application. For this series, the application is a blog API — users write posts, posts have tags, users leave comments. A well-designed initial schema with appropriate data types, constraints, indexes, and permissions makes every subsequent chapter easier: SQLAlchemy models map cleanly to well-structured tables, queries are fast because indexes exist, and security is correct because the application role has minimal permissions. This lesson designs, creates, and validates the complete blog database schema used throughout the rest of the series.

PostgreSQL Roles and Permissions #

-- ── Connect as superuser first ────────────────────────────────────────────────
-- sudo -u postgres psql  (Linux)
-- or: psql -U postgres  (if password set)

-- Create application role with login but no superuser privileges
CREATE ROLE blog_app WITH
    LOGIN
    PASSWORD 'change_me_in_production'
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    NOINHERIT;

-- Create the database owned by our app role
CREATE DATABASE blog_dev OWNER blog_app;

-- Grant all privileges on the database itself
GRANT ALL PRIVILEGES ON DATABASE blog_dev TO blog_app;

-- ── Connect to the new database as superuser to set up schema ─────────────────
\c blog_dev

-- Grant schema-level privileges (PostgreSQL 15+: public schema access is restricted)
GRANT USAGE ON SCHEMA public TO blog_app;
GRANT CREATE ON SCHEMA public TO blog_app;

-- Or after creating tables, grant table-level access:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO blog_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO blog_app;

-- Make future tables automatically accessible to blog_app
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO blog_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO blog_app;

The Blog Application Schema #

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "pgcrypto";   -- for gen_random_uuid()

-- ── users ─────────────────────────────────────────────────────────────────────
CREATE TABLE users (
    id           BIGSERIAL    PRIMARY KEY,
    public_id    UUID         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    email        TEXT         NOT NULL UNIQUE,
    name         TEXT         NOT NULL,
    password_hash TEXT        NOT NULL,
    role         TEXT         NOT NULL DEFAULT 'user'
                 CHECK (role IN ('user', 'editor', 'admin')),
    is_active    BOOLEAN      NOT NULL DEFAULT TRUE,
    created_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email     ON users(email);
CREATE INDEX idx_users_public_id ON users(public_id);

-- ── posts ─────────────────────────────────────────────────────────────────────
CREATE TABLE posts (
    id          BIGSERIAL    PRIMARY KEY,
    public_id   UUID         NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    author_id   BIGINT       NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title       TEXT         NOT NULL CHECK (LENGTH(TRIM(title)) >= 3),
    slug        TEXT         NOT NULL CHECK (slug ~ '^[a-z0-9-]+$'),
    body        TEXT         NOT NULL,
    excerpt     TEXT,
    status      TEXT         NOT NULL DEFAULT 'draft'
                CHECK (status IN ('draft', 'published', 'archived')),
    view_count  INTEGER      NOT NULL DEFAULT 0 CHECK (view_count >= 0),
    published_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    UNIQUE (author_id, slug)
);

CREATE INDEX idx_posts_author_id  ON posts(author_id);
CREATE INDEX idx_posts_status     ON posts(status);
CREATE INDEX idx_posts_slug       ON posts(slug);
CREATE INDEX idx_posts_public_id  ON posts(public_id);

-- ── tags ──────────────────────────────────────────────────────────────────────
CREATE TABLE tags (
    id         BIGSERIAL PRIMARY KEY,
    name       TEXT      NOT NULL UNIQUE CHECK (name = LOWER(name)),
    slug       TEXT      NOT NULL UNIQUE
);

-- ── post_tags (many-to-many junction) ────────────────────────────────────────
CREATE TABLE post_tags (
    post_id    BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id     BIGINT NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX idx_post_tags_tag_id ON post_tags(tag_id);

-- ── comments ─────────────────────────────────────────────────────────────────
CREATE TABLE comments (
    id         BIGSERIAL    PRIMARY KEY,
    post_id    BIGINT       NOT NULL REFERENCES posts(id)  ON DELETE CASCADE,
    author_id  BIGINT       NOT NULL REFERENCES users(id)  ON DELETE CASCADE,
    body       TEXT         NOT NULL CHECK (LENGTH(TRIM(body)) >= 1),
    is_approved BOOLEAN     NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_comments_post_id   ON comments(post_id);
CREATE INDEX idx_comments_author_id ON comments(author_id);

Automatic updated_at with a Trigger #

-- PostgreSQL does not auto-update updated_at — we need a trigger function

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply the trigger to each table that has updated_at
CREATE TRIGGER users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER posts_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER comments_updated_at
    BEFORE UPDATE ON comments
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Verify the Schema #

-- Check all tables were created
\dt

-- Describe each table
\d users
\d posts
\d tags
\d post_tags
\d comments

-- Test: insert a user and post
INSERT INTO users (email, name, password_hash, role)
VALUES ('alice@example.com', 'Alice Smith', 'hash_here', 'admin')
RETURNING id, public_id, email;

-- Use the returned id for the post
INSERT INTO posts (author_id, title, slug, body, status)
VALUES (1, 'Hello World', 'hello-world', 'My first post body.', 'published')
RETURNING id, public_id, title;

-- Verify FK constraint: this should fail
INSERT INTO posts (author_id, title, slug, body)
VALUES (9999, 'Orphan Post', 'orphan', 'Body here');
-- ERROR: insert or update violates foreign key constraint

Common Mistakes #

Mistake 1 — No indexes on foreign key columns #

❌ Wrong — foreign key without index causes slow queries:

-- Querying all posts by author_id=5 scans every row without an index
SELECT * FROM posts WHERE author_id = 5;   -- sequential scan on large table!

✅ Correct — create index immediately after FK:

CREATE INDEX idx_posts_author_id ON posts(author_id);   -- ✓

Mistake 2 — Forgetting to create sequences for application role #

❌ Wrong — blog_app cannot use BIGSERIAL sequences:

-- INSERT fails because blog_app lacks USAGE on the sequence

✅ Correct — grant sequence privileges:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO blog_app;   -- ✓

Mistake 3 — Using the superuser role in the app connection string #

❌ Wrong:

DATABASE_URL=postgresql://postgres:secret@localhost/blog_dev

✅ Correct — application role with minimum needed permissions:

DATABASE_URL=postgresql://blog_app:password@localhost/blog_dev   # ✓

Quick Reference — Schema Commands #