1+ using System . Net ;
12using System . Text ;
23using System . Text . Json ;
34using Microsoft . AspNetCore . Http ;
@@ -92,7 +93,11 @@ from pg_stat_user_indexes s
9293 ltrim(justify_interval(now() - state_change)::text, '@ ') as state_duration,
9394 ltrim(justify_interval(now() - backend_start)::text, '@ ') as connection_age,
9495 ltrim(justify_interval(now() - xact_start)::text, '@ ') as transaction_duration,
95- ltrim(justify_interval(now() - query_start)::text, '@ ') as query_duration,
96+ case
97+ when state = 'active' then ltrim(justify_interval(now() - query_start)::text, '@ ')
98+ when state = 'idle' then 'finished ' || ltrim(justify_interval(now() - state_change)::text, '@ ') || ' ago'
99+ else state
100+ end as query_duration,
96101 left(query, 100) as query_preview,
97102 query
98103 from pg_stat_activity
@@ -103,35 +108,96 @@ order by state_duration desc nulls last
103108 /// <summary>
104109 /// Returns statistics for user-defined functions and procedures.
105110 /// </summary>
106- public static async Task HandleRoutinesStats ( HttpContext context , string connectionString , string outputFormat , string ? schemaSimilarTo , ILogger ? logger )
111+ public static async Task HandleRoutinesStats ( HttpContext context , string connectionString , string outputFormat , string ? schemaSimilarTo , bool requireAuthorization , string [ ] ? authorizedRoles , string ? roleClaimType , ILogger ? logger )
107112 {
113+ if ( await CheckAuthorization ( context , requireAuthorization , authorizedRoles , roleClaimType ) is false )
114+ {
115+ return ;
116+ }
108117 await ExecuteQueryAndRespond ( context , connectionString , RoutinesQuery , outputFormat , schemaSimilarTo , logger , "Stats.Routines" , setupCommands : [ "set local intervalstyle = 'postgres_verbose'" ] ) ;
109118 }
110119
111120 /// <summary>
112121 /// Returns statistics for user tables including size, tuple counts, and vacuum info.
113122 /// </summary>
114- public static async Task HandleTablesStats ( HttpContext context , string connectionString , string outputFormat , string ? schemaSimilarTo , ILogger ? logger )
123+ public static async Task HandleTablesStats ( HttpContext context , string connectionString , string outputFormat , string ? schemaSimilarTo , bool requireAuthorization , string [ ] ? authorizedRoles , string ? roleClaimType , ILogger ? logger )
115124 {
125+ if ( await CheckAuthorization ( context , requireAuthorization , authorizedRoles , roleClaimType ) is false )
126+ {
127+ return ;
128+ }
116129 await ExecuteQueryAndRespond ( context , connectionString , TablesQuery , outputFormat , schemaSimilarTo , logger , "Stats.Tables" ) ;
117130 }
118131
119132 /// <summary>
120133 /// Returns statistics for user indexes including scan counts and definitions.
121134 /// </summary>
122- public static async Task HandleIndexesStats ( HttpContext context , string connectionString , string outputFormat , string ? schemaSimilarTo , ILogger ? logger )
135+ public static async Task HandleIndexesStats ( HttpContext context , string connectionString , string outputFormat , string ? schemaSimilarTo , bool requireAuthorization , string [ ] ? authorizedRoles , string ? roleClaimType , ILogger ? logger )
123136 {
137+ if ( await CheckAuthorization ( context , requireAuthorization , authorizedRoles , roleClaimType ) is false )
138+ {
139+ return ;
140+ }
124141 await ExecuteQueryAndRespond ( context , connectionString , IndexesQuery , outputFormat , schemaSimilarTo , logger , "Stats.Indexes" ) ;
125142 }
126143
127144 /// <summary>
128145 /// Returns current database activity from pg_stat_activity.
129146 /// </summary>
130- public static async Task HandleActivityStats ( HttpContext context , string connectionString , string outputFormat , ILogger ? logger )
147+ public static async Task HandleActivityStats ( HttpContext context , string connectionString , string outputFormat , bool requireAuthorization , string [ ] ? authorizedRoles , string ? roleClaimType , ILogger ? logger )
131148 {
149+ if ( await CheckAuthorization ( context , requireAuthorization , authorizedRoles , roleClaimType ) is false )
150+ {
151+ return ;
152+ }
132153 await ExecuteQueryAndRespond ( context , connectionString , ActivityQuery , outputFormat , null , logger , "Stats.Activity" , setupCommands : [ "set local intervalstyle = 'postgres_verbose'" ] ) ;
133154 }
134155
156+ private static async Task < bool > CheckAuthorization ( HttpContext context , bool requireAuthorization , string [ ] ? authorizedRoles , string ? roleClaimType )
157+ {
158+ if ( requireAuthorization is false && authorizedRoles is null )
159+ {
160+ return true ;
161+ }
162+
163+ if ( context . User ? . Identity ? . IsAuthenticated is false )
164+ {
165+ await Results . Problem (
166+ type : null ,
167+ statusCode : ( int ) HttpStatusCode . Unauthorized ,
168+ title : "Unauthorized" ,
169+ detail : null ) . ExecuteAsync ( context ) ;
170+ return false ;
171+ }
172+
173+ if ( authorizedRoles is not null )
174+ {
175+ bool ok = false ;
176+ foreach ( var claim in context . User ? . Claims ?? [ ] )
177+ {
178+ if ( string . Equals ( claim . Type , roleClaimType , StringComparison . Ordinal ) )
179+ {
180+ if ( authorizedRoles . Contains ( claim . Value ) is true )
181+ {
182+ ok = true ;
183+ break ;
184+ }
185+ }
186+ }
187+ if ( ok is false )
188+ {
189+ await Results . Problem (
190+ type : null ,
191+ statusCode : ( int ) HttpStatusCode . Forbidden ,
192+ title : "Forbidden" ,
193+ detail : null ) . ExecuteAsync ( context ) ;
194+ return false ;
195+ }
196+ }
197+
198+ return true ;
199+ }
200+
135201 private static async Task ExecuteQueryAndRespond (
136202 HttpContext context ,
137203 string connectionString ,
0 commit comments