At this moment, this guide doesn't cover mTLS connections to your database. It is recommended to use mTLS connections instead of plain text connections.
We'll complete this guide as soon as mTLS connections are fully tested.
If you are hosting your database elsewhere, you can configure the Toucan Helm Stack to connect to it. However, some database migrations must be executed manually.
1
Disable the embedded database
Set these parameters in your values file:
yaml: values.override.yaml
postgresql:enabled:false
2
Create the users and databases
Open your database client and run the following commands:
sql: Database client
CREATEUSERtoucanWITHPASSWORD'<password>';-- Curity (Authentication Service)CREATEDATABASEcurity;GRANT ALL PRIVILEGES ONDATABASE curity TO toucan;-- DatasetCREATEDATABASEdataset;-- (optional) Create a schema for the datasetCREATESCHEMAdataset;GRANT ALL PRIVILEGES ONDATABASE dataset TO toucan;-- LayoutCREATEDATABASElayout;CREATESCHEMAlayout;CREATESCHEMAworkspace;GRANT ALL PRIVILEGES ONDATABASE layout TO toucan;-- SpiceDBCREATEDATABASEspicedb;GRANT ALL PRIVILEGES ONDATABASE spicedb TO toucan;-- VaultCREATEDATABASEvault;GRANT ALL PRIVILEGES ONDATABASE vault TO toucan;
3
Execute manual migrations
Curity
Download Curity from their developer portal and run the migration in the idsvr/etc/postgres-create_database.sql directory.
DO NOT run the one stored at idsvr/etc/postgres-create_database.sql.
Curity schema
sql: Database client
\c curity;-- While Postgres has native support for UUID's, an extension is needed for generating them.-- This extension comes bundled with most installations of Postgres but if not must be installed separately---- https://dba.stackexchange.com/questions/122623/default-value-for-uuid-column-in-postgres--CREATE EXTENSION IFNOTEXISTS"uuid-ossp";/* Token Store : table delegations */CREATETABLEdelegations ( id VARCHAR(40) PRIMARY KEY, tenant_id VARCHAR(64) NULL,ownerVARCHAR(128) NOT NULL, created BIGINTNOT NULL, expires BIGINTNOT NULL, scope VARCHAR(1000) NULL, scope_claims TEXTNULL, client_id VARCHAR(128) NOT NULL, redirect_uri VARCHAR(512) NULL,statusVARCHAR(16) NOT NULL, claims TEXTNULL, authentication_attributes TEXTNULL, authorization_code_hash VARCHAR(89) NULL);CREATEINDEXIDX_DELEGATIONS_CLIENT_IDON delegations (client_id ASC);CREATEINDEXIDX_DELEGATIONS_STATUSON delegations (statusASC);CREATEINDEXIDX_DELEGATIONS_EXPIRESON delegations (expires ASC);CREATEINDEXIDX_DELEGATIONS_OWNERON delegations (ownerASC);CREATEINDEXIDX_DELEGATIONS_AUTHORIZATION_CODE_HASHON delegations (authorization_code_hash ASC);COMMENT ON COLUMN delegations.id IS 'Unique identifier';COMMENT ON COLUMN delegations.tenant_id IS 'The tenant ID of this delegation';COMMENT ON COLUMN delegations.owner IS 'Subject for whom the delegation is issued';COMMENT ON COLUMN delegations.expires IS 'Moment when delegation expires, as measured in number of seconds since epoch';COMMENT ON COLUMN delegations.scope IS 'Space delimited list of scope values';COMMENT ON COLUMN delegations.scope_claims IS 'JSON with the scope-claims configuration at the time of delegation issuance';COMMENT ON COLUMN delegations.client_id IS 'Reference to a client; non-enforced';COMMENT ON COLUMN delegations.redirect_uri IS 'Optional value for the redirect_uri parameter, when provided in a request for delegation';COMMENT ON COLUMN delegations.status IS 'Status of the delegation instance, from {''issued'', ''revoked''}';COMMENT ON COLUMN delegations.claims IS 'Optional JSON that contains a list of claims that are part of the delegation';COMMENT ON COLUMN delegations.authentication_attributes IS 'The JSON-serialized AuthenticationAttributes established for this delegation';COMMENT ON COLUMN delegations.authorization_code_hash IS 'A hash of the authorization code that was provided when this delegation was issued.';/* Token Store : table tokens */CREATETABLEtokens ( token_hash VARCHAR(89) NOT NULLPRIMARY KEY, id VARCHAR(64) NULL, delegations_id VARCHAR(40) NOT NULL , purpose VARCHAR(32) NOT NULL, usage VARCHAR(8) NOT NULL, format VARCHAR(32) NOT NULL, created BIGINTNOT NULL, expires BIGINTNOT NULL, scope VARCHAR(1000)NULL, scope_claims TEXTNULL,statusVARCHAR(16) NOT NULL, issuer VARCHAR(200) NOT NULL,subjectVARCHAR(64) NOT NULL, audience VARCHAR(512) NULL, not_before BIGINTNULL, claims TEXTNULL, meta_data TEXTNULL);CREATEINDEXIDX_TOKENS_IDON tokens (id);CREATEINDEXIDX_TOKENS_STATUSON tokens (statusASC);CREATEINDEXIDX_TOKENS_EXPIRESON tokens (expires ASC);COMMENT ON COLUMN tokens.token_hash IS 'Base64 encoded sha-512 hash of the token value.';COMMENT ON COLUMN tokens.id IS 'Identifier of the token, when it exists; this can be the value from the ''jti''-claim of a JWT, etc. Opaque tokens do not have an id.';COMMENT ON COLUMN tokens.delegations_id IS 'Reference to the delegation instance that underlies the token';COMMENT ON COLUMN tokens.purpose IS 'Purpose of the token, i.e. ''nonce'', ''accesstoken'', ''refreshtoken'', ''custom'', etc.';COMMENT ON COLUMN tokens.usage IS 'Indication whether the token is a bearer or proof token, from {"bearer", "proof"}';COMMENT ON COLUMN tokens.format IS 'The format of the token, i.e. ''opaque'', ''jwt'', etc.';COMMENT ON COLUMN tokens.created IS 'Moment when token record is created, as measured in number of seconds since epoch';COMMENT ON COLUMN tokens.expires IS 'Moment when token expires, as measured in number of seconds since epoch';COMMENT ON COLUMN tokens.scope IS 'Space delimited list of scope values';COMMENT ON COLUMN tokens.scope_claims IS 'Space delimited list of scope-claims values';COMMENT ON COLUMN tokens.status IS 'Status of the token from {''issued'', ''used'', ''revoked''}';COMMENT ON COLUMN tokens.issuer IS 'Optional name of the issuer of the token (jwt.iss)';COMMENT ON COLUMN tokens.subject IS 'Optional subject of the token (jwt.sub)';COMMENT ON COLUMN tokens.audience IS 'Space separated list of audiences for the token (jwt.aud)';COMMENT ON COLUMN tokens.not_before IS 'Moment before which the token is not valid, as measured in number of seconds since epoch (jwt.nbf)';COMMENT ON COLUMN tokens.claims IS 'Optional JSON-blob that contains a list of claims that are part of the token';CREATETABLEnonces ( token VARCHAR(64) NOT NULLPRIMARY KEY, reference_data TEXTNOT NULL, created BIGINTNOT NULL, ttl BIGINTNOT NULL, consumed BIGINTNULL,statusVARCHAR(16) NOT NULLDEFAULT'issued');COMMENT ON COLUMN nonces.token IS 'Value issued as random nonce';COMMENT ON COLUMN nonces.reference_data IS 'Value that is referenced by the nonce value';COMMENT ON COLUMN nonces.created IS 'Moment when nonce record is created, as measured in number of seconds since epoch';COMMENT ON COLUMN nonces.ttl IS 'Time To Live, period in seconds since created after which the nonce expires';COMMENT ON COLUMN nonces.consumed IS 'Moment when nonce was consumed, as measured in number of seconds since epoch';COMMENT ON COLUMN nonces.status IS 'Status of the nonce from {''issued'', ''revoked'', ''used''}';CREATETABLEaccounts ( account_id VARCHAR(64) PRIMARY KEYNOT NULLDEFAULT uuid_generate_v4(), tenant_id VARCHAR(64), username VARCHAR(64) NOT NULL,passwordVARCHAR(128), email VARCHAR(64), phone VARCHAR(32), attributes JSONB, active SMALLINTNOT NULLDEFAULT0, created BIGINTNOT NULL, updated BIGINTNOT NULL);CREATEUNIQUE INDEXIDX_ACCOUNTS_TENANT_USERNAMEON accounts (tenant_id, username);CREATEUNIQUE INDEXIDX_ACCOUNTS_TENANT_PHONEON accounts (tenant_id, phone);CREATEUNIQUE INDEXIDX_ACCOUNTS_TENANT_EMAILON accounts (tenant_id, email);-- Indexes enforcing uniqueness of username, phone, email for default tenant.CREATEUNIQUE INDEXIDX_ACCOUNTS_TENANT_USERNAME_DEFAULTON accounts(username) WHERE tenant_id ISNULL;CREATEUNIQUE INDEXIDX_ACCOUNTS_TENANT_PHONE_DEFAULTON accounts(phone) WHERE tenant_id ISNULL;CREATEUNIQUE INDEXIDX_ACCOUNTS_TENANT_EMAIL_DEFAULTON accounts(email) WHERE tenant_id ISNULL;CREATEINDEXIDX_ACCOUNTS_ATTRIBUTES_NAMEON accounts USING GIN ( (attributes->'name') );COMMENT ON COLUMN accounts.account_id IS 'Account id, or username, of this account. Unique.';COMMENT ON COLUMN accounts.tenant_id IS 'The tenant ID of this account. Unique in combination with username, phone, email.';COMMENT ON COLUMN accounts.username IS 'The username of this account. Unique in combination with tenant_id.';COMMENT ON COLUMN accounts.password IS 'The hashed password. Optional';COMMENT ON COLUMN accounts.email IS 'The associated email address. Unique in combination with tenant_id. Optional';COMMENT ON COLUMN accounts.phone IS 'The phone number of the account owner. Unique in combination with tenant_id. Optional';COMMENT ON COLUMN accounts.attributes IS 'Key/value map of additional attributes associated with the account.';COMMENT ON COLUMN accounts.active IS 'Indicates if this account has been activated or not. Activation is usually via email or sms.';COMMENT ON COLUMN accounts.created IS 'Time since epoch of account creation, in seconds';COMMENT ON COLUMN accounts.updated IS 'Time since epoch of latest account update, in seconds';CREATETABLElinked_accounts ( account_id VARCHAR(64) NOT NULL, tenant_id VARCHAR(64), linked_account_id VARCHAR(64) NOT NULL, linked_account_domain_name VARCHAR(64) NOT NULL, linking_account_manager VARCHAR(128), created TIMESTAMPNOT NULL,PRIMARY KEY (account_id, linked_account_id, linked_account_domain_name));CREATEUNIQUE INDEXIDX_LINKED_ACCOUNTS_TENANT_ACCOUNT_DOMAINON linked_accounts (tenant_id, linked_account_id, linked_account_domain_name);CREATEUNIQUE INDEXIDX_LINKED_ACCOUNTS_TENANT_ACCOUNT_DOMAIN_DEFAULTON linked_accounts (linked_account_id, linked_account_domain_name) WHERE tenant_id ISNULL;COMMENT ON COLUMN linked_accounts.account_id IS 'Account ID, typically a global one, of the account being linked from (the linker)';COMMENT ON COLUMN linked_accounts.tenant_id IS 'The tenant ID of this linked account';COMMENT ON COLUMN linked_accounts.linked_account_id IS 'Account ID, typically a local or legacy one, of the account being linked (the linkee)';COMMENT ON COLUMN linked_accounts.linked_account_domain_name IS 'The domain (i.e., organizational group or realm) of the account being linked';CREATETABLEcredentials ( id VARCHAR(36) PRIMARY KEYDEFAULT uuid_generate_v4(), tenant_id VARCHAR(64),subjectVARCHAR(64) NOT NULL,passwordVARCHAR(128) NOT NULL, attributes JSONB NOT NULL, created TIMESTAMPNOT NULL, updated TIMESTAMPNOT NULL);CREATEUNIQUE INDEXIDX_CREDENTIALS_TENANT_SUBJECTON credentials (tenant_id, subject);CREATEUNIQUE INDEXIDX_CREDENTIALS_TENANT_SUBJECT_DEFAULTON credentials (subject) WHERE tenant_id ISNULL;COMMENT ON COLUMN credentials.id IS 'ID of this credential (unique)';COMMENT ON COLUMN credentials.tenant_id IS 'The tenant ID of this credential';COMMENT ON COLUMN credentials.subject IS 'The subject of this credential (unique to a tenant)';COMMENT ON COLUMN credentials.password IS 'The hashed password';COMMENT ON COLUMN credentials.attributes IS 'Key/value map of additional attributes associated with the credential';COMMENT ON COLUMN credentials.created IS 'When this credential was created';COMMENT ON COLUMN credentials.updated IS 'When this credential was last updated';CREATETABLEsessions ( id VARCHAR(64) NOT NULLPRIMARY KEY, session_data TEXTNOT NULL, expires BIGINTNOT NULL);CREATEINDEXIDX_SESSIONS_IDONsessions (id ASC);CREATEINDEXIDX_SESSIONS_ID_EXPIRESONsessions (id, expires);COMMENT ON COLUMN sessions.id IS 'id given to the session';COMMENT ON COLUMN sessions.session_data IS 'Value that is referenced by the session id';COMMENT ON COLUMN sessions.expires IS 'Moment when session record expires, as measured in number of seconds since epoch';CREATETABLEdevices ( id VARCHAR(64) PRIMARY KEYNOT NULL, device_id VARCHAR(256), tenant_id VARCHAR(64), account_id VARCHAR(256), external_id VARCHAR(32), alias VARCHAR(30), form_factor VARCHAR(10), device_type VARCHAR(50),ownerVARCHAR(256), attributes JSONB, expires BIGINT, created BIGINTNOT NULL, updated BIGINTNOT NULL);CREATEUNIQUE INDEXIDX_DEVICES_TENANT_ACCOUNT_ID_DEVICE_IDON devices (tenant_id, account_id ASC, device_id ASC);CREATEUNIQUE INDEXIDX_DEVICES_TENANT_ACCOUNT_ID_DEVICE_ID_DEFAULTON devices (account_id ASC, device_id ASC) WHERE tenant_id ISNULL;CREATEINDEXIDX_DEVICE_IDON devices (device_id ASC);COMMENT ON COLUMN devices.id IS 'Unique ID of the device';COMMENT ON COLUMN devices.device_id IS 'The device ID that identifies the physical device';COMMENT ON COLUMN devices.tenant_id IS 'The tenant ID of this device';COMMENT ON COLUMN devices.account_id IS 'The user account ID that is associated with the device';COMMENT ON COLUMN devices.alias IS 'The user-recognizable name or mnemonic identifier of the device (e.g., my work iPhone)';COMMENT ON COLUMN devices.form_factor IS 'The type or form of device (e.g., laptop, phone, tablet, etc.)';COMMENT ON COLUMN devices.device_type IS 'The device type (i.e., make, manufacturer, provider, class)';COMMENT ON COLUMN devices.owner IS 'The owner of the device. This is the user who has administrative rights on the device';COMMENT ON COLUMN devices.attributes IS 'Key/value map of custom attributes associated with the device.';COMMENT ON COLUMN devices.expires IS 'Time since epoch of device expiration, in seconds';COMMENT ON COLUMN devices.created IS 'Time since epoch of device creation, in seconds';COMMENT ON COLUMN devices.updated IS 'Time since epoch of latest device update, in seconds';-- This number is user-supplied and may not be a phone number. It can be a part number or employer-provided unit number.-- It may also be blank. In any event, it is only used to provide the user with an extra queue as to which device this-- one refers. Unlike the phone number in the account table, this one is _not_ verified in any way.COMMENT ON COLUMN devices.external_id IS 'The phone or other identifying number of the device (if it has one)';CREATETABLEaudit ( id VARCHAR(64) PRIMARY KEY, instant TIMESTAMPNOT NULL, event_instant VARCHAR(64) NOT NULL,serverVARCHAR(255) NOT NULL,messageTEXTNOT NULL, event_type VARCHAR(48) NOT NULL,subjectVARCHAR(128), client VARCHAR(128),resourceVARCHAR(128), authenticated_subject VARCHAR(128), authenticated_client VARCHAR(128), acr VARCHAR(128),endpointVARCHAR(255),sessionVARCHAR(128));COMMENT ON COLUMN audit.id IS 'Unique ID of the log message';COMMENT ON COLUMN audit.instant IS 'Moment that the event was logged';COMMENT ON COLUMN audit.event_instant IS 'Moment that the event occurred';COMMENT ON COLUMN audit.server IS 'The server node where the event occurred';COMMENT ON COLUMN audit.message IS 'Message describing the event';COMMENT ON COLUMN audit.event_type IS 'Type of event that the message is about';COMMENT ON COLUMN audit.subject IS 'The subject (i.e., user) effected by the event';COMMENT ON COLUMN audit.client IS 'The client ID effected by the event';COMMENT ON COLUMN audit.resource IS 'The resource ID effected by the event';COMMENT ON COLUMN audit.authenticated_subject IS 'The authenticated subject (i.e., user) effected by the event';COMMENT ON COLUMN audit.authenticated_client IS 'The authenticated client effected by the event';COMMENT ON COLUMN audit.acr IS 'The ACR used to authenticate the subject (i.e., user)';COMMENT ON COLUMN audit.endpoint IS 'The endpoint where the event was triggered';COMMENT ON COLUMN audit.session IS 'The session ID in which the event was triggered';CREATETABLEdynamically_registered_clients ( client_id VARCHAR(64) NOT NULLPRIMARY KEY, client_secret VARCHAR(128), instance_of_client VARCHAR(64) NULL, created TIMESTAMPNOT NULL, updated TIMESTAMPNOT NULL, initial_client VARCHAR(64) NULL, authenticated_user VARCHAR(64) NULL, attributes JSONB NOT NULLDEFAULT'{}',statusVARCHAR(12) NOT NULLDEFAULT'active', scope TEXTNULL, redirect_uris TEXTNULL, grant_types VARCHAR(500) NULL);CREATEINDEXIDX_DRC_INSTANCE_OF_CLIENTON dynamically_registered_clients(instance_of_client);CREATEINDEXIDX_DRC_ATTRIBUTESON dynamically_registered_clients USING GIN (attributes);CREATEINDEXIDX_DRC_CREATEDON dynamically_registered_clients(created);CREATEINDEXIDX_DRC_STATUSON dynamically_registered_clients(status);CREATEINDEXIDX_DRC_AUTHENTICATED_USERON dynamically_registered_clients(authenticated_user);COMMENT ON COLUMN dynamically_registered_clients.client_id IS 'The client ID of this client instance';COMMENT ON COLUMN dynamically_registered_clients.created IS 'When this client was originally created (in UTC time)';COMMENT ON COLUMN dynamically_registered_clients.updated IS 'When this client was last updated (in UTC time)';COMMENT ON COLUMN dynamically_registered_clients.initial_client IS 'In case the user authenticated, this value contains a client_id value of the initial token. If the initial token was issued through a client credentials-flow, the initial_client value is set to the client that authenticated. Registration without initial token (i.e. with no authentication) will result in a null value for initial_client';
COMMENT ON COLUMN dynamically_registered_clients.authenticated_user IS 'In case a user authenticated (through a client), this value contains the sub value of the initial token';COMMENT ON COLUMN dynamically_registered_clients.attributes IS 'Arbitrary attributes tied to this client';COMMENT ON COLUMN dynamically_registered_clients.status IS 'The current status of the client, allowed values are "active", "inactive" and "revoked"';COMMENT ON COLUMN dynamically_registered_clients.scope IS 'Space separated list of scopes defined for this client (non-templatized clients only)';COMMENT ON COLUMN dynamically_registered_clients.redirect_uris IS 'Space separated list of redirect URI''s defined for this client (non-templatized clients only)';COMMENT ON COLUMN dynamically_registered_clients.grant_types IS 'Space separated list of grant types defined for this client (non-templatized clients only)';CREATETABLEdatabase_clients( client_id VARCHAR(64) NOT NULL, profile_id VARCHAR(64) NOT NULL, client_name VARCHAR(128) NULL, created TIMESTAMPNOT NULL, updated TIMESTAMPNOT NULL,ownerVARCHAR(128) NOT NULL,statusVARCHAR(16) NOT NULLDEFAULT'active', client_metadata JSONB NOT NULLDEFAULT'{}', configuration_references JSONB NOT NULLDEFAULT'{}', attributes JSONB NOT NULLDEFAULT'{}',PRIMARY KEY (client_id, profile_id));COMMENT ON COLUMN database_clients.client_id IS 'The client ID of this client instance';COMMENT ON COLUMN database_clients.profile_id IS 'The profile ID owning this client instance';COMMENT ON COLUMN database_clients.client_name IS 'The optional database client display name';COMMENT ON COLUMN database_clients.created IS 'When this client was originally created (in UTC time)';COMMENT ON COLUMN database_clients.updated IS 'When this client was last updated (in UTC time)';COMMENT ON COLUMN database_clients.owner IS 'The owner of the database client. This is the user or client who has administrative rights on the database client';COMMENT ON COLUMN database_clients.status IS 'The current status of the client, allowed values are "active", "inactive" and "revoked"';COMMENT ON COLUMN database_clients.client_metadata IS 'Metadata, as a JSON document, tied to this client, especially tags categorizing it';COMMENT ON COLUMN database_clients.configuration_references IS 'JSON document with all attributes referencing an item in the configuration';COMMENT ON COLUMN database_clients.attributes IS 'Canonical object representing this client';CREATEINDEXIDX_DATABASE_CLIENTS_PROFILE_IDON database_clients (profile_id ASC);CREATEINDEXIDX_DATABASE_CLIENTS_CLIENT_NAMEON database_clients (client_name ASC);CREATEINDEXIDX_DATABASE_CLIENTS_OWNERON database_clients (ownerASC);CREATEINDEXIDX_DATABASE_CLIENTS_METADATA_TAGSON database_clients USING GIN ((client_metadata ->'tags') jsonb_path_ops);CREATEINDEXIDX_DATABASE_CLIENTS_METADATA_TAGS_NULLON database_clients (client_metadata) WHERE client_metadata->'tags'ISNULL;CREATETABLEbuckets ( id VARCHAR(64) NOT NULLDEFAULT uuid_generate_v4(),subjectVARCHAR(128) NOT NULL, purpose VARCHAR(64) NOT NULL, tenant_id VARCHAR(64), attributes JSONB NOT NULL, created TIMESTAMPNOT NULL, updated TIMESTAMPNOT NULL,PRIMARY KEY (id));CREATEUNIQUE INDEXIDX_BUCKETS_TENANT_SUBJECT_PURPOSEon buckets (tenant_id, subject, purpose);CREATEUNIQUE INDEXIDX_BUCKETS_TENANT_SUBJECT_PURPOSE_DEFAULTon buckets (subject, purpose) WHERE tenant_id ISNULL;CREATEINDEXIDX_BUCKETS_ATTRIBUTESON buckets USING GIN (attributes);COMMENT ON COLUMN buckets.id IS 'Unique ID of the bucket';COMMENT ON COLUMN buckets.subject IS 'The subject that together with the purpose identify this bucket';COMMENT ON COLUMN buckets.purpose IS 'The purpose of this bucket, eg. "login_attempt_counter"';COMMENT ON COLUMN buckets.tenant_id IS 'The tenant ID of this bucket';COMMENT ON COLUMN buckets.attributes IS 'All attributes stored for this subject/purpose';COMMENT ON COLUMN buckets.created IS 'When this bucket was created';COMMENT ON COLUMN buckets.updated IS 'When this bucket was last updated';