Jira progress: loading…

SCHEMA-RP

ZAYAZ Database Schema Roles & Permissions

Purpose

This document defines the recommended PostgreSQL role and permission model for the ZAYAZ Aurora PostgreSQL database.

The objective is to keep ZAYAZ database access:

auditable
least-privilege by default
compatible with future production scaling
safe for multi-module development
aligned with the platform principle: Precision before automation

The model separates AWS identities from PostgreSQL identities.

AWS IAM users/roles = control AWS infrastructure access
PostgreSQL roles    = control database/schema/table access

zayaz_admin should be treated as a PostgreSQL administrative role, not as an AWS IAM user.

1. Core Role Strategy

ZAYAZ should use a small number of functional PostgreSQL roles.

Role	Purpose
`zayaz_admin`	Database owner/admin role. Used only for controlled administration.
`zayaz_migration`	Used by migrations, schema changes, DDL, and deployment pipelines.
`zayaz_app`	Used by the application backend/API. Runtime read/write role.
`zayaz_readonly`	Read-only role for inspection, support, and controlled manual review.
`zayaz_analytics`	Read-focused role for BI, dashboards, data marts, and analytical queries.
`zayaz_verifier_api`	Restricted role for verifier-facing services, especially ZARA Verifier Mode.

This keeps operational access separate from application access, and verifier access separate from internal platform access.

2. Schema Groups

ZAYAZ currently uses the following schemas.

Schema	Description
`core_data`	Legacy/raw general data
`core_dim`	Dimension tables such as countries, units, sectors
`core_telemetry`	Logs, metrics, operational telemetry
`core_metadata`	Registries, configuration, descriptors
`core_staging`	Raw ingestion from Excel, APIs, imports
`core_intermediate`	Transformation outputs
`core_aggregates`	Aggregated KPI rollups
`core_marts`	Domain-specific data marts
`core_temp`	Temporary pipeline data
`core_relations`	Pure join tables / many-to-many relations
`core_engine`	Engine computation outputs
`core_module`	Module-owned business objects and module state
`core_signals`	Signal registry
`core_zara`	ZARA orchestration, verifier queries, prompt execution, response governance
`zar`	ZAYAZ Artifact Registry Framework
`prodreg`	Product Registry

Note: In addition to the ZAYAZ core schemas, ZAYAZ also have the two following schemas (in separate databases):

zar (ZAYAZ Artifact Registry Framework)
prodreg (The Product Registry Database for all of the Viroway Ltd products)

3. Permission Philosophy

3.1 Admin Role

zayaz_admin owns the database and can administer all schemas.

It should not be used by normal application code.

3.2 Migration Role

zayaz_migration may create and alter tables, indexes, functions, views, triggers, and migrations.

It is used by controlled deployment processes.

3.3 Application Role

zayaz_app is the main runtime role used by backend services.

It can read/write operational tables but should not own schemas.

3.4 Read-Only Role

zayaz_readonly is for support and controlled review access.

It can query data but cannot mutate it.

3.5 Analytics Role

zayaz_analytics is optimized for marts, aggregates, telemetry, and reporting use cases.

It should not write to core operational schemas.

3.6 Verifier API Role

zayaz_verifier_api is used by verifier-facing services such as ZARA Verifier Mode.

It should have tightly scoped access:

read from relevant signal/evidence/trust data
write verifier queries/responses/flags only in core_zara
never mutate client ESG source data
never mutate engine outputs
never write to ZAR evidence artifacts directly

4. Recommended PostgreSQL Bootstrap SQL

Run this as the current database administrator while connected to the zayaz database.

Replace placeholder passwords before execution.

Note: Do NOT change the zayaz_admin password. It will be provided by cto@viroway.com

schema-roles.sqlGitHub ↗
-- ============================================================
-- ZAYAZ PostgreSQL Role & Permission Bootstrap
-- Database: zayaz
-- Environment: dev
-- ============================================================

