Skip to content

Commit 6b80fe0

Browse files
vbilopavvbilopav
committed
3.4.4 medata query schema persmission fix
1 parent e05a312 commit 6b80fe0

7 files changed

Lines changed: 360 additions & 3 deletions

File tree

‎NpgsqlRest/RoutineSourceQuery.cs‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,6 @@ and has_schema_privilege(current_user, n.nspname, 'USAGE')
2222
and a.attnum > 0
2323
2424
), _array_element_types as (
25-
-- For arrays of composite types, get the element type's field information
2625
select
2726
(quote_ident(n.nspname) || '.' || quote_ident(arr_t.typname))::regtype::text as array_type_name,
2827
array_agg(quote_ident(a.attname) order by a.attnum) as field_names,
@@ -36,6 +35,7 @@ pg_catalog.pg_type arr_t
3635
where
3736
n.nspname not like 'pg_%'
3837
and n.nspname <> 'information_schema'
38+
and has_schema_privilege(current_user, n.nspname, 'USAGE')
3939
and arr_t.typelem <> 0
4040
group by n.nspname, arr_t.typname
4141
@@ -48,6 +48,7 @@ and arr_t.typelem <> 0
4848
where
4949
nspname not like 'pg_%'
5050
and nspname <> 'information_schema'
51+
and has_schema_privilege(current_user, nspname, 'USAGE')
5152
and ($1 is null or nspname similar to $1)
5253
and ($2 is null or nspname not similar to $2)
5354
and ($3 is null or nspname = any($3))
@@ -82,6 +83,7 @@ left join _types t on
8283
where
8384
coalesce(r.type_udt_name, '') <> 'trigger'
8485
and r.routine_type in ('FUNCTION', 'PROCEDURE')
86+
and (r.type_udt_schema is null or has_schema_privilege(current_user, r.type_udt_schema, 'USAGE'))
8587
and ($5 is null or r.routine_name similar to $5)
8688
and ($6 is null or r.routine_name not similar to $6)
8789
and ($7 is null or r.routine_name = any($7))

‎NpgsqlRestTests/PolpTests.cs‎

Lines changed: 257 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,257 @@
1+
using NpgsqlRestTests.Setup;
2+
3+
namespace NpgsqlRestTests;
4+
5+
/// <summary>
6+
/// Tests for Principle of Least Privilege (PoLP) scenarios.
7+
/// Tests that a user with minimal permissions (only USAGE on a schema)
8+
/// can successfully call security definer functions that use user-defined types.
9+
/// </summary>
10+
public static partial class Database
11+
{
12+
public static void PolpTests()
13+
{
14+
script.Append("""
15+
16+
-- =====================================================
17+
-- Principle of Least Privilege (PoLP) Test Setup
18+
-- =====================================================
19+
20+
-- Drop and recreate the restricted user to ensure clean state each test session
21+
drop role if exists test_user;
22+
create role test_user with
23+
login
24+
nosuperuser
25+
nocreatedb
26+
nocreaterole
27+
noinherit
28+
noreplication
29+
connection limit -1
30+
password 'test_pass';
31+
32+
-- Create the schema for PoLP testing
33+
create schema polp_schema;
34+
35+
-- Create unprivileged schema objects owned by postgres which test_user cannot access
36+
create schema polp_schema_unauthorized;
37+
38+
-- Create a user-defined composite type in the polp schema
39+
create type polp_schema.polp_request as (
40+
id int,
41+
name text,
42+
amount numeric(10,2)
43+
);
44+
45+
create type polp_schema_unauthorized.secret_data as (
46+
secret_id int,
47+
secret_value text
48+
);
49+
50+
-- Create a function in the unauthorized schema that uses the secret type
51+
-- This should cause the metadata query to fail when test_user tries to resolve the type
52+
create function polp_schema_unauthorized.get_secret_data()
53+
returns polp_schema_unauthorized.secret_data
54+
language sql
55+
as $$
56+
select row(1, 'secret')::polp_schema_unauthorized.secret_data;
57+
$$;
58+
59+
-- Create a table in the unauthorized schema to create array types
60+
-- PostgreSQL automatically creates array types for composite types and tables
61+
create table polp_schema_unauthorized.secret_table (
62+
id int,
63+
value text
64+
);
65+
66+
-- Create a user-defined composite type for the response
67+
create type polp_schema.polp_response as (
68+
request_id int,
69+
processed_name text,
70+
calculated_amount numeric(10,2),
71+
status text
72+
);
73+
74+
-- Create a security definer function that uses the user-defined types
75+
-- This function runs with the privileges of the owner (postgres), not the caller (test_user)
76+
create function polp_schema.process_polp_request(
77+
_request polp_schema.polp_request
78+
)
79+
returns polp_schema.polp_response
80+
language plpgsql
81+
security definer
82+
as $$
83+
begin
84+
return row(
85+
_request.id,
86+
'Processed: ' || _request.name,
87+
_request.amount * 1.1,
88+
'SUCCESS'
89+
)::polp_schema.polp_response;
90+
end;
91+
$$;
92+
93+
-- Create another security definer function that returns the type directly
94+
create function polp_schema.get_polp_status(
95+
_id int,
96+
_name text
97+
)
98+
returns polp_schema.polp_response
99+
language sql
100+
security definer
101+
as $$
102+
select row(
103+
_id,
104+
_name,
105+
0.00,
106+
'ACTIVE'
107+
)::polp_schema.polp_response;
108+
$$;
109+
110+
-- Create a function that takes simple parameters but returns the composite type
111+
create function polp_schema.create_polp_response(
112+
_id int,
113+
_name text,
114+
_amount numeric
115+
)
116+
returns polp_schema.polp_response
117+
language sql
118+
security definer
119+
as $$
120+
select row(
121+
_id,
122+
'Created: ' || _name,
123+
_amount,
124+
'CREATED'
125+
)::polp_schema.polp_response;
126+
$$;
127+
128+
-- Create a function that returns an array of composite types
129+
create function polp_schema.get_polp_responses()
130+
returns polp_schema.polp_response[]
131+
language sql
132+
security definer
133+
as $$
134+
select array[
135+
row(1, 'Item1', 100.00, 'ACTIVE')::polp_schema.polp_response,
136+
row(2, 'Item2', 200.00, 'ACTIVE')::polp_schema.polp_response
137+
];
138+
$$;
139+
140+
-- Create a function in polp_schema (which test_user can access via USAGE)
141+
-- that returns a type from polp_schema_unauthorized (which test_user CANNOT access).
142+
-- This will trigger the bug: when the metadata query tries to resolve the return type
143+
-- using ::regtype cast, it will fail with "permission denied for schema polp_schema_unauthorized"
144+
create function polp_schema.get_secret_wrapper()
145+
returns polp_schema_unauthorized.secret_data
146+
language sql
147+
security definer
148+
as $$
149+
select (1, 'secret')::polp_schema_unauthorized.secret_data;
150+
$$;
151+
152+
-- Grant USAGE on schema to test_user
153+
grant usage on schema polp_schema to test_user;
154+
""");
155+
}
156+
}
157+
158+
/// <summary>
159+
/// Tests that verify the PoLP (Principle of Least Privilege) scenario works correctly.
160+
/// These tests use a SEPARATE fixture (PolpTestFixture) that connects as test_user
161+
/// for BOTH metadata discovery AND function execution.
162+
///
163+
/// test_user has ONLY "USAGE" on polp_schema - no EXECUTE grants.
164+
/// The security definer functions run with owner (postgres) privileges.
165+
///
166+
/// NOTE: These tests will FAIL until the metadata query is fixed to discover
167+
/// SECURITY DEFINER functions that the user can call via schema USAGE privilege.
168+
/// </summary>
169+
[Collection("PolpTestFixture")]
170+
public class PolpTests(PolpTestFixture test)
171+
{
172+
/// <summary>
173+
/// Test that test_user can call a security definer function with simple parameters.
174+
/// </summary>
175+
[Fact]
176+
public async Task Test_polp_get_status_with_simple_parameters()
177+
{
178+
var query = new QueryBuilder
179+
{
180+
{ "id", "1" },
181+
{ "name", "PolpUser" }
182+
};
183+
184+
using var response = await test.Client.GetAsync($"/api/polp-schema/get-polp-status/{query}");
185+
186+
response.StatusCode.Should().Be(HttpStatusCode.OK);
187+
188+
var responseContent = await response.Content.ReadAsStringAsync();
189+
var json = JsonDocument.Parse(responseContent);
190+
191+
json.RootElement.GetProperty("requestId").GetInt32().Should().Be(1);
192+
json.RootElement.GetProperty("processedName").GetString().Should().Be("PolpUser");
193+
json.RootElement.GetProperty("status").GetString().Should().Be("ACTIVE");
194+
}
195+
196+
/// <summary>
197+
/// Test that test_user can call a security definer function with composite type parameter.
198+
/// </summary>
199+
[Fact]
200+
public async Task Test_polp_process_request_with_composite_type()
201+
{
202+
var requestBody = new
203+
{
204+
requestId = 777,
205+
requestName = "PolpRequest",
206+
requestAmount = 500.00
207+
};
208+
209+
using var content = new StringContent(
210+
JsonSerializer.Serialize(requestBody),
211+
Encoding.UTF8,
212+
"application/json");
213+
214+
using var response = await test.Client.PostAsync("/api/polp-schema/process-polp-request/", content);
215+
216+
response.StatusCode.Should().Be(HttpStatusCode.OK);
217+
218+
var responseContent = await response.Content.ReadAsStringAsync();
219+
var json = JsonDocument.Parse(responseContent);
220+
221+
json.RootElement.GetProperty("requestId").GetInt32().Should().Be(777);
222+
json.RootElement.GetProperty("processedName").GetString().Should().Be("Processed: PolpRequest");
223+
json.RootElement.GetProperty("calculatedAmount").GetDecimal().Should().Be(550.00m);
224+
json.RootElement.GetProperty("status").GetString().Should().Be("SUCCESS");
225+
}
226+
227+
/// <summary>
228+
/// Test that test_user can call a security definer function that creates a response.
229+
/// </summary>
230+
[Fact]
231+
public async Task Test_polp_create_response_with_simple_parameters()
232+
{
233+
var requestBody = new
234+
{
235+
id = 999,
236+
name = "NewItem",
237+
amount = 250.50
238+
};
239+
240+
using var content = new StringContent(
241+
JsonSerializer.Serialize(requestBody),
242+
Encoding.UTF8,
243+
"application/json");
244+
245+
using var response = await test.Client.PostAsync("/api/polp-schema/create-polp-response/", content);
246+
247+
response.StatusCode.Should().Be(HttpStatusCode.OK);
248+
249+
var responseContent = await response.Content.ReadAsStringAsync();
250+
var json = JsonDocument.Parse(responseContent);
251+
252+
json.RootElement.GetProperty("requestId").GetInt32().Should().Be(999);
253+
json.RootElement.GetProperty("processedName").GetString().Should().Be("Created: NewItem");
254+
json.RootElement.GetProperty("calculatedAmount").GetDecimal().Should().Be(250.50m);
255+
json.RootElement.GetProperty("status").GetString().Should().Be("CREATED");
256+
}
257+
}

‎NpgsqlRestTests/Setup/Database.cs‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -60,6 +60,21 @@ public static string CreateAdditional(string appName)
6060
return builder.ConnectionString;
6161
}
6262

