8000 fix: grant pgsodium functions to service_role (#443) · sorokinvld/postgres@ded929f · GitHub
[go: up one dir, main page]
Skip to content

Commit ded929f

Browse files
soedirgopcnc
soedirgo
and
pcnc
authored
fix: grant pgsodium functions to service_role (supabase#443)
* fix: grant pgsodium functions to service_role Using supautils's extension custom scripts. The script will be run on `create extension pgsodium`. * test: service_role can execute certain pgsodium functions * chore: update schema.sql * chore: bump postgres version Co-authored-by: Paul Cioanca <paul.cioanca@supabase.io>
1 parent fb5103e commit ded929f

File tree

6 files changed

+22
-50
lines changed

6 files changed

+22
-50
lines changed

‎ansible/files/postgresql_config/supautils.conf.j2

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4,6 +4,7 @@ supautils.placeholders_disallowed_values = '"content-type"'
44
# omitted because may be unsafe: adminpack, amcheck, file_fdw, lo, old_snapshot, pageinspect, pg_buffercache, pg_freespacemap, pg_prewarm, pg_surgery, pg_visibility, pgstattuple
55
# omitted because deprecated: intagg, xml2
66
supautils.privileged_extensions = 'address_standardizer, address_standardizer_data_us, autoinc, bloom, btree_gin, btree_gist, citext, cube, dblink, dict_int, dict_xsyn, earthdistance, fuzzystrmatch, hstore, http, insert_username, intarray, isn, ltree, moddatetime, pg_cron, pg_graphql, pg_hashids, pg_jsonschema, pg_net, pg_stat_monitor, pg_stat_statements, pg_trgm, pg_walinspect, pgaudit, pgcrypto, pgjwt, pgroonga, pgroonga_database, pgrouting, pgrowlocks, pgsodium, pgtap, plcoffee, pljava, plls, plpgsql, plpgsql_check, plv8, postgis, postgis_raster, postgis_sfcgal, postgis_tiger_geocoder, postgis_topology, postgres_fdw, refint, rum, seg, sslinfo, supabase_vault, supautils, tablefunc, tcn, timescaledb, tsm_system_rows, tsm_system_time, unaccent, uuid-ossp, wrappers'
7+
supautils.privileged_extensions_custom_scripts_path = '/etc/postgresql-custom/extension-custom-scripts'
78
supautils.privileged_extensions_superuser = 'supabase_admin'
89
supautils.privileged_role = 'postgres'
910
supautils.privileged_role_allowed_configs = 'pgaudit.log, pgaudit.log_catalog, pgaudit.log_client, pgaudit.log_level, pgaudit.log_relation, pgaudit.log_rows, pgaudit.log_statement, pgaudit.log_statement_once, pgaudit.role, session_replication_role, track_io_timing'

‎ansible/files/postgresql_extension_custom_scripts/pgsodium/after-create.sql

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
grant execute on function pgsodium.crypto_aead_det_decrypt(bytea, bytea, uuid, bytea) to service_role;
2+
grant execute on function pgsodium.crypto_aead_det_encrypt(bytea, bytea, uuid, bytea) to service_role;
3+
grant execute on function pgsodium.crypto_aead_det_keygen to service_role;
4+
grant execute on function pgsodium.crypto_aead_det_noncegen to service_role;

‎ansible/tasks/internal/supautils.yml

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,15 @@
4747
owner: postgres
4848
group: postgres
4949

50+
- name: supautils - copy extension custom scripts
51+
copy:
52+
src: files/postgresql_extension_custom_scripts
53+
dest: /etc/postgresql-custom/extension-custom-scripts
54+
mode: 0664
55+
owner: postgres
56+
group: postgres
57+
become: yes
58+
5059
- name: supautils - include /etc/postgresql-custom/supautils.conf in postgresql.conf
5160
become: yes
5261
replace:

‎common.vars.pkr.hcl

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1 +1 @@
1-
postgres-version = "15.1.0.13"
1+
postgres-version = "15.1.0.14"

‎ebssurrogate/files/unit-tests/unit-test-01.sql

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
BEGIN;
2-
SELECT plan( 5 );
2+
SELECT plan(9);
33

44
-- Check installed extensions
55
SELECT extensions_are(
@@ -23,5 +23,11 @@ SELECT has_schema('pg_catalog');
2323
SELECT has_schema('information_schema');
2424
SELECT has_schema('public');
2525

26+
-- Check that service_role can execute certain pgsodium functions
27+
SELECT function_privs_are('pgsodium', 'crypto_aead_det_decrypt', array['bytea', 'bytea', 'uuid', 'bytea'], 'service_role', array['EXECUTE'])
28+
SELECT function_privs_are('pgsodium', 'crypto_aead_det_encrypt', array['bytea', 'bytea', 'uuid', 'bytea'], 'service_role', array['EXECUTE'])
29+
SELECT function_privs_are('pgsodium', 'crypto_aead_det_keygen', array[], 'service_role', array['EXECUTE'])
30+
SELECT function_privs_are('pgsodium', 'crypto_aead_det_noncegen', array[], 'service_role', array['EXECUTE'])
31+
2632
SELECT * from finish();
2733
ROLLBACK;

‎migrations/schema.sql

Lines changed: 0 additions & 48 deletions
Original file line numberDiff line numberDiff line change
@@ -510,25 +510,6 @@ END;
510510
$$;
511511

512512

513-
--
514-
-- Name: key_encrypt_secret(); Type: FUNCTION; Schema: pgsodium; Owner: -
515-
--
516-
517-
CREATE FUNCTION pgsodium.key_encrypt_secret() RETURNS trigger
518-
LANGUAGE plpgsql
519-
AS $$
520-
BEGIN
521-
new.raw_key = CASE WHEN new.raw_key IS NULL THEN NULL ELSE
522-
CASE WHEN new.parent_key IS NULL THEN NULL ELSE
523-
pgsodium.crypto_aead_det_encrypt(new.raw_key::bytea, pg_catalog.convert_to((new.id::text || new.associated_data::text)::text, 'utf8'),
524-
new.parent_key::uuid,
525-
new.raw_key_nonce
526-
) END END;
527-
RETURN new;
528-
END;
529-
$$;
530-
531-
532513
--
533514
-- Name: extension(text); Type: FUNCTION; Schema: storage; Owner: -
534515
--
@@ -754,35 +735,6 @@ CREATE TABLE auth.users (
754735
COMMENT ON TABLE auth.users IS 'Auth: Stores user login data within a secure schema.';
755736

756737

757-
--
758-
-- Name: decrypted_key; Type: VIEW; Schema: pgsodium; Owner: -
759-
--
760-
761-
CREATE VIEW pgsodium.decrypted_key AS
762-
SELECT key.id,
763-
key.status,
764-
key.created,
765-
key.expires,
766-
key.key_type,
767-
key.key_id,
768-
key.key_context,
769-
key.name,
770-
key.associated_data,
771-
key.raw_key,
772-
CASE
773-
WHEN (key.raw_key IS NULL) THEN NULL::bytea
774-
ELSE
775-
CASE
776-
WHEN (key.parent_key IS NULL) THEN NULL::bytea
777-
ELSE pgsodium.crypto_aead_det_decrypt(key.raw_key, convert_to(((key.id)::text || key.associated_data), 'utf8'::name), key.parent_key, key.raw_key_nonce)
778-
END
779-
END AS decrypted_raw_key,
780-
key.raw_key_nonce,
781-
key.parent_key,
782-
key.comment
783-
FROM pgsodium.key;
784-
785-
786738
--
787739
-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -
788740
--

0 commit comments

Comments
 (0)
0