Skip to main content
added 1058 characters in body
Source Link
J_H
  • 42.3k
  • 3
  • 38
  • 157

Often people will choose to store service credentials in a crypted vault and inject them via text file or env vars. But I will just assume that an RDBMS is perfect for your needs.

 

store service [credentials] in a column as JSON format

Yes.

One row per service. It's an all-or-nothing arrangement, since there's no use case for extracting just a subset of the credential headers.

The multiple table arrangement seems nightmarish. Suppose your InfoSec staff want to verify that 90-day password rotations have been regularly happening, or they need to deal right away with this morning's breach. Better to examine update timestamps across all services with a single-table query, than trying to track down all relevant tables and hoping the created_at column name is spelled identically in each.


With Multiple Table, I can enforce the columns to have value when saving into Db.

We mostly rely on referential integrity to preserve complex invariants on how entities relate to one another, in a way that will "keep the app honest" even if it has buggy code. Here, we don't really care about details of the one or three headers that a given service requires. All that matters is "granted permission!" versus "denied!"

So write some code which automates the permission checking and will INSERT only valid credentials into your table. Furthermore, it should be easy to run automated tests which validate that rows are still good, they still tell the truth. For example, if GitHub issues a PAT token that worked fine Monday but turns out to be expired come Tuesday, your test suite should be able to quickly bring that to your attention. You will also want to support "expiring in the next seven days" reports that let you avoid embarrassing lapses in coverage.

Expired / invalid credentials are caused by things outside the RDBMS's control, such as the passage of time or failure to pay a hosting bill. So referential integrity doesn't have much of a role to play here. Each automated check will need to communicate with the given service.

Often people will choose to store service credentials in a crypted vault and inject them via text file or env vars. But I will just assume that an RDBMS is perfect for your needs.

store service [credentials] in a column as JSON format

Yes.

One row per service. It's an all-or-nothing arrangement, since there's no use case for extracting just a subset of the credential headers.

The multiple table arrangement seems nightmarish. Suppose your InfoSec staff want to verify that 90-day password rotations have been regularly happening, or they need to deal right away with this morning's breach. Better to examine update timestamps across all services with a single-table query, than trying to track down all relevant tables and hoping the created_at column name is spelled identically in each.

Often people will choose to store service credentials in a crypted vault and inject them via text file or env vars. But I will just assume that an RDBMS is perfect for your needs.

 

store service [credentials] in a column as JSON format

Yes.

One row per service. It's an all-or-nothing arrangement, since there's no use case for extracting just a subset of the credential headers.

The multiple table arrangement seems nightmarish. Suppose your InfoSec staff want to verify that 90-day password rotations have been regularly happening, or they need to deal right away with this morning's breach. Better to examine update timestamps across all services with a single-table query, than trying to track down all relevant tables and hoping the created_at column name is spelled identically in each.


With Multiple Table, I can enforce the columns to have value when saving into Db.

We mostly rely on referential integrity to preserve complex invariants on how entities relate to one another, in a way that will "keep the app honest" even if it has buggy code. Here, we don't really care about details of the one or three headers that a given service requires. All that matters is "granted permission!" versus "denied!"

So write some code which automates the permission checking and will INSERT only valid credentials into your table. Furthermore, it should be easy to run automated tests which validate that rows are still good, they still tell the truth. For example, if GitHub issues a PAT token that worked fine Monday but turns out to be expired come Tuesday, your test suite should be able to quickly bring that to your attention. You will also want to support "expiring in the next seven days" reports that let you avoid embarrassing lapses in coverage.

Expired / invalid credentials are caused by things outside the RDBMS's control, such as the passage of time or failure to pay a hosting bill. So referential integrity doesn't have much of a role to play here. Each automated check will need to communicate with the given service.

Source Link
J_H
  • 42.3k
  • 3
  • 38
  • 157

Often people will choose to store service credentials in a crypted vault and inject them via text file or env vars. But I will just assume that an RDBMS is perfect for your needs.

store service [credentials] in a column as JSON format

Yes.

One row per service. It's an all-or-nothing arrangement, since there's no use case for extracting just a subset of the credential headers.

The multiple table arrangement seems nightmarish. Suppose your InfoSec staff want to verify that 90-day password rotations have been regularly happening, or they need to deal right away with this morning's breach. Better to examine update timestamps across all services with a single-table query, than trying to track down all relevant tables and hoping the created_at column name is spelled identically in each.