> When writing an API, it’s easy to unknowingly introduce a dangerous, implicit assumption: that the database is the ultimate source of truth. If a record is in the database, an application often treats it as authoritative. I want to explain in this post why that might be a very bad idea.
This premise is weird at best.
"the database is the ultimate source of truth" is not a "dangerous, implicit assumption", is the best rule you can get (and the rest of the post say so: all useful data comes from it!)
Instead the rest of the article is interesting on how to choose what to use for the hash, but not follow with this first paragraph :shrug:
A pepper is just a salt that is "secret", i.e. not stored next to the hashes being salted. It indeed provides the desired defense-in-depth, in that a breach of the database does not allow spoofing requests to the application; a successful attack would require both an exploit giving database access as well as an exploit exposing the secret pepper. But this is just a result of splitting state across multiple data stores---you could theoretically split data across n databases which would require an attacker to find n exploits. As such I don't fully agree with their conclusion that "database state alone should not be able to grant authentication": just because you store application-wide secrets elsewhere doesn't mean it's not part of the application state, and isn't stored in a database.
For most monolithic applications I think the whole issue is be a bit moot; if the rest of the application state is in the primary database, then an attacker with database access could presumably accomplish anything without the need to spoof another user at the authentication layer.
Lastly, this scheme doesn't provide any mechanism for rotating the pepper.
As explained in the article this wouldn't work, because changing your org means your stored hash is no longer valid (as the org is incorporated in the hmac), and you can't generated your own hashes because you're missing the pepper. So after changing your org all subsequent requests would fail.
Yeah, if user -> org tenancy is stored in the same database without any similar defence in depth then a fresh API key after updating org would work around this.
Would be a interesting topic for them to cover.
I think the same HMAC(pepper, user, org) as a validation column would work.
Better yet, encryption with AAD on any tenancy data if you have a TPM available.
Building an application that is immune to attack defeats all possible attack scenarios, yes. But no application is perfect or immune to all possible attacks, the concept of defense-in-depth is an acknowledgement of this.
Right, but what is presented here is not defense in depth. It's just an additional layer that doesn't address the issue.
The problem at hand is that the PostgreSQL session user has too many access rights and thus the SQL injection is able to wreck havoc.
The presented solution doesn't help because the SQL injection could probably bypass it easily through some other SQL way (custom triggers or rules to intercept the backend pepper or final tokens for example).
The right way to go about this is to create a schema or table per entity combined with authenticating a custom SQL user per customer and setting the RBAC rights for that user correctly.
"But let’s look at what happens if an attacker discovers a blind SQL injection vulnerability anywhere else in the application.
The attacker doesn’t need to read the database or invert any hashes. An attacker can simply register a legitimate account and generate his own valid API key."
No they can't, The given scenario has not pushed the auth into the database at all. if your accounts are also database accounts why does the user have permission to create new accounts.
I have done this as an experiment, I was dreading building a comprehensive auth system and noticed that the barely used postgres internal auth system is very well fleshed out so just made every user be a database user. The application has little concept of auth at all, it just passes the user login into the database connection and all the auth (logins, what data the user can get, what data the user can write) is all done as database policy. It worked surprisingly well, I don't know if I would use it on a real system(whatever that means, pg logins probably do not scale) The idea still sort of makes my skin crawl, Nobody does it this way, I assume for a reason. But in theory it is fine.
I probably got the idea from the schemaverse game, where the whole game is internal to postgres, and the users are given direct select access.
Its sort of a CORE database feature, the "data control language" - access permissions, schema mapping and object mapping, views abstracting functionality, stored procedures granting RUN AS powers and proxying permissions - they are all very old and well worn tools.
How many applications actually do, though? At most places I've worked it's common to integrate a schema migration tool (flyway, liquibase) that requires DDL, and I've never seen anyone go to the effort of splitting out an ephemeral instance to run the migration with a separate database connection configuration.
Anecdotal but we certainly run flyway migrations with a high privelage user not the regular low privelage "app" user who can only SELECT UPDATE and sometimes DELETE. The fact that nobody does this doesn't mean it's a bad idea just a reflection on poor industry practice (add it to the list)
Oof, I never let the application alter the tables. Makes running some applications a giant pain, but DDL should be an administrative thing, not a runtime thing.
That’s what they do, but the TPM pepper is also needed for HMACing in their threat model.
Otherwise the attacker just adds the victim’s user id to their hashing process too.
TBC, the article isn't about "trust[ing] the database", but about the choice of TCB. There's nothing special about a "database" that makes it more vulnerable than any other API platform.
You're worried about SQL injection attacks? Why are you allowing untrusted clients to do anything other than call stored procedures? They can attack the DB? Why can't they attack the backend?
And sure: cryptographically binding a password to a username in storing a durable credential doesn't hurt, I guess, and it's cheap enough. (But why are you using bearer tokens in the first place?)
My objection is only the tacit assumption in the article that a "database" is some kind of distinct object that somehow accrues special superpowers and vulnerabilities relative to any other kind of service.
A database engine is just one choice of service implementation technology, like Go or Python. A database is just a service. Any service can be insecure.
tl;dr: The author advocates for signing authentication data in the database. Even if an attacker gains write access to the DB and manipulates the auth data, the app can recognize this and prevent access.
I'd rather invest time in writing a proper DB abstraction layer for new systems or audit all parts where the DB is accessed in an existing system than implement the suggested measure.
> When writing an API, it’s easy to unknowingly introduce a dangerous, implicit assumption: that the database is the ultimate source of truth. If a record is in the database, an application often treats it as authoritative. I want to explain in this post why that might be a very bad idea.
This premise is weird at best.
"the database is the ultimate source of truth" is not a "dangerous, implicit assumption", is the best rule you can get (and the rest of the post say so: all useful data comes from it!)
Instead the rest of the article is interesting on how to choose what to use for the hash, but not follow with this first paragraph :shrug:
A pepper is just a salt that is "secret", i.e. not stored next to the hashes being salted. It indeed provides the desired defense-in-depth, in that a breach of the database does not allow spoofing requests to the application; a successful attack would require both an exploit giving database access as well as an exploit exposing the secret pepper. But this is just a result of splitting state across multiple data stores---you could theoretically split data across n databases which would require an attacker to find n exploits. As such I don't fully agree with their conclusion that "database state alone should not be able to grant authentication": just because you store application-wide secrets elsewhere doesn't mean it's not part of the application state, and isn't stored in a database.
For most monolithic applications I think the whole issue is be a bit moot; if the rest of the application state is in the primary database, then an attacker with database access could presumably accomplish anything without the need to spoof another user at the authentication layer.
Lastly, this scheme doesn't provide any mechanism for rotating the pepper.
If I have full access to your database I would instead update my org-id and read data from other orgs.
As explained in the article this wouldn't work, because changing your org means your stored hash is no longer valid (as the org is incorporated in the hmac), and you can't generated your own hashes because you're missing the pepper. So after changing your org all subsequent requests would fail.
But logging in gets you a new hash with your new org id, right?
Yeah, if user -> org tenancy is stored in the same database without any similar defence in depth then a fresh API key after updating org would work around this. Would be a interesting topic for them to cover.
I think the same HMAC(pepper, user, org) as a validation column would work. Better yet, encryption with AAD on any tenancy data if you have a TPM available.
RBAC and proper prepared statements completely defeat all these scenarios.
Building an application that is immune to attack defeats all possible attack scenarios, yes. But no application is perfect or immune to all possible attacks, the concept of defense-in-depth is an acknowledgement of this.
Right, but what is presented here is not defense in depth. It's just an additional layer that doesn't address the issue.
The problem at hand is that the PostgreSQL session user has too many access rights and thus the SQL injection is able to wreck havoc.
The presented solution doesn't help because the SQL injection could probably bypass it easily through some other SQL way (custom triggers or rules to intercept the backend pepper or final tokens for example).
The right way to go about this is to create a schema or table per entity combined with authenticating a custom SQL user per customer and setting the RBAC rights for that user correctly.
"But let’s look at what happens if an attacker discovers a blind SQL injection vulnerability anywhere else in the application.
The attacker doesn’t need to read the database or invert any hashes. An attacker can simply register a legitimate account and generate his own valid API key."
No they can't, The given scenario has not pushed the auth into the database at all. if your accounts are also database accounts why does the user have permission to create new accounts.
I have done this as an experiment, I was dreading building a comprehensive auth system and noticed that the barely used postgres internal auth system is very well fleshed out so just made every user be a database user. The application has little concept of auth at all, it just passes the user login into the database connection and all the auth (logins, what data the user can get, what data the user can write) is all done as database policy. It worked surprisingly well, I don't know if I would use it on a real system(whatever that means, pg logins probably do not scale) The idea still sort of makes my skin crawl, Nobody does it this way, I assume for a reason. But in theory it is fine.
I probably got the idea from the schemaverse game, where the whole game is internal to postgres, and the users are given direct select access.
https://github.com/Abstrct/Schemaverse
Its sort of a CORE database feature, the "data control language" - access permissions, schema mapping and object mapping, views abstracting functionality, stored procedures granting RUN AS powers and proxying permissions - they are all very old and well worn tools.
I hear this before but im no expert.
I recall the moaning long ago that one should never construct sql queries client side.
But it seems you get a luxorious api for free?
I recall people wanting access to order history.
With a bit of code the user could even create their own order process?
It seems if a user has 12 company accounts they could also have an overview db account with read access.
Maybe you can even get rid of the front end entirely :)
Why can’t the attacker with db access drop the trigger?
You could (and should) run with minimal permissions which would exclude DDL.
How many applications actually do, though? At most places I've worked it's common to integrate a schema migration tool (flyway, liquibase) that requires DDL, and I've never seen anyone go to the effort of splitting out an ephemeral instance to run the migration with a separate database connection configuration.
Anecdotal but we certainly run flyway migrations with a high privelage user not the regular low privelage "app" user who can only SELECT UPDATE and sometimes DELETE. The fact that nobody does this doesn't mean it's a bad idea just a reflection on poor industry practice (add it to the list)
Oof, I never let the application alter the tables. Makes running some applications a giant pain, but DDL should be an administrative thing, not a runtime thing.
Sure, but if this person got access to the database, what are the odds they're going to be stuck in a limited role forever?
Or just salt the string with the username before hashing.
That’s what they do, but the TPM pepper is also needed for HMACing in their threat model. Otherwise the attacker just adds the victim’s user id to their hashing process too.
Great post. Anyone who talks, and actually implements, client key rotation gets a +1 from me.
Far too many systems don’t have a zero downtime key rotation.
TBC, the article isn't about "trust[ing] the database", but about the choice of TCB. There's nothing special about a "database" that makes it more vulnerable than any other API platform.
You're worried about SQL injection attacks? Why are you allowing untrusted clients to do anything other than call stored procedures? They can attack the DB? Why can't they attack the backend?
And sure: cryptographically binding a password to a username in storing a durable credential doesn't hurt, I guess, and it's cheap enough. (But why are you using bearer tokens in the first place?)
My objection is only the tacit assumption in the article that a "database" is some kind of distinct object that somehow accrues special superpowers and vulnerabilities relative to any other kind of service.
A database engine is just one choice of service implementation technology, like Go or Python. A database is just a service. Any service can be insecure.
tl;dr: The author advocates for signing authentication data in the database. Even if an attacker gains write access to the DB and manipulates the auth data, the app can recognize this and prevent access.
I'd rather invest time in writing a proper DB abstraction layer for new systems or audit all parts where the DB is accessed in an existing system than implement the suggested measure.