| schema | name | column | data type |
|---|---|---|---|
| pg_catalog | svv_database_privileges | admin_option | bool |
| pg_catalog | svv_database_privileges | database_name | varchar(128) |
| pg_catalog | svv_database_privileges | identity_id | int4 |
| pg_catalog | svv_database_privileges | identity_name | varchar(128) |
| pg_catalog | svv_database_privileges | identity_type | varchar(128) |
| pg_catalog | svv_database_privileges | privilege_type | varchar(128) |
SELECT CAST(derived_table7.database_name AS varchar(128)) AS database_name,
CAST(CASE
WHEN derived_table7.priv_id = CAST(8 AS oid)
THEN CAST('USAGE' AS text)
WHEN derived_table7.priv_id = CAST(9 AS oid)
THEN CAST('CREATE' AS text)
WHEN derived_table7.priv_id = CAST(10 AS oid)
THEN CAST('TEMP' AS text)
ELSE CAST('UNKOWN' AS text)
END AS varchar(128)) AS privilege_type,
derived_table7.identity_id,
CAST(derived_table7.identity_name AS varchar(128)) AS identity_name,
CAST(derived_table7.identity_type AS varchar(128)) AS identity_type,
derived_table7.admin_option
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_table7 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