Row level security (SQL implementation) for user to see information of all employees reporting under them

[David_Wong]

I had to do something like this for my RLS rules although my hierarchy doesn’t go as deep, usually 4-5 levels at most. To get around the LISTAGG limitation, you can create a dataset that looks like this:

UserName, Employee Login
User C, C
User C, B
User C, A
User D, D
User D, C
User D, B
User D, A

However, with this approach, there’s another limitation that you need to be aware of. You’ll get an error if a user has more than 999 rows in your RLS rules.

Using row-level security with user-based rules to restrict access to a dataset...

I wasn’t able to use CTE in Quick Sight to create this query, so I had to do it at the data source level.

Custom SQL queries with Common Table Expressions

Are custom SQL queries with Common Table Expressions supported in Quick Sight? I’m trying to write a recursive query but I’m getting an error.

I see other users are using CTEs in Quick Sight, so maybe it depends on your data source:

Recursive CTE using custom SQL

The problem seems to be incorrect SQL syntax from the datasource (not an issue with Quick Sight) - that same query would give the same error if executed on postgresql directly. Try the query below: WITH RECURSIVE Months AS ( SELECT CAST('01-01-2021' AS timestamp) AS MyDate UNION ALL SELECT MyDate + interval '1 month' FROM Months WHERE MyDate <= CAST(NOW() AS DATE) ) select MyDate from Months