Possible security vulnarability because some pgsodium functions still have PUBLIC execute permission

[epolkerman]

I'm new to pgsodium and we wanted to look if it's functionality can be used for us for e.g. transparant column encrytion to encrypt sensitive data. I'm not a big postgresql expert but want to share my findings to see if it is really an issue or not.

For the test I used a PostrgesQL 16.4 database on SUSE15 SP6 x86_64 and pgsodium verion 3.1.9.

First I have created the pgsodium_root.key with the example script pgsodium_getkey_urandom.sh and it is loaded in the database server correctly at database startup.

I have created database test and installed extension pgsodium in it:

> psql

postgres=# create database test;
CREATE DATABASE

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# 

test=# create extension pgsodium;
CREATE EXTENSION
test=#

Following users are present and also an overview of the role grants:

test=# \du
List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name  | floyd
Attributes | 
-[ RECORD 2 ]----------------------------------------------------------
Role name  | gonzo
Attributes | 
-[ RECORD 3 ]----------------------------------------------------------
Role name  | kermit
Attributes | 
-[ RECORD 4 ]----------------------------------------------------------
Role name  | pgsodium_keyholder
Attributes | Cannot login
-[ RECORD 5 ]----------------------------------------------------------
Role name  | pgsodium_keyiduser
Attributes | Cannot login
-[ RECORD 6 ]----------------------------------------------------------
Role name  | pgsodium_keymaker
Attributes | Cannot login
-[ RECORD 7 ]----------------------------------------------------------
Role name  | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS

test=# \drg
List of role grants
-[ RECORD 1 ]-----------------
Role name | gonzo
Member of | pgsodium_keyiduser
Options   | INHERIT, SET
Grantor   | postgres
-[ RECORD 2 ]-----------------
Role name | gonzo
Member of | pgsodium_keymaker
Options   | INHERIT, SET
Grantor   | postgres
-[ RECORD 3 ]-----------------
Role name | kermit
Member of | pgsodium_keyiduser
Options   | INHERIT, SET
Grantor   | postgres
-[ RECORD 4 ]-----------------
Role name | pgsodium_keyholder
Member of | pgsodium_keyiduser
Options   | INHERIT, SET
Grantor   | postgres
-[ RECORD 5 ]-----------------
Role name | pgsodium_keymaker
Member of | pgsodium_keyholder
Options   | INHERIT, SET
Grantor   | postgres
-[ RECORD 6 ]-----------------
Role name | pgsodium_keymaker
Member of | pgsodium_keyiduser
Options   | INHERIT, SET
Grantor   | postgres

Starting from zero and following the documentation I derived a key with function pgsodium.create_key. Started with regular user floyd to see if this user can use the function:

test=# \c test floyd localhost
Password for user floyd: 
You are now connected to database "test" as user "floyd" on host "localhost" (address "127.0.0.1") at port "5432".
test=> select * from pgsodium.create_key();
-[ RECORD 1 ]---+-------------------------------------
id              | 9aba691f-0110-418b-a5ff-935730c2551e
name            | 
status          | valid
key_type        | aead-det
key_id          | 1
key_context     | \x7067736f6469756d
created         | 2024-08-20 08:44:33.971819+02
expires         | 
associated_data | 

Not what I expected.... A regular use could fill the database with a lot of keys. Curious about this, I decided to encrypt some data with the created key to see what is happening:

