Redshift Observatory

System Table Tracker

System view pg_catalog.svv_rls_relation version 1.0.72982 / 2024-08-17

View Text

SELECT rls_info_on_all_rels.datname,
       rls_info_on_all_rels.relschema,
       rls_info_on_all_rels.relname,
       rls_info_on_all_rels.relkind,
       rls_info_on_all_rels.is_rls_on,
       rls_info_on_all_rels.is_rls_datashare_on,
       rls_info_on_all_rels.rls_conjunction_type,
       rls_info_on_all_rels.rls_datashare_conjunction_type
FROM (SELECT pgc.oid AS reloid,
             CAST(current_database() AS varchar(128)) AS datname,
             CAST(pns.nspname AS varchar(128)) AS relschema,
             CAST(pgc.relname AS varchar(128)) AS relname,
             CAST(CASE
                    WHEN pgc.relkind = CAST('r' AS "char")
                      THEN CAST('table' AS text)
                    WHEN pgc.relkind = CAST('v' AS "char")
                      THEN CASE
                             WHEN mvinfo.value IS NOT NULL
                               THEN CAST('materialized view' AS text)
                             WHEN pgc.relnatts = 0
                               THEN CAST('late binding view' AS text)
                             ELSE CAST('view' AS text)
                           END
                    ELSE CAST('unknown' AS text)
                  END AS varchar(17)) AS relkind,
             CAST(CASE
                    WHEN rinfo.colnum = 20
                     AND rinfo.value = CAST('1' AS text)
                      THEN CAST('t' AS text)
                    ELSE CAST('f' AS text)
                  END AS char) AS is_rls_on,
             CAST(CASE
                    WHEN rdsinfo.colnum = 25
                      THEN CASE
                             WHEN rdsinfo.value = CAST('0' AS text)
                               THEN CAST('f' AS text)
                             WHEN rdsinfo.value = CAST('1' AS text)
                               THEN CAST('t' AS text)
                             ELSE CAST('u' AS text)
                           END
                    ELSE CASE
                           WHEN CAST(CASE
                                       WHEN rinfo.colnum = 20
                                        AND rinfo.value = CAST('1' AS text)
                                         THEN CAST('t' AS text)
                                       ELSE CAST('f' AS text)
                                     END AS char) = CAST('t' AS bpchar)
                             THEN CAST('t' AS text)
                           ELSE CAST('f' AS text)
                         END
                  END AS char) AS is_rls_datashare_on,
             CAST(CASE
                    WHEN rminfo.colnum = 27
                     AND CAST(CAST(rminfo.value AS integer) & 1 AS boolean)
                      THEN CAST('or' AS text)
                    WHEN rminfo.colnum = 27
                     AND NOT CAST(CAST(rminfo.value AS integer) & 1 AS boolean)
                      THEN CAST('and' AS text)
                    ELSE CAST('and' AS text)
                  END AS varchar(3)) AS rls_conjunction_type,
             CAST(CASE
                    WHEN rminfo.colnum = 27
                     AND CAST(CAST(rminfo.value AS integer) & 2 AS boolean)
                      THEN CAST('or' AS text)
                    WHEN rminfo.colnum = 27
                     AND NOT CAST(CAST(rminfo.value AS integer) & 2 AS boolean)
                      THEN CAST('and' AS text)
                    ELSE CAST('and' AS text)
                  END AS varchar(3)) AS rls_datashare_conjunction_type
      FROM pg_namespace AS pns
           INNER JOIN pg_class AS pgc ON pgc.relnamespace = pns.oid
           LEFT JOIN (SELECT pgce.reloid,
                             pgce.colnum,
                             pgce.value
                      FROM pg_class_extended AS pgce
                      WHERE pgce.colnum = 20) AS rinfo ON rinfo.reloid = pgc.oid
           LEFT JOIN (SELECT pgce.reloid,
                             pgce.value
                      FROM pg_class_extended AS pgce
                      WHERE pgce.colnum = 6) AS mvinfo ON mvinfo.reloid = pgc.oid
           LEFT JOIN (SELECT pgce.reloid,
                             pgce.colnum,
                             pgce.value
                      FROM pg_class_extended AS pgce
                      WHERE pgce.colnum = 25) AS rdsinfo ON rdsinfo.reloid = pgc.oid
           LEFT JOIN (SELECT pgce.reloid,
                             pgce.colnum,
                             pgce.value
                      FROM pg_class_extended AS pgce
                      WHERE pgce.colnum = 27) AS rminfo ON rminfo.reloid = pgc.oid
      WHERE (CAST(CASE
                    WHEN rinfo.colnum = 20
                     AND rinfo.value = CAST('1' AS text)
                      THEN CAST('t' AS text)
                    ELSE CAST('f' AS text)
                  END AS char) = CAST('t' AS bpchar)
          OR CAST(CASE
                    WHEN rdsinfo.colnum = 25
                      THEN CASE
                             WHEN rdsinfo.value = CAST('0' AS text)
                               THEN CAST('f' AS text)
                             WHEN rdsinfo.value = CAST('1' AS text)
                               THEN CAST('t' AS text)
                             ELSE CAST('u' AS text)
                           END
                    ELSE CASE
                           WHEN CAST(CASE
                                       WHEN rinfo.colnum = 20
                                        AND rinfo.value = CAST('1' AS text)
                                         THEN CAST('t' AS text)
                                       ELSE CAST('f' AS text)
                                     END AS char) = CAST('t' AS bpchar)
                             THEN CAST('t' AS text)
                           ELSE CAST('f' AS text)
                         END
                  END AS char) = CAST('t' AS bpchar))
        AND has_schema_privilege(CAST("current_user"() AS name),
                                 CAST(pns.nspname AS text),
                                 CAST('USAGE' AS text))) AS rls_info_on_all_rels
WHERE EXISTS (SELECT 1
              FROM pg_identity
              WHERE pg_identity.useid = current_user_id()
                AND pg_identity.usesuper = TRUE)
   OR has_system_privilege(CAST("current_user"() AS name),
                           CAST('CREATE RLS POLICY' AS text))

Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers