Policy per foreign data wrapper in Postgres


I’ve been reading up on Postgres database secrets in Vault.

One of the things that Postgres does very well is a data mesh setup of foreign data wrappers which enables direct SQL interface to a wide range of foreign data sources, including but not limited to Postgres instances themselves, OGR geospatial vector data, Steampipe based REST APIs, MySQL, Oracle, DB2, Clickhouse, Kafka, MongoDB, DynamoDB, BigQuery, Elasticsearch, Redis, Git, CUDA, Neo4j, Keycloak, or Telegram bots.

The foreign resources are set up using user mapping of roles.

This is a trivial setup for a pair of Postgres services but as your mesh grows, so does the number of mappings; assuming full connectivity of N instances, and as much as M arbitrary data sources, the total number of mappings reaches N*(N+M) that is for 3 instances and 5 data sources, we’re talking about 24 distinct user mappings which could be set up for any of the users with access to the database, and may as well differ from one another depending on the exact arrangement of permissions per user, per source. Therefore, each and every user mapping can be treated as a secret in its own right, whereas it’s best observed from a single source of truth, rotated, and updated accordingly by means of a policy.

Now, if I were to make a setup like this in Vault, how would I approach it?

A persona in Vault with sufficient permissions can read from the secret engine to assume their role, and in doing so would retrieve their credentials for the time being. This allows me to map Vault personas to Postgres roles one-to-one. However, it wouldn’t happen to know anything about the individual foreign data mappings as these are regarding Postgres users, not roles.

Moreover, they are subject to change, and so are the database connections for the existing mappings in Vault; at some point, the foreign user mappings have to enter the policy domain and I’m not sure if Vault is able to do that. If it could, the permissions could then be managed reliably from a single source of truth! If not, it seems a worthwhile case still to further enable Vault secret management for Postgres.

Best regards,