Schema Operations: Organizing Database Objects

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

-- ============================================
-- Fully Qualified Object Names
-- ============================================
-- Without schema qualification (uses default/search_path):
SELECT * FROM customers;
 
-- With schema qualification (explicit):
SELECT * FROM sales.customers;
 
-- With full qualification (database.schema.table):
-- Note: Cross-database queries have limited support
SELECT * FROM company_db.sales.customers;  -- SQL Server style
 
 
-- ============================================
-- Objects in Different Schemas Can Share Names
-- ============================================
-- This is perfectly valid:
CREATE TABLE sales.config (setting TEXT, value TEXT);
CREATE TABLE hr.config (setting TEXT, value TEXT);
CREATE TABLE audit.config (setting TEXT, value TEXT);
 
-- Each schema has its own 'config' table—no conflict
SELECT * FROM sales.config;   -- Gets sales configuration
SELECT * FROM hr.config;      -- Gets HR configuration
SELECT * FROM audit.config;   -- Gets audit configuration
 
 
-- ============================================
-- Default Schema Behavior
-- ============================================
-- PostgreSQL: Objects go to first schema in search_path
-- Usually 'public' by default
 
CREATE TABLE my_table (id INT);  
-- Creates: public.my_table (if search_path starts with public)
 
-- Check current search_path
SHOW search_path;  
-- Output: "$user", public
 
-- The "$user" means: schema matching current username
-- If user 'alice' creates a table, it goes to 'alice' schema (if exists)
-- Otherwise falls through to 'public'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

-- ============================================
-- Basic CREATE SCHEMA
-- ============================================
-- Create an empty schema
CREATE SCHEMA sales;
 
-- Create schema if it doesn't exist (PostgreSQL 9.3+)
CREATE SCHEMA IF NOT EXISTS sales;
 
-- Create schema with specific owner
CREATE SCHEMA hr AUTHORIZATION admin_user;
 
-- Create schema with owner matching schema name (SQL standard)
CREATE SCHEMA AUTHORIZATION alice;  
-- Creates schema named 'alice' owned by 'alice'
 
 
-- ============================================
-- CREATE SCHEMA with Initial Objects
-- ============================================
-- SQL Standard: Create schema with contained objects
CREATE SCHEMA inventory
    CREATE TABLE products (
        product_id      INTEGER PRIMARY KEY,
        product_name    VARCHAR(200) NOT NULL,
        unit_price      DECIMAL(10, 2)
    )
    CREATE TABLE warehouse_stock (
        warehouse_id    INTEGER,
        product_id      INTEGER REFERENCES products(product_id),
        quantity        INTEGER DEFAULT 0,
        PRIMARY KEY (warehouse_id, product_id)
    )
    CREATE VIEW low_stock_products AS
        SELECT p.product_name, ws.quantity
        FROM products p
        JOIN warehouse_stock ws ON p.product_id = ws.product_id
        WHERE ws.quantity < 10;
 
-- All objects created in 'inventory' schema atomically
 
 
-- ============================================
-- Multi-Tenant Schema Pattern
-- ============================================
-- Create isolated schemas for each tenant
CREATE SCHEMA tenant_001;
CREATE SCHEMA tenant_002;
CREATE SCHEMA tenant_003;
 
-- Create identical structure in each
-- (Usually done via a script or function)
CREATE TABLE tenant_001.users (
    user_id     SERIAL PRIMARY KEY,
    username    VARCHAR(100) NOT NULL
);
CREATE TABLE tenant_001.orders (
    order_id    SERIAL PRIMARY KEY,
    user_id     INTEGER REFERENCES tenant_001.users(user_id)
);
 
-- Repeat for each tenant...
 
 
-- ============================================
-- Development/Environment Schemas
-- ============================================
CREATE SCHEMA staging;
CREATE SCHEMA development;
CREATE SCHEMA production;
 
-- Same tables, different environments
CREATE TABLE staging.features (id INT, data TEXT);
CREATE TABLE production.features (id INT, data TEXT);
 
