AWS Redshift

Redshift Drivers

In some situations, you may find that the Heimdall management console can test successfully to Redshift, but the proxy does not work properly. If you inspect the logs, you may see the console is reporting a 403 exception. This is due to the proxy trying to load additional jar files that normally are contained from the Redshift zip file.

To resolve this, first extract all the jar files from the Redshift zip file, which contains all the jar dependencies for the Redshift driver, including the AWS SDK. Next, in the driver configuration, add all the jar files to the driver config. This may be over ten files. Once added, make sure the proxy restarts, and at this point you should see this error resolved.

Redshift and SSL

Redshift by default connects via TLS and requires TLS including CA validation. When connecting to the Heimdall proxy for Redshift, it is likely that initially, the self-signed certificate will be presented, AND TLS will need to be enabled. To resolve this, make sure the Redshift driver is updated, and use the sslmode option to "prefer" for testing. For production, it is advised that a correct and valid TLS certificate be loaded via the admin->certificate tab, and configured in the proxy tls settings. Older Redshift drivers will not accept the prefer setting.

Redshift and Heimdall

The Heimdall Database proxy is as of June 26, 2023, the only "Redshift Ready" proxy supported as a Redshift Ready product, and we will continue working with the Redshift team to improve compatibility and performance.

Active Directory Password & Group Synchronization (DRAFT)

CREATE OR REPLACE PROCEDURE heimdall.sync_user(username VARCHAR, password VARCHAR, ldapgroups VARCHAR(max))
    LANGUAGE plpgsql
AS $$

DECLARE
    -- configuration variables
    current_schema_name TEXT;
    create_roles_if_missing BOOLEAN;
    lookupTableName TEXT;
    lookupTableKey TEXT; -- ldap_group
    lookupTableValue TEXT; -- role

    -- logic variables
    sql VARCHAR(max);
    groupname RECORD;
    has_rows BOOLEAN;
    lookup_table_exists BOOLEAN;
    lookup_table_has_rows BOOLEAN;
    user_exists BOOLEAN;
    group_exists BOOLEAN;

