Index

public.oauth_clients

Description

Registered OAuth 2.x clients (public and confidential).

Columns

Name Type Default Nullable Children Parents Comment
allowed_grant_types grant_type[] ARRAY[‘authorization_code’::grant_type, ‘refresh_token’::grant_type] false Enabled grant types for this client.
application_type application_type ‘web’::application_type false Client application category; affects default policy and validations.
bearer_allowed boolean false false If TRUE, AS may issue Bearer (non-DPoP) tokens to this client.
client_id text false Public identifier for the OAuth client (unique among non-deleted rows).
client_name text false Human-readable display name for the client.
client_secret bytea true Hashed/HMACed secret for confidential clients; plaintext is never stored.
client_secret_expires_at timestamp with time zone true When the client secret expires (optional).
created_at timestamp with time zone now() false Creation timestamp.
deleted_at timestamp with time zone true Soft-delete timestamp; non-NULL means logically deleted.
id uuid gen_random_uuid() false public.oauth_access_tokens public.oauth_auth_codes public.oauth_refresh_tokens public.oauth_user_client_scopes Internal primary key (UUID).
origin text false Allowed origin (https, or loopback http) for browser/SPAs.
pkce_methods_allowed pkce_method[] ARRAY[‘S256’::pkce_method] false Allowed PKCE methods (usually only “S256”).
post_logout_redirect_uris text[] ‘{}’::text[] true Allowed post-logout redirect URIs for OIDC logout.
redirect_uris text[] false Allowed redirect URIs for the Authorization Code flow.
require_pkce boolean true false Whether PKCE is required for this client.
scopes text[] ‘{}’::text[] false Default/allowed scopes for this client.
token_endpoint_auth_method token_endpoint_auth_method ‘none’::token_endpoint_auth_method false Authentication method at token endpoint (“none” = public, “client_secret_post” = confidential).
updated_at timestamp with time zone now() false Last update timestamp (maintained by trigger).

Constraints

Name Type Definition
oauth_clients_allowed_grant_types_not_null n NOT NULL allowed_grant_types
oauth_clients_application_type_not_null n NOT NULL application_type
oauth_clients_bearer_allowed_not_null n NOT NULL bearer_allowed
oauth_clients_client_id_not_null n NOT NULL client_id
oauth_clients_client_name_not_null n NOT NULL client_name
oauth_clients_created_at_not_null n NOT NULL created_at
oauth_clients_id_not_null n NOT NULL id
oauth_clients_origin_not_null n NOT NULL origin
oauth_clients_origin_shape_chk CHECK CHECK ((origin ~* ’^(https://[^/\\s]+
oauth_clients_pkce_methods_allowed_not_null n NOT NULL pkce_methods_allowed
oauth_clients_pkey PRIMARY KEY PRIMARY KEY (id)
oauth_clients_public_grants_chk CHECK CHECK (((token_endpoint_auth_method <> ‘none’::token_endpoint_auth_method) OR (NOT (‘client_credentials’::grant_type = ANY (allowed_grant_types)))))
oauth_clients_public_pkce_chk CHECK CHECK (((token_endpoint_auth_method <> ‘none’::token_endpoint_auth_method) OR (require_pkce = true)))
oauth_clients_redirect_uris_not_null n NOT NULL redirect_uris
oauth_clients_redirects_nonempty_chk CHECK CHECK ((cardinality(redirect_uris) >= 1))
oauth_clients_require_pkce_not_null n NOT NULL require_pkce
oauth_clients_scopes_not_null n NOT NULL scopes
oauth_clients_secret_presence_chk CHECK CHECK ((((token_endpoint_auth_method = ‘client_secret_post’::token_endpoint_auth_method) AND (client_secret IS NOT NULL)) OR ((token_endpoint_auth_method = ‘none’::token_endpoint_auth_method) AND (client_secret IS NULL))))
oauth_clients_token_endpoint_auth_method_not_null n NOT NULL token_endpoint_auth_method
oauth_clients_updated_at_not_null n NOT NULL updated_at
post_logout_redirect_uris_valid CHECK CHECK (are_valid_oauth_uris_or_null(post_logout_redirect_uris))
redirect_uris_valid CHECK CHECK (are_valid_oauth_uris(redirect_uris))
scopes_token_chars CHECK CHECK (scopes_all_valid(scopes))

Indexes

Name Definition Comment
idx_oauth_clients_client_id CREATE INDEX idx_oauth_clients_client_id ON public.oauth_clients USING btree (client_id) Speeds lookups by client_id (active or deleted).
oauth_clients_pkey CREATE UNIQUE INDEX oauth_clients_pkey ON public.oauth_clients USING btree (id)
uq_oauth_clients_client_id_active CREATE UNIQUE INDEX uq_oauth_clients_client_id_active ON public.oauth_clients USING btree (client_id) WHERE (deleted_at IS NULL) Ensures client_id is unique among active (non-deleted) clients; allows reuse after soft delete.

Triggers

Name Definition
set_timestamp_oauth_clients CREATE TRIGGER set_timestamp_oauth_clients BEFORE UPDATE ON public.oauth_clients FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp()

Relations

er

Generated by tbls