Redshift Observatory

System Table Tracker

System view pg_catalog.svv_attached_masking_policy version 1.0.52931 / 2023-07-07

View Text

SELECT derived_table15.policy_name,
       derived_table15.schema_name,
       derived_table15.table_name,
       derived_table15.table_type,
       derived_table15.grantor,
       derived_table15.grantee,
       derived_table15.grantee_type,
       derived_table15.priority,
       derived_table15.input_columns,
       derived_table15.output_columns
FROM ((SELECT p.policy_name,
              p.schema_name,
              p.table_name,
              p.table_type,
              p.grantor,
              CAST(e.usename AS varchar) AS grantee,
              CAST('user' AS varchar) AS grantee_type,
              p.priority,
              p.input_columns,
              p.output_columns
       FROM (SELECT r.permid,
                    CAST(p.polname AS varchar) AS policy_name,
                    CAST(n.nspname AS varchar) AS schema_name,
                    CAST(c.relname AS varchar) AS table_name,
                    CAST(CASE
                           WHEN c.relkind = CAST('r' AS "char")
                             THEN CAST('table' AS text)
                           ELSE CAST('unknown' AS text)
                         END AS varchar) AS table_type,
                    CAST(o.usename AS varchar) AS grantor,
                    pm.polpriority AS priority,
                    mask_policy_attached_atts_out(c.oid,
                                                  CAST(pm.polattrsin AS varchar)) AS input_columns,
                    mask_policy_attached_atts_out(c.oid,
                                                  CAST(pm.polattrsout AS varchar)) AS output_columns
             FROM pg_policy_mask AS p
                  INNER JOIN pg_permission AS r ON r.privid = p.polid
                                               AND r.privtype = 5
                  INNER JOIN pg_class AS c ON c.oid = r.objid
                  INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace
                  INNER JOIN pg_permission_mask AS pm ON pm.permid = r.permid
                  LEFT JOIN pg_identity AS o ON o.useid = pm.polmodifiedby) 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

       UNION

       SELECT p.policy_name,
              p.schema_name,
              p.table_name,
              p.table_type,
              p.grantor,
              CAST(e.rolname AS varchar) AS grantee,
              CAST('role' AS varchar) AS grantee_type,
              p.priority,
              p.input_columns,
              p.output_columns
       FROM (SELECT r.permid,
                    CAST(p.polname AS varchar) AS policy_name,
                    CAST(n.nspname AS varchar) AS schema_name,
                    CAST(c.relname AS varchar) AS table_name,
                    CAST(CASE
                           WHEN c.relkind = CAST('r' AS "char")
                             THEN CAST('table' AS text)
                           ELSE CAST('unknown' AS text)
                         END AS varchar) AS table_type,
                    CAST(o.usename AS varchar) AS grantor,
                    pm.polpriority AS priority,
                    mask_policy_attached_atts_out(c.oid,
                                                  CAST(pm.polattrsin AS varchar)) AS input_columns,
                    mask_policy_attached_atts_out(c.oid,
                                                  CAST(pm.polattrsout AS varchar)) AS output_columns
             FROM pg_policy_mask AS p
                  INNER JOIN pg_permission AS r ON r.privid = p.polid
                                               AND r.privtype = 5
                  INNER JOIN pg_class AS c ON c.oid = r.objid
                  INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace
                  INNER JOIN pg_permission_mask AS pm ON pm.permid = r.permid
                  LEFT JOIN pg_identity AS o ON o.useid = pm.polmodifiedby) 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)

      UNION

      SELECT p.policy_name,
             p.schema_name,
             p.table_name,
             p.table_type,
             p.grantor,
             CAST('public' AS varchar) AS grantee,
             CAST('public' AS varchar) AS grantee_type,
             p.priority,
             p.input_columns,
             p.output_columns
      FROM (SELECT r.permid,
                   CAST(p.polname AS varchar) AS policy_name,
                   CAST(n.nspname AS varchar) AS schema_name,
                   CAST(c.relname AS varchar) AS table_name,
                   CAST(CASE
                          WHEN c.relkind = CAST('r' AS "char")
                            THEN CAST('table' AS text)
                          ELSE CAST('unknown' AS text)
                        END AS varchar) AS table_type,
                   CAST(o.usename AS varchar) AS grantor,
                   pm.polpriority AS priority,
                   mask_policy_attached_atts_out(c.oid,
                                                 CAST(pm.polattrsin AS varchar)) AS input_columns,
                   mask_policy_attached_atts_out(c.oid,
                                                 CAST(pm.polattrsout AS varchar)) AS output_columns
            FROM pg_policy_mask AS p
                 INNER JOIN pg_permission AS r ON r.privid = p.polid
                                              AND r.privtype = 5
                 INNER JOIN pg_class AS c ON c.oid = r.objid
                 INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace
                 INNER JOIN pg_permission_mask AS pm ON pm.permid = r.permid
                 LEFT JOIN pg_identity AS o ON o.useid = pm.polmodifiedby) AS p
      WHERE NOT EXISTS (SELECT 1
                        FROM pg_role_permission
                        WHERE pg_role_permission.permid = p.permid)
        AND NOT EXISTS (SELECT 1
                        FROM pg_user_permission
                        WHERE pg_user_permission.permid = p.permid)) AS derived_table15
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