0

I use Postgres database to store user information. Some of that information is sensitive so it was decided to store that information in Vault. The user table in Postgres has id's generated by a Postgres sequence and also it has unique identifiers for each user which come from an external system. My teammate wants to store user data in Vault by using Postgres user row id as the key in Vault while I think it's better to store by unique user identifier as the key.

The disadvantages of using Postgres id's is that:

  • Vault is tightly coupled with Postgres
  • When I use local DB for development the ids might be different so I will also have to use a local instance of Vault for development instead of using a staging Vault instance
  • I can't think of an exact example but I think using Postgres id's can somehow become inconsistent with Vault id's (maybe when a transaction fails but Vault is updated with a new user id which will become a "zombie" key).

The advantages of using Postgres id's is that:

  • Vault keys don't contain unique user identifier which makes them more secure

Which approach is better?

1
  • 4
    "When I use local DB for development the ids might be different so I will also have to use a local instance of Vault for development instead of using a staging Vault instance" - hence you want to able to mix up production or staging data in Vault with development data in Postgreql? Sure you really need this?
    – Doc Brown
    May 2 at 6:51

2 Answers 2

1

To start, some terminology helps, I think:

The user table in Postgres has id's generated by a Postgres sequence

This is called a surrogate key.

also it has unique identifiers for each user which come from an external system

I believe these would be considered the 'natural keys' or 'logical keys' (my preferred nomenclature.)

In my mind, the real value of a surrogate key is that it is not tied to anything external to the database. This matters because things that are used as identifiers externally are subject to changes that you may not always control. I learned this lesson from early in my career: most every table in a database I worked with was keyed logically to the same type of entity. The Logical key needed to change so we then spent months updating all the tables and code that accessed them. Then a few years later, the logical keys were reverted back and we did all that work again but in reverse. Hardly any of that waste would have been necessary if the database was built using surrogate keys. There would have been a single table that held the logical keys.

Based on that, my knee-jerk reaction is to agree with you and say to not expose the surrogate keys. However, I can see some value in the idea that the Vault is an extension of the postgres DB and that by not putting the real logical keys, it makes the secured data less useful if exposed without access to the mappings to logical keys.

4
  • Just to make sure I understood you correctly: even though you have negative experience with using logical keys you still recommend to use logical keys as vault keys right?
    – Yos
    May 2 at 19:27
  • 1
    I'm on the fence, I guess. It really comes down to whether the vault can ever be considered to stand alone or if that's desirable. If you want to be able to look up things directly from the vault without referencing the 'main' DB, you should use the logical keys. If you are considering the vault to be a logical extension of the core DB that is never accessed independently, you may want to use the surrogate keys, especially if tying the real world keys to the data there is considered a risk.
    – JimmyJames
    May 2 at 19:49
  • 2
    I hesitate to give a hard recommendation here. There are many factors here. For example, if you needed to rebuild your surrogate keys for some reason, you will need to synchronize that across the two databases. In other DB platforms, I've seen issues with how sequences are generated but I'm not sure that wasn't just a result of poor design and/or poor practices. Another option is to use UUIDs which Postgres seems to have good support for. 128-bit keys tend to constrain your indexing strategies, though.
    – JimmyJames
    May 2 at 19:56
  • 2
    One more thing: I may not have been clear: what I am strongly advising against is exposing your surrogate keys to external systems. There's a good chance you will lose control over them and defeat a lot of their purpose. That's why determining whether the vault is an independent system is crucial.
    – JimmyJames
    May 2 at 20:04
0

Row IDs are an internal structure which makes any solution depending on them highly non-portable. Future versions of PostgreSQL might change how row IDs work, importing your contents to another DB will typically mix up the row IDs, and changing to a different DB will almost definitely change the row IDs. Nothing at the application level should rely on something so low-level.

3
  • Maybe I am misunderstanding the question or you answer but you seem to be talking about the internal row ids and I think the OP is talking about a integer sequence.
    – JimmyJames
    May 2 at 17:47
  • 1
    They mention "row ID", which has a specific meaning in RDBMSes. But you're right, maybe they mean "the ID column of the row".
    – l0b0
    May 2 at 20:48
  • Yeah, I think the terminology in the question is a little confusing around this.
    – JimmyJames
    May 2 at 20:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.