Edmondo's Vault

Extensions and Grants

May 18, 20265 min read

Extensions and Grants

CREATE EXTENSION — what it actually does

The word “CREATE” is misleading. CREATE EXTENSION does not install software onto the server. The extension’s shared library (.so / .dylib) and SQL script are already present on the filesystem, placed there by the OS package manager (apt install postgresql-16-pgvector) or compiled from source. Some extensions also require listing in shared_preload_libraries — a postgresql.conf parameter that lists libraries to load into every Postgres backend at server startup, before any database connects — (e.g. pg_stat_statements), which requires a server restart.

What CREATE EXTENSION actually does:

  1. Registers the extension in the current database’s catalog (pg_extension).
  2. Runs the extension’s install SQL script, which creates types, functions, operators, and casts into the database.

This is catalog registration, not software installation. If the .so file is missing from the filesystem, CREATE EXTENSION fails — it cannot fetch or compile anything.

This is why managed Postgres services — RDS (Amazon Relational Database Service), Cloud SQL (Google Cloud's managed Postgres/MySQL), AlloyDB (Google's Postgres-compatible database with a custom storage layer) — ship a curated list of supported extensions. The .so files are pre-installed on the host. You still run CREATE EXTENSION per-database to register them.

Extension lifecycle

-- Idempotent registration (safe in migrations)
CREATE EXTENSION IF NOT EXISTS vector;
 
-- Pin to a specific schema
CREATE EXTENSION IF NOT EXISTS vector SCHEMA public;
 
-- Upgrade to a newer version (after the .so is updated on disk)
ALTER EXTENSION vector UPDATE;
ALTER EXTENSION vector UPDATE TO '0.8.0';
 
-- Remove from catalog (does not delete the .so)
DROP EXTENSION vector;
DROP EXTENSION vector CASCADE;  -- also drops dependent objects

Placing extensions in a dedicated schema (e.g. extensions) keeps public clean, but most teams use public for simplicity. pgvector operators work regardless of schema as long as search_path includes it.

Trusted vs untrusted extensions

Since Postgres 13, extensions can declare themselves as trusted. A trusted extension can be installed by any user who has CREATE privilege on the current database — no superuser required.

pgvector is trusted. So are hstore (key-value store type for Postgres), citext (case-insensitive text type), pgcrypto (cryptographic functions: hashing, encryption, UUID generation), and most pure-SQL extensions.

Untrusted extensions (e.g. plpythonu — PL/Python untrusted, allows running arbitrary Python including filesystem access; dblink — allows connecting to other Postgres databases and executing queries) can execute arbitrary code or access the filesystem, so they require superuser.

On managed Postgres, you typically don't have superuser. You get rds_superuser (RDS — a privileged role that has most superuser capabilities except those that could compromise the managed service's isolation) or cloudsqlsuperuser (Cloud SQL's equivalent), which can install trusted extensions but not untrusted ones. Always check the provider's supported extensions list.

GRANT best practices with DDL

Should grants live in the same migration file as the DDL, or in a separate file?

Argument for together: the table and its permissions are a single unit of intent. If you create a table in migration N but add grants in migration N+5, there’s a window where the table exists but the app role can’t use it. Deployments that run migrations then immediately serve traffic will break.

Argument for separate: grants are access policy, DDL is structure. Different review audiences (security team vs backend engineers).

Pragmatic answer: grants in the same migration file, right after the DDL. This is the dominant pattern in Rails, Django, and most migration frameworks. A typical migration file follows this pattern:

CREATE TABLE IF NOT EXISTS content_chunks ( ... );
CREATE INDEX IF NOT EXISTS content_chunks_embedding_idx ON content_chunks ...;
GRANT SELECT, INSERT, UPDATE, DELETE ON content_chunks TO app_role;

If your organisation requires separate security review, use a single migration file but separate the DDL and grants with a clear comment block. The reviewer can focus on the grants section without needing a separate file.

Principle of least privilege

Two roles, two levels of power:

RolePurposePrivileges
Migration role (or superuser)Runs schema changesCREATE, ALTER, DROP, CREATE EXTENSION
App role (app_role)Runtime queriesSELECT, INSERT, UPDATE, DELETE

Never grant ALL PRIVILEGES to the app role. If the app can DROP TABLE, a SQL injection can drop your tables.

GRANT ALL PRIVILEGES includes TRUNCATE, REFERENCES, and TRIGGER — none of which an app role should need. Be explicit about the four DML (Data Manipulation Language) verbs: SELECT, INSERT, UPDATE, DELETE.

Common grant patterns

-- 1. Let the app role look up objects in the schema
GRANT USAGE ON SCHEMA myschema TO app_role;
 
-- 2. Grant DML on all existing tables
GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES IN SCHEMA myschema TO app_role;
 
-- 3. Grant DML on future tables (crucial)
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

What GRANT USAGE ON SCHEMA actually does

USAGE grants the ability to look up objects in the schema — without it, the role cannot even reference a table by name (it gets “permission denied for schema”). USAGE does not grant any access to the data inside those objects. You still need separate GRANT SELECT, INSERT, etc. on individual tables. Think of it as access to the building directory: you can see which rooms (tables) exist, but you need separate keys (DML grants) to enter them.

USAGE is not required by GRANT SELECT — you can grant SELECT on a specific table without schema-level USAGE, and the role can query that table using the fully-qualified name (schema.table). But without USAGE, the role can’t discover other objects in the schema, and search_path resolution won’t find them. In practice, always grant USAGE first.

Pattern 3 ( ALTER DEFAULT PRIVILEGES) is the one people forget. Without it, every new table requires an explicit GRANT. With it, any table created in that schema by the migration role automatically gets the right permissions. Set it once in your bootstrap migration.

Note that ALTER DEFAULT PRIVILEGES applies to objects created by the role that runs the statement. If your migration role is migration_role, run ALTER DEFAULT PRIVILEGES as that role — otherwise the defaults won’t apply.

See also

Graph View

Backlinks