-- Developers work in staging, changes promoted to production
 
 
-- ============================================
-- Check Existing Schemas
-- ============================================
-- PostgreSQL: List all schemas
SELECT schema_name, schema_owner
FROM information_schema.schemata;
 
-- Or use psql shortcut
-- dn
 
-- SQL Server: List schemas
-- SELECT name, principal_id FROM sys.schemas;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94

-- ============================================
-- Viewing and Setting Search Path
-- ============================================
-- Check current search path
SHOW search_path;
-- Default output: "$user", public
 
-- Set search path for current session
SET search_path TO sales, hr, public;
 
-- Now unqualified references search: sales → hr → public
SELECT * FROM customers;  
-- Finds: sales.customers if exists, else hr.customers, else public.customers
 
-- Set search path permanently for a user
ALTER USER developer SET search_path = development, public;
 
-- Set search path permanently for a database
ALTER DATABASE company_db SET search_path = app, shared, public;
 
 
-- ============================================
-- Search Path in Practice
-- ============================================
-- Setup: Tables in different schemas
CREATE SCHEMA app;
CREATE SCHEMA audit;
 
CREATE TABLE app.settings (key TEXT, value TEXT);
CREATE TABLE audit.settings (key TEXT, value TEXT);
CREATE TABLE public.settings (key TEXT, value TEXT);
 
-- With default search path:
SET search_path = "$user", public;
SELECT * FROM settings;  -- Gets public.settings
 
-- Switch to application context:
SET search_path = app, audit, public;
SELECT * FROM settings;  -- Gets app.settings
SELECT * FROM audit.settings;  -- Explicit always works
 
 
-- ============================================
-- The "$user" Schema
-- ============================================
-- If you connect as user 'alice':
-- "$user" is replaced with "alice"
-- So search_path of "$user", public becomes: alice, public
 
-- This enables per-user private tables
CREATE SCHEMA alice AUTHORIZATION alice;
CREATE TABLE alice.my_notes (note TEXT);  -- Alice's private table
 
-- When Alice queries:
SET search_path = "$user", public;
SELECT * FROM my_notes;  -- Gets alice.my_notes
 
 
-- ============================================
-- Security Implications of Search Path
-- ============================================
-- DANGER: Malicious schema injection
-- If an attacker can create objects in a schema that's searched
-- before the intended schema, they can hijack function calls!
 
-- Example vulnerability:
-- search_path = public, app (public is searched FIRST)
-- If attacker creates public.important_function()...
 
-- BEST PRACTICE: Always list application schemas FIRST
SET search_path = app, public;  -- Safe: app's objects found first
 
-- Or use explicit schema qualification for security-sensitive operations
SELECT app.sensitive_function(data) FROM app.private_table;
 
 
-- ============================================
-- Schema Qualification in Queries
-- ============================================
-- Fully qualified: Not affected by search path
SELECT 
    s.order_id,
    s.customer_id,
    h.employee_name
FROM sales.orders s
JOIN hr.employees h ON s.salesperson_id = h.employee_id;
 
-- Mixed: Unqualified tables use search path
SET search_path = sales, hr;
SELECT 
    orders.order_id,      -- Found in sales (first in path with 'orders')
    employees.name        -- Found in hr (has 'employees')
FROM orders
JOIN employees ON orders.salesperson_id = employees.employee_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

-- ============================================
-- Renaming Schemas
-- ============================================
-- Rename a schema (PostgreSQL)
ALTER SCHEMA old_sales RENAME TO sales_archive;
 
-- All objects inside retain their names
-- What was old_sales.customers is now sales_archive.customers
 
 
-- ============================================
-- Changing Schema Ownership
-- ============================================
-- Transfer ownership to another user/role
ALTER SCHEMA sales OWNER TO sales_manager;
 
-- The new owner gets full control over the schema
-- Including ability to create, alter, and drop objects within it
 
 
-- ============================================
-- Practical Examples
-- ============================================
-- Scenario 1: Archiving a schema
-- Rename to indicate archived status
ALTER SCHEMA legacy_app RENAME TO legacy_app_archived_2024;
 
