10000 Revoke all from public on c functions by AndersAstrand · Pull Request #318 · percona/postgres · GitHub
[go: up one dir, main page]
Skip to content

Revoke all from public on c functions #318

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
wants to merge 1 commit into
base: TDE_REL_17_STABLE
Choose a base branch
from
Open

Revoke all from public on c functions #318

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
13 changes: 12 additions & 1 deletion contrib/pg_tde/expected/access_control.out
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -75,7 +75,18 @@ SELECT pg_tde_verify_server_key();
ERROR: principal key not configured for current database
SELECT pg_tde_verify_default_key();
ERROR: principal key not configured for current database
-- only superuser
-- Only superusers can execute key management functions, regardless of role grants
RESET ROLE;
GRANT EXECUTE ON FUNCTION pg_tde_add_database_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_add_global_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_change_database_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_change_global_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_delete_database_key_provider(TEXT) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_delete_global_key_provider(TEXT) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_set_default_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_set_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_set_server_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) TO regress_pg_tde_access_control;
SET ROLE regress_pg_tde_access_control;
SELECT pg_tde_add_database_key_provider_file('local-file-provider', '/tmp/pg_tde_test_keyring.per');
ERROR: must be superuser to modify key providers
SELECT pg_tde_change_global_key_provider_file('local-file-provider', '/tmp/pg_tde_test_keyring.per');
Expand Down
28 changes: 24 additions & 4 deletions contrib/pg_tde/pg_tde--1.0-rc.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -8,6 +8,7 @@ CREATE FUNCTION pg_tde_add_database_key_provider(provider_type TEXT, provider_na
RETURNS INT
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_add_database_key_provider(TEXT, TEXT, JSON) FROM PUBLIC;

CREATE FUNCTION pg_tde_add_database_key_provider_file(provider_name TEXT, file_path TEXT)
RETURNS INT
Expand Down Expand Up @@ -105,6 +106,7 @@ CREATE FUNCTION pg_tde_list_all_database_key_providers
RETURNS SETOF RECORD
LANGUAGE C STRICT
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_list_all_database_key_providers() FROM PUBLIC;

CREATE FUNCTION pg_tde_list_all_global_key_providers
(OUT id INT,
Expand All @@ -114,12 +116,14 @@ CREATE FUNCTION pg_tde_list_all_global_key_providers
RETURNS SETOF RECORD
LANGUAGE C STRICT
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_list_all_global_key_providers() FROM PUBLIC;

-- Global Tablespace Key Provider Management
CREATE FUNCTION pg_tde_add_global_key_provider(provider_type TEXT, provider_name TEXT, options JSON)
RETURNS INT
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_add_global_key_provider(TEXT, TEXT, JSON) FROM PUBLIC;

CREATE FUNCTION pg_tde_add_global_key_provider_file(provider_name TEXT, file_path TEXT)
RETURNS INT
Expand Down Expand Up @@ -214,6 +218,7 @@ CREATE FUNCTION pg_tde_change_database_key_provider(provider_type TEXT, provider
RETURNS INT
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_change_database_key_provider(TEXT, TEXT, JSON) FROM PUBLIC;

CREATE FUNCTION pg_tde_change_database_key_provider_file(provider_name TEXT, file_path TEXT)
RETURNS INT
Expand Down Expand Up @@ -308,6 +313,7 @@ CREATE FUNCTION pg_tde_change_global_key_provider(provider_type TEXT, provider_n
RETURNS INT
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_change_global_key_provider(TEXT, TEXT, JSON) FROM PUBLIC;

CREATE FUNCTION pg_tde_change_global_key_provider_file(provider_name TEXT, file_path TEXT)
RETURNS INT
Expand Down Expand Up @@ -402,41 +408,50 @@ RETURNS BOOLEAN
STRICT
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_is_encrypted(REGCLASS) FROM PUBLIC;

CREATE FUNCTION pg_tde_set_key_using_database_key_provider(key_name TEXT, provider_name TEXT DEFAULT NULL, ensure_new_key BOOLEAN DEFAULT FALSE)
RETURNS VOID
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_set_key_using_database_key_provider(TEXT, TEXT, BOOLEAN) FROM PUBLIC;

CREATE FUNCTION pg_tde_set_key_using_global_key_provider(key_name TEXT, provider_name TEXT DEFAULT NULL, ensure_new_key BOOLEAN DEFAULT FALSE)
RETURNS VOID
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_set_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) FROM PUBLIC;

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)
RETURNS VOID
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_set_server_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) FROM PUBLIC;


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)
RETURNS VOID
AS 'MODULE_PATHNAME'
LANGUAGE C;
REVOKE ALL ON FUNCTION pg_tde_set_default_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) FROM PUBLIC;

CREATE FUNCTION pg_tde_verify_key()
RETURNS VOID
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_verify_key() FROM PUBLIC;

CREATE FUNCTION pg_tde_verify_server_key()
RETURNS VOID
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_verify_server_key() FROM PUBLIC;

CREATE FUNCTION pg_tde_verify_default_key()
RETURNS VOID
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_verify_default_key() FROM PUBLIC;