test=> select * from pgsodium.crypto_aead_det_encrypt (
test(> 'this is the message',  -- a message to encrypt
test(> 'this is associated data',  -- some authenticated associated data
test(> '9aba691f-0110-418b-a5ff-935730c2551e'::uuid -- key ID
test(> );
ERROR:  permission denied for function crypto_aead_det_encrypt

That is as I expected. Below the extended listing of both functions

test=> \df+ pgsodium.create_key
List of functions
-[ RECORD 1 ]-------+-------------------------------------------------------------------------
Schema              | pgsodium
Name                | create_key
Result data type    | pgsodium.valid_key
Argument data types | key_type pgsodium.key_type DEFAULT 'aead-det'::pgsodium.key_type, name text DEFAULT NULL::text, raw_key bytea DEFAULT NULL::bytea, raw_key_nonce bytea DEFAULT NULL::bytea, parent_key uuid DEFAULT NULL::uuid, key_context bytea DEFAULT '\x7067736f6469756d'::bytea, expires timestamp with time zone DEFAULT NULL::timestamp with time zone, associated_data text DEFAULT ''::text
Type                | func
Volatility          | volatile
Parallel            | unsafe
Owner               | pgsodium_keymaker
Security            | definer
Access privileges   | =X/pgsodium_keymaker                                                                                                                                                                                                                                                                                                                                                                 +
                    | pgsodium_keymaker=X/pgsodium_keymaker                                                                                                                                                                                                                                                                                                                                                +
                    | pgsodium_keyiduser=X/pgsodium_keymaker
Language            | plpgsql
Internal name       | 
Description         | 

test=> \df+ pgsodium.crypto_aead_det_encrypt
List of functions
-[ RECORD 1 ]-------+-----------------------------------------------------------------------------------------------------------------------------------
Schema              | pgsodium
Name                | crypto_aead_det_encrypt
Result data type    | bytea
Argument data types | message bytea, additional bytea, key bytea, nonce bytea DEFAULT NULL::bytea
Type                | func
Volatility          | immutable
Parallel            | unsafe
Owner               | postgres
Security            | invoker
Access privileges   | postgres=X/postgres                                                                                                               +
                    | pgsodium_keyholder=X/postgres
Language            | c
Internal name       | pgsodium_crypto_aead_det_encrypt
Description         | 
-[ RECORD 2 ]-------+-----------------------------------------------------------------------------------------------------------------------------------
Schema              | pgsodium
Name                | crypto_aead_det_encrypt
Result data type    | bytea
Argument data types | message bytea, additional bytea, key_id bigint, context bytea DEFAULT '\x7067736f6469756d'::bytea, nonce bytea DEFAULT NULL::bytea
Type                | func
Volatility          | immutable
Parallel            | unsafe
Owner               | postgres
Security            | invoker
Access privileges   | postgres=X/postgres                                                                                                               +
                    | pgsodium_keyiduser=X/postgres
Language            | c
Internal name       | pgsodium_crypto_aead_det_encrypt_by_id
Description         | 
-[ RECORD 3 ]-------+-----------------------------------------------------------------------------------------------------------------------------------
Schema              | pgsodium
Name                | crypto_aead_det_encrypt
Result data type    | bytea
Argument data types | message bytea, additional bytea, key_uuid uuid
Type                | func
Volatility          | stable
Parallel            | unsafe
Owner               | pgsodium_keymaker
Security            | definer
Access privileges   | pgsodium_keymaker=X/pgsodium_keymaker                                                                                             +
                    | pgsodium_keyiduser=X/pgsodium_keymaker
Language            | plpgsql
Internal name       | 
Description         | 
-[ RECORD 4 ]-------+-----------------------------------------------------------------------------------------------------------------------------------
Schema              | pgsodium
Name                | crypto_aead_det_encrypt
Result data type    | bytea
Argument data types | message bytea, additional bytea, key_uuid uuid, nonce bytea
Type                | func
Volatility          | stable
Parallel            | unsafe
Owner               | pgsodium_keymaker
Security            | definer
Access privileges   | pgsodium_keymaker=X/pgsodium_keymaker                                                                                             +
                    | pgsodium_keyiduser=X/pgsodium_keymaker
Language            | plpgsql
Internal name       | 
Description         | 

As can be seen, the access privileges for function pgsodium.create_key has PUBLIC execute permission???

To see the reason why I dived into the extension sql files. In e.g. file pgsodium--1.1.1-1.2.0.sql there are loop blocks to explicitly revoke permissions from PUBLIC:

DO $$
DECLARE
	func text;
BEGIN
	FOREACH func IN ARRAY
	ARRAY[
		'crypto_secretbox(bytea, bytea, bytea)',
		'crypto_secretbox_open(bytea, bytea, bytea)',
		'crypto_auth(bytea, bytea)',
		'crypto_auth_verify(bytea, bytea, bytea)',
		'crypto_box',
		'crypto_box_open',
		'crypto_aead_ietf_encrypt(bytea, bytea, bytea, bytea)',
		'crypto_aead_ietf_decrypt(bytea, bytea, bytea, bytea)',
		'crypto_auth_hmacsha256',
		'crypto_auth_hmacsha256_verify',
		'crypto_auth_hmacsha512',
		'crypto_auth_hmacsha512_verify',
		'crypto_sign_init',
		'crypto_sign_update',
		'crypto_sign_final_create',
		'crypto_sign_final_verify',
		'crypto_sign_update_agg1',
		'crypto_sign_update_agg2'
	]
	LOOP
		EXECUTE format($i$
			REVOKE ALL ON FUNCTION %s FROM PUBLIC;
			GRANT EXECUTE ON FUNCTION %s TO pgsodium_keyholder;
		$i$, func, func);
	END LOOP;
END
$$;

Later on in e.g. pgsodium--3.0.4--3.0.5.sql this approach is used also:

DO $$
DECLARE
  func text;
BEGIN
    FOREACH func IN ARRAY
      ARRAY[
        'pgsodium.crypto_auth_hmacsha256(bytea, bigint, bytea)',
        'pgsodium.crypto_auth_hmacsha256_verify(bytea, bytea, bigint, bytea)',
        'pgsodium.crypto_auth_hmacsha512(bytea, bigint, bytea)',
        'pgsodium.crypto_auth_hmacsha512_verify(bytea, bytea, bigint, bytea)',
        'pgsodium.crypto_auth_hmacsha256(bytea, uuid)',
        'pgsodium.crypto_auth_hmacsha256_verify(bytea, bytea, uuid)',
        'pgsodium.crypto_auth_hmacsha512(bytea, uuid)',
        'pgsodium.crypto_auth_hmacsha512_verify(bytea, bytea, uuid)',
        'pgsodium.crypto_auth(bytea, uuid)',
        'pgsodium.crypto_auth_verify(bytea, bytea, uuid)',
        'pgsodium.crypto_shorthash(bytea, uuid)',
        'pgsodium.crypto_generichash(bytea, uuid)',
        'pgsodium.crypto_kdf_derive_from_key(integer, bigint, bytea, uuid)',
        'pgsodium.crypto_secretbox(bytea, bytea, uuid)',
        'pgsodium.crypto_secretbox_open(bytea, bytea, uuid)',
        'pgsodium.crypto_aead_det_encrypt(bytea, bytea, uuid)',
        'pgsodium.crypto_aead_det_decrypt(bytea, bytea, uuid)',
        'pgsodium.crypto_aead_det_encrypt(bytea, bytea, uuid, bytea)',
        'pgsodium.crypto_aead_det_decrypt(bytea, bytea, uuid, bytea)',
        'pgsodium.crypto_aead_ietf_encrypt(bytea, bytea, bytea, uuid)',
        'pgsodium.crypto_aead_ietf_decrypt(bytea, bytea, bytea, uuid)'
    ]
    LOOP
        EXECUTE format($i$
            REVOKE ALL ON FUNCTION %s FROM PUBLIC;
            GRANT EXECUTE ON FUNCTION %s TO pgsodium_keyiduser;
        $i$, func, func);
    END LOOP;
END
$$;

This explains that regular user floyd does not have permission to execute function pgsodium.crypto_aead_det_encrypt. Back to function pgsodium.create_key...

In sql file pgsodium--2.0.2--3.0.0.sql the following statements are used (and later on for schema pgsodium_masks):

-- By default, public can't use any table, functions, or sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA pgsodium REVOKE ALL ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA pgsodium REVOKE ALL ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA pgsodium REVOKE ALL ON SEQUENCES FROM PUBLIC;

According to the PostgreSQL documentation (I don't know how it was described a year ago, because I can only see the documentation for the current version) it says that the default access privileges are (https://www.postgresql.org/docs/16/ddl-priv.html)

Table 5.2. Summary of Access Privileges

Object Type	All Privileges	Default PUBLIC Privileges	psql Command
DATABASE	CTc	Tc	\l
DOMAIN	U	U	\dD+
FUNCTION or PROCEDURE	X	X	\df+
FOREIGN DATA WRAPPER	U	none	\dew+
FOREIGN SERVER	U	none	\des+
LANGUAGE	U	U	\dL+
LARGE OBJECT	rw	none	\dl+
PARAMETER	sA	none	\dconfig+
SCHEMA	UC	none	\dn+
SEQUENCE	rwU	none	\dp
TABLE (and table-like objects)	arwdDxt	none	\dp
Table column	arwx	none	\dp
TABLESPACE	C	none	\db+
TYPE	U	U	\dT+

According to this table the ALTER DEFAULT PRIVILEGES lines for TABLES and SEQUENCES are not necessary because PUBLIC has no privileges on those objects by default. But what is more interesting is what is described here in the documentation (https://www.postgresql.org/docs/16/sql-alterdefaultprivileges.html):

(This is found below heading Examples)
Remove the public EXECUTE permission that is normally granted on functions, for all functions subsequently created by role admin:

ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

Note however that you cannot accomplish that effect with a command limited to a single schema. This command has no effect, unless it is undoing a matching GRANT:

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

That's because per-schema default privileges can only add privileges to the global setting, not remove privileges granted by it.

If I understand this correctly, the line ALTER DEFAULT PRIVILEGES IN SCHEMA pgsodium REVOKE ALL ON FUNCTIONS FROM PUBLIC; does not revoke execute permissions from PUBLIC at all!!!

This is proven for function pgsodium.create_key, because in sql file pgsodium--3.0.4--3.0.4.sql this function is dropped in line 24 and later on re-created on line 64. On line 108 and 109 you see following:

ALTER FUNCTION pgsodium.create_key OWNER TO pgsodium_keymaker;
GRANT EXECUTE ON FUNCTION pgsodium.create_key TO pgsodium_keyiduser;

In the function description above you can see that it is true, but it has also PUBLIC execute permission because to my opinion the ALTER DEFAULT PRIVILEGES does not work the way it was intended for the sql extension files

I think it is necessary to revoke explicitly PUBLIC execute permission for the pgsodium functions, because all the created function from pgsodium--2.0.2-3.0.0.sql and later on have PUBLIC execute permission unless explicitly revoked in a loop block. I don't know if there are pgsodium functions that may have PUBLIC execute permission so it is hard to say for me how big the impact will be