Service Account RBAC Design

New script: ores.sql/create/iam/iam_service_db_users_create.sql

Creates one PostgreSQL role per service (ores_iam, ores_refdata, …). Idempotent: CREATE ROLE IF NOT EXISTS.

New script: ores.sql/create/iam/iam_service_db_grants_create.sql

Encodes the GRANT matrix above. Uses GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <schema> TO <role> for owned schemas, and explicit GRANT SELECT ON <schema>.<table> TO <role> for shared reads.

Each service's database config points database.user to its dedicated DB user. This is an env/config change, not a code change.

New populate scripts (replacing existing permission seed data):

iam_permissions_populate.sql

Drop all existing permission rows and re-seed using the full component:resource:action scheme above.

iam_roles_populate.sql

Drop all existing roles and re-seed one role per service (role_iam_service, role_refdata_service, …).

iam_role_permissions_populate.sql

Assign permissions to roles per the matrix above.

iam_service_account_roles_populate.sql

Assign each service account its role. This script runs after iam_service_accounts_populate.sql.

All scripts are idempotent (upsert / on conflict do nothing).

File: projects/ores.iam.core/include/ores.iam.core/messaging/auth_handler.hpp

After start_service_session() succeeds, fetch the account's roles:

auto roles = authz_svc.get_account_roles(sess->account_id);
std::vector<std::string> role_names;
for (const auto& r : roles)
    role_names.push_back(r.name);
claims.roles = std::move(role_names);

The interactive login handler already does this in the HTTP layer; mirror the pattern here. No schema change required — jwt_claims.roles already exists.

File: projects/ores.service/src/service/request_context.cpp

The context currently extracts tenant_id, username, party_id from the JWT. Extend it to also extract claims.roles and store them so handlers can read them without re-parsing the token.

File: projects/ores.service/include/ores.service/service/request_context.hpp

Add a roles field (std::vector<std::string>) to the context struct.

File: projects/ores.service/include/ores.service/messaging/handler_helpers.hpp

Add a helper that takes the request_context and a required permission code, calls authorization_service::check_permission(), and returns an error reply if the check fails. Signature sketch:

[[nodiscard]] bool require_permission(
    const request_context& ctx,
    std::string_view permission,
    ores::nats::service::client& nats,
    const ores::nats::message& msg);

Returns true if the caller has the permission; otherwise sends an error_reply with error_code::forbidden and returns false, so the handler can early-return cleanly.

For each handler that writes (save, delete, trigger, etc.), add a require_permission() call at the top of the handler body, before any business logic. Read handlers can be gated too but are lower priority.

Start with the handlers that service accounts actually call:

• scheduler write handlers (called by reporting_service)
• iam tenants.list (called by reporting_service)
• reporting handlers (called by the scheduler trigger subject)

Roll out to remaining handlers in a follow-up.

Service account tokens must use the same short TTL as user tokens (not a long-lived 13-hour token). The make_service_token_provider in ores.iam.client already supports proactive refresh via refresh_if_needed(); this step ensures:

1. IAM issues service tokens with the standard user token TTL (configurable, default ~15 minutes).
2. service_token_provider detects expiry (via X-Error: token_expired on the NATS response) and re-authenticates reactively, mirroring the interactive path in nats_client.
3. The proactive background refresh margin is tuned to refresh well before expiry (e.g. at 80% of TTL).

This ensures a compromised service token has the same limited validity window as a user token.

File: build/scripts/generate_nats_certs.sh

A script (following the same bash-wrapper-over-python pattern) that:

