Manage user permissions for all objects in the custom schema (Amazon Redshift)

What you need before you start

• The target database user: <db_user>

• The schema name: custom

• The level of access requested (examples below)

• You must be the schema owner of custom or a superuser/admin.

How Redshift permissions work (quick model)

Schema access is two layers:

1. Schema-level privileges (lets a user reference or create objects in the schema)

• USAGE — required to access objects in the schema at all

• CREATE — allows creating objects in the schema (tables, views, etc.)

2. Object-level privileges (what the user can do to tables/views/materialized views)

• Common: SELECT, INSERT, UPDATE, DELETE, REFERENCES

• Some features may use additional privileges depending on object type.

Most “permission denied” issues happen because USAGE on the schema is missing even when object privileges exist.

Step 1 — Always grant schema USAGE (baseline)

If the user needs access to anything inside custom, grant USAGE:

GRANT USAGE ON SCHEMA custom TO <db_user>;

Step 2 — Grant schema CREATE (only if requested)

If the user must create objects in custom:

GRANT CREATE ON SCHEMA custom TO <db_user>;

Step 3 — Grant privileges on all existing objects in custom

Pick the privilege set that matches the request and apply it to all existing objects.

Read-only (common)

GRANT SELECT ON ALL TABLES IN SCHEMA custom TO <db_user>;

Write to existing tables (DML)

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA custom TO <db_user>;

Full DML + read

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA custom TO <db_user>;

References (less common; for FK-like constraints / dependency features)

GRANT REFERENCES ON ALL TABLES IN SCHEMA custom TO <db_user>;

Note: In Redshift, GRANT ... ON ALL TABLES IN SCHEMA ... covers tables and (regular) views. For materialized views, see below.

Materialized views (if present / required)

Depending on cluster/version and object usage, you may need to explicitly grant privileges on materialized views.

If your environment supports schema-wide grants for materialized views:

GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA custom TO <db_user>;

If that statement errors, grant per MV:

GRANT SELECT ON MATERIALIZED VIEW custom.<mv_name> TO <db_user>;

(Repeat for other privileges only if your use case requires them; most MV access is SELECT.)

Step 4 — Ensure future objects inherit the right permissions (recommended)

The commands above affect existing objects only. To ensure new objects created later in custom also grant the requested access, set default privileges.

Default privileges for future tables/views

Run as each user who creates objects in custom (or as superuser using FOR USER):

ALTER DEFAULT PRIVILEGES IN SCHEMA custom
GRANT SELECT ON TABLES TO <db_user>;

For write access:

ALTER DEFAULT PRIVILEGES IN SCHEMA custom
GRANT INSERT, UPDATE, DELETE ON TABLES TO <db_user>;

For full read/write:

ALTER DEFAULT PRIVILEGES IN SCHEMA custom
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <db_user>;

If you need to set defaults for a specific creator:

ALTER DEFAULT PRIVILEGES FOR USER <creator_user> IN SCHEMA custom
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO <db_user>;

Default privileges are per object creator. If multiple roles/users create objects in custom, repeat for each creator (or manage creation via a controlled owner role).

Verification

Confirm schema privileges

SELECT n.nspname AS schema,
       u.usename AS grantee,
       has_schema_privilege(u.usename, n.oid, 'USAGE')  AS has_usage,
       has_schema_privilege(u.usename, n.oid, 'CREATE') AS has_create
FROM pg_namespace n
CROSS JOIN pg_user u
WHERE n.nspname = 'custom'
  AND u.usename = '<db_user>';

Confirm object privileges (tables/views)

SELECT *
FROM svv_table_privileges
WHERE schema_name = 'custom'
  AND grantee = '<db_user>';

Optional practical test (run as <db_user>):

SELECT 1 FROM custom.<some_object> LIMIT 1;

Rollback (revoke permissions)

Revoke schema privileges

REVOKE CREATE ON SCHEMA custom FROM <db_user>;
REVOKE USAGE  ON SCHEMA custom FROM <db_user>;

Revoke object privileges on existing tables/views

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA custom FROM <db_user>;

Revoke materialized view privileges (if granted schema-wide)

REVOKE ALL PRIVILEGES ON ALL MATERIALIZED VIEWS IN SCHEMA custom FROM <db_user>;

Revoke default privileges (future objects)

Run as the same creator(s) used to set them:

ALTER DEFAULT PRIVILEGES IN SCHEMA custom
REVOKE ALL ON TABLES FROM <db_user>;

Or per creator:

ALTER DEFAULT PRIVILEGES FOR USER <creator_user> IN SCHEMA custom
REVOKE ALL ON TABLES FROM <db_user>;

Common scenarios and which commands to use

“User needs access to query everything in custom”

• GRANT USAGE ON SCHEMA ...

• GRANT SELECT ON ALL TABLES IN SCHEMA ...

• Default privileges for future objects (recommended)

“User needs to load/update data in custom tables”

• GRANT USAGE ON SCHEMA ...

• GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ...

• Default privileges for future objects (recommended)

“User needs to create tables/views in custom”

• GRANT USAGE, CREATE ON SCHEMA ...

• (Optionally) grants on objects depending on what else they need

-- Read access
GRANT SELECT ON TABLE custom.<table_name> TO <db_user>;

-- Write access (tables only)
GRANT INSERT, UPDATE, DELETE ON TABLE custom.<table_name> TO <db_user>;

-- If it's a view (read-only)
GRANT SELECT ON VIEW custom.<view_name> TO <db_user>;

-- Remove specific privileges
REVOKE SELECT ON TABLE custom.<table_name> FROM <db_user>;
REVOKE INSERT, UPDATE, DELETE ON TABLE custom.<table_name> FROM <db_user>;

-- Remove all privileges on that object
REVOKE ALL PRIVILEGES ON TABLE custom.<table_name> FROM <db_user>;
REVOKE ALL PRIVILEGES ON VIEW  custom.<view_name>  FROM <db_user>;

GRANT USAGE ON SCHEMA custom TO <db_user>;