☁️ Amazon Redshift

-- Margin user/group details--------------
SET margin_password = '<a secure password>';
SET margin_database = '<database>';                 -- e.g. 'analytics'
SET source_schema_1 = '<schema>';                   -- e.g. 'public'
SET source_table_1  = '<table>';                    -- e.g. 'events'

-- Create Margin user and group
CREATE USER margin_user PASSWORD '${margin_password}';
CREATE GROUP margin_role;
ALTER GROUP margin_role ADD USER margin_user;

-- Allow Margin to create/manage its own schemas in the target database
GRANT CREATE ON DATABASE ${margin_database} TO GROUP margin_role;
GRANT TEMPORARY ON DATABASE ${margin_database} TO GROUP margin_role;

-- Grant read access to source schema(s)
GRANT USAGE ON SCHEMA ${source_schema_1} TO GROUP margin_role;
GRANT SELECT ON ALL TABLES IN SCHEMA ${source_schema_1} TO GROUP margin_role;

-- Optional: grant specific tables only instead of all tables
-- GRANT SELECT ON TABLE ${source_schema_1}.${source_table_1} TO GROUP margin_role;

-- Set up default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA ${source_schema_1}
GRANT SELECT ON TABLES TO GROUP margin_role;

-- Optional: explicitly exclude sensitive tables
-- REVOKE SELECT ON TABLE ${source_schema_1}.<sensitive_table> FROM GROUP margin_role;

-- If you need to restrict access to sensitive data, create a filtered view
CREATE SCHEMA IF NOT EXISTS margin_secure;

CREATE OR REPLACE VIEW margin_secure.secure_${source_table_1}_view AS
SELECT
    id,
    event_name,
    timestamp,
    non_sensitive_column1,
    non_sensitive_column2
    -- Omit sensitive columns like PII, financial data, etc.
FROM ${source_schema_1}.${source_table_1};

-- Grant access to the view
GRANT SELECT ON margin_secure.secure_${source_table_1}_view TO GROUP margin_role;

-- Margin should be able to create/drop schemas it owns in the workspace database
CREATE SCHEMA IF NOT EXISTS margin_validation_schema;
CREATE TABLE margin_validation_schema.permission_check (id INT);
DROP TABLE margin_validation_schema.permission_check;
DROP SCHEMA margin_validation_schema;