-- Scenario 2: Transferring responsibility
-- New DBA taking over maintenance
ALTER SCHEMA inventory OWNER TO new_dba;
 
-- Scenario 3: Merging teams
-- HR department absorbed by People Operations
ALTER SCHEMA hr RENAME TO people_ops;
ALTER SCHEMA hr_archive RENAME TO people_ops_archive;
 
 
-- ============================================
-- Limitations of ALTER SCHEMA
-- ============================================
-- You CANNOT:
-- - Move objects between schemas with ALTER SCHEMA
-- - Change the schema's default privileges (must use ALTER DEFAULT PRIVILEGES)
-- - Merge two schemas
 
-- Moving objects requires ALTER TABLE/VIEW/etc:
ALTER TABLE old_schema.customers SET SCHEMA new_schema;
ALTER VIEW old_schema.customer_summary SET SCHEMA new_schema;
ALTER SEQUENCE old_schema.customer_id_seq SET SCHEMA new_schema;
 
 
-- ============================================
-- Batch Moving Objects Between Schemas
-- ============================================
-- Generate ALTER statements to move all tables
SELECT 'ALTER TABLE ' || schemaname || '.' || tablename 
       || ' SET SCHEMA new_schema;'
FROM pg_tables
WHERE schemaname = 'old_schema';
 
-- Execute the generated statements
-- (In practice, save to file and run as script)
 
 
-- ============================================
-- Checking Schema Ownership
-- ============================================
SELECT 
    nspname AS schema_name,
    pg_get_userbyid(nspowner) AS owner
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%' 
  AND nspname != 'information_schema';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

-- ============================================
-- Basic DROP SCHEMA
-- ============================================
-- Drop an empty schema
DROP SCHEMA empty_schema;
 
