Redshift Research Project

System Table Tracker

System view pg_catalog.svv_relation_privileges version 1.0.41465 / 2022-09-10

schema name column data type
pg_catalog svv_relation_privileges admin_option bool
pg_catalog svv_relation_privileges identity_id int4
pg_catalog svv_relation_privileges identity_name varchar(128)
pg_catalog svv_relation_privileges identity_type varchar(128)
pg_catalog svv_relation_privileges namespace_name varchar(128)
pg_catalog svv_relation_privileges privilege_type varchar(128)
pg_catalog svv_relation_privileges relation_name varchar(128)

View Text

SELECT CAST(derived_table3.namespace_name AS varchar(128)) AS namespace_name,
       CAST(derived_table3.relation_name AS varchar(128)) AS relation_name,
       CAST(CASE
              WHEN derived_table3.priv_id = CAST(0 AS oid)
                THEN CAST('INSERT' AS text)
              WHEN derived_table3.priv_id = CAST(1 AS oid)
                THEN CAST('SELECT' AS text)
              WHEN derived_table3.priv_id = CAST(2 AS oid)
                THEN CAST('UPDATE' AS text)
              WHEN derived_table3.priv_id = CAST(3 AS oid)
                THEN CAST('DELETE' AS text)
              WHEN derived_table3.priv_id = CAST(4 AS oid)
                THEN CAST('RULE' AS text)
              WHEN derived_table3.priv_id = CAST(5 AS oid)
                THEN CAST('REFERENCES' AS text)
              WHEN derived_table3.priv_id = CAST(6 AS oid)
                THEN CAST('TRIGGER' AS text)
              WHEN derived_table3.priv_id = CAST(11 AS oid)
                THEN CAST('DROP' AS text)
              ELSE CAST('UNKOWN' AS text)
            END AS varchar(128)) AS privilege_type,
       derived_table3.identity_id,
       CAST(derived_table3.identity_name AS varchar(128)) AS identity_name,
       CAST(derived_table3.identity_type AS varchar(128)) AS identity_type,
       derived_table3.admin_option
