mirror of
https://github.com/netbirdio/dex.git
synced 2026-05-22 18:43:53 -07:00
jnfrati
485 lines
11 KiB
Go
485 lines
11 KiB
Go
package sql
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
)
|
|
|
|
func (c *conn) migrate() (int, error) {
|
|
_, err := c.Exec(`
|
|
create table if not exists migrations (
|
|
num integer not null,
|
|
at timestamptz not null
|
|
);
|
|
`)
|
|
if err != nil {
|
|
return 0, fmt.Errorf("creating migration table: %v", err)
|
|
}
|
|
|
|
i := 0
|
|
done := false
|
|
|
|
var flavorMigrations []migration
|
|
for _, m := range migrations {
|
|
if m.flavor == nil || m.flavor == c.flavor {
|
|
flavorMigrations = append(flavorMigrations, m)
|
|
}
|
|
}
|
|
|
|
for {
|
|
err := c.ExecTx(func(tx *trans) error {
|
|
// Within a transaction, perform a single migration.
|
|
var (
|
|
num sql.NullInt64
|
|
n int
|
|
)
|
|
if err := tx.QueryRow(`select max(num) from migrations;`).Scan(&num); err != nil {
|
|
return fmt.Errorf("select max migration: %v", err)
|
|
}
|
|
if num.Valid {
|
|
n = int(num.Int64)
|
|
}
|
|
if n >= len(flavorMigrations) {
|
|
done = true
|
|
return nil
|
|
}
|
|
|
|
migrationNum := n + 1
|
|
m := flavorMigrations[n]
|
|
for i := range m.stmts {
|
|
if _, err := tx.Exec(m.stmts[i]); err != nil {
|
|
return fmt.Errorf("migration %d statement %d failed: %v", migrationNum, i+1, err)
|
|
}
|
|
}
|
|
|
|
q := `insert into migrations (num, at) values ($1, now());`
|
|
if _, err := tx.Exec(q, migrationNum); err != nil {
|
|
return fmt.Errorf("update migration table: %v", err)
|
|
}
|
|
return nil
|
|
})
|
|
if err != nil {
|
|
return i, err
|
|
}
|
|
if done {
|
|
break
|
|
}
|
|
i++
|
|
}
|
|
|
|
return i, nil
|
|
}
|
|
|
|
type migration struct {
|
|
stmts []string
|
|
|
|
// If flavor is nil the migration will take place for all database backend flavors.
|
|
// If specified, only for that corresponding flavor, in that case stmts can be written
|
|
// in the specific SQL dialect.
|
|
flavor *flavor
|
|
}
|
|
|
|
// All SQL flavors share migration strategies.
|
|
var migrations = []migration{
|
|
{
|
|
stmts: []string{
|
|
`
|
|
create table client (
|
|
id text not null primary key,
|
|
secret text not null,
|
|
redirect_uris bytea not null, -- JSON array of strings
|
|
trusted_peers bytea not null, -- JSON array of strings
|
|
public boolean not null,
|
|
name text not null,
|
|
logo_url text not null
|
|
);`,
|
|
`
|
|
create table auth_request (
|
|
id text not null primary key,
|
|
client_id text not null,
|
|
response_types bytea not null, -- JSON array of strings
|
|
scopes bytea not null, -- JSON array of strings
|
|
redirect_uri text not null,
|
|
nonce text not null,
|
|
state text not null,
|
|
force_approval_prompt boolean not null,
|
|
|
|
logged_in boolean not null,
|
|
|
|
claims_user_id text not null,
|
|
claims_username text not null,
|
|
claims_email text not null,
|
|
claims_email_verified boolean not null,
|
|
claims_groups bytea not null, -- JSON array of strings
|
|
|
|
connector_id text not null,
|
|
connector_data bytea,
|
|
|
|
expiry timestamptz not null
|
|
);`,
|
|
`
|
|
create table auth_code (
|
|
id text not null primary key,
|
|
client_id text not null,
|
|
scopes bytea not null, -- JSON array of strings
|
|
nonce text not null,
|
|
redirect_uri text not null,
|
|
|
|
claims_user_id text not null,
|
|
claims_username text not null,
|
|
claims_email text not null,
|
|
claims_email_verified boolean not null,
|
|
claims_groups bytea not null, -- JSON array of strings
|
|
|
|
connector_id text not null,
|
|
connector_data bytea,
|
|
|
|
expiry timestamptz not null
|
|
);`,
|
|
`
|
|
create table refresh_token (
|
|
id text not null primary key,
|
|
client_id text not null,
|
|
scopes bytea not null, -- JSON array of strings
|
|
nonce text not null,
|
|
|
|
claims_user_id text not null,
|
|
claims_username text not null,
|
|
claims_email text not null,
|
|
claims_email_verified boolean not null,
|
|
claims_groups bytea not null, -- JSON array of strings
|
|
|
|
connector_id text not null,
|
|
connector_data bytea
|
|
);`,
|
|
`
|
|
create table password (
|
|
email text not null primary key,
|
|
hash bytea not null,
|
|
username text not null,
|
|
user_id text not null
|
|
);`,
|
|
`
|
|
-- keys is a weird table because we only ever expect there to be a single row
|
|
create table keys (
|
|
id text not null primary key,
|
|
verification_keys bytea not null, -- JSON array
|
|
signing_key bytea not null, -- JSON object
|
|
signing_key_pub bytea not null, -- JSON object
|
|
next_rotation timestamptz not null
|
|
);`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table refresh_token
|
|
add column token text not null default '';`,
|
|
`
|
|
alter table refresh_token
|
|
add column created_at timestamptz not null default '0001-01-01 00:00:00 UTC';`,
|
|
`
|
|
alter table refresh_token
|
|
add column last_used timestamptz not null default '0001-01-01 00:00:00 UTC';`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
create table offline_session (
|
|
user_id text not null,
|
|
conn_id text not null,
|
|
refresh bytea not null,
|
|
PRIMARY KEY (user_id, conn_id)
|
|
);`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
create table connector (
|
|
id text not null primary key,
|
|
type text not null,
|
|
name text not null,
|
|
resource_version text not null,
|
|
config bytea
|
|
);`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table auth_code
|
|
add column claims_preferred_username text not null default '';`,
|
|
`
|
|
alter table auth_request
|
|
add column claims_preferred_username text not null default '';`,
|
|
`
|
|
alter table refresh_token
|
|
add column claims_preferred_username text not null default '';`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table offline_session
|
|
add column connector_data bytea;
|
|
`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table auth_request
|
|
modify column state varchar(4096);
|
|
`,
|
|
},
|
|
flavor: &flavorMySQL,
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
create table device_request (
|
|
user_code text not null primary key,
|
|
device_code text not null,
|
|
client_id text not null,
|
|
client_secret text ,
|
|
scopes bytea not null, -- JSON array of strings
|
|
expiry timestamptz not null
|
|
);`,
|
|
`
|
|
create table device_token (
|
|
device_code text not null primary key,
|
|
status text not null,
|
|
token bytea,
|
|
expiry timestamptz not null,
|
|
last_request timestamptz not null,
|
|
poll_interval integer not null
|
|
);`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table auth_request
|
|
add column code_challenge text not null default '';`,
|
|
`
|
|
alter table auth_request
|
|
add column code_challenge_method text not null default '';`,
|
|
`
|
|
alter table auth_code
|
|
add column code_challenge text not null default '';`,
|
|
`
|
|
alter table auth_code
|
|
add column code_challenge_method text not null default '';`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table refresh_token
|
|
add column obsolete_token text default '';`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table device_token
|
|
add column code_challenge text not null default '';`,
|
|
`
|
|
alter table device_token
|
|
add column code_challenge_method text not null default '';`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table auth_request
|
|
add column hmac_key bytea;`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table password
|
|
add column preferred_username text not null default '';`,
|
|
`
|
|
alter table password
|
|
add column groups bytea not null default convert_to('[]', 'UTF8');`,
|
|
},
|
|
flavor: &flavorPostgres,
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table password
|
|
add column preferred_username text not null default '';`,
|
|
`
|
|
alter table password
|
|
add column groups bytea not null default '[]';`,
|
|
},
|
|
flavor: &flavorSQLite3,
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table password
|
|
add column preferred_username text not null default '';`,
|
|
`
|
|
alter table password
|
|
add column groups bytea;`,
|
|
`
|
|
update password
|
|
set groups = '[]'
|
|
where groups is null;`,
|
|
`
|
|
alter table password
|
|
modify column groups bytea not null;`,
|
|
},
|
|
flavor: &flavorMySQL,
|
|
},
|
|
// Migration for adding name and email_verified to password table (Postgres)
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table password
|
|
add column name text not null default '';`,
|
|
`
|
|
alter table password
|
|
add column email_verified boolean;`,
|
|
},
|
|
flavor: &flavorPostgres,
|
|
},
|
|
// Migration for adding name and email_verified to password table (SQLite3)
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table password
|
|
add column name text not null default '';`,
|
|
`
|
|
alter table password
|
|
add column email_verified boolean;`,
|
|
},
|
|
flavor: &flavorSQLite3,
|
|
},
|
|
// Migration for adding name and email_verified to password table (MySQL)
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table password
|
|
add column name text not null default '';`,
|
|
`
|
|
alter table password
|
|
add column email_verified boolean;`,
|
|
},
|
|
flavor: &flavorMySQL,
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table connector
|
|
add column grant_types bytea;`,
|
|
},
|
|
},
|
|
// Migration for adding allowed_connectors to client table
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table client
|
|
add column allowed_connectors bytea;`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
create table user_identity (
|
|
user_id text not null,
|
|
connector_id text not null,
|
|
claims_user_id text not null,
|
|
claims_username text not null,
|
|
claims_preferred_username text not null default '',
|
|
claims_email text not null,
|
|
claims_email_verified boolean not null,
|
|
claims_groups bytea not null,
|
|
consents bytea not null,
|
|
created_at timestamptz not null,
|
|
last_login timestamptz not null,
|
|
blocked_until timestamptz not null,
|
|
PRIMARY KEY (user_id, connector_id)
|
|
);`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
create table auth_session (
|
|
user_id text not null,
|
|
connector_id text not null,
|
|
nonce text not null default '',
|
|
client_states bytea not null,
|
|
created_at timestamptz not null,
|
|
last_activity timestamptz not null,
|
|
ip_address text not null default '',
|
|
user_agent text not null default '',
|
|
PRIMARY KEY (user_id, connector_id)
|
|
);`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table auth_request
|
|
add column mfa_validated boolean not null default false;`,
|
|
`
|
|
alter table user_identity
|
|
add column mfa_secrets bytea;`,
|
|
`
|
|
alter table client
|
|
add column mfa_chain bytea;`,
|
|
`alter table auth_request add column prompt text not null default '';`,
|
|
`alter table auth_request add column max_age integer not null default -1;`,
|
|
`alter table auth_request add column auth_time timestamptz not null default '1970-01-01 00:00:00';`,
|
|
`alter table auth_code add column auth_time timestamptz not null default '1970-01-01 00:00:00';`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table auth_session
|
|
add column absolute_expiry timestamptz not null default '1970-01-01 00:00:00';`,
|
|
`
|
|
alter table auth_session
|
|
add column idle_expiry timestamptz not null default '1970-01-01 00:00:00';`,
|
|
`alter table client add column post_logout_redirect_uris bytea;`,
|
|
`alter table auth_session add column logout_state bytea;`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`alter table auth_request add column webauthn_session_data bytea;`,
|
|
`alter table user_identity add column webauthn_credentials bytea;`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`
|
|
alter table client
|
|
add column sso_shared_with bytea;`,
|
|
},
|
|
},
|
|
{
|
|
stmts: []string{
|
|
`alter table auth_session
|
|
add column connector_data bytea;`,
|
|
},
|
|
},
|
|
// Convert password groups values backfilled from the original default '[]'
|
|
// from SQLite TEXT storage class to BLOB.
|
|
{
|
|
stmts: []string{
|
|
`update password set groups = CAST(groups as BLOB);`,
|
|
},
|
|
flavor: &flavorSQLite3,
|
|
},
|
|
}
|