-- 1. Create functional login roles
CREATE ROLE zayaz_admin LOGIN PASSWORD 'CHANGE_ME_ADMIN_PASSWORD';
CREATE ROLE zayaz_migration LOGIN PASSWORD 'CHANGE_ME_MIGRATION_PASSWORD';
CREATE ROLE zayaz_app LOGIN PASSWORD 'CHANGE_ME_APP_PASSWORD';
CREATE ROLE zayaz_readonly LOGIN PASSWORD 'CHANGE_ME_READONLY_PASSWORD';
CREATE ROLE zayaz_analytics LOGIN PASSWORD 'CHANGE_ME_ANALYTICS_PASSWORD';
CREATE ROLE zayaz_verifier_api LOGIN PASSWORD 'CHANGE_ME_VERIFIER_API_PASSWORD';

-- 2. Lock down public schema
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 3. Grant database connection
GRANT CONNECT ON DATABASE zayaz TO
  zayaz_admin,
  zayaz_migration,
  zayaz_app,
  zayaz_readonly,
  zayaz_analytics,
  zayaz_verifier_api;

-- 4. Grant schema usage
GRANT USAGE ON SCHEMA
  core_data,
  core_dim,
  core_telemetry,
  core_metadata,
  core_staging,
  core_intermediate,
  core_aggregates,
  core_marts,
  core_temp,
  core_relations,
  core_engine,
  core_module,
  core_signals,
  core_zar,
  core_prodreg,
  core_zara
TO
  zayaz_admin,
  zayaz_migration,
  zayaz_app,
  zayaz_readonly,
  zayaz_analytics,
  zayaz_verifier_api;

-- 5. Admin full access
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA
  core_data,
  core_dim,
  core_telemetry,
  core_metadata,
  core_staging,
  core_intermediate,
  core_aggregates,
  core_marts,
  core_temp,
  core_relations,
  core_engine,
  core_module,
  core_signals,
  core_zar,
  core_prodreg,
  core_zara
TO zayaz_admin;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA
  core_data,
  core_dim,
  core_telemetry,
  core_metadata,
  core_staging,
  core_intermediate,
  core_aggregates,
  core_marts,
  core_temp,
  core_relations,
  core_engine,
  core_module,
  core_signals,
  core_zar,
  core_prodreg,
  core_zara
TO zayaz_admin;

-- 6. Migration role: full DDL/DML access
GRANT CREATE ON SCHEMA
  core_data,
  core_dim,
  core_telemetry,
  core_metadata,
  core_staging,
  core_intermediate,
  core_aggregates,
  core_marts,
  core_temp,
  core_relations,
  core_engine,
  core_module,
  core_signals,
  core_zar,
  core_prodreg,
  core_zara
TO zayaz_migration;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA
  core_data,
  core_dim,
  core_telemetry,
  core_metadata,
  core_staging,
  core_intermediate,
  core_aggregates,
  core_marts,
  core_temp,
  core_relations,
  core_engine,
  core_module,
  core_signals,
  core_zar,
  core_prodreg,
  core_zara
TO zayaz_migration;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA
  core_data,
  core_dim,
  core_telemetry,
  core_metadata,
  core_staging,
  core_intermediate,
  core_aggregates,
  core_marts,
  core_temp,
  core_relations,
  core_engine,
  core_module,
  core_signals,
  core_zar,
  core_prodreg,
  core_zara
TO zayaz_migration;

-- 7. Application runtime access
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA
  core_data,
  core_staging,
  core_intermediate,
  core_telemetry,
  core_engine,
  core_module,
  core_relations,
  core_zara
TO zayaz_app;

GRANT SELECT ON ALL TABLES IN SCHEMA
  core_dim,
  core_metadata,
  core_signals,
  core_zar,
  core_prodreg,
  core_aggregates,
  core_marts
TO zayaz_app;

GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA
  core_data,
  core_staging,
  core_intermediate,
  core_telemetry,
  core_engine,
  core_module,
  core_relations,
  core_zara
TO zayaz_app;

