Redshift Research Project

System Table Tracker

System Table Version 1.0.41465 / 2022-09-10

schema name column data type
pg_catalog svv_language_privileges admin_option bool
pg_catalog svv_language_privileges identity_id int4
pg_catalog svv_language_privileges identity_name varchar(128)
pg_catalog svv_language_privileges identity_type varchar(128)
pg_catalog svv_language_privileges language_name varchar(128)
pg_catalog svv_language_privileges privilege_type varchar(128)

View Text

SELECT CAST(derived_table6.language_name AS varchar(128)) AS language_name,
       CAST(CASE
              WHEN derived_table6.priv_id = CAST(8 AS oid)
                THEN CAST('USAGE' AS text)
              ELSE CAST('UNKOWN' AS text)
            END AS varchar(128)) AS privilege_type,
       derived_table6.identity_id,
       CAST(derived_table6.identity_name AS varchar(128)) AS identity_name,
       CAST(derived_table6.identity_type AS varchar(128)) AS identity_type,
       derived_table6.admin_option
FROM (((SELECT perms.lanname AS language_name,
               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, perms.permid) AS admin_option
        FROM pg_user_permission
             INNER JOIN (SELECT pg_permission.permid,
                                pg_language.lanname,
                                pg_permission.privid
                         FROM pg_permission
                              INNER JOIN pg_language ON pg_language.oid = pg_permission.objid
                         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(CAST('pg_language' AS regclass) AS oid)
                           AND pg_permission.objsubid = 0) AS perms ON 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 perms.lanname AS language_name,
               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_language.lanname,
                                pg_permission.privid
                         FROM pg_permission
                              INNER JOIN pg_language ON pg_language.oid = pg_permission.objid
                         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(CAST('pg_language' AS regclass) AS oid)
                           AND pg_permission.objsubid = 0) AS perms ON 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 perms.lanname AS language_name,
              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_language.lanname,
                               pg_permission.privid
                        FROM pg_permission
                             INNER JOIN pg_language ON pg_language.oid = pg_permission.objid
                        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(CAST('pg_language' AS regclass) AS oid)
                          AND pg_permission.objsubid = 0) AS perms ON 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.lanname AS language_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 pg_permission.permid,
                   pg_language.lanname,
                   pg_permission.privid
            FROM pg_permission
                 INNER JOIN pg_language ON pg_language.oid = pg_permission.objid
            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(CAST('pg_language' AS regclass) AS oid)
              AND pg_permission.objsubid = 0) AS perms
      WHERE pg_perm_has_public_grantee(perms.permid)) AS derived_table6


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