63+
/// <summary>
64+
/// Creates a connection string for the test_user with minimal privileges (PoLP testing).
65+
/// </summary>
66+
public static string CreatePolpConnection()
67+
{
68+
var builder = new NpgsqlConnectionStringBuilder(InitialConnectionString)
69+
{
70+
Database = Dbname,
71+
Username = "test_user",
72+
Password = "test_pass"
73+
};
74+
75+
return builder.ConnectionString;
76+
}
77+
6378
public static void DropIfExists()
6479
{
6580
using NpgsqlConnection connection = new(InitialConnectionString);

‎NpgsqlRestTests/Setup/PolpProgram.cs‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
using Microsoft.AspNetCore.Builder;
2+
3+
namespace NpgsqlRestTests.Setup;
4+
5+
/// <summary>
6+
/// Marker class for PoLP (Principle of Least Privilege) tests WebApplicationFactory.
7+
/// The actual configuration is done in PolpTestFixture via ConfigureWebHost.
8+
/// </summary>
9+
public class PolpProgram
10+
{
11+
// This is intentionally empty - it's just a marker type for WebApplicationFactory<PolpProgram>
12+
// The actual app configuration happens in PolpTestFixture
13+
}

‎NpgsqlRestTests/Setup/PolpTestFixture.cs‎

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,56 @@
1+
using Microsoft.AspNetCore.Builder;
2+
using Microsoft.AspNetCore.Hosting;
3+
4+
namespace NpgsqlRestTests.Setup;
5+
6+
[CollectionDefinition("PolpTestFixture")]
7+
public class PolpTestFixtureCollection : ICollectionFixture<PolpTestFixture> { }
8+
9+
/// <summary>
10+
/// Test fixture for PoLP (Principle of Least Privilege) tests.
11+
/// Creates a separate web application that uses test_user credentials
12+
/// for BOTH metadata discovery AND function execution.
13+
/// </summary>
14+
public class PolpTestFixture : IDisposable
15+
{
16+
private readonly WebApplication _app;
17+
private readonly HttpClient _client;
18+
19+
public HttpClient Client => _client;
20+
21+
public PolpTestFixture()
22+
{
23+
// Ensure the database is created with superuser (postgres)
24+
// This creates all the schemas, types, functions, and grants needed for testing
25+
Database.Create();
26+
27+
var connectionString = Database.CreatePolpConnection(); // Uses test_user
28+
29+
var builder = WebApplication.CreateBuilder();
30+
builder.WebHost.UseUrls("http://127.0.0.1:0"); // Use random available port
31+
32+
_app = builder.Build();
33+
34+
_app.UseNpgsqlRest(new NpgsqlRestOptions(connectionString)
35+
{
36+
// Only include polp_schema - test_user only has USAGE on this schema
37+
IncludeSchemas = ["polp_schema"],
38+
CommentsMode = CommentsMode.ParseAll,
39+
});
40+
41+
_app.StartAsync().GetAwaiter().GetResult();
42+
43+
var serverAddress = _app.Urls.First();
44+
_client = new HttpClient { BaseAddress = new Uri(serverAddress) };
45+
_client.Timeout = TimeSpan.FromHours(1);
46+
}
47+
48+
#pragma warning disable CA1816 // Dispose methods should call SuppressFinalize
49+
public void Dispose()
50+
#pragma warning restore CA1816 // Dispose methods should call SuppressFinalize
51+
{
52+
_client.Dispose();
53+
_app.StopAsync().GetAwaiter().GetResult();
54+
_app.DisposeAsync().GetAwaiter().GetResult();
55+
}
56+
}

0 commit comments

Comments
 (0)