8000 Revoke all from public on c functions by default · percona/postgres@eaa8f25 · GitHub
[go: up one dir, main page]
Skip to content

Commit eaa8f25

Browse files
AndersAstrandAndersAstrand
committed
Revoke all from public on c functions by default
Even though we do have some access control in most of our C functions, it seems dangerous to not revoke them from public by default. A whitelist of allowed functions seems safer than a black list.
1 parent 34438f5 commit eaa8f25

File tree

5 files changed

+82
-7
lines changed

5 files changed

+82
-7
lines changed

‎contrib/pg_tde/expected/access_control.out

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -75,7 +75,18 @@ SELECT pg_tde_verify_server_key();
7575
ERROR: principal key not configured for current database
7676
SELECT pg_tde_verify_default_key();
7777
ERROR: principal key not configured for current database
78-
-- only superuser
78+
-- Only superusers can execute key management functions, regardless of role grants
79+
RESET ROLE;
80+
GRANT EXECUTE ON FUNCTION pg_tde_add_database_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
81+
GRANT EXECUTE ON FUNCTION pg_tde_add_global_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
82+
GRANT EXECUTE ON FUNCTION pg_tde_change_database_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
83+
GRANT EXECUTE ON FUNCTION pg_tde_change_global_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
84+
GRANT EXECUTE ON FUNCTION pg_tde_delete_database_key_provider(TEXT) TO regress_pg_tde_access_control;
85+
GRANT EXECUTE ON FUNCTION pg_tde_delete_global_key_provider(TEXT) TO regress_pg_tde_access_control;
86+
GRANT EXECUTE ON FUNCTION pg_tde_set_default_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) TO regress_pg_tde_access_control;
87+
GRANT EXECUTE ON FUNCTION pg_tde_set_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) TO regress_pg_tde_access_control;
88+
GRANT EXECUTE ON FUNCTION pg_tde_set_server_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) TO regress_pg_tde_access_control;
89+
SET ROLE regress_pg_tde_access_control;
7990
SELECT pg_tde_add_database_key_provider_file('local-file-provider', '/tmp/pg_tde_test_keyring.per');
8091
ERROR: must be superuser to modify key providers
8192
SELECT pg_tde_change_global_key_provider_file('local-file-provider', '/tmp/pg_tde_test_keyring.per');

‎contrib/pg_tde/pg_tde--1.0-rc.sql