FROM ((((((SELECT owned_perms.nspname AS namespace_name,
                  owned_perms.relname AS relation_name,
                  owned_perms.privid AS priv_id,
                  pg_identity.useid AS identity_id,
                  pg_identity.usename AS identity_name,
                  CAST('user' AS varchar) AS identity_type,
                  pg_user_has_admin_option(pg_identity.useid, owned_perms.permid) AS admin_option
           FROM pg_user_permission
                INNER JOIN (SELECT pg_permission.permid,
                                   pg_namespace.nspname,
                                   pg_class.relname,
                                   pg_permission.privid
                            FROM pg_permission
                                 INNER JOIN pg_class ON pg_class.oid = pg_permission.objid
                                                    AND (pg_class.relkind = CAST('r' AS "char")
                                                      OR pg_class.relkind = CAST('v' AS "char"))
                                                    AND pg_class.relowner = current_user_id()
                                 INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
                            WHERE pg_permission.dbid = (SELECT pg_database.oid
                                                        FROM pg_database
                                                        WHERE pg_database.datname = current_database())
                              AND pg_permission.privtype = 1
                              AND pg_permission.objtype = CAST(1259 AS oid)
                              AND pg_permission.objsubid = 0) AS owned_perms ON owned_perms.permid = pg_user_permission.permid
                INNER JOIN pg_identity ON pg_identity.useid = pg_user_permission.userid
                                      AND pg_identity.usename !~~ CAST('f346c9b8%' AS text)

           UNION

           SELECT owned_perms.nspname AS namespace_name,
                  owned_perms.relname AS relation_name,
                  owned_perms.privid AS priv_id,
                  pg_role.rolid AS identity_id,
                  pg_role.rolname AS identity_name,
                  CAST('role' AS varchar) AS identity_type,
                  FALSE AS admin_option
           FROM pg_role_permission
                INNER JOIN (SELECT pg_permission.permid,
                                   pg_namespace.nspname,
                                   pg_class.relname,
                                   pg_permission.privid
                            FROM pg_permission
                                 INNER JOIN pg_class ON pg_class.oid = pg_permission.objid
                                                    AND (pg_class.relkind = CAST('r' AS "char")
                                                      OR pg_class.relkind = CAST('v' AS "char"))
                                                    AND pg_class.relowner = current_user_id()
                                 INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
                            WHERE pg_permission.dbid = (SELECT pg_database.oid
                                                        FROM pg_database
                                                        WHERE pg_database.datname = current_database())
                              AND pg_permission.privtype = 1
                              AND pg_permission.objtype = CAST(1259 AS oid)
                              AND pg_permission.objsubid = 0) AS owned_perms ON owned_perms.permid = pg_role_permission.permid
                INNER JOIN pg_role ON pg_role.rolid = pg_role_permission.roleid
                                  AND pg_role.rolname !~~ CAST('/%' AS text))

          UNION

          SELECT owned_perms.nspname AS namespace_name,
                 owned_perms.relname AS relation_name,
                 owned_perms.privid AS priv_id,
                 pg_group.grosysid AS identity_id,
                 pg_group.groname AS identity_name,
                 CAST('group' AS varchar) AS identity_type,
                 FALSE AS admin_option
          FROM pg_role_permission
               INNER JOIN (SELECT pg_permission.permid,
                                  pg_namespace.nspname,
                                  pg_class.relname,
                                  pg_permission.privid
                           FROM pg_permission
                                INNER JOIN pg_class ON pg_class.oid = pg_permission.objid
                                                   AND (pg_class.relkind = CAST('r' AS "char")
                                                     OR pg_class.relkind = CAST('v' AS "char"))
                                                   AND pg_class.relowner = current_user_id()
                                INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
                           WHERE pg_permission.dbid = (SELECT pg_database.oid
                                                       FROM pg_database
                                                       WHERE pg_database.datname = current_database())
                             AND pg_permission.privtype = 1
                             AND pg_permission.objtype = CAST(1259 AS oid)
                             AND pg_permission.objsubid = 0) AS owned_perms ON owned_perms.permid = pg_role_permission.permid
               INNER JOIN pg_group ON pg_group.grosysid = pg_role_permission.roleid)

         UNION

         SELECT other_perms.nspname AS namespace_name,
                other_perms.relname AS relation_name,
                other_perms.privid AS priv_id,
                accessible_users.useid AS identity_id,
                accessible_users.usename AS identity_name,
                CAST('user' AS varchar) AS identity_type,
                pg_user_has_admin_option(accessible_users.useid, other_perms.permid) AS admin_option
         FROM pg_user_permission
              INNER JOIN (SELECT pg_permission.permid,
                                 pg_namespace.nspname,
                                 pg_class.relname,
                                 pg_permission.privid
                          FROM pg_permission
                               INNER JOIN pg_class ON pg_class.oid = pg_permission.objid
                                                  AND (pg_class.relkind = CAST('r' AS "char")
                                                    OR pg_class.relkind = CAST('v' AS "char"))
                                                  AND pg_class.relowner <> current_user_id()
                               INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
                          WHERE pg_permission.dbid = (SELECT pg_database.oid
                                                      FROM pg_database
                                                      WHERE pg_database.datname = current_database())
                            AND pg_permission.privtype = 1
                            AND pg_permission.objtype = CAST(1259 AS oid)
                            AND pg_permission.objsubid = 0) AS other_perms ON other_perms.permid = pg_user_permission.permid
              INNER JOIN (SELECT pg_identity.useid,
                                 pg_identity.usename
                          FROM pg_identity
                          WHERE pg_identity.usename !~~ CAST('f346c9b8%' AS text)
                            AND (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('ACCESS SYSTEM TABLE' AS text))
                              OR pg_identity.useid = current_user_id())) AS accessible_users ON accessible_users.useid = pg_user_permission.userid)

        UNION

        SELECT other_perms.nspname AS namespace_name,
               other_perms.relname AS relation_name,
               other_perms.privid AS priv_id,
               pg_role_permission.roleid AS identity_id,
               accessible_roles.rolname AS identity_name,
               CAST('role' AS varchar) AS identity_type,
               FALSE AS admin_option
        FROM pg_role_permission
             INNER JOIN (SELECT pg_permission.permid,
                                pg_namespace.nspname,
                                pg_class.relname,
                                pg_permission.privid
                         FROM pg_permission
                              INNER JOIN pg_class ON pg_class.oid = pg_permission.objid
                                                 AND (pg_class.relkind = CAST('r' AS "char")
                                                   OR pg_class.relkind = CAST('v' AS "char"))
                                                 AND pg_class.relowner <> current_user_id()
                              INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
                         WHERE pg_permission.dbid = (SELECT pg_database.oid
                                                     FROM pg_database
                                                     WHERE pg_database.datname = current_database())
                           AND pg_permission.privtype = 1
                           AND pg_permission.objtype = CAST(1259 AS oid)
                           AND pg_permission.objsubid = 0) AS other_perms ON other_perms.permid = pg_role_permission.permid
             INNER JOIN (SELECT pg_role.rolid,
                                pg_role.rolname
                         FROM pg_role
                         WHERE pg_role.rolname !~~ CAST('/%' AS text)
                           AND (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('ACCESS SYSTEM TABLE' AS text))
                             OR user_is_member_of(CAST("current_user"() AS name),
                                                  pg_role.rolname)
                             OR current_user_id() = pg_role.rolowner)) AS accessible_roles ON accessible_roles.rolid = pg_role_permission.roleid)

       UNION

       SELECT other_perms.nspname AS namespace_name,
              other_perms.relname AS relation_name,
              other_perms.privid AS priv_id,
              pg_role_permission.roleid AS identity_id,
              accessible_groups.groname AS identity_name,
              CAST('group' AS varchar) AS identity_type,
              FALSE AS admin_option
       FROM pg_role_permission
            INNER JOIN (SELECT pg_permission.permid,
                               pg_namespace.nspname,
                               pg_class.relname,
                               pg_permission.privid
                        FROM pg_permission
                             INNER JOIN pg_class ON pg_class.oid = pg_permission.objid
                                                AND (pg_class.relkind = CAST('r' AS "char")
                                                  OR pg_class.relkind = CAST('v' AS "char"))
                                                AND pg_class.relowner <> current_user_id()
                             INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
                        WHERE pg_permission.dbid = (SELECT pg_database.oid
                                                    FROM pg_database
                                                    WHERE pg_database.datname = current_database())
                          AND pg_permission.privtype = 1
                          AND pg_permission.objtype = CAST(1259 AS oid)
                          AND pg_permission.objsubid = 0) AS other_perms ON other_perms.permid = pg_role_permission.permid
            INNER JOIN (SELECT pg_group.groname,
                               pg_group.grosysid
                        FROM pg_group
                        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('ACCESS SYSTEM TABLE' AS text))
                           OR user_is_member_of(CAST("current_user"() AS name),
                                                pg_group.groname)) AS accessible_groups ON accessible_groups.grosysid = pg_role_permission.roleid)

      UNION

      SELECT perms.nspname AS namespace_name,
             perms.relname AS relation_name,
             perms.privid AS priv_id,
             0 AS identity_id,
             CAST('public' AS name) AS identity_name,
             CAST('public' AS varchar) AS identity_type,
             FALSE AS admin_option
      FROM (SELECT owned_perms.permid,
                   owned_perms.nspname,
                   owned_perms.relname,
                   owned_perms.privid
            FROM (SELECT pg_permission.permid,
                         pg_namespace.nspname,
                         pg_class.relname,
                         pg_permission.privid
                  FROM pg_permission
                       INNER JOIN pg_class ON pg_class.oid = pg_permission.objid
                                          AND (pg_class.relkind = CAST('r' AS "char")
                                            OR pg_class.relkind = CAST('v' AS "char"))
                                          AND pg_class.relowner = current_user_id()
                       INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
                  WHERE pg_permission.dbid = (SELECT pg_database.oid
                                              FROM pg_database
                                              WHERE pg_database.datname = current_database())
                    AND pg_permission.privtype = 1
                    AND pg_permission.objtype = CAST(1259 AS oid)
                    AND pg_permission.objsubid = 0) AS owned_perms

            UNION

            SELECT other_perms.permid,
                   other_perms.nspname,
                   other_perms.relname,
                   other_perms.privid
            FROM (SELECT pg_permission.permid,
                         pg_namespace.nspname,
                         pg_class.relname,
                         pg_permission.privid
                  FROM pg_permission
                       INNER JOIN pg_class ON pg_class.oid = pg_permission.objid
                                          AND (pg_class.relkind = CAST('r' AS "char")
                                            OR pg_class.relkind = CAST('v' AS "char"))
                                          AND pg_class.relowner <> current_user_id()
                       INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
                  WHERE pg_permission.dbid = (SELECT pg_database.oid
                                              FROM pg_database
                                              WHERE pg_database.datname = current_database())
                    AND pg_permission.privtype = 1
                    AND pg_permission.objtype = CAST(1259 AS oid)
                    AND pg_permission.objsubid = 0) AS other_perms) AS perms
      WHERE pg_perm_has_public_grantee(perms.permid)) AS derived_table3


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