Okta is the preferred Identity as a Service (IDaaS) solution for thousands of organizations worldwide. Here at QloudX, we too have a few large customers who have chosen Okta as their unified identity platform for every application they use throughout the company. All employees get an Okta account which they use to sign into any company apps they need.

And so, when we started using Redshift for analytics, users started asking, “Why can’t I use my Okta credentials to sign into Redshift?” Until then, every user was manually created within Redshift itself. As you can imagine, this approach has many issues:

• Redshift users are completely disjointed from the company-wide “official” user directory.
• Keeping the hundreds of user accounts in-sync across the Redshift clusters in dev, stage & prod is tedious & error-prone.

Soon, we started exploring whether it’s even possible to use Okta to log in to Redshift. Fortunately, we found an excellent post on the AWS Big Data Blog that walks you through exactly how to achieve this:

Federate Amazon Redshift access with Okta as an identity provider

This post was instrumental in our success. Without it, we would have spent days experimenting without much progress.

Managing User Permissions

We have hundreds of users, groups & schemas in our Redshift. Ensuring that someone logging into Redshift via Okta did not inadvertently get more permissions than they should have, was very important for us.

To test this, we started by developing a couple of not-so-trivial queries that would show us exactly what permissions a user & group has.

User Access to Schemas

This first query neatly lists out users, schemas & what permissions the users have on the schemas:

SELECT u.usename AS user, s.schemaname AS schema,
  has_schema_privilege(u.usename, s.schemaname, 'usage') AS usage_permission,
  has_schema_privilege(u.usename, s.schemaname, 'create') AS create_permission
FROM pg_user u CROSS JOIN (SELECT DISTINCT schemaname FROM pg_tables) s
WHERE u.usename IN ('user1@mycompany.com', 'user2@mycompany.com', 'user3@mycompany.com')
AND s.schemaname IN ('testing_okta');

The query is essentially using Redshift’s HAS_SCHEMA_PRIVILEGE function to get the user’s privileges. Notice how the usernames are email addresses; that’s how the users are created in Redshift when they log in via Okta for the first time.

This query produces an output like this:

The “usage” permission on a schema allows users to access objects in the schema & the “create” permission allows users to create objects in the schema.

Redshift Group Permissions

This next query lists out the permissions that members of a Redshift group have. Since Redshift groups are used as the primary means of restricting permissions (by creating Okta groups by the same name), this was critical information for us to ensure the least privilege after Okta integration.

SELECT schema, table_name, g.groname AS group,
  CASE WHEN charindex('r', split_part(split_part(array_to_string(relacl,
    '|'), g.groname, 2), '/', 1)) > 0 THEN 'SELECT ' ELSE '' END ||
  CASE WHEN charindex('a', split_part(split_part(array_to_string(relacl,
    '|'), g.groname, 2), '/', 1)) > 0 THEN 'INSERT ' ELSE '' END ||
  CASE WHEN charindex('w', split_part(split_part(array_to_string(relacl,
    '|'), g.groname, 2), '/', 1)) > 0 THEN 'UPDATE ' ELSE '' END ||
  CASE WHEN charindex('d', split_part(split_part(array_to_string(relacl,
    '|'), g.groname, 2), '/', 1)) > 0 THEN 'DELETE ' ELSE '' END
  AS permisssions FROM (
    SELECT n.nspname AS schema, c.relname AS table_name, c.relacl
    FROM pg_user u1 CROSS JOIN pg_class c
    LEFT JOIN pg_namespace n ON c.relnamespace = n.oid
    LEFT JOIN pg_user u2 ON c.relowner = u2.usesysid
    WHERE c.relowner = u1.usesysid AND n.nspname = 'testing_okta'
  ) JOIN pg_group g ON array_to_string(relacl, '|')
      LIKE '%' || g.groname || '%'
WHERE relacl IS NOT NULL AND g.groname = 'testing_okta';

This query essentially decodes the pg_class.relacl value using this, & presents the results in a human-friendly manner, like so:

Having verified all permissions, both at the user & group level, we were at last able to roll out this Redshift Okta integration to production!

Conclusion

If you too are looking to integrate Okta with Redshift, I hope this article, the AWS blog post, & the queries provided here make your journey as smooth as possible. 😊

About the Author ✍🏻

Harish KM is a Principal DevOps Engineer at QloudX & a top-ranked AWS Ambassador since 2020. 👨🏻‍💻

With over a decade of industry experience as everything from a full-stack engineer to a cloud architect, Harish has built many world-class solutions for clients around the world! 👷🏻‍♂️

With over 20 certifications in cloud (AWS, Azure, GCP), containers (Kubernetes, Docker) & DevOps (Terraform, Ansible, Jenkins), Harish is an expert in a multitude of technologies. 📚

These days, his focus is on the fascinating world of DevOps & how it can transform the way we do things! 🚀

Okta Redshift