Lines changed: 24 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,7 @@ CREATE FUNCTION pg_tde_add_database_key_provider(provider_type TEXT, provider_na
88
RETURNS INT
99
LANGUAGE C
1010
AS 'MODULE_PATHNAME';
11+
REVOKE ALL ON FUNCTION pg_tde_add_database_key_provider(TEXT, TEXT, JSON) FROM PUBLIC;
1112

1213
CREATE FUNCTION pg_tde_add_database_key_provider_file(provider_name TEXT, file_path TEXT)
1314
RETURNS INT
@@ -105,6 +106,7 @@ CREATE FUNCTION pg_tde_list_all_database_key_providers
105106
RETURNS SETOF RECORD
106107
LANGUAGE C STRICT
107108
AS 'MODULE_PATHNAME';
109+
REVOKE ALL ON FUNCTION pg_tde_list_all_database_key_providers() FROM PUBLIC;
108110

109111
CREATE FUNCTION pg_tde_list_all_global_key_providers
110112
(OUT id INT,
@@ -114,12 +116,14 @@ CREATE FUNCTION pg_tde_list_all_global_key_providers
114116
RETURNS SETOF RECORD
115117
LANGUAGE C STRICT
116118
AS 'MODULE_PATHNAME';
119+
REVOKE ALL ON FUNCTION pg_tde_list_all_global_key_providers() FROM PUBLIC;
117120

118121
-- Global Tablespace Key Provider Management
119122
CREATE FUNCTION pg_tde_add_global_key_provider(provider_type TEXT, provider_name TEXT, options JSON)
120123
RETURNS INT
121124
LANGUAGE C
122125
AS 'MODULE_PATHNAME';
126+
REVOKE ALL ON FUNCTION pg_tde_add_global_key_provider(TEXT, TEXT, JSON) FROM PUBLIC;
123127

124128
CREATE FUNCTION pg_tde_add_global_key_provider_file(provider_name TEXT, file_path TEXT)
125129
RETURNS INT
@@ -214,6 +218,7 @@ CREATE FUNCTION pg_tde_change_database_key_provider(provider_type TEXT, provider
214218
RETURNS INT
215219
LANGUAGE C
216220
AS 'MODULE_PATHNAME';
221+
REVOKE ALL ON FUNCTION pg_tde_change_database_key_provider(TEXT, TEXT, JSON) FROM PUBLIC;
217222

218223
CREATE FUNCTION pg_tde_change_database_key_provider_file(provider_name TEXT, file_path TEXT)
219224
RETURNS INT
@@ -308,6 +313,7 @@ CREATE FUNCTION pg_tde_change_global_key_provider(provider_type TEXT, provider_n
308313
RETURNS INT
309314
LANGUAGE C
310315
AS 'MODULE_PATHNAME';
316+
REVOKE ALL ON FUNCTION pg_tde_change_global_key_provider(TEXT, TEXT, JSON) FROM PUBLIC;
311317

312318
CREATE FUNCTION pg_tde_change_global_key_provider_file(provider_name TEXT, file_path TEXT)
313319
RETURNS INT
@@ -402,41 +408,50 @@ RETURNS BOOLEAN
402408
STRICT
403409
LANGUAGE C
404410
AS 'MODULE_PATHNAME';
411+
REVOKE ALL ON FUNCTION pg_tde_is_encrypted(REGCLASS) FROM PUBLIC;
405412

406413
CREATE FUNCTION pg_tde_set_key_using_database_key_provider(key_name TEXT, provider_name TEXT DEFAULT NULL, ensure_new_key BOOLEAN DEFAULT FALSE)
407414
RETURNS VOID
408415
LANGUAGE C
409416
AS 'MODULE_PATHNAME';
417+
REVOKE ALL ON FUNCTION pg_tde_set_key_using_database_key_provider(TEXT, TEXT, BOOLEAN) FROM PUBLIC;
410418

411419
CREATE FUNCTION pg_tde_set_key_using_global_key_provider(key_name TEXT, provider_name TEXT DEFAULT NULL, ensure_new_key BOOLEAN DEFAULT FALSE)
412420
RETURNS VOID
413421
LANGUAGE C
414422
AS 'MODULE_PATHNAME';
423+
REVOKE ALL ON FUNCTION pg_tde_set_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) FROM PUBLIC;
415424

416425
CREATE FUNCTION pg_tde_set_server_key_using_global_key_provider(key_name TEXT, provider_name TEXT DEFAULT NULL, ensure_new_key BOOLEAN DEFAULT FALSE)
417426
RETURNS VOID
418427
LANGUAGE C
419428
AS 'MODULE_PATHNAME';
429+
REVOKE ALL ON FUNCTION pg_tde_set_server_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) FROM PUBLIC;
430+
420431

421432
CREATE FUNCTION pg_tde_set_default_key_using_global_key_provider(key_name TEXT, provider_name TEXT DEFAULT NULL, ensure_new_key BOOLEAN DEFAULT FALSE)
422433
RETURNS VOID
423434
AS 'MODULE_PATHNAME'
424435
LANGUAGE C;
436+
REVOKE ALL ON FUNCTION pg_tde_set_default_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) FROM PUBLIC;
425437

426438
CREATE FUNCTION pg_tde_verify_key()
427439
RETURNS VOID
428440
LANGUAGE C
429441
AS 'MODULE_PATHNAME';
442+
REVOKE ALL ON FUNCTION pg_tde_verify_key() FROM PUBLIC;
430443

431444
CREATE FUNCTION pg_tde_verify_server_key()
432445
RETURNS VOID
433446
LANGUAGE C
434447
AS 'MODULE_PATHNAME';
448+
REVOKE ALL ON FUNCTION pg_tde_verify_server_key() FROM PUBLIC;
435449

436450
CREATE FUNCTION pg_tde_verify_default_key()
437451
RETURNS VOID
438452
LANGUAGE C
439453
AS 'MODULE_PATHNAME';
454+
REVOKE ALL ON FUNCTION pg_tde_verify_default_key() FROM PUBLIC;
440455

441456
CREATE FUNCTION pg_tde_key_info()
442457
RETURNS TABLE ( key_name TEXT,
@@ -445,6 +460,7 @@ RETURNS TABLE ( key_name TEXT,
445460
key_creation_time TIMESTAMP WITH TIME ZONE)
446461
LANGUAGE C
447462
AS 'MODULE_PATHNAME';
463+
REVOKE ALL ON FUNCTION pg_tde_key_info() FROM PUBLIC;
448464

449465
CREATE FUNCTION pg_tde_server_key_info()
450466
RETURNS TABLE ( key_name TEXT,
@@ -453,6 +469,7 @@ RETURNS TABLE ( key_name TEXT,
453469
key_creation_time TIMESTAMP WITH TIME ZONE)
454470
LANGUAGE C
455471
AS 'MODULE_PATHNAME';
472+
REVOKE ALL ON FUNCTION pg_tde_server_key_info() FROM PUBLIC;
456473

457474
CREATE FUNCTION pg_tde_default_key_info()
458475
RETURNS TABLE ( key_name TEXT,
@@ -461,24 +478,29 @@ RETURNS TABLE ( key_name TEXT,
461478
key_creation_time TIMESTAMP WITH TIME ZONE)
462479
LANGUAGE C
463480
AS 'MODULE_PATHNAME';
481+
REVOKE ALL ON FUNCTION pg_tde_default_key_info() FROM PUBLIC;
464482

465483
CREATE FUNCTION pg_tde_delete_global_key_provider(provider_name TEXT)
466484
RETURNS VOID
467485
LANGUAGE C
468486
AS 'MODULE_PATHNAME';
487+
REVOKE ALL ON FUNCTION pg_tde_delete_global_key_provider(TEXT) FROM PUBLIC;
469488

470489
CREATE FUNCTION pg_tde_delete_database_key_provider(provider_name TEXT)
471490
RETURNS VOID
472491
LANGUAGE C
473492
AS 'MODULE_PATHNAME';
493+
REVOKE ALL ON FUNCTION pg_tde_delete_database_key_provider(TEXT) FROM PUBLIC;
474494

475495
CREATE FUNCTION pg_tde_version() RETURNS TEXT LANGUAGE C AS 'MODULE_PATHNAME';
496+
REVOKE ALL ON FUNCTION pg_tde_version() FROM PUBLIC;
476497

477498
-- Table access method
478499
CREATE FUNCTION pg_tdeam_handler(internal)
479500
RETURNS TABLE_AM_HANDLER
480501
LANGUAGE C
481502
AS 'MODULE_PATHNAME';
503+
REVOKE ALL ON FUNCTION pg_tdeam_handler(internal) FROM PUBLIC;
482504

483505
CREATE ACCESS METHOD tde_heap TYPE TABLE HANDLER pg_tdeam_handler;
484506
COMMENT ON ACCESS METHOD tde_heap IS 'tde_heap table access method';
@@ -487,11 +509,13 @@ CREATE FUNCTION pg_tde_ddl_command_start_capture()
487509
RETURNS EVENT_TRIGGER
488510
LANGUAGE C
489511
AS 'MODULE_PATHNAME';
512+
REVOKE ALL ON FUNCTION pg_tde_ddl_command_start_capture() FROM PUBLIC;
490513

491514
CREATE FUNCTION pg_tde_ddl_command_end_capture()
492515
RETURNS EVENT_TRIGGER
493516
LANGUAGE C
494517
AS 'MODULE_PATHNAME';
518+
REVOKE ALL ON FUNCTION pg_tde_ddl_command_end_capture() FROM PUBLIC;
495519

496520
CREATE EVENT TRIGGER pg_tde_ddl_start
497521
ON ddl_command_start
@@ -572,7 +596,3 @@ BEGIN
572596
EXECUTE format('REVOKE EXECUTE ON FUNCTION pg_tde_verify_default_key() FROM %I', target_role);
573597
END;
574598
$$;
575-
576-
-- Revoking all the privileges from the public role
577-
SELECT pg_tde_revoke_database_key_management_from_role('public');
578-
SELECT pg_tde_revoke_key_viewer_from_role('public');

‎contrib/pg_tde/sql/access_control.sql

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,19 @@ SELECT pg_tde_verify_key();
3434
SELECT pg_tde_verify_server_key();
3535
SELECT pg_tde_verify_default_key();
3636

37-
-- only superuser
37+
-- Only superusers can execute key management functions, regardless of role grants
38+
RESET ROLE;
39+
GRANT EXECUTE ON FUNCTION pg_tde_add_database_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
40+
GRANT EXECUTE ON FUNCTION pg_tde_add_global_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
41+
GRANT EXECUTE ON FUNCTION pg_tde_change_database_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
42+
GRANT EXECUTE ON FUNCTION pg_tde_change_global_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
43+
GRANT EXECUTE ON FUNCTION pg_tde_delete_database_key_provider(TEXT) TO regress_pg_tde_access_control;
44+
GRANT EXECUTE ON FUNCTION pg_tde_delete_global_key_provider(TEXT) TO regress_pg_tde_access_control;
45+
GRANT EXECUTE ON FUNCTION pg_tde_set_default_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) TO regress_pg_tde_access_control;
46+
GRANT EXECUTE ON FUNCTION pg_tde_set_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) TO regress_pg_tde_access_control;
47+
GRANT EXECUTE ON FUNCTION pg_tde_set_server_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) TO regress_pg_tde_access_control;
48+
49+
SET ROLE regress_pg_tde_access_control;
3850
SELECT pg_tde_add_database_key_provider_file('local-file-provider', '/tmp/pg_tde_test_keyring.per');
3951
SELECT pg_tde_change_global_key_provider_file('local-file-provider', '/tmp/pg_tde_test_keyring.per');
4052
SELECT pg_tde_delete_database_key_provider('local-file-provider');
@@ -44,7 +56,6 @@ SELECT pg_tde_delete_global_key_provider('global-file-provider');
4456
SELECT pg_tde_set_key_using_global_key_provider('key1', 'global-file-provider');
4557
SELECT pg_tde_set_default_key_using_global_key_provider('key1', 'global-file-provider');
4658
SELECT pg_tde_set_server_key_using_global_key_provider('key1', 'global-file-provider');
47-
4859
RESET ROLE;
4960

5061
SELECT pg_tde_revoke_key_viewer_from_role('regress_pg_tde_access_control');

‎contrib/pg_tde/t/001_basic.pl

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,23 @@
1616

1717
PGTDE::psql($node, 'postgres', 'CREATE EXTENSION IF NOT EXISTS pg_tde;');
1818

19+
# No C functions are granted to public by default
20+
PGTDE::psql(
21+
$node, 'postgres',
22+
q{
23+
SELECT
24+
proname
25+
FROM
26+
pg_catalog.pg_proc
27+
JOIN pg_catalog.pg_language ON prolang = pg_language.oid
28+
LEFT JOIN LATERAL aclexplode(proacl) ON TRUE
29+
WHERE
30+
proname LIKE 'pg_tde%' AND
31+
lanname = 'c' AND
32+
(grantee IS NULL OR grantee = 0)
33+
ORDER BY proname;
34+
});
35+
1936
PGTDE::psql($node, 'postgres',
2037
"SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_tde';");
2138

‎contrib/pg_tde/t/expected/001_basic.out

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,20 @@
11
CREATE EXTENSION IF NOT EXISTS pg_tde;
2+
SELECT
3+
proname
4+
FROM
5+
pg_catalog.pg_proc
6+
JOIN pg_catalog.pg_language ON prolang = pg_language.oid
7+
LEFT JOIN LATERAL aclexplode(proacl) ON TRUE
8+
WHERE
9+
proname LIKE 'pg_tde%' AND
10+
lanname = 'c' AND
11+
(grantee IS NULL OR grantee = 0)
12+
ORDER BY proname;
13+
proname
14+
---------
15+
(0 rows)
16+
17+
218
SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_tde';
319
extname | extversion
420
---------+------------

0 commit comments

Comments
 (0)
0