Redshift Observatory

System Table Tracker

System view pg_catalog.svv_rls_attached_policy version 1.0.43931 / 2022-11-18

View Text

SELECT derived_table14.relschema,
       derived_table14.relname,
       derived_table14.relkind,
       derived_table14.polname,
       derived_table14.grantor,
       derived_table14.grantee,
       derived_table14.granteekind,
       derived_table14.is_pol_on,
       derived_table14.is_rls_on
FROM ((SELECT p.relschema,
              p.relname,
              p.relkind,
              p.polname,
              CAST(o.usename AS varchar) AS grantor,
              CAST(e.usename AS varchar) AS grantee,
              CAST('user' AS varchar) AS granteekind,
              p.is_pol_on,
              p.is_rls_on
       FROM (SELECT CAST(n.nspname AS varchar) AS relschema,
                    CAST(c.relname AS varchar) AS relname,
                    CAST(CASE
                           WHEN c.relkind = CAST('r' AS "char")
                             THEN CAST('table' AS text)
                           ELSE CAST('unknown' AS text)
                         END AS varchar) AS relkind,
                    CAST(p.polname AS varchar) AS polname,
                    TRUE AS is_pol_on,
                    COALESCE(e.value = CAST('1' AS text),
                             FALSE) AS is_rls_on,
                    p.polid,
                    c.oid AS relid,
                    r.permid
             FROM pg_policy AS p
                  INNER JOIN pg_permission AS r ON r.privid = p.polid
                                               AND r.privtype = 4
                  INNER JOIN pg_class AS c ON c.oid = r.objid
                  INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace
                  LEFT JOIN pg_class_extended AS e ON e.reloid = c.oid
                                                  AND e.colnum = 20) AS p
            INNER JOIN pg_user_permission AS up ON up.permid = p.permid
            INNER JOIN pg_identity AS e ON e.useid = up.userid
            LEFT JOIN pg_identity AS o ON o.useid = rls_get_attached_user_grantor(p.polid, p.relid, up.userid)

       UNION

       SELECT p.relschema,
              p.relname,
              p.relkind,
              p.polname,
              CAST(o.usename AS varchar) AS grantor,
              CAST(e.rolname AS varchar) AS grantee,
              CAST('role' AS varchar) AS granteekind,
              p.is_pol_on,
              p.is_rls_on
       FROM (SELECT CAST(n.nspname AS varchar) AS relschema,
                    CAST(c.relname AS varchar) AS relname,
                    CAST(CASE
                           WHEN c.relkind = CAST('r' AS "char")
                             THEN CAST('table' AS text)
                           ELSE CAST('unknown' AS text)
                         END AS varchar) AS relkind,
                    CAST(p.polname AS varchar) AS polname,
                    TRUE AS is_pol_on,
                    COALESCE(e.value = CAST('1' AS text),
                             FALSE) AS is_rls_on,
                    p.polid,
                    c.oid AS relid,
                    r.permid
             FROM pg_policy AS p
                  INNER JOIN pg_permission AS r ON r.privid = p.polid
                                               AND r.privtype = 4
                  INNER JOIN pg_class AS c ON c.oid = r.objid
                  INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace
                  LEFT JOIN pg_class_extended AS e ON e.reloid = c.oid
                                                  AND e.colnum = 20) AS p
            INNER JOIN pg_role_permission AS rp ON rp.permid = p.permid
            INNER JOIN pg_role AS e ON e.rolid = rp.roleid
            LEFT JOIN pg_identity AS o ON o.useid = rls_get_attached_role_grantor(p.polid, p.relid, rp.roleid))

      UNION

      SELECT p.relschema,
             p.relname,
             p.relkind,
             p.polname,
             CAST(o.usename AS varchar) AS grantor,
             CAST('public' AS varchar) AS grantee,
             CAST('public' AS varchar) AS granteekind,
             p.is_pol_on,
             p.is_rls_on
      FROM (SELECT CAST(n.nspname AS varchar) AS relschema,
                   CAST(c.relname AS varchar) AS relname,
                   CAST(CASE
                          WHEN c.relkind = CAST('r' AS "char")
                            THEN CAST('table' AS text)
                          ELSE CAST('unknown' AS text)
                        END AS varchar) AS relkind,
                   CAST(p.polname AS varchar) AS polname,
                   TRUE AS is_pol_on,
                   COALESCE(e.value = CAST('1' AS text),
                            FALSE) AS is_rls_on,
                   p.polid,
                   c.oid AS relid,
                   r.permid
            FROM pg_policy AS p
                 INNER JOIN pg_permission AS r ON r.privid = p.polid
                                              AND r.privtype = 4
                 INNER JOIN pg_class AS c ON c.oid = r.objid
                 INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace
                 LEFT JOIN pg_class_extended AS e ON e.reloid = c.oid
                                                 AND e.colnum = 20) AS p
           LEFT JOIN pg_identity AS o ON o.useid = rls_get_attached_user_grantor(p.polid, p.relid, 0)
      WHERE is_rls_attached_to_public(p.polid, p.relid)) AS derived_table14
WHERE EXISTS (SELECT 1
              FROM pg_identity
              WHERE pg_identity.useid = current_user_id()
                AND pg_identity.usesuper = TRUE)
   OR user_is_member_of(CAST("current_user"() AS name),
                        CAST('sys:secadmin' AS name))

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