1#[cfg(feature = "postgres")]
21use super::encrypted_columns::{ColumnCipher, EncryptedColumn, ROOT_DEK_BYTES};
22#[cfg(feature = "postgres")]
23use anyhow::{bail, Result};
24#[cfg(feature = "postgres")]
25use zeroize::Zeroizing;
26
27pub const USERSTORE_SCHEMA: &str = r#"
36CREATE TABLE IF NOT EXISTS users (
37 username TEXT PRIMARY KEY CHECK (username <> ''),
38 sub TEXT NOT NULL UNIQUE CHECK (sub <> ''),
39 name BYTEA CONSTRAINT users_name_storage_check CHECK (
40 name IS NULL OR (
41 octet_length(name) >= 32
42 AND substring(name from 1 for 4) = decode('48534331', 'hex')
43 )
44 ),
45 email BYTEA CONSTRAINT users_email_storage_check CHECK (
46 email IS NULL OR (
47 octet_length(email) >= 32
48 AND substring(email from 1 for 4) = decode('48534331', 'hex')
49 )
50 ),
51 email_verified BOOLEAN,
52 active BOOLEAN NOT NULL DEFAULT TRUE,
53 external_id BYTEA CONSTRAINT users_external_id_storage_check CHECK (
54 external_id IS NULL OR (
55 octet_length(external_id) >= 32
56 AND substring(external_id from 1 for 4) = decode('48534331', 'hex')
57 )
58 ),
59 key_custody TEXT
60 CHECK (key_custody IS NULL OR key_custody IN ('self_custody', 'managed')),
61 created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
62 updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
63);
64CREATE TABLE IF NOT EXISTS user_did_bindings (
65 username TEXT PRIMARY KEY REFERENCES users(username) ON DELETE CASCADE,
66 atproto_did TEXT NOT NULL UNIQUE CHECK (atproto_did <> '')
67);
68CREATE TABLE IF NOT EXISTS oidc_bindings (
69 issuer TEXT NOT NULL CHECK (issuer <> ''),
70 issuer_sub TEXT NOT NULL CHECK (issuer_sub <> ''),
71 username TEXT NOT NULL REFERENCES users(username) ON DELETE CASCADE,
72 PRIMARY KEY (issuer, issuer_sub),
73 UNIQUE (issuer, username)
74);
75CREATE TABLE IF NOT EXISTS pubkeys (
76 fingerprint TEXT PRIMARY KEY CHECK (fingerprint <> ''),
77 username TEXT NOT NULL REFERENCES users(username) ON DELETE CASCADE,
78 pubkey BYTEA NOT NULL
79 CONSTRAINT pubkeys_pubkey_storage_check CHECK (
80 octet_length(pubkey) = 64
81 AND substring(pubkey from 1 for 4) = decode('48534331', 'hex')
82 ),
83 label BYTEA CONSTRAINT pubkeys_label_storage_check CHECK (
84 label IS NULL OR (
85 octet_length(label) >= 32
86 AND substring(label from 1 for 4) = decode('48534331', 'hex')
87 )
88 ),
89 algorithm TEXT NOT NULL DEFAULT 'ed25519'
90 CHECK (algorithm IN ('ed25519', 'ed25519+ml-dsa-65')),
91 pq_pubkey BYTEA,
92 created_at BIGINT NOT NULL,
93 last_used_at BIGINT,
94 CONSTRAINT pubkeys_algorithm_pq_storage_check CHECK (
95 (algorithm = 'ed25519' AND pq_pubkey IS NULL)
96 OR (
97 algorithm = 'ed25519+ml-dsa-65'
98 AND pq_pubkey IS NOT NULL
99 AND octet_length(pq_pubkey) = 1984
100 AND substring(pq_pubkey from 1 for 4) = decode('48534331', 'hex')
101 )
102 )
103);
104CREATE INDEX IF NOT EXISTS pubkeys_username_idx ON pubkeys(username);
105CREATE INDEX IF NOT EXISTS oidc_bindings_username_idx ON oidc_bindings(username);
106CREATE TABLE IF NOT EXISTS user_encryption_keys (
107 username TEXT PRIMARY KEY REFERENCES users(username) ON DELETE CASCADE,
108 wrapped_dek BYTEA NOT NULL
109);
110"#;
111
112#[cfg(feature = "postgres")]
125pub(crate) fn seal_text(
126 cipher: Option<&ColumnCipher>,
127 root: Option<&Zeroizing<[u8; ROOT_DEK_BYTES]>>,
128 username: &str,
129 column: EncryptedColumn<'_>,
130 value: Option<String>,
131) -> Result<Option<Vec<u8>>> {
132 match (value, cipher, root) {
133 (None, _, _) => Ok(None),
134 (Some(text), None, None) => Ok(Some(text.into_bytes())),
135 (Some(text), Some(cipher), Some(root)) => Ok(Some(cipher.encrypt(
136 root,
137 username,
138 column,
139 text.as_bytes(),
140 )?)),
141 _ => bail!("cipher/root state mismatch in seal_text"),
142 }
143}
144
145#[cfg(feature = "postgres")]
148pub(crate) fn open_text(
149 cipher: Option<&ColumnCipher>,
150 root: Option<&Zeroizing<[u8; ROOT_DEK_BYTES]>>,
151 username: &str,
152 column: EncryptedColumn<'_>,
153 raw: Option<Vec<u8>>,
154) -> Result<Option<String>> {
155 match (raw, cipher, root) {
156 (None, _, _) => Ok(None),
157 (Some(bytes), None, None) => Ok(Some(String::from_utf8(bytes)?)),
158 (Some(bytes), Some(cipher), Some(root)) => {
159 let pt = cipher.decrypt(root, username, column, &bytes)?;
160 Ok(Some(String::from_utf8(pt.to_vec())?))
161 }
162 _ => bail!("cipher/root state mismatch in open_text"),
163 }
164}
165
166#[cfg(feature = "postgres")]
168pub(crate) fn seal_raw(
169 cipher: Option<&ColumnCipher>,
170 root: Option<&Zeroizing<[u8; ROOT_DEK_BYTES]>>,
171 username: &str,
172 column: EncryptedColumn<'_>,
173 value: &[u8],
174) -> Result<Vec<u8>> {
175 match (cipher, root) {
176 (None, None) => Ok(value.to_vec()),
177 (Some(cipher), Some(root)) => cipher.encrypt(root, username, column, value),
178 _ => bail!("cipher/root state mismatch in seal_raw"),
179 }
180}
181
182#[cfg(feature = "postgres")]
184pub(crate) fn open_raw(
185 cipher: Option<&ColumnCipher>,
186 root: Option<&Zeroizing<[u8; ROOT_DEK_BYTES]>>,
187 username: &str,
188 column: EncryptedColumn<'_>,
189 raw: &[u8],
190) -> Result<Zeroizing<Vec<u8>>> {
191 match (cipher, root) {
192 (None, None) => Ok(Zeroizing::new(raw.to_vec())),
193 (Some(cipher), Some(root)) => cipher.decrypt(root, username, column, raw),
194 _ => bail!("cipher/root state mismatch in open_raw"),
195 }
196}