Redshift Observatory

System Table Tracker

System view pg_catalog.svv_default_privileges version 1.0.54239 / 2023-08-04

schema name column data type
pg_catalog svv_default_privileges admin_option bool
pg_catalog svv_default_privileges grantee_id int4
pg_catalog svv_default_privileges grantee_name varchar(128)
pg_catalog svv_default_privileges grantee_type varchar(128)
pg_catalog svv_default_privileges object_type varchar(16)
pg_catalog svv_default_privileges owner_id int4
pg_catalog svv_default_privileges owner_name varchar(128)
pg_catalog svv_default_privileges owner_type varchar(128)
pg_catalog svv_default_privileges privilege_type varchar(128)
pg_catalog svv_default_privileges schema_name varchar(128)

View Text

SELECT CAST(derived_table12.schema_name AS varchar(128)) AS schema_name,
       CAST(CASE
              WHEN derived_table12.object_type = CAST('r' AS "char")
                THEN CAST('RELATION' AS text)
              WHEN derived_table12.object_type = CAST('f' AS "char")
                THEN CAST('FUNCTION' AS text)
              WHEN derived_table12.object_type = CAST('p' AS "char")
                THEN CAST('PROCEDURE' AS text)
              ELSE CAST('UNKNOWN' AS text)
            END AS varchar(16)) AS object_type,
       derived_table12.owner_id,
       CAST(derived_table12.owner_name AS varchar(128)) AS owner_name,
       CAST(CASE
              WHEN derived_table12.owner_type = 1
                THEN CAST('user' AS text)
              WHEN derived_table12.owner_type = 2
                THEN CAST('role' AS text)
              ELSE CAST('unknown' AS text)
            END AS varchar(128)) AS owner_type,
       CAST(CASE
              WHEN derived_table12.priv_id = CAST(0 AS oid)
                THEN CAST('INSERT' AS text)
              WHEN derived_table12.priv_id = CAST(1 AS oid)
                THEN CAST('SELECT' AS text)
              WHEN derived_table12.priv_id = CAST(2 AS oid)
                THEN CAST('UPDATE' AS text)
              WHEN derived_table12.priv_id = CAST(3 AS oid)
                THEN CAST('DELETE' AS text)
              WHEN derived_table12.priv_id = CAST(4 AS oid)
                THEN CAST('RULE' AS text)
              WHEN derived_table12.priv_id = CAST(5 AS oid)
                THEN CAST('REFERENCES' AS text)
              WHEN derived_table12.priv_id = CAST(6 AS oid)
                THEN CAST('TRIGGER' AS text)
              WHEN derived_table12.priv_id = CAST(7 AS oid)
                THEN CAST('EXECUTE' AS text)
              WHEN derived_table12.priv_id = CAST(11 AS oid)
                THEN CAST('DROP' AS text)
              ELSE CAST('UNKOWN' AS text)
            END AS varchar(128)) AS privilege_type,
       derived_table12.grantee_id,
       CAST(derived_table12.grantee_name AS varchar(128)) AS grantee_name,
       CAST(derived_table12.grantee_type AS varchar(128)) AS grantee_type,
       derived_table12.admin_option
