🔐 Production-ready PostgreSQL script for complete multi-tenant database isolation - creates users that can only access their own database, compatible with all migration frameworks (Drizzle, Prisma, TypeORM, etc.)

create-isolated-database-user.sql

-- PostgreSQL Isolated Database and User Creation Template
-- 
-- This SQL script creates completely isolated PostgreSQL users and databases
-- for multi-tenant applications with proper security boundaries.
--
-- FEATURES:
-- - Complete database isolation (users can only access their own database)
-- - Compatible with migration frameworks (Drizzle, Prisma, TypeORM, etc.)
-- - Retroactive isolation cleanup (fixes previously created databases)
-- - Comprehensive privilege management for schema migrations
-- - Follows principle of least privilege
--
-- SECURITY MODEL:
-- - Users created WITHOUT superuser privileges
-- - CONNECT privileges revoked from PUBLIC on all databases
-- - Bidirectional isolation: new users can't access old DBs, old users can't access new DBs
-- - Master user retains full access for administration
--
-- USAGE:
-- 1. Replace the placeholder variables with actual values:
--    - :username - The database username to create
--    - :password - The password for the user
--    - :database_name - The database name to create
--    - :master_username - The master/admin username (usually 'postgres')
--    - :default_database - The default database to connect to (usually 'postgres')
--
-- 2. Execute with psql:
--    psql -h hostname -p port -U master_user -d default_db -f create-isolated-database-user.sql \
--         -v username=myuser -v password=mypass -v database_name=mydb \
--         -v master_username=postgres -v default_database=postgres
--
-- PREREQUISITES:
-- - PostgreSQL 9.1+ (for CREATE ROLE syntax)
-- - Master/admin user with sufficient privileges
-- - Connection to the PostgreSQL instance

-- =============================================================================
-- STEP 1: CREATE USER WITH MINIMAL PRIVILEGES
-- =============================================================================

-- Create the user with LOGIN privilege but no elevated roles
-- Note: Use NOINHERIT to prevent inheriting privileges from PUBLIC role
-- Do NOT grant superuser privileges as they override database connection restrictions
CREATE ROLE :username WITH LOGIN PASSWORD :'password' NOINHERIT;

-- =============================================================================
-- STEP 2: REVOKE GLOBAL DATABASE ACCESS FROM PUBLIC
-- =============================================================================

-- Revoke connect privilege from PUBLIC on all existing databases
-- This is the key step that prevents database switching
REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;
REVOKE CONNECT ON DATABASE :default_database FROM PUBLIC;

-- =============================================================================
-- STEP 3: COMPREHENSIVE CLEANUP - FIX ISOLATION ON EXISTING DATABASES
-- =============================================================================

-- This ensures that existing databases also have proper isolation
-- and prevents cross-database access between users
DO $$
DECLARE
    db_name_var TEXT;
    user_name_var TEXT;
    db_owner_name TEXT;
BEGIN
    -- For each existing database (except system databases and the new one)
    FOR db_name_var IN
        SELECT datname FROM pg_database
        WHERE datname NOT IN ('template0', 'template1', 'postgres', :'default_database', :'database_name')
        AND datname NOT LIKE 'admin_%'
    LOOP
        -- Get the actual owner of this database
        SELECT rolname INTO db_owner_name
        FROM pg_database d
        JOIN pg_roles r ON d.datdba = r.oid
        WHERE d.datname = db_name_var;

        -- Revoke CONNECT from PUBLIC on this database
        EXECUTE 'REVOKE CONNECT ON DATABASE ' || quote_ident(db_name_var) || ' FROM PUBLIC';

        -- Revoke CONNECT from all users except the actual database owner and master user
        FOR user_name_var IN
            SELECT usename FROM pg_user
            WHERE usename != :'master_username'
            AND usename NOT LIKE 'admin_%'
            AND usename != 'postgres'
            AND usename != COALESCE(db_owner_name, 'none')  -- Exclude the actual database owner
        LOOP
            EXECUTE 'REVOKE CONNECT ON DATABASE ' || quote_ident(db_name_var) || ' FROM ' || quote_ident(user_name_var);
        END LOOP;
    END LOOP;
END
$$;

-- =============================================================================
-- STEP 4: PREVENT NEW USER FROM ACCESSING EXISTING DATABASES
-- =============================================================================

-- Revoke CONNECT from the new user on all existing databases
-- This ensures the new user can only access their own database
DO $$
DECLARE
    db_name_var TEXT;
BEGIN
    FOR db_name_var IN
        SELECT datname FROM pg_database
        WHERE datname NOT IN ('template0', :'database_name')  -- Exclude template0 and new database
        AND datname NOT LIKE 'admin_%'  -- Exclude any admin databases
    LOOP
        EXECUTE 'REVOKE CONNECT ON DATABASE ' || quote_ident(db_name_var) || ' FROM ' || quote_ident(:'username');
    END LOOP;
END
$$;

-- =============================================================================
-- STEP 5: CREATE THE NEW DATABASE
-- =============================================================================

-- Create the database
CREATE DATABASE :database_name;

-- Revoke CONNECT from PUBLIC on the new database
REVOKE CONNECT ON DATABASE :database_name FROM PUBLIC;

