1

People say "don't expose primary keys from the database in your API" because its a major security leak, so I'm trying to come up with a way for:

  • RESTful HTTP requests to reference server-side resources without requiring their DB PK
    • example: I want to update a Widget resource with a PK ID of 293, so I want the equivalent of a POST /myapi/widgets/293 but without having to mention 293
  • RESTful HTTP responses should be able to reference server-side resources without sending back their DB PKs
    • example: I want to fetch all blue Widgets, so I want to request GET /myapi/widgets?color=blue and any Widgets that come back in the JSON array should not have their DB PKs

I can solve the 2nd issue (the read) via DTOs, and just make sure that I have a WidgetDto that doesn't include the Widget#id field at all.

But as far as referencing resources without their database keys, what are typical solutions here?

The only thing I can think of is to generate UUIDs for each resource (each Widget) and store it in the DB alongside its primary key, so:

[widgets] table
===
[id] --> bigint primary key auto increment not null
[ref_id] --> varchar(36) not null (UUID)

...and then rotate UUIDs every so often. But that creates its own headaches if something client-side is caching or currently using a resource reference and we change its UUID on the server-side. Any ideas? Thanks!

9
  • 6
    'People say "don't expose primary keys from the database in your API" because its a major security leak' - people say all kinds of things without providing additional context. It's not a major thing in an of itself. It's a problem, for example, if your keys are sequential (and thus predictable), and you're concerned that an attacker might somehow exploit that predictability, or just spam your API with requests, or that your competition might use that to learn something about your business (like how many sales you make per hour, and things like that). 1/2 Jun 29, 2022 at 14:59
  • 1
    If your key is an UUID, you don't have that problem, so you don't have to rotate the UUIDs. Also, you may have different concerns/needs for internal company APIs vs outward (public) facing APIs. See this answer as well. 2/2 Jun 29, 2022 at 14:59
  • Why the DV? Question is on topic, shows research/effort, is not a duplicate. Jun 29, 2022 at 15:29
  • 1
    I myself didn't downvote. Sometimes people here downvote and leave no comments - so ignore the few initial downvotes to give your question some time, then see how it's faring and decide what to do from there. Jun 29, 2022 at 15:37
  • @hotmeatballsoup Starting your question with a bold assertion which is at best dubious is generally a good way to attract downvotes. Jun 29, 2022 at 16:46

3 Answers 3

5

The fundamental problem with exposing the simple, surrogate keys which tend to be the database's Primary Keys is that it leaks your implementation outside of your database.

If you have an API that [someone finds out] supports ...

GET /myapi/widgets/293

... then there's nothing whatever stopping them from trying ...

GET /myapi/widgets/294
GET /myapi/widgets/295
GET /myapi/widgets/296
GET /myapi/widgets/297
. . . 

... until they get something back.

The other issue with this approach is that these [supposedly database-only] values can be persisted outside of your database. Say your Application knows it's working with widget 21153, so it stores that value away for its own use (doesn't matter where). Then, your database undergoes a reorganisation (for whatever reason) and all those supposedly database-only, internal values get changed. (Yes, yes, I know; Primary Keys should never, ever change, but ...)

The next time your Application fires up, it either fails to find that widget, because it now has a different number or, worse still, it returns a totally different widget (which, these days, could well constitute a reportable Data Breach).

