Repository navigation
Expand file tree
/
Copy pathsupabase_vault.sql
More file actions
132 lines (112 loc) · 5.07 KB
/
Copy pathsupabase_vault.sql
File metadata and controls
132 lines (112 loc) · 5.07 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
CREATE SCHEMA vault;
ALTER SCHEMA vault OWNER TO postgres;
CREATE FUNCTION vault._crypto_aead_det_decrypt(message bytea
, additional bytea
, key_id bigint
, context bytea = CAST('\x7067736f6469756d' AS bytea)
, nonce bytea = CAST(NULL AS bytea))
RETURNS bytea
LANGUAGE c IMMUTABLE AS '$libdir/supabase_vault'
, 'pgsodium_crypto_aead_det_decrypt_by_id';
ALTER FUNCTION vault._crypto_aead_det_decrypt (message bytea, additional bytea, key_id bigint, context bytea, nonce bytea) OWNER TO postgres;
CREATE FUNCTION vault._crypto_aead_det_encrypt(message bytea
, additional bytea
, key_id bigint
, context bytea = CAST('\x7067736f6469756d' AS bytea)
, nonce bytea = CAST(NULL AS bytea))
RETURNS bytea
LANGUAGE c IMMUTABLE AS '$libdir/supabase_vault'
, 'pgsodium_crypto_aead_det_encrypt_by_id';
ALTER FUNCTION vault._crypto_aead_det_encrypt (message bytea, additional bytea, key_id bigint, context bytea, nonce bytea) OWNER TO postgres;
CREATE FUNCTION vault._crypto_aead_det_noncegen()
RETURNS bytea
LANGUAGE c IMMUTABLE AS '$libdir/supabase_vault'
, 'pgsodium_crypto_aead_det_noncegen';
ALTER FUNCTION vault._crypto_aead_det_noncegen () OWNER TO postgres;
CREATE FUNCTION vault.create_secret(new_secret text
, new_name text = CAST(NULL AS text)
, new_description text = CAST('' AS text)
, new_key_id uuid = CAST(NULL AS uuid))
RETURNS uuid
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'pg_catalog', 'pg_temp'
AS $$
DECLARE
rec record;
BEGIN
INSERT INTO vault.secrets (secret, name, description)
VALUES (
new_secret,
new_name,
new_description
)
RETURNING * INTO rec;
UPDATE vault.secrets s
SET secret = pg_catalog.encode(vault._crypto_aead_det_encrypt(
message := pg_catalog.convert_to(rec.secret, 'utf8'),
additional := pg_catalog.convert_to(s.id::pg_catalog.text, 'utf8'),
key_id := 0,
context := 'pgsodium'::pg_catalog.bytea,
nonce := rec.nonce
), 'base64')
WHERE s.id OPERATOR(pg_catalog.=) rec.id;
RETURN rec.id;
END
$$;
ALTER FUNCTION vault.create_secret (new_secret text, new_name text, new_description text, new_key_id uuid) OWNER TO postgres;
CREATE FUNCTION vault.update_secret(secret_id uuid
, new_secret text = CAST(NULL AS text)
, new_name text = CAST(NULL AS text)
, new_description text = CAST(NULL AS text)
, new_key_id uuid = CAST(NULL AS uuid))
RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path TO 'pg_catalog', 'pg_temp'
AS $$
BEGIN
UPDATE vault.secrets s
SET
secret = CASE WHEN new_secret IS NULL THEN s.secret
ELSE pg_catalog.encode(vault._crypto_aead_det_encrypt(
message := pg_catalog.convert_to(new_secret, 'utf8'),
additional := pg_catalog.convert_to(s.id::pg_catalog.text, 'utf8'),
key_id := 0,
context := 'pgsodium'::pg_catalog.bytea,
nonce := s.nonce
), 'base64') END,
name = coalesce(new_name, s.name),
description = coalesce(new_description, s.description),
updated_at = pg_catalog.now()
WHERE s.id OPERATOR(pg_catalog.=) secret_id;
END
$$;
ALTER FUNCTION vault.update_secret (secret_id uuid, new_secret text, new_name text, new_description text, new_key_id uuid) OWNER TO postgres;
GRANT USAGE
ON SCHEMA vault
TO bob
WITH GRANT OPTION;
REVOKE ALL PRIVILEGES
ON FUNCTION vault._crypto_aead_det_decrypt (message bytea, additional bytea, key_id bigint, context bytea, nonce bytea)
FROM PUBLIC;
GRANT ALL PRIVILEGES
ON FUNCTION vault._crypto_aead_det_decrypt (message bytea, additional bytea, key_id bigint, context bytea, nonce bytea)
TO bob
WITH GRANT OPTION;
REVOKE ALL PRIVILEGES
ON FUNCTION vault._crypto_aead_det_encrypt (message bytea, additional bytea, key_id bigint, context bytea, nonce bytea)
FROM PUBLIC;
REVOKE ALL PRIVILEGES
ON FUNCTION vault._crypto_aead_det_noncegen ()
FROM PUBLIC;
REVOKE ALL PRIVILEGES
ON FUNCTION vault.create_secret (new_secret text, new_name text, new_description text, new_key_id uuid)
FROM PUBLIC;
GRANT ALL PRIVILEGES
ON FUNCTION vault.create_secret (new_secret text, new_name text, new_description text, new_key_id uuid)
TO bob
WITH GRANT OPTION;
REVOKE ALL PRIVILEGES
ON FUNCTION vault.update_secret (secret_id uuid, new_secret text, new_name text, new_description text, new_key_id uuid)
FROM PUBLIC;
GRANT ALL PRIVILEGES
ON FUNCTION vault.update_secret (secret_id uuid, new_secret text, new_name text, new_description text, new_key_id uuid)
TO bob
WITH GRANT OPTION