Redshift Observatory

System Table Tracker

System view pg_catalog.svv_database_privileges version 1.0.77706 / 2024-11-05

View Text

SELECT CAST(derived_table8.database_name AS varchar(128)) AS database_name,
       CAST(pg_get_privilege_type_text(derived_table8.priv_id) AS varchar(128)) AS privilege_type,
       derived_table8.identity_id,
       CAST(derived_table8.identity_name AS varchar(128)) AS identity_name,
       CAST(derived_table8.identity_type AS varchar(128)) AS identity_type,
       derived_table8.admin_option,
       CAST(pg_get_privilege_scope_text(derived_table8.priv_id,
                                        CAST(CAST('pg_database' AS regclass) AS oid)) AS varchar(128)) AS privilege_scope
FROM ((((((SELECT owned_perms.datname AS database_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_database.datname,
                                   pg_permission.privid
                            FROM pg_permission
                                 INNER JOIN pg_database ON pg_database.oid = pg_permission.objid
                                                       AND pg_database.datdba = current_user_id()
                            WHERE pg_permission.dbid = CAST(0 AS oid)
                              AND pg_permission.privtype = 1
                              AND pg_permission.objtype = CAST(1262 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.datname AS database_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_database.datname,
                                   pg_permission.privid
                            FROM pg_permission
                                 INNER JOIN pg_database ON pg_database.oid = pg_permission.objid
                                                       AND pg_database.datdba = current_user_id()
                            WHERE pg_permission.dbid = CAST(0 AS oid)
                              AND pg_permission.privtype = 1
                              AND pg_permission.objtype = CAST(1262 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.datname AS database_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_database.datname,
                                  pg_permission.privid
                           FROM pg_permission
                                INNER JOIN pg_database ON pg_database.oid = pg_permission.objid
                                                      AND pg_database.datdba = current_user_id()
                           WHERE pg_permission.dbid = CAST(0 AS oid)
                             AND pg_permission.privtype = 1
                             AND pg_permission.objtype = CAST(1262 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.datname AS database_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_database.datname,
                                 pg_permission.privid
                          FROM pg_permission
                               INNER JOIN pg_database ON pg_database.oid = pg_permission.objid
                                                     AND pg_database.datdba <> current_user_id()
                          WHERE pg_permission.dbid = CAST(0 AS oid)
                            AND pg_permission.privtype = 1
                            AND pg_permission.objtype = CAST(1262 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.datname AS database_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_database.datname,
                                pg_permission.privid
                         FROM pg_permission
                              INNER JOIN pg_database ON pg_database.oid = pg_permission.objid
                                                    AND pg_database.datdba <> current_user_id()
                         WHERE pg_permission.dbid = CAST(0 AS oid)
                           AND pg_permission.privtype = 1
                           AND pg_permission.objtype = CAST(1262 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.datname AS database_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_database.datname,
                               pg_permission.privid
                        FROM pg_permission
                             INNER JOIN pg_database ON pg_database.oid = pg_permission.objid
                                                   AND pg_database.datdba <> current_user_id()
                        WHERE pg_permission.dbid = CAST(0 AS oid)
                          AND pg_permission.privtype = 1
                          AND pg_permission.objtype = CAST(1262 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.datname AS database_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.datname,
                   owned_perms.privid
            FROM (SELECT pg_permission.permid,
                         pg_database.datname,
                         pg_permission.privid
                  FROM pg_permission
                       INNER JOIN pg_database ON pg_database.oid = pg_permission.objid
                                             AND pg_database.datdba = current_user_id()
                  WHERE pg_permission.dbid = CAST(0 AS oid)
                    AND pg_permission.privtype = 1
                    AND pg_permission.objtype = CAST(1262 AS oid)
                    AND pg_permission.objsubid = 0) AS owned_perms

            UNION

            SELECT other_perms.permid,
                   other_perms.datname,
                   other_perms.privid
            FROM (SELECT pg_permission.permid,
                         pg_database.datname,
                         pg_permission.privid
                  FROM pg_permission
                       INNER JOIN pg_database ON pg_database.oid = pg_permission.objid
                                             AND pg_database.datdba <> current_user_id()
                  WHERE pg_permission.dbid = CAST(0 AS oid)
                    AND pg_permission.privtype = 1
                    AND pg_permission.objtype = CAST(1262 AS oid)
                    AND pg_permission.objsubid = 0) AS other_perms) AS perms
      WHERE pg_perm_has_public_grantee(perms.permid)) AS derived_table8

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