What's a good way to handle encrypted columns?

[r0manarmy]

Let's say I have a table with a column that holds data encrypted by an application. Ideally I'd want both encryption and decryption to be handled in a middleware ish way so I can't accidentally forget to call the encryption methods and write clear text into that column. I'm also using sqlc in my project and most of the pgx code is generated. I haven't found a way to get sqlc to do something like this earlier question #2121

I found 3 approaches to get there, but not sure which one is lines up best with the way pgx is meant to be extended.

Option 1: Create a new postgres type, with a new OID, then register the type and a codec using conn.TypeMap().RegisterType. It works, but requires creating a new type.

Option 2: Register my encoder/decoder with TryWrapEncodePlanFuncs and TryWrapScanPlanFuncs. This seems like the most likely approach, but would be good to confirm.

Option 3: Wrap the existing ByteaCodec with my own implementation, handle the specific struct representing my encrypted column and pass everything else through to the default implementation. This one feels the sketchiest of the options, but would error quickly if I make a mistake and try to map a non bytea column to my struct.

[yudin-s]

For a normal PostgreSQL bytea column, I would not create a new PostgreSQL type just to trigger encryption.

The most pgx-shaped path is to make the Go type for that column implement the interfaces that ByteaCodec already understands:

• pgtype.BytesValuer for encoding, where BytesValue() returns ciphertext bytes.
• pgtype.BytesScanner for scanning, where ScanBytes(...) decrypts into your application type.

Then configure sqlc to map that specific column to your custom Go type. That is the part that prevents accidental plaintext writes: generated code accepts EncryptedFoo, not plain []byte or string, for that column.

TryWrapEncodePlanFuncs / TryWrapScanPlanFuncs are useful when a codec does not already know how to bridge your Go type into a supported interface. For bytea, the built-in codec already has the bridge points, so I would start with BytesValuer / BytesScanner before adding global plan wrappers. If you use simple protocol or another path where pgx does not know the OID from the query, also register the default mapping for your Go type to bytea.

I would avoid wrapping/replacing ByteaCodec unless you really need to change bytea behavior globally. It is easier to create surprising behavior for unrelated bytea values that way.

Security caveat: keep the encryption envelope explicit in your type: version, nonce, ciphertext, authentication tag, and key ID if you rotate keys. Use randomized authenticated encryption unless you specifically need deterministic values for equality lookup, and treat deterministic encryption as a separate design choice with leakage tradeoffs.

So my ranking would be: custom Go type plus sqlc column override plus BytesValuer/BytesScanner first; plan wrappers only if you need an adapter layer; new PostgreSQL type only if the database type itself should be distinct. If that matches the extension point you were looking for, please mark this as answered.

[r0manarmy]

pgtype.BytesValuer for encoding, where BytesValue() returns ciphertext bytes.
pgtype.BytesScanner for scanning, where ScanBytes(...) decrypts into your application type.

Unfortunately this approach would run into the same issue as the other question (and the thing I want to avoid). I don't want each instance of of my custom type needs access to the encryption keys. I'm looking for a way to keep them as plain data holders and give pgx an a service that it'll use to encrypt/decrypt data in the process of reading and writing the data to postgres.

[yudin-s]

That constraint changes the shape. If the values must stay plain data holders, then I would not use BytesValuer / BytesScanner on the value type. I would put the encryption service in a pgx codec or scan/encode plan that is registered on the connection type map.

The important catch is dispatch scope. pgx chooses codecs from the type map by PostgreSQL type OID/name plus the Go value/target type. A plain bytea column does not tell the codec which application column it came from, so a service-backed codec for raw bytea tends to become global bytea behavior. That is risky unless every bytea in that connection should be encrypted.

For column-specific behavior, I would make the database type distinct, for example a domain over bytea:

CREATE DOMAIN encrypted_bytea AS bytea;

Then use that type only for encrypted columns, map those columns in sqlc to your plain Go holder type, and in pgx register a codec for the encrypted_bytea OID in AfterConnect / connection setup. That codec can close over your encryption service:

conn.TypeMap().RegisterType(&pgtype.Type{
    Name:  "encrypted_bytea",
    OID:   encryptedByteaOID,
    Codec: EncryptedByteaCodec{keys: keyService},
})

conn.TypeMap().RegisterDefaultPgType(PlainSecret{}, "encrypted_bytea")

Conceptually:

• PlanEncode accepts PlainSecret, asks the service to encrypt it, then delegates the resulting ciphertext bytes to the normal bytea encoding path.
• PlanScan accepts *PlainSecret, first scans/copies the database bytes, then asks the service to decrypt into the target.
• The value type only stores plaintext fields. It does not know keys or implement encryption methods.

If you keep the column as ordinary bytea, the closest equivalent is overriding/wrapping ByteaCodec for bytea, but then you need a very strong guard so unrelated bytea values are not accidentally encrypted/decrypted. A distinct domain/type is the cleaner boundary because it gives pgx an OID that means "this bytea is encrypted application data".

So my updated ranking for your requirement would be: custom PostgreSQL domain/type plus pgx codec that owns the service first; global bytea codec only if all bytea traffic on that connection is in scope; value-level BytesValuer/BytesScanner only if you are willing to let the value carry or reach the key service.

[r0manarmy]

@yudin-s thanks, but if I wanted to know what Claude thinks, I would have just prompted Claude directly.

[jackc]

My initial thought is this would be best handled with a layer above pgx than directly in pgx. But if you really need to do it this way, then the BytesValuer / BytesScanner approach is what I would try first. To ensure that no plaintext is ever written to the database you could have a check constraint that requires a particular prefix. Your encryption scheme may already have some sort of prefix, and if not it's trivial to add to your BytesValuer / BytesScanner. You can either apply the check constraint directly or make a DOMAIN like suggested above (in which case you have the belt and suspenders of also registering the new type / OID in pgx).

[r0manarmy]

@jackc it's a personal project, so there isn't a specific way I need to do it. Admittedly it's self inflicted, but a level just above pgx is sqlc generated code and I didn't find any ways to modify what it generates. So my thoughts were either go one level up from that and make sure I call encrypt/decrypt on sqlc generated queries, or try to jump in the middle of encode/decode step and layer it in "transparently" there.

I didn't know if new type/OID was the way that functionality was meant to be used or if I was slightly abusing it.

For my own learning, how come you didn't recommend TryWrap approach?

[jackc]

For my own learning, how come you didn't recommend TryWrap approach?

Implementing the built-in interfaces like BytesValuer and BytesScanner is simpler if it is possible. The TryWrap approach is for things where you can't implement it that way (like adding pgx support to a 3rd party package).