Pennylane Accounting and financial management SaaS Founded in 2020 ~2000 customers ~40 engineers, of which ~30 ruby devs Ruby on rails, React, PostGRES, Heroku, AWS
Our data model 99% of data is partitioned by customer (“company”) We dogfood our own product - all of our own financial data is in our database Our customers’ financial data is very sensitive - we need to be extra-careful that the partitioning is perfect
Our tooling Access control is managed via CanCanCan in a single Ability file We leverage PostGRES row-level security (RLS) to restrict access to our own company’s data We filter and store PostGRES changelog data with AWS for auditing purposes
Ability class Ability def initialize(user) can :manage, Company, id: user.company_id can :manage, Invoice, company_id: user.company_id can :manage, InvoiceLine, invoice: { company_id: user.company_id } end end
sync_from_prod Devtool to export production data from a specific customer to development environment > COPY TO STDOUT(SELECT * FROM companies WHERE id = xxx); > COPY TO STDOUT(SELECT * FROM invoices WHERE company_id = xxx); > COPY TO STDOUT(SELECT * FROM invoice_lines WHERE invoice_id IN ( SELECT id FROM invoices WHERE company_id = xxx ));
Data pipeline Plugged into PostGRES replication stream, stores row-level change events We want to filter out data linked to Pennylane Complex logic when invoice_line changes
Denormalizing Advantages: - Very easy to find out who can access what (just look at company_id) - Consistency checking: it takes one JOIN to check that an invoice_line is attached to the correct company Pitfalls: - Data redundancy - Data consistency - PostGRES column correlations may hinder query planner performance
Ability class Ability def initialize(user) can :manage, Company, id: user.company_id can :manage, Invoice, company_id: user.company_id can :manage, InvoiceLine, company_id: user.company_id end end
Permanent attributes define_method(:"#{attribute}_not_changed") do if persisted? && has_attribute?(attribute) \ && !attribute_was(attribute).nil? && attribute_changed?(attribute) errors.add(attribute, :permanent) end end validate :"#{attribute}_not_changed"
Insecure Direct Object Reference PUT /invoices/4, { invoice: { supplier_id: 99 } } GET /invoices/4 It’s possible to attach invoices to another company’s suppliers 💣
Preloading associations def build_scope super.tap do |orig_scope| orig_scope.where!(company_id: @__company_id) \ if klass.has_attribute?(:company_id) && defined?(@__company_id) end end patch applied on ActiveRecord::Associations::Preloader::Association
Where_exists (https://github.com/EugZol/where_exists) > Invoice.where_exists(:invoice_lines) SELECT "invoices".* FROM "invoices" WHERE ((EXISTS ( SELECT 1 FROM "invoice_lines" WHERE ("invoice_lines"."invoice_id" = "invoices"."id") AND "invoice_lines"."company_id" = "invoices"."company_id" )))
Denormalizing Advantages: - Very easy to find out who can access what (just look at company_id) - Consistency checking: it takes one JOIN to check that an invoice_line is attached to the correct company - “Belt and suspenders” approach to IDOR Pitfalls: - Data redundancy isn’t such a big problem ✔ - Data consistency seems solved ✔ - PostGRES column correlations may hinder query planner performance (for another time)