Redshift Research Project

System Table Tracker

System Table Version 1.0.41465 / 2022-09-10

schema name column data type
pg_catalog svv_system_privileges identity_id int4
pg_catalog svv_system_privileges identity_name varchar(128)
pg_catalog svv_system_privileges identity_type varchar(128)
pg_catalog svv_system_privileges system_privilege varchar(128)

View Text

SELECT CAST(CASE
              WHEN derived_table2.sys_priv_id = CAST(100 AS oid)
                THEN CAST('CREATE USER' AS text)
              WHEN derived_table2.sys_priv_id = CAST(101 AS oid)
                THEN CAST('DROP USER' AS text)
              WHEN derived_table2.sys_priv_id = CAST(102 AS oid)
                THEN CAST('ALTER USER' AS text)
              WHEN derived_table2.sys_priv_id = CAST(103 AS oid)
                THEN CAST('CREATE ROLE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(104 AS oid)
                THEN CAST('DROP ROLE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(105 AS oid)
                THEN CAST('GRANT ROLE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(106 AS oid)
                THEN CAST('ALTER DEFAULT PRIVILEGES' AS text)
              WHEN derived_table2.sys_priv_id = CAST(107 AS oid)
                THEN CAST('CREATE SCHEMA' AS text)
              WHEN derived_table2.sys_priv_id = CAST(108 AS oid)
                THEN CAST('DROP SCHEMA' AS text)
              WHEN derived_table2.sys_priv_id = CAST(109 AS oid)
                THEN CAST('CREATE TABLE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(110 AS oid)
                THEN CAST('DROP TABLE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(111 AS oid)
                THEN CAST('ALTER TABLE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(112 AS oid)
                THEN CAST('TRUNCATE TABLE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(113 AS oid)
                THEN CAST('CREATE OR REPLACE VIEW' AS text)
              WHEN derived_table2.sys_priv_id = CAST(114 AS oid)
                THEN CAST('DROP VIEW' AS text)
              WHEN derived_table2.sys_priv_id = CAST(115 AS oid)
                THEN CAST('CREATE OR REPLACE FUNCTION' AS text)
              WHEN derived_table2.sys_priv_id = CAST(116 AS oid)
                THEN CAST('CREATE OR REPLACE EXTERNAL FUNCTION' AS text)
              WHEN derived_table2.sys_priv_id = CAST(117 AS oid)
                THEN CAST('DROP FUNCTION' AS text)
              WHEN derived_table2.sys_priv_id = CAST(118 AS oid)
                THEN CAST('CREATE OR REPLACE STORED PROCEDURES' AS text)
              WHEN derived_table2.sys_priv_id = CAST(119 AS oid)
                THEN CAST('DROP PROCEDURE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(120 AS oid)
                THEN CAST('SYSTEM CREATE MODEL' AS text)
              WHEN derived_table2.sys_priv_id = CAST(121 AS oid)
                THEN CAST('DROP MODEL' AS text)
              WHEN derived_table2.sys_priv_id = CAST(122 AS oid)
                THEN CAST('CREATE DATASHARE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(123 AS oid)
                THEN CAST('ALTER DATASHARE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(124 AS oid)
                THEN CAST('DROP DATASHARE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(125 AS oid)
                THEN CAST('CREATE LIBRARY' AS text)
              WHEN derived_table2.sys_priv_id = CAST(126 AS oid)
                THEN CAST('DROP LIBRARY' AS text)
              WHEN derived_table2.sys_priv_id = CAST(127 AS oid)
                THEN CAST('VACUUM' AS text)
              WHEN derived_table2.sys_priv_id = CAST(128 AS oid)
                THEN CAST('ANALYZE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(129 AS oid)
                THEN CAST('CANCEL' AS text)
              WHEN derived_table2.sys_priv_id = CAST(130 AS oid)
                THEN CAST('ACCESS SYSTEM TABLE' AS text)
              WHEN derived_table2.sys_priv_id = CAST(900 AS oid)
                THEN CAST('IGNORE RLS' AS text)
              WHEN derived_table2.sys_priv_id = CAST(901 AS oid)
                THEN CAST('EXPLAIN RLS' AS text)
              WHEN derived_table2.sys_priv_id = CAST(950 AS oid)
                THEN CAST('ALTER TABLE ENABLE ROW LEVEL SECURITY' AS text)
              WHEN derived_table2.sys_priv_id = CAST(951 AS oid)
                THEN CAST('CREATE RLS POLICY' AS text)
              WHEN derived_table2.sys_priv_id = CAST(952 AS oid)
                THEN CAST('ATTACH RLS POLICY' AS text)
              WHEN derived_table2.sys_priv_id = CAST(953 AS oid)
                THEN CAST('DETACH RLS POLICY' AS text)
              WHEN derived_table2.sys_priv_id = CAST(954 AS oid)
                THEN CAST('DROP RLS POLICY' AS text)
              ELSE CAST('UNKOWN' AS text)
            END AS varchar(128)) AS system_privilege,
       derived_table2.identity_id,
       CAST(derived_table2.identity_name AS varchar(128)) AS identity_name,
       CAST(derived_table2.identity_type AS varchar(128)) AS identity_type
FROM (SELECT sys_privs.privid AS sys_priv_id,
             accessible_users.useid AS identity_id,
             accessible_users.usename AS identity_name,
             CAST('user' AS varchar) AS identity_type
      FROM pg_user_permission
           INNER JOIN (SELECT pg_permission.permid,
                              pg_permission.privid
                       FROM pg_permission
                       WHERE pg_permission.dbid = CAST(0 AS oid)
                         AND pg_permission.objtype = CAST(0 AS oid)
                         AND pg_permission.objsubid = 0
                         AND pg_permission.privtype = 1) AS sys_privs ON sys_privs.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 sys_privs.privid AS sys_priv_id,
             pg_role_permission.roleid AS identity_id,
             accessible_roles.rolname AS identity_name,
             CAST('role' AS varchar) AS identity_type
      FROM pg_role_permission
           INNER JOIN (SELECT pg_permission.permid,
                              pg_permission.privid
                       FROM pg_permission
                       WHERE pg_permission.dbid = CAST(0 AS oid)
                         AND pg_permission.objtype = CAST(0 AS oid)
                         AND pg_permission.objsubid = 0
                         AND pg_permission.privtype = 1) AS sys_privs ON sys_privs.permid = pg_role_permission.permid
           INNER JOIN (SELECT pg_role.rolid,
                              pg_role.rolname
                       FROM pg_role
                       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_role.rolname)
                          OR current_user_id() = pg_role.rolowner) AS accessible_roles ON accessible_roles.rolid = pg_role_permission.roleid) AS derived_table2


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