OP-02: Get PostgreSQL connection string

This operation composes a PostgreSQL connection string for the requested instance and user and returns it. Unlike the Oracle and SQL Server operations it never probes the database, so the string is cached and returned without validation. The optional serverName query value fills the server part; when it is blank the literal placeholder {SERVER} is kept.

Field	Value
Operation ID	OP-02
Trigger	HTTP request from an authorized caller
Entry endpoint	GET /v1/Connections/PostgreSQL/{instance}/{username}
Owning component	ConnectionsController.GetPostgreSqlConnectionString, delegating to ConnectionManager.GetPostgreSqlConnectionString
Inputs	Route values instance and username; optional query value serverName ([FromQuery]); Environment API template postgresqlconnectiontemplate and a secure credential
Outputs	ActionResult<string> holding the credential-bearing connection string, possibly containing {SERVER}
Failure response	404 for a missing secure setting; unhandled failures (missing pipe, missing template) have a status that is not visible in source**

Steps

1. Authorize the caller against resource DBConnAPIRetrieveResource (BR-DBC-025)
2. Replace a null or whitespace serverName with the literal {SERVER} (BR-DBC-021)
3. Write the audit log entry (BR-DBC-022)
4. Build the cache key POSTGRES.<server>.<instance>.<user> (BR-DBC-002) and the lower-cased secure-setting key postgres_<instance>_<user>, which has no server component (BR-DBC-005, BR-DBC-006)
5. Read the template postgresqlconnectiontemplate on every request, before the cache check, and replace {SERVER} and {INSTANCE} (BR-DBC-007)
6. A missing template raises an unhandled null reference; the status is not visible in source**
7. Look the key up in the in-memory cache (BR-DBC-001)
8. On a hit: return the cached string with status 200 and stop. Only secure-credential retrieval, validation and cache insertion are skipped; the template lookup in step 5 already happened
9. On a miss, check that the secure setting exists (BR-DBC-009)
10. On failure: ConnectionManagerException with status 404, rendered by package middleware†
11. Decrypt the secure setting and split it at the first pipe (BR-DBC-010)
12. A missing pipe raises ArgumentOutOfRangeException outside the retry loop; the status is not visible in source**
13. Substitute {USER} and {PW} (BR-DBC-008)
14. Run the validation step: it always returns true and never reaches the database (BR-DBC-020); the retry loop therefore succeeds on the first attempt (BR-DBC-012)
15. Add the string to the cache and return it with status 200 (BR-DBC-003, BR-DBC-004)

Process flow

The following diagram visualizes this operation's control flow.

flowchart TD
    A["Authorize DBConnAPIRetrieveResource"] -->|"Denied"| Z["Authorization denial, status not visible in source"]
    A --> B{"serverName blank?"}
    B -->|"Yes"| C["Use placeholder SERVER"]
    B -->|"No"| D["Use supplied serverName"]
    C --> E["Audit log, build cache key and secure-setting key"]
    D --> E
    E --> T["Read Environment API template (every request)"]
    T -->|"Template missing"| X["Unhandled/package error, status not visible in source"]
    T --> F2{"Key in cache?"}
    F2 -->|"Hit"| R["Return cached string, 200"]
    F2 -->|"Miss"| F{"Secure setting exists?"}
    F -->|"No"| G["404 ConnectionManagerException"]
    F -->|"Yes"| H["Decrypt credential, split at first pipe"]
    H -->|"No pipe"| X2["Unhandled/package error, status not visible in source"]
    H --> H2["Compose string"]
    H2 --> I["Validation step always true, no probe"]
    I --> J["Cache and return 200"]

Transaction and side effects

Aspect	Finding
Unit of work	None; no database access
Commit point	None
Records written	None
Downstream calls	SAIF Environment API through IEnvironmentClient
Repeat-call behavior	A repeat request reads the template again and is then served from the cache, skipping the secure read; the cache is never invalidated (BR-DBC-004)

Logging

Surface	Written content
ISaifLogService (Info)	Audit entry per request (BR-DBC-022); a validation success entry on a cache miss

Error handling

• Secure setting missing: 404 from ConnectionManagerException through package middleware (DynamicErrorHandler†)
• Missing pipe in the credential or missing template: unhandled exceptions handled by package middleware; the status is not visible in source**
• A 400 from a connection test cannot occur because PostgreSQL is not tested
• Authorization failure: produced by the package authorization handler†; the status is not visible in source**

Related documentation

• API index
• Overview
• Architecture
• Hosting pipeline
• Endpoints
• Authentication and authorization
• Dependencies
• Configuration
• Operations and processes
• Business rules
• Data model
• Operational runbook
• Tech debt

Note: Statements marked with † are derived from dependency library documentation via the Legends CLI. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

• DBConnectionAPI/DBConnectionAPI/Controllers/ConnectionsController.cs
• DBConnectionAPI/DBConnectionAPI/Services/ConnectionManager.cs
• DBConnectionAPI/DBConnectionAPI/Exceptions/ConnectionManagerException.cs
• DBConnectionAPI/DBConnectionAPI/EnvironmentKeys.cs