1use serde_json::{json, Value};
2use tokio_postgres::Client;
3use crate::errors::Result as MCPResult;
4
5pub async fn security_audit(client: &Client, _params: &Option<&Value>) -> MCPResult<Value> {
6 let superusers: Vec<Value> = client.query(
7 "SELECT rolname AS role,
8 rolsuper AS is_superuser,
9 rolcreatedb AS can_create_db,
10 rolcreaterole AS can_create_role,
11 rolcanlogin AS can_login,
12 rolvaliduntil AS valid_until
13 FROM pg_catalog.pg_roles
14 WHERE rolsuper = true
15 ORDER BY rolname",
16 &[],
17 ).await?.iter().map(|row| {
18 json!({
19 "role": row.get::<_, String>(0),
20 "superuser": row.get::<_, bool>(1),
21 "can_create_db": row.get::<_, bool>(2),
22 "can_create_role": row.get::<_, bool>(3),
23 "can_login": row.get::<_, bool>(4),
24 "valid_until": row.get::<_, Option<String>>(5),
25 })
26 }).collect();
27
28 let world_readable: Vec<Value> = client.query(
29 "SELECT schemaname, tablename, tableowner,
30 has_table_privilege('PUBLIC', quote_ident(schemaname)||'.'||quote_ident(tablename), 'SELECT') AS public_select,
31 has_table_privilege('PUBLIC', quote_ident(schemaname)||'.'||quote_ident(tablename), 'INSERT') AS public_insert,
32 has_table_privilege('PUBLIC', quote_ident(schemaname)||'.'||quote_ident(tablename), 'UPDATE') AS public_update,
33 has_table_privilege('PUBLIC', quote_ident(schemaname)||'.'||quote_ident(tablename), 'DELETE') AS public_delete
34 FROM pg_catalog.pg_tables
35 WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
36 AND (has_table_privilege('PUBLIC', quote_ident(schemaname)||'.'||quote_ident(tablename), 'SELECT')
37 OR has_table_privilege('PUBLIC', quote_ident(schemaname)||'.'||quote_ident(tablename), 'INSERT')
38 OR has_table_privilege('PUBLIC', quote_ident(schemaname)||'.'||quote_ident(tablename), 'UPDATE')
39 OR has_table_privilege('PUBLIC', quote_ident(schemaname)||'.'||quote_ident(tablename), 'DELETE'))
40 ORDER BY schemaname, tablename",
41 &[],
42 ).await?.iter().map(|row| {
43 json!({
44 "schema": row.get::<_, String>(0),
45 "table": row.get::<_, String>(1),
46 "owner": row.get::<_, String>(2),
47 "public_select": row.get::<_, bool>(3),
48 "public_insert": row.get::<_, bool>(4),
49 "public_update": row.get::<_, bool>(5),
50 "public_delete": row.get::<_, bool>(6),
51 })
52 }).collect();
53
54 let default_privs: Vec<Value> = client.query(
55 "SELECT pg_catalog.pg_get_userbyid(defacluser) AS grantee,
56 n.nspname AS schema,
57 CASE defaclobjtype
58 WHEN 'r' THEN 'table'
59 WHEN 'f' THEN 'function'
60 WHEN 'S' THEN 'sequence'
61 WHEN 'T' THEN 'type'
62 WHEN 'n' THEN 'schema'
63 ELSE defaclobjtype::text
64 END AS object_type,
65 pg_catalog.array_to_string(defaclacl, ', ') AS privileges
66 FROM pg_catalog.pg_default_acl da
67 JOIN pg_catalog.pg_namespace n ON n.oid = da.defaclnamespace
68 ORDER BY grantee, schema, object_type",
69 &[],
70 ).await?.iter().map(|row| {
71 json!({
72 "role": row.get::<_, String>(0),
73 "schema": row.get::<_, String>(1),
74 "object_type": row.get::<_, String>(2),
75 "privileges": row.get::<_, String>(3),
76 })
77 }).collect();
78
79 Ok(json!({
80 "superusers": superusers,
81 "world_readable_tables": world_readable,
82 "default_privileges": default_privs,
83 "summary": {
84 "superuser_count": superusers.len(),
85 "world_readable_count": world_readable.len(),
86 "has_default_privs": !default_privs.is_empty(),
87 }
88 }))
89}
90
91pub async fn audit_role_usage(client: &Client, params: &Option<&Value>) -> MCPResult<Value> {
92 let role_filter = params.as_ref().and_then(|p| p.get("role").and_then(|v| v.as_str()));
93
94 let roles: Vec<Value> = if let Some(role) = role_filter {
95 client.query(
96 "SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb,
97 rolcanlogin, rolconnlimit, rolvaliduntil,
98 pg_catalog.shobj_description(oid, 'pg_authid') AS description
99 FROM pg_catalog.pg_roles
100 WHERE rolname = $1
101 ORDER BY rolname",
102 &[&role],
103 ).await?.into_iter().map(|row| {
104 json!({
105 "oid": row.get::<_, i64>(0),
106 "role": row.get::<_, String>(1),
107 "superuser": row.get::<_, bool>(2),
108 "inherit": row.get::<_, bool>(3),
109 "can_create_role": row.get::<_, bool>(4),
110 "can_create_db": row.get::<_, bool>(5),
111 "can_login": row.get::<_, bool>(6),
112 "connection_limit": row.get::<_, i32>(7),
113 "valid_until": row.get::<_, Option<String>>(8),
114 "description": row.get::<_, Option<String>>(9),
115 })
116 }).collect()
117 } else {
118 client.query(
119 "SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb,
120 rolcanlogin, rolconnlimit, rolvaliduntil,
121 pg_catalog.shobj_description(oid, 'pg_authid') AS description
122 FROM pg_catalog.pg_roles
123 ORDER BY rolname",
124 &[],
125 ).await?.into_iter().map(|row| {
126 json!({
127 "oid": row.get::<_, i64>(0),
128 "role": row.get::<_, String>(1),
129 "superuser": row.get::<_, bool>(2),
130 "inherit": row.get::<_, bool>(3),
131 "can_create_role": row.get::<_, bool>(4),
132 "can_create_db": row.get::<_, bool>(5),
133 "can_login": row.get::<_, bool>(6),
134 "connection_limit": row.get::<_, i32>(7),
135 "valid_until": row.get::<_, Option<String>>(8),
136 "description": row.get::<_, Option<String>>(9),
137 })
138 }).collect()
139 };
140
141 Ok(json!({
142 "roles": roles,
143 "total_roles": roles.len(),
144 "login_roles": roles.iter().filter(|r| r.get("can_login").and_then(|v| v.as_bool()).unwrap_or(false)).count(),
145 "superuser_roles": roles.iter().filter(|r| r.get("superuser").and_then(|v| v.as_bool()).unwrap_or(false)).count(),
146 }))
147}