Self-Service Materialized View Refresh in Amazon Redshift Using SECURITY DEFINER

Overview

As data teams grow, managing access to materialized views (MVs) in Amazon Redshift — including refreshing materialized views to keep data current — can become a frequent coordination point between developers and administrators. By default, only the owner of a materialized view can run REFRESH MATERIALIZED VIEW — which means every new MV typically requires an admin to either transfer ownership or set up explicit permissions.

This article walks through a practical pattern that gives developers and automated refresh_mv_user the ability to refresh MVs independently, using a dynamic SECURITY DEFINER stored procedure. Once an administrator sets this up, the team can move forward without needing to raise a request for each new MV.

---

Use Case

Consider a setup where:

• Developers create tables and materialized views as part of their data pipeline work
• A refresh_mv_user (automated job, Lambda function, or Query Editor v2 schedule) is responsible for periodically refreshing those MVs
• The team would like the refresh_mv_user to handle any MV — current or future — without requiring admin involvement each time

This pattern fits that scenario well. It is especially useful when multiple developers share a Redshift cluster and need independent control over their pipelines.

---

Architecture

Developer (<developer_user>)
    │
    ├── Creates tables in <schema_name>
    └── Creates MVs in <mv_schema_name>
              │
              │  Permission grant (one-time admin setup)
              │  grants SELECT to refresh_mv_user on all objects
              ▼
refresh_mv_user  ──►  CALL <mv_schema_name>.refresh_any_mv('<mv_schema_name>', '<mv_name>')
                                    │
                                    │  SECURITY DEFINER
                                    │  procedure runs as admin internally
                                    ▼
                         REFRESH MATERIALIZED VIEW <mv_name>  ──► success

---

Key Concepts

Why SECURITY DEFINER?

By default, a stored procedure runs with the caller's privileges (SECURITY INVOKER). Since refresh_mv_user does not own the MV, a direct refresh would fail.

SECURITY DEFINER allows the procedure to run with the owner's (admin's) privileges, regardless of who calls it. A helpful analogy: think of it as a valet key — the valet can park the car without owning it, and only within the boundaries the owner has defined.

Attribute	Runs As	Use Case
SECURITY INVOKER (default)	Calling user	General access
SECURITY DEFINER	Procedure owner	Controlled privilege delegation

Note: The admin user performing the setup steps in this article should have either superuser access or the sys:secadmin role in Redshift.

Why a Dynamic Procedure?

A static procedure hardcodes MV names and needs to be updated every time a new MV is added. A dynamic procedure accepts the schema and MV name as parameters, so it works for any MV refresh_mv_user has SELECT access on — no procedure changes needed.

---

Implementation

Step 1 — Admin: Grant SELECT Access to refresh_mv_user

There are two approaches depending on the team's preference. Option A is recommended as it is simpler to manage.

Option A — Schema-Level Grant (Recommended)

This approach grants SELECT on all current and future tables within a schema in a single statement. It is broader and easier to manage, especially when multiple developers work in the same schema.

-- Grants SELECT on all tables (current and future) in <schema_name>
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <refresh_mv_user>;

-- Grants SELECT on all MVs (current and future) in <mv_schema_name>
GRANT SELECT ON ALL TABLES IN SCHEMA <mv_schema_name> TO <refresh_mv_user>;

Recommended — covers the entire schema without tracking individual developers. Well suited for shared schemas where multiple team members contribute objects.

Option B — ALTER DEFAULT PRIVILEGES (per developer user)

This approach automatically grants SELECT to refresh_mv_user on any future objects created by a specific developer. It needs to be run once per developer.

-- Grants SELECT on all future tables created by <developer_user> in <schema_name>
ALTER DEFAULT PRIVILEGES FOR USER <developer_user> IN SCHEMA <schema_name>
    GRANT SELECT ON TABLES TO <refresh_mv_user>;

-- Grants SELECT on all future MVs created by <developer_user> in <mv_schema_name>
ALTER DEFAULT PRIVILEGES FOR USER <developer_user> IN SCHEMA <mv_schema_name>
    GRANT SELECT ON TABLES TO <refresh_mv_user>;

Note: This applies only to objects created after this statement is run. Existing tables and MVs need a separate explicit grant.

---

Step 2 — Admin: Create the Dynamic Refresh Procedure

A single generic procedure handles any MV — no updates needed as new MVs are added.

CREATE OR REPLACE PROCEDURE <mv_schema_name>.refresh_any_mv(
    p_schema  VARCHAR,
    p_mv_name VARCHAR
)
SECURITY DEFINER
SET search_path = <mv_schema_name>, <schema_name>, pg_catalog
AS $$
DECLARE
    v_caller VARCHAR;
BEGIN
    -- session_user = original caller (not affected by SECURITY DEFINER)
    -- current_user = admin (effective user inside the procedure)
    v_caller := session_user;

    -- Guard 1: Verify the MV exists
    IF NOT EXISTS (
        SELECT 1 FROM SVV_MV_INFO
        WHERE schema_name = p_schema
          AND name        = p_mv_name
    ) THEN
        RAISE EXCEPTION 'MV %.% does not exist', p_schema, p_mv_name;
    END IF;

    -- Guard 2: Verify caller has SELECT on the MV
    IF NOT HAS_TABLE_PRIVILEGE(v_caller, p_schema || '.' || p_mv_name, 'SELECT') THEN
        RAISE EXCEPTION 'Permission denied: % does not have SELECT on MV %.%',
            v_caller, p_schema, p_mv_name;
    END IF;

    -- Refresh using admin privileges
    EXECUTE 'REFRESH MATERIALIZED VIEW '
            || quote_ident(p_schema) || '.' || quote_ident(p_mv_name);

    RAISE INFO 'Refreshed %.% as %', p_schema, p_mv_name, v_caller;
