title: How to use RLS with compund field
body: <p>In <strong>Redshift</strong> we have a table <em>(let's call it <code>entity</code>)</em> which among other columns it has two important ones: <code>hierarchy_id</code> &amp; <code>entity_timestampt</code>, the <code>hierarchy_id</code> is a combination of the ids of three hierarchical dimensions <em>(<code>A</code>, <code>B</code>, <code>C</code>; each one having a relationship of one-to-many with the next one)</em>.<br />
  Thus: <code>hierarchy_id == A.a_id || '-' || B.b_id || '-' || C.c_id</code><br />
  Additionally the table is distributed according to <code>DISTKEY(hierarchy_id)</code> and sorted using <code>COMPOUND SORTKEY(hierarchy_id, entity_timestampt)</code>.</p>
  <p>Over this table we need to generate multiple reports, some of them are fixed to the depths level of the hierarchy, while others will be filtered by higher parts and group the results by the lowers. However, the first layer of the hierarchy <em>(the <code>A</code> dimension)</em> is what defines our security model, users will never have access to different <code>A</code> dimensions other than the one they belong <em>(this is our tenant information)</em>.<br />
  The current design proven to be useful for that matter when we were prototyping the reports in plain <strong>SQL</strong> as we could do things like this for the depths queries:</p>
  <pre class="lang-sql prettyprint-override"><code>WHERE
    entity.hierarchy_id = 'fixed_a_id-fixed_b_id-fixed_c_id' AND
    entity.entity_timestampt BETWEEN 'start_date' AND 'end_data'
  </code></pre>
  <p>Or like this for filtering by other points of the hierarchy:</p>
  <pre class="lang-sql prettyprint-override"><code>WHERE
    entity.hierarchy_id LIKE 'fixed_a_id-%' AND
    entity.entity_timestampt BETWEEN 'start_date' AND 'end_data'
  </code></pre>
  <p>Which would still take advantage of the <code>DISTKEY</code> &amp; <code>SORTKEY</code> setup, even though we are filtering just for a partial path of the hierarchy.</p>
  <p>Now we want to use <strong>QuickSight</strong> for creating and sharing those reports using the embedding capabilities. But we haven't found a way to filter the data of the analysis as we want.<br />
  We tried to use the RLS by tags for annonymous users, but we have found two problems:</p>
  <ol>
  <li>How to inject the <code>A.a_id</code> part of the query in the API that generates the embedding URL in a secure way <em>(i.e. that users can't change it)</em>, While allowing them to configure the other parts of the hierarchy. And finally combining those independent pieces in the filter; without needing to generate a new URL each time users change the other parts.<br />
  <em>(however, we may live with this limitation but)</em></li>
  <li>How to do partial filters; i.e., the ones that looked like <code>LIKE 'fixed_a_id-fixed_b_id-%'</code> Since it seems RLS is always an equals condition.</li>
  </ol>
  <p>Is there any way to make <strong>QuickSight</strong> to work as we want with our current table design? Or would we need to change the design?<br />
  For the latter, we have thought on keeping the three dimension ids as separated columns, that way we may add RLS for the <code>A.a_id</code> column and use parameters for the other ones, the problem would be for the reports that group by lower parts of the hierarchy, it is not clear how we could define the <code>DISTKEY</code> and <code>SORTKEY</code> so that the queries are properly optimized.</p>