-- 8. Read-only access
GRANT SELECT ON ALL TABLES IN SCHEMA
  core_data,
  core_dim,
  core_telemetry,
  core_metadata,
  core_staging,
  core_intermediate,
  core_aggregates,
  core_marts,
  core_relations,
  core_engine,
  core_module,
  core_signals,
  core_zar,
  core_prodreg,
  core_zara
TO zayaz_readonly;

-- 9. Analytics access
GRANT SELECT ON ALL TABLES IN SCHEMA
  core_dim,
  core_telemetry,
  core_metadata,
  core_intermediate,
  core_aggregates,
  core_marts,
  core_engine,
  core_signals,
  core_zar,
  core_zara
TO zayaz_analytics;

-- Optional: allow analytics temp workspace writes only in core_temp
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA core_temp TO zayaz_analytics;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA core_temp TO zayaz_analytics;

-- 10. Verifier API access
-- Read-only into registries/evidence/trust-supporting schemas
GRANT SELECT ON ALL TABLES IN SCHEMA
  core_dim,
  core_metadata,
  core_signals,
  core_zar,
  core_engine,
  core_telemetry,
  core_module
TO zayaz_verifier_api;

-- Write only to ZARA verifier-facing tables once they exist
-- Run these after the relevant tables are created:
-- GRANT SELECT, INSERT, UPDATE ON core_zara.zara_verifier_queries TO zayaz_verifier_api;
-- GRANT SELECT, INSERT, UPDATE ON core_zara.zara_verifier_responses TO zayaz_verifier_api;
-- GRANT SELECT, INSERT, UPDATE ON core_zara.zara_verifier_flags TO zayaz_verifier_api;
-- GRANT SELECT, INSERT ON core_zara.zara_sandbox_simulations TO zayaz_verifier_api;

-- 11. Default privileges for future tables created by zayaz_migration
ALTER DEFAULT PRIVILEGES FOR ROLE zayaz_migration IN SCHEMA
  core_data,
  core_staging,
  core_intermediate,
  core_telemetry,
  core_engine,
  core_module,
  core_relations,
  core_zara
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO zayaz_app;

ALTER DEFAULT PRIVILEGES FOR ROLE zayaz_migration IN SCHEMA
  core_dim,
  core_metadata,
  core_signals,
  core_zar,
  core_prodreg,
  core_aggregates,
  core_marts
GRANT SELECT ON TABLES TO zayaz_app;

ALTER DEFAULT PRIVILEGES FOR ROLE zayaz_migration IN SCHEMA
  core_data,
  core_dim,
  core_telemetry,
  core_metadata,
  core_staging,
  core_intermediate,
  core_aggregates,
  core_marts,
  core_relations,
  core_engine,
  core_module,
  core_signals,
  core_zar,
  core_prodreg,
  core_zara
GRANT SELECT ON TABLES TO zayaz_readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE zayaz_migration IN SCHEMA
  core_dim,
  core_telemetry,
  core_metadata,
  core_intermediate,
  core_aggregates,
  core_marts,
  core_engine,
  core_signals,
  core_zar,
  core_zara
GRANT SELECT ON TABLES TO zayaz_analytics;

ALTER DEFAULT PRIVILEGES FOR ROLE zayaz_migration IN SCHEMA
  core_data,
  core_staging,
  core_intermediate,
  core_telemetry,
  core_engine,
  core_module,
  core_relations,
  core_zara
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO zayaz_app;

ALTER DEFAULT PRIVILEGES FOR ROLE zayaz_migration IN SCHEMA core_temp
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO zayaz_analytics;

5. Important Note on Existing Tables

PostgreSQL default privileges only affect future tables created by the role specified in ALTER DEFAULT PRIVILEGES.

Therefore:

Run GRANT ... ON ALL TABLES after creating existing tables.
Run ALTER DEFAULT PRIVILEGES before migrations start creating future tables.
Make sure migrations run under zayaz_migration, not zayaz_admin.

6. ZARA-Specific Role Logic

ZARA is an AI orchestration and reasoning layer, not a simple reporting module.

