Archetype: sql_service_accounts_populate.mustache

{{! GENERATED FILE — tangled from projects/ores.codegen/library/templates/sql_service.org. Edit the org source. }}
{{{sql_license}}}

/**
 * AUTO-GENERATED FILE - DO NOT EDIT MANUALLY
 * Template: sql_service_accounts_populate.mustache
 *
 * Service Accounts Population Script
 *
 * Creates system service accounts for non-human processes.
 * Service accounts belong to the system tenant and cannot login with passwords.
 * They authenticate by creating sessions directly at startup.
 *
 * Account names match the environment-scoped database user names
 * (e.g. ores_local2_iam_service).
 *
 * This script is idempotent.
 */

\echo '--- Service Accounts ---'

-- Capture service DB passwords from the environment so they are never stored
-- in plaintext in committed SQL. The backtick syntax runs a shell command at
-- psql execution time; the result is bound to the named variable.
\set wt_service_pw          `echo "$ORES_WT_DB_PASSWORD"`
\set http_service_pw        `echo "$ORES_HTTP_SERVER_DB_PASSWORD"`
{{#service_registry}}
{{#services}}
\set {{psql_var}}_pw    `echo "$ORES_{{env_key}}_DB_PASSWORD"`
{{/services}}
{{/service_registry}}

select ores_iam_service_accounts_upsert_fn(
    :'ddl_user',
    'ddl@system.ores',
    'System service account for DDL operations and schema migrations'
);

select ores_iam_service_accounts_upsert_fn(
    :'cli_user',
    'cli@system.ores',
    'System service account for CLI operations'
);

select ores_iam_service_accounts_upsert_fn(
    :'wt_user',
    'wt@system.ores',
    'System service account for Wt web application',
    :'wt_service_pw'
);

select ores_iam_service_accounts_upsert_fn(
    :'shell_user',
    'shell@system.ores',
    'System service account for interactive shell'
);

select ores_iam_service_accounts_upsert_fn(
    :'http_user',
    'http@system.ores',
    'System service account for HTTP REST API server',
    :'http_service_pw'
);

select ores_iam_service_accounts_upsert_fn(
    :'test_ddl_user',
    'test_ddl@system.ores',
    'System service account for test DDL operations'
);

select ores_iam_service_accounts_upsert_fn(
    :'test_dml_user',
    'test_dml@system.ores',
    'System service account for test DML operations'
);

{{#service_registry}}
{{#services}}
select ores_iam_service_accounts_upsert_fn(
    :'{{psql_var}}_user',
    '{{email}}',
    'System service account for {{description}} NATS domain service',
    :'{{psql_var}}_pw'
);

{{/services}}
{{/service_registry}}
-- Summary
select 'Service Accounts' as entity, count(*) as count
from ores_iam_accounts_tbl
where account_type != 'user'
  and valid_to = ores_utility_infinity_timestamp_fn();