If, instead, the communication is based on the Natural Key that identifies the same record, in this case "Ladiesman217's glasses", then this problem is completely avoided.
Of course you need to "unformat" these Natural Keys (into matching Primary Key values) on the way "in", which means more indexes and such like to support this, and you need to "format" into natural Keys on the way back "out". (Same could be said of Dates and Numbers, but that's another story).
Nothing comes for free.

Basically, database communication with the Outside World should be done in terms meaningful to the Outside World. Leaking database-specifics only makes things more fragile in the long term.

2
  • 3
    I agree that all of the listed things are issues, but a significant portion of them seem to stem from already having zero authorization on your resources; which is a significant problem in and of itself since your answer is working off of the assumption that it is at the very least problematic and at worst a data breach when someone manages to access a resource that they shouldn't. The solution here isn't to obfuscate the identifier, it's to implement actual authorization in the on the resources in question.
    – Flater
    Jul 13, 2022 at 12:47
  • 1
    I agree on the enforcement of authorization as being the ultimate solution here. Also "Ladiesman217's glasses" as a Natural Key also exposes a user named "Ladiesman217", and an attacker might be able to launch an indirect object reference attack based if they have a dictionary of valid usernames. So it its slightly less predictable than exposing a PK, but not by much. Jul 28, 2022 at 14:15
2

People say "don't expose primary keys from the database in your API" because its a major security leak

I think you have misunderstood what is being said here. It is only a 'major security leak' to expose primary keys from you database if those primary keys are sensitive information.

So you can think of this in the more general sense of don't expose sensitive information via your public api

For example, if you have a Customer table in your DB that uses an auto-incrementing PK you might not want your competitors knowing that you only have 100 customers. Or you might want a particular customer know that they were only your second customer.

But sometimes widget 35 is just widget 35. Sometimes (most of the time) you don't care if someone knows there is probably also a widget 36.

You do not need to develop a convoluted and costly solution to replace widget 35 with widget a0cd09e3-0aa3-41a8-a5e6-6346b29f53e6 just to hide that it is the 35th widget, unless that is sensitive information which in most cases it won't be.

If you do need to implement this then yes your idea of generating some unique id for each record and exposing that instead works.

I wouldn't use a UUID though as those are designed to be unique against all other UUIDs in the world, and you only need it to be unique in your table.

So just hash your auto-incrementing PK with some other columns as use that (if you just has the primary key it might be obvious that it is just a hash of small numbers). If the hash is too long just truncate it, you will probably be safe enough since again it only has to be unique in your table.

You can then expose this to your customers, and SELECT from the DB using this hash when the API has to find the record

GET /myapi/customers/a2cf08

and

SELECT id, name, address FROM customers WHERE hash = a2cf08

that kinda thing. But again really wouldn't bother for widget 35

then rotate UUIDs every so often

Why would you need to rotate them?

Customer 4ab42f is always going to be customer 4ab42f.

2

People say "don't expose primary keys from the database in your API" because its a major security leak

This is an oversimplification.

It is not inherently a security leak. That being said, arguments are made for sequential identifier types making it very simple for an attacker to simply increment/decrement the value being used in order to access other resources.
However, if this is an issue in your case, this should not be solved using obfuscation (= using a non-sequential identifier), but rather through proper authentication and authorization. That is a very big topic that I can't address in this answer.

What is correct, however, is that it is an implementation leak, i.e. your database implementation is part of your API interface. The rest of the answer addresses this point.

But as far as referencing resources without their database keys, what are typical solutions here?

Nothing is stopping you from adding a column to your database table, lets call it Code, which contains a value that uniquely identifies a row. It's effectively doing the same job as your PK column is, but this column is slightly different in that:

  • You (= your codebase) arbitrarily decided the value
  • The database does not impose any key constraints on this column

Essentially, you have two unique identifiers: the PK, which is used by the database; and Code, which is used by your codebase (and API).

At this stage you are able to rewrite your codebase to only refer to that Code field when referring to a specific resource, and no code (except your persistence logic) ever has to touch the PK field itself.


Some tangential mentions:

  • The specific data type of Code is not really relevant, you can pick whatever you like. That being said, I often prefer to use UUID/GUID values since I can reliably generate unique values without needing to validate whether this value is already in use.
  • For the sake of performance, you should index the Code column, since it will obviously be frequently used for lookups.
  • Similarly, you can still add a uniqueness constraint on the database column as a last line of defense in case your code fails and ends up using a duplicate code.
  • Having a secondary identifier makes your data store resilient against data restores. Suppose your database contains 3 people with IDs 1, 3 and 4 (because 2 used to exist but has since been deleted). If you recreate your data store, the automatically generated IDs would be 1, 2, 3, meaning that person/3 now refers to a completely different person than it did before!
    • It is possible to alternatively solve this by allowing identity insertion when restoring data; but it's IMHO significantly easier to simply prevent this from being an issue by not having anyone rely on the specific PK value to begin with.

But that creates its own headaches if something client-side is caching or currently using a resource reference and we change its UUID on the server-side. Any ideas?

You should not be changing the UUIDs once created. You're creating your own problem. Essentially, imagine if your town decided to rename all streets every year. Imagine how many issues this will cause for everyone's recordkeeping.

What about mail in transit, did the sender account for the new address already or were they still using the old one? What about visiting a friend who gave you their address last year? Should everyone in this town now send out a message to all their friends with their updated address? How is a third party going to distinguish between a street name change and the person actually moving house, since both entail changing the person's address? ...

There's just no point to doing this. An identifier should stay the same, so that you can actually use it without silly hassles.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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