-- =============================================================================
-- STEP 6: PREVENT EXISTING USERS FROM ACCESSING NEW DATABASE
-- =============================================================================

-- Revoke CONNECT from all existing users on the new database (except master user)
-- This ensures complete isolation between database users
DO $$
DECLARE
    user_name TEXT;
BEGIN
    FOR user_name IN
        SELECT usename FROM pg_user
        WHERE usename != :'master_username'
        AND usename != :'username'
        AND usename NOT LIKE 'admin_%'  -- Skip admin/system users
        AND usename != 'postgres'   -- Skip postgres user if it exists
    LOOP
        EXECUTE 'REVOKE CONNECT ON DATABASE ' || quote_ident(:'database_name') || ' FROM ' || quote_ident(user_name);
    END LOOP;
END
$$;

-- =============================================================================
-- STEP 7: GRANT ACCESS TO THE NEW USER
-- =============================================================================

-- Grant connect privilege for the user to ONLY their specific database
GRANT CONNECT ON DATABASE :database_name TO :username;

-- Grant connect privileges back only to the master user for existing databases
GRANT CONNECT ON DATABASE template1 TO :master_username;
GRANT CONNECT ON DATABASE postgres TO :master_username;
GRANT CONNECT ON DATABASE :default_database TO :master_username;

-- =============================================================================
-- STEP 8: CONFIGURE SCHEMA PERMISSIONS (RUN IN NEW DATABASE)
-- =============================================================================

-- Connect to the new database to set up schema permissions
\c :database_name

-- Grant usage on the public schema
GRANT USAGE ON SCHEMA public TO :username;

-- Grant all privileges on all tables in the public schema to the new user
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO :username;

-- Grant all privileges on all sequences in the public schema to the new user
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO :username;

-- Grant all privileges on all functions in the public schema (needed for some migrations)
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO :username;

-- Set default privileges for future objects (important for migrations)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO :username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO :username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO :username;

-- Allow the user to create objects in their database (essential for migrations)
GRANT CREATE ON DATABASE :database_name TO :username;
GRANT CREATE ON SCHEMA public TO :username;

-- =============================================================================
-- STEP 9: VERIFICATION QUERIES
-- =============================================================================

-- Verify user creation
SELECT 
    usename as username,
    usecreatedb as can_create_db,
    usesuper as is_superuser,
    userepl as can_replicate
FROM pg_user 
WHERE usename = :'username';

-- Verify database creation
SELECT 
    datname as database_name,
    pg_get_userbyid(datdba) as owner
FROM pg_database 
WHERE datname = :'database_name';

-- Verify database connection permissions
SELECT 
    d.datname as database_name,
    r.rolname as role_name,
    has_database_privilege(r.rolname, d.datname, 'CONNECT') as can_connect
FROM pg_database d
CROSS JOIN pg_roles r
WHERE d.datname NOT IN ('template0')
AND d.datname NOT LIKE 'admin_%'
AND r.rolname = :'username'
ORDER BY d.datname;

-- =============================================================================
-- USAGE EXAMPLES
-- =============================================================================

/*
-- Example 1: Create user 'myapp_user' with database 'myapp_db'
psql -h localhost -p 5432 -U postgres -d postgres -f create-isolated-database-user.sql \
     -v username=myapp_user \
     -v password=secure_password_123 \
     -v database_name=myapp_db \
     -v master_username=postgres \
     -v default_database=postgres

-- Example 2: Create user for cloud PostgreSQL service
psql -h myinstance.example.com -p 5432 -U master_user -d myapp -f create-isolated-database-user.sql \
     -v username=client1_user \
     -v password=$(uuidgen) \
     -v database_name=client1_db \
     -v master_username=master_user \
     -v default_database=myapp

-- Example 3: Test isolation after creation
-- This should succeed (user accessing their own database)
psql -h hostname -p 5432 -U myapp_user -d myapp_db -c "SELECT current_database(), current_user;"

-- This should fail (user trying to access other database)
psql -h hostname -p 5432 -U myapp_user -d postgres -c "SELECT 1;"
*/

-- =============================================================================
-- MIGRATION FRAMEWORK COMPATIBILITY NOTES
-- =============================================================================

/*
This script is compatible with:

1. DRIZZLE ORM:
   - Can create/drop tables, indexes, sequences
   - Can run migrations with proper schema permissions
   - Supports both push and migrate commands

2. PRISMA:
   - Can run prisma migrate deploy
   - Can create shadow database for development
   - Supports introspection and schema validation

3. ATLAS (Database Migration Tool):
   - Can apply schema migrations
   - Can compare and diff schemas
   - Supports versioned migrations

4. TYPEORM:
   - Can run synchronize and migrations
   - Can create/update entities and relations
   - Supports both CLI and programmatic migrations

5. SEQUELIZE:
   - Can run db:migrate and db:seed
   - Can create/drop tables and constraints
   - Supports both CLI and programmatic operations

The key is that each user has:
- USAGE on public schema
- ALL PRIVILEGES on tables, sequences, functions
- DEFAULT PRIVILEGES for future objects
- CREATE privileges on database and schema
*/