-- Conditional drop (avoids error if doesn't exist)
DROP SCHEMA IF EXISTS maybe_exists;
 
-- Drop schema with all contents (DANGEROUS)
DROP SCHEMA populated_schema CASCADE;
-- This deletes ALL objects in the schema: tables, views, functions, etc.
 
 
-- ============================================
-- RESTRICT vs CASCADE
-- ============================================
-- Default behavior (RESTRICT): Fail if schema contains objects
DROP SCHEMA sales;
-- ERROR: cannot drop schema sales because other objects depend on it
-- DETAIL: table sales.customers depends on schema sales
--         table sales.orders depends on schema sales
--         view sales.order_summary depends on schema sales
 
-- RESTRICT is explicit but same as default
DROP SCHEMA sales RESTRICT;
-- Same error
 
-- CASCADE: Drop schema AND all contents
DROP SCHEMA sales CASCADE;
-- NOTICE: drop cascades to table sales.customers
-- NOTICE: drop cascades to table sales.orders
-- NOTICE: drop cascades to view sales.order_summary
-- Schema and all objects are gone
 
 
-- ============================================
-- Safe DROP SCHEMA Patterns
-- ============================================
-- Pattern 1: Check contents before dropping
SELECT 
    table_type,
    table_name
FROM information_schema.tables
WHERE table_schema = 'schema_to_drop';
 
-- Pattern 2: Count objects to get sense of impact
SELECT 
    COUNT(*) AS table_count,
    SUM(CASE WHEN table_type = 'VIEW' THEN 1 ELSE 0 END) AS view_count
FROM information_schema.tables
WHERE table_schema = 'schema_to_drop';
 
-- Pattern 3: Rename before drop (reversible step)
ALTER SCHEMA old_app RENAME TO old_app_pending_delete;
-- Wait a week for any issues to surface
DROP SCHEMA old_app_pending_delete CASCADE;
 
 
-- ============================================
-- Dependencies on Schema Objects
-- ============================================
-- Objects in OTHER schemas might reference this schema's objects
 
-- Check for cross-schema dependencies before dropping:
SELECT 
    dependent_ns.nspname AS dependent_schema,
    dependent_obj.relname AS dependent_object,
    source_ns.nspname AS depends_on_schema,
    source_obj.relname AS depends_on_object
FROM pg_depend d
JOIN pg_class dependent_obj ON d.objid = dependent_obj.oid
JOIN pg_namespace dependent_ns ON dependent_obj.relnamespace = dependent_ns.oid
JOIN pg_class source_obj ON d.refobjid = source_obj.oid
JOIN pg_namespace source_ns ON source_obj.relnamespace = source_ns.oid
WHERE source_ns.nspname = 'schema_to_drop'
  AND dependent_ns.nspname != source_ns.nspname;
 
-- If results: Those objects in OTHER schemas will break!
 
 
-- ============================================
-- Cannot Drop Special Schemas
-- ============================================
-- These will fail:
DROP SCHEMA pg_catalog CASCADE;   -- System catalog
DROP SCHEMA information_schema;   -- SQL standard metadata
-- ERROR: cannot drop schema pg_catalog because it is required by the database system
 
-- Public CAN be dropped (but usually shouldn't)
DROP SCHEMA public CASCADE;  -- Works...
-- But breaks default behavior—recreate immediately if needed:
CREATE SCHEMA public;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117

-- ============================================
-- Granting Schema Access
-- ============================================
-- Allow a role to access objects in a schema
GRANT USAGE ON SCHEMA sales TO readonly_user;
 
-- USAGE alone doesn't give access to objects—it's the gateway
-- User still needs SELECT/INSERT/etc on actual tables
 
 
-- ============================================
-- Permission Levels Explained
-- ============================================
-- To access sales.customers, user needs:
-- 1. USAGE on schema 'sales' (to enter the schema)
-- 2. SELECT on table 'sales.customers' (to query the table)
 
-- Grant schema access
GRANT USAGE ON SCHEMA sales TO analyst;
 
-- Grant table access
GRANT SELECT ON sales.customers TO analyst;
GRANT SELECT ON sales.orders TO analyst;
 
-- Or grant on all tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO analyst;
 
 
-- ============================================
-- Default Privileges for Future Objects
-- ============================================
-- Grant permissions that apply to objects created in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
    GRANT SELECT ON TABLES TO readonly_role;
 
-- Now any new table created in 'sales' automatically grants SELECT to readonly_role
 
-- Default privileges for other object types
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
    GRANT EXECUTE ON FUNCTIONS TO app_role;
 
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
    GRANT USAGE ON SEQUENCES TO app_role;
 
 
-- ============================================
-- Creating Objects in Schema
-- ============================================
-- Allow a role to create objects in a schema
GRANT CREATE ON SCHEMA development TO developer_role;
 
-- Developer can now create tables, views, etc. in 'development'
 
 
-- ============================================
-- Revoking Permissions
-- ============================================
-- Remove schema access
REVOKE USAGE ON SCHEMA sales FROM former_employee;
 
-- Remove all privileges on all objects
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA sales FROM former_employee;
 
-- Remove default privileges (for future objects)
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
    REVOKE SELECT ON TABLES FROM former_employee;
 
 
-- ============================================
-- Schema Ownership and ALL Privileges
-- ============================================
-- Schema owner has implicit ALL privileges
-- Transfer ownership to grant full control
ALTER SCHEMA hr OWNER TO hr_admin;
 
-- hr_admin now controls all aspects of 'hr' schema
 
 
-- ============================================
-- Common Permission Patterns
-- ============================================
-- Pattern 1: Read-only access for analysts
CREATE ROLE analyst_role;
GRANT USAGE ON SCHEMA reporting TO analyst_role;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO analyst_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO analyst_role;
 
-- Pattern 2: Full access for application
CREATE ROLE app_role;
GRANT USAGE ON SCHEMA app TO app_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app TO app_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT ALL ON TABLES TO app_role;
 
-- Pattern 3: Schema ownership delegation
CREATE ROLE sales_team;
CREATE SCHEMA sales_data AUTHORIZATION sales_team;
-- sales_team owns and manages everything in sales_data
 
 
-- ============================================
-- Checking Schema Permissions
-- ============================================
-- PostgreSQL: Check user's schema privileges
SELECT 
    nspname AS schema_name,
    pg_catalog.has_schema_privilege(current_user, nspname, 'USAGE') AS has_usage,
    pg_catalog.has_schema_privilege(current_user, nspname, 'CREATE') AS has_create
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%';
 
-- Check which roles have access to a schema
SELECT 
    grantee, 
    privilege_type
FROM information_schema.role_usage_grants
WHERE object_schema = 'sales';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97

-- ============================================
-- Pattern 1: Business Domain Organization
-- ============================================
CREATE SCHEMA sales;        -- Customer-facing sales data
CREATE SCHEMA inventory;    -- Warehouse and stock
CREATE SCHEMA finance;      -- Accounting and billing
CREATE SCHEMA hr;           -- Employee data (restricted)
CREATE SCHEMA shared;       -- Cross-domain reference data
 
-- Domain-specific tables
CREATE TABLE sales.customers (...);
CREATE TABLE sales.orders (...);
CREATE TABLE inventory.products (...);
CREATE TABLE inventory.warehouses (...);
CREATE TABLE finance.invoices (...);
CREATE TABLE hr.employees (...);
CREATE TABLE shared.countries (...);
CREATE TABLE shared.currencies (...);
 
 
-- ============================================
-- Pattern 2: Layered Architecture (Data Warehouse)
-- ============================================
CREATE SCHEMA raw;          -- Raw ingested data (exact copy from sources)
CREATE SCHEMA staging;      -- Cleaned, validated data in progress
CREATE SCHEMA dimensional;  -- Star schema facts and dimensions
CREATE SCHEMA reporting;    -- Views optimized for BI tools
CREATE SCHEMA ml;           -- Feature tables for machine learning
 
-- Data flows through layers:
-- raw → staging → dimensional → reporting
--                           ↘ ml
 
 
-- ============================================
-- Pattern 3: Multi-Tenant Isolation
-- ============================================
-- Template schema with base structure
CREATE SCHEMA tenant_template;
CREATE TABLE tenant_template.users (...);
CREATE TABLE tenant_template.orders (...);
 
-- Per-tenant schemas cloned from template
CREATE SCHEMA tenant_acme;
CREATE TABLE tenant_acme.users (LIKE tenant_template.users INCLUDING ALL);
CREATE TABLE tenant_acme.orders (LIKE tenant_template.orders INCLUDING ALL);
 
-- Tenant isolation at connection level
-- Each tenant's app configures: SET search_path = tenant_acme;
 
 
-- ============================================
-- Pattern 4: API Versioning
-- ============================================
CREATE SCHEMA api_v1;   -- Legacy, deprecated
CREATE SCHEMA api_v2;   -- Current stable
CREATE SCHEMA api_v3;   -- Beta/development
 
-- Views expose different structures per version
CREATE VIEW api_v1.customers AS 
    SELECT id, name, email FROM core.customers;  -- Simple
 
CREATE VIEW api_v2.customers AS 
    SELECT id, name, email, phone, created_at FROM core.customers;  -- Extended
 
CREATE VIEW api_v3.customers AS 
    SELECT id, full_name as name, email, phone_numbers, 
           created_at, updated_at, status
    FROM core.customers;  -- New structure
 
 
-- ============================================
-- Naming Conventions
-- ============================================
-- Good schema names:
-- ✓ sales, hr, inventory        (domain names)
-- ✓ staging, production         (lifecycle stages)
-- ✓ tenant_001, tenant_acme     (tenant identifiers)
-- ✓ api_v1, api_v2              (versioned interfaces)
 
-- Bad schema names:
-- ✗ schema1, new_schema         (meaningless)
-- ✗ johns_stuff                  (personal, unclear)
-- ✗ SALES, HR                   (case sensitivity issues)
-- ✗ data-warehouse              (hyphens cause quoting issues)
 
 
-- ============================================
-- Documentation via Comments
-- ============================================
COMMENT ON SCHEMA sales IS 
    'Customer-facing sales data including orders, customers, and transactions. 
     Owner: Sales Engineering. Contact: sales-eng@company.com';
 
COMMENT ON SCHEMA hr IS
    'RESTRICTED: Employee data, compensation, reviews. 
     Access requires HR approval. Owner: HR Systems.';