Api Key Exposure in Sails with Postgresql

When a Sails application stores or references API keys in a Postgresql database, several specific conditions can lead to unintentional exposure. Sails, an MVC framework for Node.js, often manages configuration and secrets through environment variables or models that map to database records. If API keys are stored as plaintext columns in Postgresql tables and the application logic inadvertently exposes those records over HTTP, the keys can be leaked to unauthorized clients.

Common exposure paths include insecure controller actions that return full model instances, missing row-level permissions, or overly permissive CORS settings that allow cross-origin requests to sensitive endpoints. Because Postgresql is often used as the primary data store for Sails applications via the waterline-postgresql adapter, developers may assume the ORM layer fully abstracts security. In practice, if query methods do not explicitly filter sensitive fields or enforce strict policies, serialized JSON responses can include key material.

Another vector specific to this combination is logging or debugging behavior. Sails apps with Postgresql integrations sometimes log query results or error details containing connection strings or credentials. If those logs are accessible to third parties or included in error messages returned to API consumers, keys can be extracted. Additionally, schema design issues—such as storing keys in columns without appropriate masking or encryption—amplify risk when backups or exported data are mishandled.

During a middleBrick scan, this vector is detected through unauthenticated endpoint probing and OpenAPI/Swagger analysis that reveals models or operations returning sensitive attributes. Cross-referencing the Postgresql schema definitions used by Sails with runtime responses helps identify mismatches where key-bearing fields should be omitted. The scan also flags missing authentication on administrative or debug endpoints that may expose raw database records, including API keys, to anyone able to reach the service.

To illustrate, consider a Sails model ApiKey mapped to a Postgresql table with columns id, service, and key. If a controller action returns this record without redaction, the key is exposed:

// api/controllers/ApiKeyController.js
find: async function (req, res) {
  try {
    const keys = await ApiKey.find();
    return res.ok(keys); // Exposes key column to client
  } catch (err) {
    return res.serverError(err);
  }
}

Without query filtering or output validation, the JSON response includes the key field. middleBrick flags such patterns by correlating model definitions, route configurations, and actual response payloads during its checks for Data Exposure and Unsafe Consumption.

Remediation focuses on ensuring that API keys stored in Postgresql are never returned directly by Sails controllers and that access to sensitive data is tightly controlled. The first step is to adjust the model and query behavior so that sensitive columns are excluded from automatic serialization and responses.

Use a custom toJSON method in your Sails model to omit the key column when instances are serialized. This prevents accidental exposure through Waterline methods used by controllers:

// api/models/ApiKey.js
module.exports = {
  tableName: 'api_keys',
  attributes: {
    service: { type: 'string', required: true },
    key: { type: 'string', required: true, columnName: 'api_key' },
    // Exclude key from JSON output
    toJSON: function () {
      const obj = this.toObject();
      delete obj.key;
      return obj;
    },
    toJSONForAdmin: function () {
      return this.toJSON();
    }
  }
};

Next, update controller logic to explicitly select only necessary fields and avoid returning sensitive data. Use Postgresql column selection to limit what is fetched and returned:

// api/controllers/ApiKeyController.js
find: async function (req, res) {
  try {
    const keys = await ApiKey.find()
      .select(['id', 'service']); // Exclude 'key' column
    return res.ok(keys);
  } catch (err) {
    return res.serverError(err);
  }
}

For more precise control, construct raw Postgresql queries that exclude sensitive columns and use parameterized statements to prevent injection:

// api/controllers/ApiKeyController.js
listServices: async function (req, res) {
  const client = await sails.getDatastore('default').manager;
  const result = await client.query(
    'SELECT id, service FROM api_keys WHERE owner_id = $1',
    [req.user.id]
  );
  return res.ok(result.rows);
}

Enforce row-level security in Postgresql by creating policies that restrict access to rows based on the requesting user. This ensures that even if a query is improperly constructed, the database will not return unauthorized rows:

-- Enable Row Level Security
ALTER TABLE api_keys ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own keys
CREATE POLICY api_key_user_policy ON api_keys
  FOR SELECT USING (owner_id = current_setting('app.current_user_id')::UUID);

-- Set user context for the session
SET app.current_user_id = 'user-uuid-here';

Finally, rotate any exposed keys immediately and audit your Sails routes and model definitions with middleBrick to confirm that no endpoints inadvertently return sensitive fields. The CLI can be run locally to validate remediation:

middlebrick scan https://api.example.com/openapi.json

Refer to the dashboard to track improvements over time and ensure that Postgresql-backed endpoints comply with data exposure best practices.