Therefore, core_zara should contain ZARA-specific operational tables such as:

zara_verifier_queries
zara_verifier_responses
zara_verifier_flags
zara_prompt_runs
zara_model_runs
zara_guardrail_decisions
zara_context_snapshots
zara_evidence_bindings
zara_sandbox_simulations

Verifier-facing services should use zayaz_verifier_api, not zayaz_app.

This prevents a verifier workflow from accidentally obtaining broad mutation rights across client data, engine outputs, or artifact registries.

7. Verifier Mode Permission Boundary

The zayaz_verifier_api role should be allowed to:

read relevant signal definitions
read relevant evidence/artifact metadata
read engine outputs
read trust and telemetry data required for assurance
create ZARA verifier queries
create ZARA verifier responses
create verifier flags
create non-persistent sandbox simulation records

The zayaz_verifier_api role should not be allowed to:

edit client source data
edit engine computation outputs
alter ZAR artifact records
generate missing ESG data directly
submit disclosures
approve data it generated itself
create or alter schemas/tables

This is the database-level reinforcement of ZARA Verifier Mode.

8. Operational Rules

8.1 Never Use `zayaz_admin` in Application Code

zayaz_admin exists for emergency administration and controlled database management.

Runtime services should use:

zayaz_app
zayaz_verifier_api
zayaz_analytics

depending on context.

8.2 Store Passwords in AWS Secrets Manager

Each login role should have its own secret.

Recommended naming:

/zayaz/dev/db/zayaz_admin
/zayaz/dev/db/zayaz_migration
/zayaz/dev/db/zayaz_app
/zayaz/dev/db/zayaz_readonly
/zayaz/dev/db/zayaz_analytics
/zayaz/dev/db/zayaz_verifier_api

8.3 Rotate Credentials

Recommended rotation cadence:

Role	Rotation
`zayaz_admin`	Manual, high-control
`zayaz_migration`	Every 90 days or CI/CD rotation
`zayaz_app`	Every 60–90 days
`zayaz_readonly`	Every 90 days
`zayaz_analytics`	Every 90 days
`zayaz_verifier_api`	Every 60–90 days

8.4 Production Hardening

For production:

disable direct public database access
use private subnets
route through backend/API only
use RDS Proxy for application pooling
use AWS IAM/RDS IAM authentication where appropriate
enable CloudWatch logs
enable Performance Insights
enable deletion protection
enforce backups and restore testing

9. Verification Commands

List roles:

\du

List schemas:

\dn

List tables in a schema:

\dt core_zara.*

Check table privileges:

SELECT
  grantee,
  table_schema,
  table_name,
  privilege_type
FROM information_schema.role_table_grants
WHERE table_schema LIKE 'core_%'
ORDER BY table_schema, table_name, grantee, privilege_type;

Check schema privileges:

SELECT
  nspname AS schema_name,
  rolname AS role_name,
  has_schema_privilege(rolname, nspname, 'USAGE') AS has_usage,
  has_schema_privilege(rolname, nspname, 'CREATE') AS has_create
FROM pg_namespace
CROSS JOIN pg_roles
WHERE nspname LIKE 'core_%'
  AND rolname LIKE 'zayaz_%'
ORDER BY nspname, rolname;

10. Final Architecture Decision

The ZAYAZ database should use:

One Aurora PostgreSQL database: zayaz
Multiple schemas: core_*
Functional roles: zayaz_*
Application access: least privilege
Verifier access: evidence-bound and non-mutating

This supports ZAYAZ as a scalable ESG trust infrastructure, where every signal, artifact, computation, ZARA response, verifier action, and audit event remains separated, governable, and explainable.

GitHub Repo Request for Change (RFC)

Purpose​

1. Core Role Strategy

2. Schema Groups

3. Permission Philosophy

3.1 Admin Role​

3.2 Migration Role​

3.3 Application Role​

3.4 Read-Only Role​

3.5 Analytics Role​

3.6 Verifier API Role​