Skip to main content

Amazon Redshift

· 16 min read
Pradeep Kadambar
Pradeep Kadambar
Creator
Amazon Redshift is a data warehouse product which forms part of the larger cloud-computing platform Amazon Web Services.

Identities ❌ | Accounts ✔️ | Groups ✔️ | App Roles ❌ | Entitlements ❌
note

For the purpose of this integration, we will use PostgreSQL JDBC driver, since Amazon Redshift officially supports this connection option. Configuring a JDBC connection - Amazon Redshift

Application​

Steps

  1. Login to RSA G&L console as System Administrator.
  2. Navigate to Resources > Application.
  3. Click on Create Application and select Other Application.
  4. Enter the details as shown below.
    img
  5. Click Finish.

Account Collector​

This section explains the process for configuring an account collector for the Amazon Redshift application. We will use the PostgreSQL database collector for this purpose.

Steps

  1. Login to RSA G&L console as System Administrator.
  2. Navigate to Resources > Applications.
  3. Click on Amazon Redshift.
  4. Navigate to Collectors tab and click on Create Account Collector. Enter the details as shown below.

    img
  5. Click Next and enter the Oracle database details. Use the Test Connection to make sure the connection details are accurate.

    img
  6. Click Next. Select the appropriate data that you intend to collect.

    img
  7. Click Next. On the Account Data page, enter the following query to collect account information.
-- All Users
SELECT pg_user.usename AS accountID
FROM pg_user
GROUP BY 1;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The following are the important attributes

img
8. Click Next. On the User Account Mappings Data configuration page enter the following query to map the Amazon Redshift account to user identity.

-- All Users
SELECT pg_user.usename AS accountID,
       pg_user.usename AS userID
FROM pg_user
GROUP BY 1;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The following are the important attributes

img
9. Click Next. In the Group Data configuration section enter the following query to collect groups from Amazon Redshift.

-- All Groups
SELECT pg_group.groname AS groupName
FROM pg_group
GROUP BY 1;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The following are the important attributes

img
10. In the Account Membership Data configuration section enter the following query to collect groups membership information from Amazon Redshift.

-- Users and Groups
SELECT pg_group.groname AS groupName,
       pg_user.usename AS accountID
FROM pg_group, pg_user
WHERE pg_user.usesysid = ANY (pg_group.grolist)
GROUP BY 1,2;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
  1. Click Next. In the User Resolution Rules, map the user to account.

    img
  2. Click Next. In the Member Account Resolution Rules, map the account collector.

    img
  3. Click Finish. Use the Test function to make sure the configurations are accurate.
COL033