Inkmi is Dream Jobs for CTOs and written as a decoupled monolith in Go, HTMX, Alpinejs, NATS.io and Postgres. I document my adventures and challenges in writing the application here on this blog, tune in again.
Do you use coupons? I mean, does your platform and code support coupons? It often does, because marketing wants Coupons! Coupons! Coupons! And even when you started with an external subscription provider, you might have coupons in-house, because marketing wants to be flexible.
If you do, read on. If not, read on too, because you might need it in the future.
How would we model coupons in SQL?
CREATE TABLE Coupons (
id uuid,
code text,
-- needs to be updated for every usage!
usages int,
maxusages int,
percentage int,
valut_untime timestamptz
)
There are already some things wrong with this coupon, it does not scale because usages
is a write operation, and when marketing puts out a great coupon, “50% off”, lots and lots of people will use the coupon at the same time and want to write to that database.
You might instead tie usages to users and have your own table:
CREATE TABLE Coupon (
id uuid,
code text,
maxusages int,
valut_untime timestamptz
)
CREATE TABLE CouponUsage (
coupon_id uuid,
user_id uuid
)
CREATE TABLE User (
id uuid
...
)
To check max usage here, you need to access the CouponUsage
table with a COUNT
each time, potentially a slow operation (you could use a probabilistic data structure, even in Postgres). And if you use an ORM framework, it’s easy to misuse and load all User
s and their Order
s and everything just to check maxusages
. And this brings your database connections down. Happened to a startup where I was CTO. And the ORM was Hibernate. And the developer, no blame, was inexperienced.
There is another potential problem here, percentage
Here it’s an int
, so 10 probably means “10%”. If it was a float, what does “0.2” mean? “20%”? I thought so, and created a bug on a large website, because the developer thought “0.2” means “0.2%” and “20.0” would mean - well “20.0%”. So we had “0.2%” coupons for a large marketing campaign, that obviously failed, when people expected 20%. Not a computer scientist, I guess.
But I digress. On to the biggest problem of that SQL table.
Fraud.
Nothing prevents someone with DB access to issue:
INSERT INTO Coupon VALUES ("MYSECRET", "1000", "90");
And then enjoy your companies’ products for 90% off. Yihaaa! And it might be admins, db-admins, devops people. Plus, everyone with a write-account and a PHP DB frontend—surprisingly many in early startups.
We can easily prevent this, we sign the coupon.
All code here is not production ready-you need to understand what you do, especially about the current in-use algorithms and parameters, don’t copy and paste security related things from the internet.
To prevent fraud, we add a signature to the coupon
ALTER TABLE Coupon ADD COLUMN signature BYTEA;
And then in the application on creation of the coupon we create the signature, on reading the coupon we validate the signature. You can also have all the data including the code in a JSONB field and encrypt it (additionally have the code
for queries as a SQL field in that case).
To create the signature we transform the coupon into a string or byte array, the easiest way often is to marshall
the coupon into JSON. Then we create a hash for the string, create a signature from the private key and the hash. We can validate the signature then with creating the hash again and checking with a public key.
With this in place, someone needs to know the secret signing key to create valid coupons. A simple INSERT
no longer works. Depending on where you get the secret key from (a file, ENV, Vault, AWS), the group of people who can forge a coupon gets smaller and smaller. But even with the key in an ENV environment variable, PHP DB users and DB admins no longer can forge coupons—with the key supplied by AWS, practically no one can if done correctly (implement key rotation).
On to some Go
code. One way to sign as mentioned is to marshall
the coupon into JSON, then create a signature for the JSON object, then store the signature. To validate, do the same thing again, but validate the signature with the stored signature.
package main
import (
"crypto"
"crypto/rand"
"crypto/rsa"
"crypto/sha256"
"encoding/json"
"fmt"
"github.com/gofrs/uuid"
)
// the struct we want to sign
type Coupon struct {
// be careful with JSON for validation,
// know what you're doing,
// private fields are not used,
// which means they can be forged!
// use Go json tags and
// verify with unit tests what
// you are doing
Code string
MaxUsages int
Usages int
Percentage int
}
type CouponDb struct {
id uuid.UUID
code string
signature []byte
coupon Coupon
}
func main() {
// Normally get a key from Systemd/Vault/AWS
// to make the code work and compile we create a key
privateKey, err := rsa.GenerateKey(rand.Reader, 2048)
if err != nil {
panic(err)
}
publicKey := privateKey.PublicKey
// this is the tamper-proof part of the coupon
c := Coupon{
Code: "SUPER20",
MaxUsages: 10,
Usages: 0,
Percentage: 20,
}
signature, err := generateSignature(c, privateKey)
if err != nil {
panic(err)
}
// this is the DB part for looking up a coupon
cdb := CouponDb{
code: c.Code,
coupon: c,
signature: signature,
}
// normally store coupon in DB here
// ...
// then read coupon from DB
// somewhere else and validate it
valid := validateCoupon(cdb, publicKey)
if valid {
fmt.Println("coupon valid")
} else {
fmt.Println("coupon invalid")
}
// let's forge a coupon and
// make it sweeter for us!
cdb.coupon = Coupon{
Code: "SUPER20",
MaxUsages: 10,
Usages: 0,
// with 90% off we get things very cheap!
Percentage: 90,
}
// should not validate now
valid = validateCoupon(cdb, publicKey)
if valid {
fmt.Println("tampered coupon valid")
} else {
fmt.Println("tampered coupon invalid")
}
}
For creating the signature we marshal the coupon to JSON, and build a signature for that:
func generateSignature(c Coupon, privateKey *rsa.PrivateKey) ([]byte, error) {
data, err := json.Marshal(c)
msgHash := sha256.New()
_, err = msgHash.Write(data)
if err != nil {
return nil, err
}
msgHashSum := msgHash.Sum(nil)
signature, err := rsa.SignPSS(
rand.Reader, privateKey, crypto.SHA256, msgHashSum, nil)
if err != nil {
return nil, err
}
return signature, nil
}
For validating the signature, we do the same but first check if the code
is correct, then
we marshall
to JSON and check the signature:
func validateCoupon(cdb CouponDb, publicKey rsa.PublicKey) bool {
if cdb.code != cdb.coupon.Code {
fmt.Println("Code not the same")
return false
}
validateData, err := json.Marshal(cdb.coupon)
validateHash := sha256.New()
_, err = validateHash.Write(validateData)
if err != nil {
return false
}
validateHashSum := validateHash.Sum(nil)
err = rsa.VerifyPSS(
&publicKey, crypto.SHA256, validateHashSum, cdb.signature, nil)
if err != nil {
fmt.Println("could not verify signature: ", err)
return false
}
return true
}
This way only the application or someone else with the private key can create coupons. People can’t tamper with the percentage, the usages or anything else without the application stopping accepting the coupon and probably sending an alarm when it gets a coupon from the database that has been tampered with.
Also don’t forget audit logs in the database for changes to sensitive tables like coupon from the very beginning, not when SOC2/ISO is coming.
About Inkmi
Inkmi is a website with Dream Jobs for CTOs. We're on a mission to transform the industry to create more dream jobs for CTOs. If you're a seasoned CTO looking for a new job, or a senior developer ready for your first CTO calling, head over to https://www.inkmi.com and tell us what your dream job looks like!