postgres-ownership-inventory.sql

Table of Contents

-- Veridon: read-only Eigentümer- und Berechtigungsinventur

-- Veridon: read-only Eigentümer- und Berechtigungsinventur

-- Kann mit dem heutigen Login veridon ausgeführt werden. -- Erzeugt, ändert und löscht keine Datenbankobjekte. -- Die Ausgabe entscheidet, ob neben Rollen und Schemas auch die Eigentümerschaft -- der Datenbank veridotest vom heutigen Runtime-Login übertragen werden muss.

SELECT current_database() AS database_name, current_user AS current_login, version() AS postgres_version;

-- Datenbank-Owner und explizite Datenbank-ACL. SELECT d.datname, pg_get_userbyid(d.datdba) AS database_owner, d.datacl AS database_acl, has_database_privilege(current_user, d.datname, 'CONNECT') AS can_connect, has_database_privilege(current_user, d.datname, 'CREATE') AS can_create_schema, has_database_privilege(current_user, d.datname, 'TEMP') AS can_create_temp FROM pg_database d WHERE d.datname = current_database();

-- Schema-Owner, rohe ACL und effektiv ermittelte Rechte des heutigen Logins. SELECT n.nspname AS application_schema, pg_get_userbyid(n.nspowner) AS schema_owner, n.nspacl AS schema_acl, has_schema_privilege(current_user, n.oid, 'USAGE') AS current_login_can_use, has_schema_privilege(current_user, n.oid, 'CREATE') AS current_login_can_create FROM pg_namespace n WHERE n.nspname IN ('audit', 'cmdb', 'iam', 'workflow', 'workflow_engine') ORDER BY n.nspname;

-- Eigentümer und Anzahl der Relationen. relkind: -- r=table, p=partitioned table, S=sequence, v=view, m=materialized view, f=foreign table. SELECT n.nspname AS application_schema, c.relkind, pg_get_userbyid(c.relowner) AS object_owner, count(*) AS object_count FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname IN ('audit', 'cmdb', 'iam', 'workflow', 'workflow_engine') AND c.relkind IN ('r', 'p', 'S', 'v', 'm', 'f') GROUP BY n.nspname, c.relkind, c.relowner ORDER BY n.nspname, c.relkind, object_owner;

-- Default Privileges, die zukünftige Flyway-Objekte beeinflussen können. SELECT pg_get_userbyid(d.defaclrole) AS grantor_role, COALESCE(n.nspname, '') AS target_schema, d.defaclobjtype, d.defaclacl FROM pg_default_acl d LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace WHERE n.nspname IN ('audit', 'cmdb', 'iam', 'workflow', 'workflow_engine') OR d.defaclnamespace = 0 ORDER BY grantor_role, target_schema, d.defaclobjtype;

-- Direkte Rollenmitgliedschaften des aktuellen Logins. SELECT pg_get_userbyid(m.roleid) AS granted_role, pg_get_userbyid(m.member) AS member_role, m.admin_option FROM pg_auth_members m WHERE m.member = (SELECT oid FROM pg_roles WHERE rolname = current_user) ORDER BY granted_role;

-- Übersicht: auf welche Veridon-Schemas der heutige Login effektiv zugreifen kann. SELECT n.nspname AS application_schema, has_schema_privilege(current_user, n.oid, 'USAGE') AS can_use, has_schema_privilege(current_user, n.oid, 'CREATE') AS can_create FROM pg_namespace n WHERE n.nspname IN ('audit', 'cmdb', 'iam', 'workflow', 'workflow_engine') ORDER BY n.nspname;

database_name | current_login | postgres_version
---------------+---------------+----------------------------------------------------------------------------------------------------------- veridotest | veridon | PostgreSQL 18.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit (1 row)

datname | database_owner | database_acl | can_connect | can_create_schema | can_create_temp ------------+----------------+-----------------------------------------------------------+-------------+-------------------+----------------- veridotest | postgres | {=Tc/postgres,postgres=CTc/postgres,veridon=CTc/postgres} | t | t | t (1 row)

grantor_role | target_schema | defaclobjtype | defaclacl --------------+---------------+---------------+----------- (0 rows)

granted_role | member_role | admin_option --------------+-------------+-------------- (0 rows)