1. Creates build/keys/nats/ directory (already git-ignored via build/keys/*.pem / build/keys/*.key).
2. Generates the internal CA (ca.key, ca.crt) if not already present.
3. Generates a server keypair (nats-server.key, nats-server.crt) signed by the CA, with SAN localhost and the deployment hostname.
4. For each service name in a hardcoded list generates a client keypair (<service>.key, <service>.crt) signed by the CA, with CN set to the service name (e.g. ores.reporting.service).

The script is idempotent: existing files are not overwritten unless --force is passed. This allows certs to be regenerated on rotation without accidentally overwriting a key that is still in use.

Certificates and keys are written under build/keys/nats/ and are never committed (already covered by the existing build/keys/*.pem gitignore rule; extend it to cover build/keys/nats/).

File: build/config/nats.conf (new)

port: 4222

tls {
  cert_file:  "build/keys/nats/nats-server.crt"
  key_file:   "build/keys/nats/nats-server.key"
  ca_file:    "build/keys/nats/ca.crt"
  verify:     true          # require client certificates (mTLS)
  timeout:    5
}

The verify: true field enables mutual TLS: the broker rejects any connection that does not present a certificate signed by ca.crt.

Update build/scripts/start-services.sh to pass --config build/config/nats.conf when launching nats-server. The URL passed to services changes from nats://localhost:4222 to tls://localhost:4222.

File: projects/ores.nats/include/ores.nats/config/nats_options.hpp

struct nats_options final {
    std::string url = "nats://localhost:4222";
    std::string subject_prefix;

    // mTLS — all three must be set together or all left empty.
    std::string tls_ca_cert;      // path to CA certificate (ca.crt)
    std::string tls_client_cert;  // path to client certificate (<service>.crt)
    std::string tls_client_key;   // path to client private key (<service>.key)
};

File: projects/ores.nats/src/service/client.cpp

After natsOptions_SetURL, add:

if (!impl_->opts.tls_ca_cert.empty()) {
    natsOptions_SetSecure(opts, true);
    natsOptions_LoadCATrustedCertificates(opts,
        impl_->opts.tls_ca_cert.c_str());
    natsOptions_LoadCertificatesChain(opts,
        impl_->opts.tls_client_cert.c_str(),
        impl_->opts.tls_client_key.c_str());
}

The nats.c library's TLS API (natsOptions_SetSecure, natsOptions_LoadCATrustedCertificates, natsOptions_LoadCertificatesChain) maps directly to these fields. No new library dependency is required.

File: projects/ores.nats/src/config/nats_configuration.cpp

Add three new CLI options (--nats-tls-ca, --nats-tls-cert, --nats-tls-key) and corresponding environment variable fallbacks (ORES_NATS_TLS_CA, ORES_NATS_TLS_CERT, ORES_NATS_TLS_KEY) following the same pattern as --nats-url.

The init-environment.sh script populates these variables per-service, pointing each service at its own keypair under build/keys/nats/.

Add a step to the CI workflow (and init-environment.sh) that calls generate_nats_certs.sh before starting the NATS server. In CI, the --force flag regenerates keys on every run (ephemeral). In developer environments, keys are generated once and reused.

In every SQL and shell script that references comms_user / comms_password:

projects/ores.sql/setup_user.sql

rename variable and validation block

projects/ores.sql/recreate_database.sql

rename comms_user blocks

projects/ores.sql/recreate_database.sh

rename variable names and help text

projects/ores.sql/setup_database.sh

rename -v comms_user flag

projects/ores.sql/setup_schema.sql

rename if referenced (currently not)

projects/ores.sql/drop_roles.sql

rename comms_user entry in user array

projects/ores.sql/populate/iam/iam_service_accounts_populate.sql

rename the upsert call from :'comms_user' to :'shell_user'

In build/scripts/init-environment.sh:

• Rename ORES_DB_COMMS_USER → ORES_DB_SHELL_USER
• Rename ORES_DB_COMMS_PASSWORD → ORES_DB_SHELL_PASSWORD
• Rename the emitted section from ORES_COMMS_SHELL_DB_* → ORES_SHELL_DB_USER, ORES_SHELL_DB_PASSWORD, ORES_SHELL_DB_DATABASE

In projects/ores.shell/src/config/parser.cpp:

Change make_mapper("COMMS_SHELL") to make_mapper("SHELL") so the binary reads ORES_SHELL_DB_* from the environment.

Update all references in:

• projects/ores.sql/modeling/database_lifecycle.org
• doc/recipes/shell_recipes.org (ORES_COMMS_SHELL_LOGIN_PASSWORD → ORES_SHELL_LOGIN_PASSWORD; ores_comms_user → ores_shell_user)
• Any .claude/skills or doc/llm/skills files referencing COMMS_SHELL