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