FROM (((((((SELECT owned_default_perms.nspname AS schema_name,
                   owned_default_perms.objtype AS object_type,
                   owned_default_perms.ownerid AS owner_id,
                   owned_default_perms.ownername AS owner_name,
                   owned_default_perms.ownertype AS owner_type,
                   owned_default_perms.privid AS priv_id,
                   pg_identity.useid AS grantee_id,
                   pg_identity.usename AS grantee_name,
                   CAST('user' AS varchar) AS grantee_type,
                   pg_user_has_default_admin_option(pg_identity.useid, owned_default_perms.defpermid) AS admin_option
            FROM pg_shdepend
                 INNER JOIN (SELECT pg_default_permission.defpermid,
                                    pg_namespace.nspname,
                                    pg_unique_identity.authid AS ownerid,
                                    pg_unique_identity.authname AS ownername,
                                    pg_unique_identity.authtype AS ownertype,
                                    pg_default_permission.objtype,
                                    pg_default_permission.privid
                             FROM pg_default_permission
                                  LEFT JOIN pg_namespace ON pg_namespace.oid = pg_default_permission.namespaceid
                                  INNER JOIN pg_unique_identity ON pg_unique_identity.authtype = pg_default_permission.authtype
                                                               AND pg_unique_identity.authid = pg_default_permission.authid
                                                               AND pg_default_permission.authtype = 1
                                                               AND pg_default_permission.authid = current_user_id()
                             WHERE pg_default_permission.dbid = (SELECT pg_database.oid
                                                                 FROM pg_database
                                                                 WHERE pg_database.datname = current_database())) AS owned_default_perms ON owned_default_perms.defpermid = pg_shdepend.objid
                                                                                                                                        AND pg_shdepend.classid = CAST(4770 AS oid)
                 INNER JOIN pg_identity ON CAST(pg_identity.useid AS oid) = pg_shdepend.refobjid
                                       AND pg_shdepend.refclassid = CAST(4771 AS oid)
                                       AND pg_identity.usename !~~ CAST('f346c9b8%' AS text)

            UNION

            SELECT owned_default_perms.nspname AS schema_name,
                   owned_default_perms.objtype AS object_type,
                   owned_default_perms.ownerid AS owner_id,
                   owned_default_perms.ownername AS owner_name,
                   owned_default_perms.ownertype AS owner_type,
                   owned_default_perms.privid AS priv_id,
                   pg_role.rolid AS grantee_id,
                   pg_role.rolname AS grantee_name,
                   CAST('role' AS varchar) AS grantee_type,
                   FALSE AS admin_option
            FROM pg_shdepend
                 INNER JOIN (SELECT pg_default_permission.defpermid,
                                    pg_namespace.nspname,
                                    pg_unique_identity.authid AS ownerid,
                                    pg_unique_identity.authname AS ownername,
                                    pg_unique_identity.authtype AS ownertype,
                                    pg_default_permission.objtype,
                                    pg_default_permission.privid
                             FROM pg_default_permission
                                  LEFT JOIN pg_namespace ON pg_namespace.oid = pg_default_permission.namespaceid
                                  INNER JOIN pg_unique_identity ON pg_unique_identity.authtype = pg_default_permission.authtype
                                                               AND pg_unique_identity.authid = pg_default_permission.authid
                                                               AND pg_default_permission.authtype = 1
                                                               AND pg_default_permission.authid = current_user_id()
                             WHERE pg_default_permission.dbid = (SELECT pg_database.oid
                                                                 FROM pg_database
                                                                 WHERE pg_database.datname = current_database())) AS owned_default_perms ON owned_default_perms.defpermid = pg_shdepend.objid
                                                                                                                                        AND pg_shdepend.classid = CAST(4770 AS oid)
                 INNER JOIN pg_role ON CAST(pg_role.rolid AS oid) = pg_shdepend.refobjid
                                   AND pg_shdepend.refclassid = CAST(4775 AS oid)
                                   AND pg_role.rolname !~~ CAST('/%' AS text))

           UNION

           SELECT owned_default_perms.nspname AS schema_name,
                  owned_default_perms.objtype AS object_type,
                  owned_default_perms.ownerid AS owner_id,
                  owned_default_perms.ownername AS owner_name,
                  owned_default_perms.ownertype AS owner_type,
                  owned_default_perms.privid AS priv_id,
                  pg_group.grosysid AS grantee_id,
                  pg_group.groname AS grantee_name,
                  CAST('group' AS varchar) AS grantee_type,
                  FALSE AS admin_option
           FROM pg_shdepend
                INNER JOIN (SELECT pg_default_permission.defpermid,
                                   pg_namespace.nspname,
                                   pg_unique_identity.authid AS ownerid,
                                   pg_unique_identity.authname AS ownername,
                                   pg_unique_identity.authtype AS ownertype,
                                   pg_default_permission.objtype,
                                   pg_default_permission.privid
                            FROM pg_default_permission
                                 LEFT JOIN pg_namespace ON pg_namespace.oid = pg_default_permission.namespaceid
                                 INNER JOIN pg_unique_identity ON pg_unique_identity.authtype = pg_default_permission.authtype
                                                              AND pg_unique_identity.authid = pg_default_permission.authid
                                                              AND pg_default_permission.authtype = 1
                                                              AND pg_default_permission.authid = current_user_id()
                            WHERE pg_default_permission.dbid = (SELECT pg_database.oid
                                                                FROM pg_database
                                                                WHERE pg_database.datname = current_database())) AS owned_default_perms ON owned_default_perms.defpermid = pg_shdepend.objid
                                                                                                                                       AND pg_shdepend.classid = CAST(4770 AS oid)
                INNER JOIN pg_group ON CAST(pg_group.grosysid AS oid) = pg_shdepend.refobjid
                                   AND pg_shdepend.refclassid = CAST(4775 AS oid))

          UNION

          SELECT other_default_perms.nspname AS schema_name,
                 other_default_perms.objtype AS object_type,
                 other_default_perms.ownerid AS owner_id,
                 other_default_perms.ownername AS owner_name,
                 other_default_perms.ownertype AS owner_type,
                 other_default_perms.privid AS priv_id,
                 accessible_users.useid AS grantee_id,
                 accessible_users.usename AS grantee_name,
                 CAST('user' AS varchar) AS grantee_type,
                 pg_user_has_default_admin_option(accessible_users.useid, other_default_perms.defpermid) AS admin_option
          FROM pg_shdepend
               INNER JOIN (SELECT pg_default_permission.defpermid,
                                  pg_namespace.nspname,
                                  pg_unique_identity.authid AS ownerid,
                                  pg_unique_identity.authname AS ownername,
                                  pg_unique_identity.authtype AS ownertype,
                                  pg_default_permission.objtype,
                                  pg_default_permission.privid
                           FROM pg_default_permission
                                LEFT JOIN pg_namespace ON pg_namespace.oid = pg_default_permission.namespaceid
                                INNER JOIN pg_unique_identity ON pg_unique_identity.authtype = pg_default_permission.authtype
                                                             AND pg_unique_identity.authid = pg_default_permission.authid
                                                             AND pg_default_permission.authtype = 1
                                                             AND pg_default_permission.authid <> current_user_id()
                           WHERE pg_default_permission.dbid = (SELECT pg_database.oid
                                                               FROM pg_database
                                                               WHERE pg_database.datname = current_database())) AS other_default_perms ON other_default_perms.defpermid = pg_shdepend.objid
                                                                                                                                      AND pg_shdepend.classid = CAST(4770 AS oid)
               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 CAST(accessible_users.useid AS oid) = pg_shdepend.refobjid
                                                                                             AND pg_shdepend.refclassid = CAST(4771 AS oid))

         UNION

         SELECT other_default_perms.nspname AS schema_name,
                other_default_perms.objtype AS object_type,
                other_default_perms.ownerid AS owner_id,
                other_default_perms.ownername AS owner_name,
                other_default_perms.ownertype AS owner_type,
                other_default_perms.privid AS priv_id,
                accessible_roles.rolid AS grantee_id,
                accessible_roles.rolname AS grantee_name,
                CAST('role' AS varchar) AS grantee_type,
                FALSE AS admin_option
         FROM pg_shdepend
              INNER JOIN (SELECT pg_default_permission.defpermid,
                                 pg_namespace.nspname,
                                 pg_unique_identity.authid AS ownerid,
                                 pg_unique_identity.authname AS ownername,
                                 pg_unique_identity.authtype AS ownertype,
                                 pg_default_permission.objtype,
                                 pg_default_permission.privid
                          FROM pg_default_permission
                               LEFT JOIN pg_namespace ON pg_namespace.oid = pg_default_permission.namespaceid
                               INNER JOIN pg_unique_identity ON pg_unique_identity.authtype = pg_default_permission.authtype
                                                            AND pg_unique_identity.authid = pg_default_permission.authid
                                                            AND pg_default_permission.authtype = 1
                                                            AND pg_default_permission.authid <> current_user_id()
                          WHERE pg_default_permission.dbid = (SELECT pg_database.oid
                                                              FROM pg_database
                                                              WHERE pg_database.datname = current_database())) AS other_default_perms ON other_default_perms.defpermid = pg_shdepend.objid
                                                                                                                                     AND pg_shdepend.classid = CAST(4770 AS oid)
              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 CAST(accessible_roles.rolid AS oid) = pg_shdepend.refobjid
                                                                                           AND pg_shdepend.refclassid = CAST(4775 AS oid))

        UNION

        SELECT other_default_perms.nspname AS schema_name,
               other_default_perms.objtype AS object_type,
               other_default_perms.ownerid AS owner_id,
               other_default_perms.ownername AS owner_name,
               other_default_perms.ownertype AS owner_type,
               other_default_perms.privid AS priv_id,
               accessible_groups.grosysid AS grantee_id,
               accessible_groups.groname AS grantee_name,
               CAST('group' AS varchar) AS grantee_type,
               FALSE AS admin_option
        FROM pg_shdepend
             INNER JOIN (SELECT pg_default_permission.defpermid,
                                pg_namespace.nspname,
                                pg_unique_identity.authid AS ownerid,
                                pg_unique_identity.authname AS ownername,
                                pg_unique_identity.authtype AS ownertype,
                                pg_default_permission.objtype,
                                pg_default_permission.privid
                         FROM pg_default_permission
                              LEFT JOIN pg_namespace ON pg_namespace.oid = pg_default_permission.namespaceid
                              INNER JOIN pg_unique_identity ON pg_unique_identity.authtype = pg_default_permission.authtype
                                                           AND pg_unique_identity.authid = pg_default_permission.authid
                                                           AND pg_default_permission.authtype = 1
                                                           AND pg_default_permission.authid <> current_user_id()
                         WHERE pg_default_permission.dbid = (SELECT pg_database.oid
                                                             FROM pg_database
                                                             WHERE pg_database.datname = current_database())) AS other_default_perms ON other_default_perms.defpermid = pg_shdepend.objid
                                                                                                                                    AND pg_shdepend.classid = CAST(4770 AS oid)
             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 CAST(accessible_groups.grosysid AS oid) = pg_shdepend.refobjid
                                                                                        AND pg_shdepend.refclassid = CAST(4775 AS oid))

       UNION

       SELECT default_perms.nspname AS schema_name,
              default_perms.objtype AS object_type,
              default_perms.ownerid AS owner_id,
              default_perms.ownername AS owner_name,
              default_perms.ownertype AS owner_type,
              default_perms.privid AS priv_id,
              0 AS grantee_id,
              CAST('public' AS name) AS grantee_name,
              CAST('public' AS varchar) AS grantee_type,
              FALSE AS admin_option
       FROM (SELECT owned_default_perms.defpermid,
                    owned_default_perms.nspname,
                    owned_default_perms.ownerid,
                    owned_default_perms.ownername,
                    owned_default_perms.ownertype,
                    owned_default_perms.objtype,
                    owned_default_perms.privid
             FROM (SELECT pg_default_permission.defpermid,
                          pg_namespace.nspname,
                          pg_unique_identity.authid AS ownerid,
                          pg_unique_identity.authname AS ownername,
                          pg_unique_identity.authtype AS ownertype,
                          pg_default_permission.objtype,
                          pg_default_permission.privid
                   FROM pg_default_permission
                        LEFT JOIN pg_namespace ON pg_namespace.oid = pg_default_permission.namespaceid
                        INNER JOIN pg_unique_identity ON pg_unique_identity.authtype = pg_default_permission.authtype
                                                     AND pg_unique_identity.authid = pg_default_permission.authid
                                                     AND pg_default_permission.authtype = 1
                                                     AND pg_default_permission.authid = current_user_id()
                   WHERE pg_default_permission.dbid = (SELECT pg_database.oid
                                                       FROM pg_database
                                                       WHERE pg_database.datname = current_database())) AS owned_default_perms

             UNION

             SELECT other_default_perms.defpermid,
                    other_default_perms.nspname,
                    other_default_perms.ownerid,
                    other_default_perms.ownername,
                    other_default_perms.ownertype,
                    other_default_perms.objtype,
                    other_default_perms.privid
             FROM (SELECT pg_default_permission.defpermid,
                          pg_namespace.nspname,
                          pg_unique_identity.authid AS ownerid,
                          pg_unique_identity.authname AS ownername,
                          pg_unique_identity.authtype AS ownertype,
                          pg_default_permission.objtype,
                          pg_default_permission.privid
                   FROM pg_default_permission
                        LEFT JOIN pg_namespace ON pg_namespace.oid = pg_default_permission.namespaceid
                        INNER JOIN pg_unique_identity ON pg_unique_identity.authtype = pg_default_permission.authtype
                                                     AND pg_unique_identity.authid = pg_default_permission.authid
                                                     AND pg_default_permission.authtype = 1
                                                     AND pg_default_permission.authid <> current_user_id()
                   WHERE pg_default_permission.dbid = (SELECT pg_database.oid
                                                       FROM pg_database
                                                       WHERE pg_database.datname = current_database())) AS other_default_perms) AS default_perms
       WHERE pg_default_perm_has_public_grantee(default_perms.defpermid))

      UNION

      SELECT default_perms.nspname AS schema_name,
             default_perms.objtype AS object_type,
             default_perms.ownerid AS owner_id,
             default_perms.ownername AS owner_name,
             default_perms.ownertype AS owner_type,
             default_perms.privid AS priv_id,
             default_perms.ownerid AS grantee_id,
             default_perms.ownername AS grantee_name,
             CAST('user' AS varchar) AS grantee_type,
             TRUE AS admin_option
      FROM (SELECT owned_default_perms.defpermid,
                   owned_default_perms.nspname,
                   owned_default_perms.ownerid,
                   owned_default_perms.ownername,
                   owned_default_perms.ownertype,
                   owned_default_perms.objtype,
                   owned_default_perms.privid
            FROM (SELECT pg_default_permission.defpermid,
                         pg_namespace.nspname,
                         pg_unique_identity.authid AS ownerid,
                         pg_unique_identity.authname AS ownername,
                         pg_unique_identity.authtype AS ownertype,
                         pg_default_permission.objtype,
                         pg_default_permission.privid
                  FROM pg_default_permission
                       LEFT JOIN pg_namespace ON pg_namespace.oid = pg_default_permission.namespaceid
                       INNER JOIN pg_unique_identity ON pg_unique_identity.authtype = pg_default_permission.authtype
                                                    AND pg_unique_identity.authid = pg_default_permission.authid
                                                    AND pg_default_permission.authtype = 1
                                                    AND pg_default_permission.authid = current_user_id()
                  WHERE pg_default_permission.dbid = (SELECT pg_database.oid
                                                      FROM pg_database
                                                      WHERE pg_database.datname = current_database())) AS owned_default_perms

            UNION

            SELECT other_default_perms.defpermid,
                   other_default_perms.nspname,
                   other_default_perms.ownerid,
                   other_default_perms.ownername,
                   other_default_perms.ownertype,
                   other_default_perms.objtype,
                   other_default_perms.privid
            FROM (SELECT pg_default_permission.defpermid,
                         pg_namespace.nspname,
                         pg_unique_identity.authid AS ownerid,
                         pg_unique_identity.authname AS ownername,
                         pg_unique_identity.authtype AS ownertype,
                         pg_default_permission.objtype,
                         pg_default_permission.privid
                  FROM pg_default_permission
                       LEFT JOIN pg_namespace ON pg_namespace.oid = pg_default_permission.namespaceid
                       INNER JOIN pg_unique_identity ON pg_unique_identity.authtype = pg_default_permission.authtype
                                                    AND pg_unique_identity.authid = pg_default_permission.authid
                                                    AND pg_default_permission.authtype = 1
                                                    AND pg_default_permission.authid <> current_user_id()
                  WHERE pg_default_permission.dbid = (SELECT pg_database.oid
                                                      FROM pg_database
                                                      WHERE pg_database.datname = current_database())) AS other_default_perms
            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))) AS default_perms
      WHERE pg_default_perm_has_owner_grantee(default_perms.defpermid)) AS derived_table12


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