Redshift Observatory

System Table Tracker

System view pg_catalog.svv_schema_privileges version 1.0.71912 / 2024-08-02

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

View Text

SELECT CAST(derived_table6.namespace_name AS varchar(128)) AS namespace_name,
       CAST(CASE
              WHEN derived_table6.priv_id = CAST(8 AS oid)
                THEN CAST('USAGE' AS text)
              WHEN derived_table6.priv_id = CAST(9 AS oid)
                THEN CAST('CREATE' AS text)
              WHEN derived_table6.priv_id = CAST(13 AS oid)
                THEN CAST('ALTER' AS text)
              ELSE CAST('UNKNOWN' 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 owned_perms.nspname AS namespace_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_permission.privid
                            FROM pg_permission
                                 INNER JOIN pg_namespace ON pg_namespace.oid = pg_permission.objid
                                                        AND pg_namespace.nspowner = current_user_id()
                            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_namespace' AS regclass) 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.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_permission.privid
                            FROM pg_permission
                                 INNER JOIN pg_namespace ON pg_namespace.oid = pg_permission.objid
                                                        AND pg_namespace.nspowner = current_user_id()
                            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_namespace' AS regclass) 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.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_permission.privid
                           FROM pg_permission
                                INNER JOIN pg_namespace ON pg_namespace.oid = pg_permission.objid
                                                       AND pg_namespace.nspowner = current_user_id()
                           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_namespace' AS regclass) 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.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_permission.privid
                          FROM pg_permission
                               INNER JOIN pg_namespace ON pg_namespace.oid = pg_permission.objid
                                                      AND pg_namespace.nspowner <> current_user_id()
                          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_namespace' AS regclass) 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.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_permission.privid
                         FROM pg_permission
                              INNER JOIN pg_namespace ON pg_namespace.oid = pg_permission.objid
                                                     AND pg_namespace.nspowner <> current_user_id()
                         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_namespace' AS regclass) 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.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_permission.privid
                        FROM pg_permission
                             INNER JOIN pg_namespace ON pg_namespace.oid = pg_permission.objid
                                                    AND pg_namespace.nspowner <> current_user_id()
                        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_namespace' AS regclass) 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.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.privid
            FROM (SELECT pg_permission.permid,
                         pg_namespace.nspname,
                         pg_permission.privid
                  FROM pg_permission
                       INNER JOIN pg_namespace ON pg_namespace.oid = pg_permission.objid
                                              AND pg_namespace.nspowner = current_user_id()
                  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_namespace' AS regclass) AS oid)
                    AND pg_permission.objsubid = 0) AS owned_perms

            UNION

            SELECT other_perms.permid,
                   other_perms.nspname,
                   other_perms.privid
            FROM (SELECT pg_permission.permid,
                         pg_namespace.nspname,
                         pg_permission.privid
                  FROM pg_permission
                       INNER JOIN pg_namespace ON pg_namespace.oid = pg_permission.objid
                                              AND pg_namespace.nspowner <> current_user_id()
                  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_namespace' AS regclass) AS oid)
                    AND pg_permission.objsubid = 0) AS other_perms) 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