CREATE FUNCTION pg_tde_key_info()
RETURNS TABLE ( key_name TEXT,
Expand All @@ -445,6 +460,7 @@ RETURNS TABLE ( key_name TEXT,
key_creation_time TIMESTAMP WITH TIME ZONE)
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_key_info() FROM PUBLIC;

CREATE FUNCTION pg_tde_server_key_info()
RETURNS TABLE ( key_name TEXT,
Expand All @@ -453,6 +469,7 @@ RETURNS TABLE ( key_name TEXT,
key_creation_time TIMESTAMP WITH TIME ZONE)
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_server_key_info() FROM PUBLIC;

CREATE FUNCTION pg_tde_default_key_info()
RETURNS TABLE ( key_name TEXT,
Expand All @@ -461,24 +478,29 @@ RETURNS TABLE ( key_name TEXT,
key_creation_time TIMESTAMP WITH TIME ZONE)
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_default_key_info() FROM PUBLIC;

CREATE FUNCTION pg_tde_delete_global_key_provider(provider_name TEXT)
RETURNS VOID
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_delete_global_key_provider(TEXT) FROM PUBLIC;

CREATE FUNCTION pg_tde_delete_database_key_provider(provider_name TEXT)
RETURNS VOID
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_delete_database_key_provider(TEXT) FROM PUBLIC;

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

-- Table access method
CREATE FUNCTION pg_tdeam_handler(internal)
RETURNS TABLE_AM_HANDLER
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tdeam_handler(internal) FROM PUBLIC;

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

CREATE FUNCTION pg_tde_ddl_command_end_capture()
RETURNS EVENT_TRIGGER
LANGUAGE C
AS 'MODULE_PATHNAME';
REVOKE ALL ON FUNCTION pg_tde_ddl_command_end_capture() FROM PUBLIC;

CREATE EVENT TRIGGER pg_tde_ddl_start
ON ddl_command_start
Expand Down Expand Up @@ -572,7 +596,3 @@ BEGIN
EXECUTE format('REVOKE EXECUTE ON FUNCTION pg_tde_verify_default_key() FROM %I', target_role);
END;
$$;

-- Revoking all the privileges from the public role
SELECT pg_tde_revoke_database_key_management_from_role('public');
SELECT pg_tde_revoke_key_viewer_from_role('public');
15 changes: 13 additions & 2 deletions contrib/pg_tde/sql/access_control.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -34,7 +34,19 @@ SELECT pg_tde_verify_key();
SELECT pg_tde_verify_server_key();
SELECT pg_tde_verify_default_key();

-- only superuser
-- Only superusers can execute key management functions, regardless of role grants
RESET ROLE;
GRANT EXECUTE ON FUNCTION pg_tde_add_database_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_add_global_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_change_database_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_change_global_key_provider(TEXT, TEXT, JSON) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_delete_database_key_provider(TEXT) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_delete_global_key_provider(TEXT) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_set_default_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_set_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) TO regress_pg_tde_access_control;
GRANT EXECUTE ON FUNCTION pg_tde_set_server_key_using_global_key_provider(TEXT, TEXT, BOOLEAN) TO regress_pg_tde_access_control;

SET ROLE regress_pg_tde_access_control;
SELECT pg_tde_add_database_key_provider_file('local-file-provider', '/tmp/pg_tde_test_keyring.per');
SELECT pg_tde_change_global_key_provider_file('local-file-provider', '/tmp/pg_tde_test_keyring.per');
SELECT pg_tde_delete_database_key_provider('local-file-provider');
Expand All @@ -44,7 +56,6 @@ SELECT pg_tde_delete_global_key_provider('global-file-provider');
SELECT pg_tde_set_key_using_global_key_provider('key1', 'global-file-provider');
SELECT pg_tde_set_default_key_using_global_key_provider('key1', 'global-file-provider');
SELECT pg_tde_set_server_key_using_global_key_provider('key1', 'global-file-provider');

RESET ROLE;

SELECT pg_tde_revoke_key_viewer_from_role('regress_pg_tde_access_control');
Expand Down
17 changes: 17 additions & 0 deletions contrib/pg_tde/t/001_basic.pl
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -16,6 +16,23 @@

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

# No C functions are granted to public by default
PGTDE::psql(
$node, 'postgres',
q{
SELECT
proname
FROM
pg_catalog.pg_proc
JOIN pg_catalog.pg_language ON prolang = pg_language.oid
LEFT JOIN LATERAL aclexplode(proacl) ON TRUE
WHERE
proname LIKE 'pg_tde%' AND
lanname = 'c' AND
(grantee IS NULL OR grantee = 0)
ORDER BY proname;
});

PGTDE::psql($node, 'postgres',
"SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_tde';");

Expand Down
16 changes: 16 additions & 0 deletions contrib/pg_tde/t/expected/001_basic.out
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,4 +1,20 @@
CREATE EXTENSION IF NOT EXISTS pg_tde;
SELECT
proname
FROM
pg_catalog.pg_proc
JOIN pg_catalog.pg_language ON prolang = pg_language.oid
LEFT JOIN LATERAL aclexplode(proacl) ON TRUE
WHERE
proname LIKE 'pg_tde%' AND
lanname = 'c' AND
(grantee IS NULL OR grantee = 0)
ORDER BY proname;
proname
---------
(0 rows)


SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_tde';
extname | extversion
---------+------------
Expand Down
0