END;
$$ LANGUAGE plpgsql;

---

Step 3 — Admin: Grant Access to refresh_mv_user

-- USAGE: allows refresh_mv_user to resolve the procedure's schema path
GRANT USAGE ON SCHEMA <mv_schema_name>
    TO <refresh_mv_user>;

-- EXECUTE: allows refresh_mv_user to invoke the procedure
GRANT EXECUTE ON PROCEDURE <mv_schema_name>.refresh_any_mv(VARCHAR, VARCHAR)
    TO <refresh_mv_user>;

---

Step 4 — refresh_mv_user: Call the Procedure

refresh_mv_user (Lambda, EventBridge, Query Editor v2) calls a single procedure for any MV:

CALL <mv_schema_name>.refresh_any_mv('<mv_schema_name>', '<mv_name>');

No ownership transfer. No static passwords. No admin request.

---

Permission Grant Options: Comparison

	Option A — Schema-Level Grant (Recommended)	Option B — ALTER DEFAULT PRIVILEGES
Covers existing objects	Yes	No — future only
Covers future objects	Yes — all objects in schema	Yes — for that specific developer
Per-developer setup needed	No	Yes
Best for	Shared schemas, multiple contributors	Isolated developer schemas
Maintenance	Run once per schema	Run once per new developer

---

Security Considerations

search_path Locking

SET search_path = <mv_schema_name>, <schema_name>, pg_catalog

Locking the search_path ensures the procedure always resolves objects from the intended schemas. Without this, a user could create an object with the same name in a writable schema to redirect the procedure's behaviour.

SQL Injection Prevention

quote_ident(p_schema) || '.' || quote_ident(p_mv_name)

quote_ident() safely escapes the schema and MV name parameters, preventing unintended SQL from being injected through the procedure's inputs.

session_user vs current_user

Inside a SECURITY DEFINER procedure:

• current_user = admin (the procedure owner) — used for object access
• session_user = the original caller — used for authorization checks

Using session_user for the privilege check ensures the validation always reflects who actually called the procedure, not the elevated identity it runs under.

Principle of Least Privilege

refresh_mv_user can only refresh MVs it has SELECT on. It cannot refresh objects in schemas it has not been granted access to, even through this procedure.

---

How session_user and current_user Behave

-- Outside the procedure (as refresh_mv_user):
SELECT current_user;  -- returns: <refresh_mv_user>
SELECT session_user;  -- returns: <refresh_mv_user>

-- Inside the SECURITY DEFINER procedure:
SELECT current_user;  -- returns: admin  (elevated — used for object access)
SELECT session_user;  -- returns: <refresh_mv_user>  (original caller — used for auth check)

This distinction is what makes the authorization check reliable. The session_user reflects the true identity of the caller and cannot be altered by the privilege elevation.

---

Verifying Refresh History

-- All MV refresh executions
SELECT query_id, user_name, status, start_time, end_time,
       DATEDIFF(seconds, start_time, end_time) AS duration_sec
FROM SYS_QUERY_HISTORY
WHERE query_text ILIKE '%REFRESH MATERIALIZED VIEW%'
ORDER BY start_time DESC
LIMIT 20;

-- Runs by refresh_mv_user specifically
SELECT query_id, user_name, status, start_time, query_text
FROM SYS_QUERY_HISTORY
WHERE user_name = '<refresh_mv_user>'
ORDER BY start_time DESC;

-- Distinguish manual (Query Editor v2) vs automated (Lambda/CLI) runs
-- Query Editor v2 prepends /* RQEV2-<trace-id> */ to all queries it sends
SELECT query_id, user_name, start_time,
       CASE WHEN query_text ILIKE '%RQEV2%' THEN 'Query Editor v2'
            ELSE 'Automated (Lambda / CLI)'
       END AS run_source
FROM SYS_QUERY_HISTORY
WHERE query_text ILIKE '%REFRESH MATERIALIZED VIEW%'
ORDER BY start_time DESC;

---

Admin Effort: Before vs After

Action	Before This Setup	After This Setup
New table created by developer	Admin must GRANT SELECT	Covered by schema-level grant
New MV created by developer	Admin must update procedure or transfer ownership	Covered automatically
refresh_mv_user refreshes a new MV	Admin request required	CALL refresh_any_mv(...) — no request needed
New developer onboarding	Admin per-object grants	Admin grants schema access once

---

Summary

Combining a dynamic SECURITY DEFINER stored procedure with schema-level SELECT grants creates a self-service MV refresh workflow that scales well with team growth:

• Admin sets up once — procedure, permission grants, and EXECUTE access
• Developers work independently — create tables and MVs without coordination overhead
• refresh_mv_user operates autonomously — refreshes any MV it has SELECT access on
• Security is maintained — callers cannot refresh beyond what they have been granted

This pattern works particularly well for teams that share a Redshift cluster and want to move quickly without creating dependency on a central admin team for routine data pipeline operations.