BEGIN
    -- set these variables to match your config
    current_schema_name := 'heimdall';   -- where the sync_user and lookup table live
    create_roles_if_missing := true; -- set to true to create roles when they don't exist
    lookupTableName := 'lookup_table';
    lookupTableKey := 'ldap_group';
    lookupTableValue := 'db_group';

    EXECUTE 'CREATE TEMPORARY TABLE grouplist (g varchar(max))';
    EXECUTE 'CREATE TEMPORARY TABLE finalgroups (g varchar(max))';

    CREATE TEMPORARY TABLE numbers AS (
      SELECT ROW_NUMBER() OVER() AS n
      FROM 
      (
        SELECT 1
        FROM pg_catalog.pg_attribute
        LIMIT 100 -- you can adjust this value to fit the maximum number of elements in your CSV strings
      ) a
    );

    INSERT INTO grouplist
    SELECT TRIM('"' FROM TRIM(SPLIT_PART(ldapgroups, ',', n::integer)))
    FROM numbers
    WHERE n <= REGEXP_COUNT(ldapgroups, ',') + 1;

    -- find out if we have a lookup table to map groups via
    SELECT EXISTS (SELECT 1 FROM pg_tables WHERE schemaname = current_schema_name AND tablename=lookupTableName) INTO lookup_table_exists;

    IF lookup_table_exists THEN
       -- check whether lookup_table has any rows. If not there's no reason to process queries related to it.
        EXECUTE 'SELECT COUNT(*) FROM ' || current_schema_name || '.' || lookupTableName || ' lt LIMIT 1' INTO lookup_table_has_rows;
    END IF;

    IF lookup_table_exists AND lookup_table_has_rows THEN
        RAISE NOTICE '%.sync_user: lookup table found', current_schema_name;
        -- we have a lookup table, use it to map the inbound groups to local groups
        sql := 'insert into finalgroups select ' || lookupTableValue || ' from ' || current_schema_name || '.' || lookupTableName || ' lt, grouplist gl where lt.' || lookupTableKey || ' = g;';
        EXECUTE sql;
        -- and find the unmapped groups
        sql := 'insert into finalgroups select g from ' || current_schema_name || '.' || lookupTableName || ' lt, grouplist gl where g not in (' || lookupTableKey || ');';
        EXECUTE sql;
    ELSE
    -- no lookup group, so just copy the grouplist into the final groups table
        INSERT INTO finalgroups SELECT g FROM grouplist;
    END IF;

    -- at this point, finalgroups should have the complete grouplist of what groups we want for the user
        SELECT EXISTS (SELECT 1 FROM pg_user WHERE usename = username) INTO user_exists;

        -- sync user and password
    IF user_exists THEN
        sql := 'ALTER USER ' || quote_ident(username) || ' WITH PASSWORD ''' || password || ''';';
        EXECUTE sql;
        sql := 'ALTER USER ' || quote_ident(username) || ' WITH PASSWORD ''' || 'REDACTED' || ''';';
        RAISE NOTICE '%.sync_user: % updated', current_schema_name, sql;
          -- now find user's groups that are not in the finalgroup table
        FOR groupname IN SELECT groname FROM pg_group JOIN pg_user ON usesysid = ANY(grolist)
        WHERE usename = username LOOP
            -- this select is not yet working properly
            SELECT EXISTS (SELECT 1 FROM finalgroups WHERE groupname.groname = ANY(SELECT g FROM finalgroups)) INTO has_rows;
            IF NOT has_rows THEN
                RAISE NOTICE '%.sync_user: dropping % from user %', current_schema_name, groupname.groname, username;
                -- now revoke membership of the group from the user
                sql = 'ALTER GROUP '|| groupname.groname ||' DROP USER '|| username || ';';
                EXECUTE sql;
                RAISE NOTICE '%.sync_user: %', current_schema_name, sql;
            END IF;
        END LOOP;
    ELSE
        sql := 'CREATE USER ' || quote_ident(username) || ' WITH PASSWORD ''' || password || ''';';
        EXECUTE sql;
        sql := 'CREATE USER ' || quote_ident(username) || ' WITH PASSWORD ''' || 'REDACTED' || ''';';
        RAISE NOTICE '%.sync_user: % created', current_schema_name, sql;
        END IF;

        FOR groupname IN SELECT g FROM finalgroups LOOP
            SELECT EXISTS (SELECT groname FROM pg_group WHERE groname ILIKE groupname.g) INTO group_exists;
            RAISE NOTICE 'group: %, group_exists: %', groupname.g, group_exists;
        IF (NOT group_exists) AND create_roles_if_missing THEN
            sql := 'CREATE GROUP ' || groupname.g;
            EXECUTE sql;
            RAISE NOTICE '%.sync_user: %', current_schema_name, sql;
        END IF;
        -- add group to user
        IF group_exists OR create_roles_if_missing THEN
            sql = 'ALTER GROUP '|| groupname.g ||' ADD USER '|| username || ';';
            EXECUTE sql;
            RAISE NOTICE '%.sync_user: %', current_schema_name, sql;
        END IF;
    END LOOP;

    -- make sure we drop these to prevent reuse issues on the same connection
    DROP TABLE numbers;
    DROP TABLE grouplist;
    DROP TABLE finalgroups;

RETURN;
END;

$$
;

Active Directory Password & Group Synchronization extra options

By default, a group with the same name is created in the database for each extracted ldap group. By creating the table below for each key found (ldap_group), a role with the specified name in the value (role) will be created instead. Composite Primary Key allows to map more than one role to particular ldap_group; If 'role' is empty then no role is created and granted to user for this particular ldap_group.

CREATE TABLE IF NOT EXISTS heimdall.lookup_table (
        ldap_group varchar(255),
        db_group varchar(64),
        PRIMARY KEY (ldap_group, db_group)
    )