postgres/nix/tests/expected/z_multigres-orioledb-17_vault.out at 2532c3d3b3bbfca96e1963ff8d57ed9cbe95118f · supabase/postgres · GitHub

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
-- Tests role privileges on the vault objects
-- INSERT and UPDATE privileges should not be present on the vault tables for postgres and service_role, only SELECT and DELETE
WITH schema_obj AS (
  SELECT oid, nspname
  FROM pg_namespace
  WHERE nspname = 'vault'
)
SELECT
  s.nspname AS schema,
  c.relname AS object_name,
  acl.grantee::regrole::text AS grantee,
  acl.privilege_type,
  acl.is_grantable
FROM pg_class c
JOIN schema_obj s ON s.oid = c.relnamespace
CROSS JOIN LATERAL aclexplode(c.relacl) AS acl
WHERE c.relkind IN ('r', 'v', 'm', 'f', 'p')
  AND acl.privilege_type <> 'MAINTAIN'
UNION ALL
SELECT
  s.nspname AS schema,
  p.proname AS object_name,
  acl.grantee::regrole::text AS grantee,
  acl.privilege_type,
  acl.is_grantable
FROM pg_proc p
JOIN schema_obj s ON s.oid = p.pronamespace
CROSS JOIN LATERAL aclexplode(p.proacl) AS acl
ORDER BY object_name, grantee, privilege_type;
 schema |        object_name        |    grantee     | privilege_type | is_grantable
--------+---------------------------+----------------+----------------+--------------
 vault  | _crypto_aead_det_decrypt  | postgres       | EXECUTE        | t
 vault  | _crypto_aead_det_decrypt  | service_role   | EXECUTE        | f
 vault  | _crypto_aead_det_decrypt  | supabase_admin | EXECUTE        | f
 vault  | _crypto_aead_det_encrypt  | supabase_admin | EXECUTE        | f
 vault  | _crypto_aead_det_noncegen | supabase_admin | EXECUTE        | f
 vault  | create_secret             | postgres       | EXECUTE        | t
 vault  | create_secret             | service_role   | EXECUTE        | f
 vault  | create_secret             | supabase_admin | EXECUTE        | f
 vault  | decrypted_secrets         | postgres       | DELETE         | t
 vault  | decrypted_secrets         | postgres       | REFERENCES     | t
 vault  | decrypted_secrets         | postgres       | SELECT         | t
 vault  | decrypted_secrets         | postgres       | TRUNCATE       | t
 vault  | decrypted_secrets         | service_role   | DELETE         | f
 vault  | decrypted_secrets         | service_role   | SELECT         | f
 vault  | decrypted_secrets         | supabase_admin | DELETE         | f
 vault  | decrypted_secrets         | supabase_admin | INSERT         | f
 vault  | decrypted_secrets         | supabase_admin | REFERENCES     | f
 vault  | decrypted_secrets         | supabase_admin | SELECT         | f
 vault  | decrypted_secrets         | supabase_admin | TRIGGER        | f
 vault  | decrypted_secrets         | supabase_admin | TRUNCATE       | f
 vault  | decrypted_secrets         | supabase_admin | UPDATE         | f
 vault  | secrets                   | postgres       | DELETE         | t
 vault  | secrets                   | postgres       | REFERENCES     | t
 vault  | secrets                   | postgres       | SELECT         | t
 vault  | secrets                   | postgres       | TRUNCATE       | t
 vault  | secrets                   | service_role   | DELETE         | f
 vault  | secrets                   | service_role   | SELECT         | f
 vault  | secrets                   | supabase_admin | DELETE         | f
 vault  | secrets                   | supabase_admin | INSERT         | f
 vault  | secrets                   | supabase_admin | REFERENCES     | f
 vault  | secrets                   | supabase_admin | SELECT         | f
 vault  | secrets                   | supabase_admin | TRIGGER        | f
 vault  | secrets                   | supabase_admin | TRUNCATE       | f
 vault  | secrets                   | supabase_admin | UPDATE         | f
 vault  | update_secret             | postgres       | EXECUTE        | t
 vault  | update_secret             | service_role   | EXECUTE        | f
 vault  | update_secret             | supabase_admin | EXECUTE        | f
(37 rows)

-- vault indexes with owners
SELECT
    ns.nspname AS schema,
    t.relname AS table,
    i.relname AS index_name,
    r.rolname AS index_owner,
    CASE
        WHEN idx.indisunique THEN 'Unique'
        ELSE 'Non Unique'
    END AS index_type
FROM
    pg_class t
JOIN
    pg_namespace ns ON t.relnamespace = ns.oid
JOIN
    pg_index idx ON t.oid = idx.indrelid
JOIN
    pg_class i ON idx.indexrelid = i.oid
JOIN
    pg_roles r ON i.relowner = r.oid
WHERE
    ns.nspname = 'vault'
ORDER BY
    t.relname,
    i.relname;
 schema |  table  |    index_name    |  index_owner   | index_type
--------+---------+------------------+----------------+------------
 vault  | secrets | secrets_name_idx | supabase_admin | Unique
 vault  | secrets | secrets_pkey     | supabase_admin | Unique
(2 rows)

-- assert search_path is preserved after after-create script is run
show search_path;
            search_path
------------------------------------
 "\$user", public, auth, extensions
(1 row)