We do the same thing, every employee can access our main financial/back office SQL database, but we just use PostgreSQL with row level security[0]. We never bothered to complicate it like the post does.
I want to build a shared postgres db with hundreds of small apps (OLTP) accessing shared tables using a RLS model model against well defined tables.
What are other limitations and mitigations folks have used or encountered to support stability and security? Things like
- Query timeouts to prevent noisy neighbors
- connection pooling (e.g. pgbouncer) also for noisy neighbors
- client schema compatibility (e.g. some applications running older versions, have certain assumptions about the schema that may change over time)
If you have people running crappy SQL SELECT, it can be a problem. statement-timeout[0] is your friend here. You still have to be on watch, and teach your users not to write crappy SQL.
You can also limit it by creating read-only replica's and making SELECT's happen on the replica. We don't usually bother, since 99% of our users are employees of ours, we can teach them to not be stupid. Since their usage doesn't change much over time, we can usually just hand them a SQL query and say: here run this instead.
Most of our employees don't even know they have SQL access, it's not like we force people to learn SQL to get their job done. Because of RLS and views, the ones that do SQL don't have to know much SQL, even if they do happen to use it. SELECT * from employees; gets them access to basically all the employee info they could want, but only to the employees they have access to. If you are a manager with 10 people, your select returns only your 10 people.
The payroll staff runs the same query and gets all of the employees they handle payroll for. Since our payroll is done inside of PostgreSQL(thanks plpython[1]), we can do some crazy access control stuff that most systems would never even dream about. Whenever new auditors come in and see that our payroll staff is limited to seeing only the info they need to do payroll, and only for their subset of employees they actually pay, they are awestruck.
The random vendors that can't be taught, we usually hand them a nightly SQLite dump instead. I.e let them pay the CPU cost of their crappy SQL.
Around client schema compatibility. This happens with other models too(API, etc). It's not unique to PG or SQL Databases. You have to plan for it. Since most all of our users interact with views and not with the actual underlying tables, it's not usually that big of a deal. In the extreme cases, where we can't just keep around a view for them, we have to help them along(sometimes kicking and screaming) into a new version.
I'd be so uncomfortable with this. It sounds like you're placing the full burden of access on a single boundary. I mean, maybe there's more to it that you haven't spoken about here, but "everything rests on this one postgres feature" is an unacceptably unsafe state to me.
Well, I mean it's not only RLS, but yes it's only PostgreSQL doing the access control as far as if they can see a particular table or row.
Every user gets their own role in PG, so the rest of the PG access control system is also used.
We have your normal SSO system(Azure) and if Tootie employee doesn't need access to Asset Control, they don't get any access to the asset schema for instance.
What would be your method?
You would have some app that your dev team runs that handles access control, so your app gets unrestricted access to the DB. Now your app is the single boundary, and it forces everyone to go through your app. How is that better? It also complicates your queries, with a ton of extra where conditions.
A bunch of bespoke access control code you hope is reliable or a feature of the database that's well tested and been around for a long time. pgtap[0] is amazing for ensuring our access control (and the rest of the DB) works.
If some random utility wants to access data, you either have to do something special access wise, or have them also go through your app(let's hope you have an API and it allows for whatever the special is). For us, that random utility gets SQL access just like everyone else. They get RLS applied, etc. They can be naive and assume they have total control, because when they do select * from employees; they get access to only the employee column and rows we want that utility to have.
We have a bunch of tools over the decades that need access to various bits of our data for reason(s). Rather than make them all do wacky stuff with specialized API's, they just get bog standard PG SQL. We don't have to train vendor Tito how to deal with our stuff, we just hand them their auth info to PG and they can go to town. When people want Excel spreadsheets, they just launch excel, do a data query and their data just shows up magically. All from within Excel, using the standard excel data query tools, no SQL needed.
I don't know because I don't know your use case. At minimum, direct db access means that every postgres CVE something I'd have to consider deeply. Even just gating access behind an API where the API is the one that gets the role or accepts some sort of token etc would make me feel more comfortable.
> Now your app is the single boundary,
No, the app would still use RLS.
I'm not saying what you're doing is bad, but as described I'd be pretty uncomfortable with that deployment model.
I don't think you thought this through? The problem with the app being constrained to RLS is you have User A and User B accessing your API, how do you get them access to the different data they need? It means the RLS is very wide open, since it needs to be able to see what User A and B can see. This forces your app to be the single boundary in pretty much all cases. Sure maybe you can give it a role where it has limited DDL rights(i.e not create table access or whatever).
> At minimum, direct db access means that every postgres CVE something I'd have to consider deeply.
I mean, not really, in practice? Most are just denial of service type bugs, not instant exploits. . Most of the DoS issues are not that big of a deal for us. They could affect us, but 99.9% of the time, they don't in reality, before we upgrade. RLS has been in PG for a good many years, it's quite stable. Sure, we upgrade PostgreSQL regularly, but you should do that anyway, regardless of RLS usage or not.
It's not like RLS is just some random feature they are misusing. It's specifically for security and is absolutely reliable. Maybe you should do a bit more research before making comments like this.
You can (and in some cases should) combine this with other boundaries, like access control or specific query logic. RLS moves the core checks closer to the data being controlled (i.e. the database) specifically to prevent the errors like forgetting to add the "where user_id = xxx" clause. It is super-valuable in compliance scenarios where someone like your DB Admin has permission to control access but not see any data, and consumers (both devs and end users) can write queries to see different levels of access but are not allowed to control data.
Obviously it's not a silver bullet and the isolation can be confusing when debugging, but generally a single point for your applying RBAC is a feature not a shortcoming. The next level of security might be how you define your roles.
I actually believe the simplest, most secure client scenario is physical isolation, where you give the user/consumer only the data they are allowed to use and then don't try to control it (someone mentioned this above, using parquet & duckdb). There's downsides here too: doesn't work for write scenarios, can be resource intensive or time delayed, doesn't handle chain of custody well, etc. You typically have two strategies:
1. pick the best approach for the specific situation.
2. pick your one tool as your hammer and be a d!ck about it.
This is the real world not everybody can get a perfectly isolated database instance. Also you do realize that is not necessarily any more secure than RLS right? Something still has to control what database the code connects to. That could have a flaw just as much as you could have a flaw when setting up RLS.
RLS is one of the simplest things to set up properly, if you can't figure it out I don't think I'm the one who doesn't know what they're talking about.
row level security is not a feature specific to Postgres, but more a pretty standard and acceptable way to control access in a multitenant or multicontext environment that pretty much every data provider supports/implements. When it comes to answering a single specific question (like the one RLS targets) I believe you DO want a single, simple answer, vs. something like "it uses these n independent things working in conjunction..."
Your SSO system is a lot of pressure on one control too. Nobody seems to have problems with Azure or Okta or whatever SSO system you use having every key to the kingdom.
RLS has been around a long time and is very stable and doesn't change much. SSO providers keep adding stuff ALL the time, and they regularly have issues. PG RLS is very boring in comparison.
I don't remember the last CVE or outage we had with PG that broke stuff. I can't remember a single instance of RLS causing us access control problems on a wide scale. Since we tied their job(s) to their access control many years ago, it's very rare that we even have the random fat-fingered access control issue for a single user anymore either. I think the last one was a year ago?
Conceptually that's no different to any security measures that prevent you from accessing data you're not supposed to? At the end of the day with all data that is colocated you're trusting that some permission feature somewhere is preventing you from accessing data you're not supposed to.
We trust that Amazon or Google or Microsoft are successful in protecting customer data for example. We trust that when you log into your bank account the money you see is yours, and when you deposit it we trust that the money goes into your account. But it's all just mostly logical separation.
> At the end of the day with all data that is colocated you're trusting that some permission feature somewhere is preventing you from accessing data you're not supposed to.
Right but ideally more than one.
> But it's all just mostly logical separation.
Yes, ideally multiple layers of this. You don't all share one RDS instance and then get row level security.
Can you give an example of more than one layer of logical separation at the data layer?
We all know that authentication should have multiple factors. But that's a different problem. Fundamentally at the point you're reading or writing data you're asking the question "does X has permission to read/write Y".
I don't know their use case enough to understand what would or would not be an appropriate mitigation. For example, with regards to financial data, you could have client side encryption on values where those keys are brokered separately. I can't exactly design their system for them, but they're describing a system in which every employee has direct database access and the database holds financial information.
Right, encryption would protect the data. But still, at the end of the day you're trusting the permission model of the database. Encryption won't prevent you updating a row or deleting a row if the database permission model failed.
Well, I think we basically agree? My suggestion is merely that a database holding financial data should have more than a single layer of security. Granting direct access to a database is a pretty scary thing. A simple example would be that any vulnerability in the database is directly accessible, even just by placing a broker in between users and the database I'd likely start to feel a lot better, and now I'd have a primitive for layering on additional security measures.
Encryption is an extremely powerful measure for this use case. If the data does not need to be indexed, you could literally take over the database process entirely and still not have access, it definitely doesn't rely on the permission model of the db because the keys would be brokered elsewhere.
Neat, thank you! sc-im looks amazing, and it's even in the Fedora repos (though the repo version doesn't support xlsx, so I'll compile myself and try it out)
Edit: Quite painless! Opened some test xlsx files without issue. Did get a stack trace on a very complicated one, so when I have time I'll try and dig in deeper. Added a doc to the wiki in case it's helpful to other: https://github.com/andmarti1424/sc-im/wiki/Building-sc%E2%80...
Yes, lots of large providers really want defense in depth at the router/firewall/important services level.
They want separate OS's in case a giant CVE or bug comes out for platform X but isn't on platform Y. So multi-platform core services is a key component of systems that need to stay up and alive long-term. Things like Routers, Firewalls, DNS, BGP, etc. Use different software and hardware stacks.
We use FreeBSD and Debian @ work, though we are playing around with Nix and Guix now to maybe replace the Debian someday.
> Linux is more welcoming to new users. And the difference is only growing.
Unless you want documentation. :) Linux documentation is ALL over the place, and it's hard to know if X docs apply to your particular mess.
On OpenBSD it's `man <thing>` and you get your docs. Yes man exists on Linux, but lots of tools, especially "new" stuff or linux specific stuff doesn't come with man pages and those that do have man pages, can be missing lots of stuff.
Systemd docs tend to be somewhat useless, missing all sorts of stuff. Troubleshooting systemd by reading the docs is usually a waste of time. Reading the source code or getting lucky with a web search is usually your only hope.
When you realize companies can borrow against receivables and payables also....
But it eventually comes out, so while you can do it short-term, it's a terrible long-term strategy. Your stock will eventually crash and burn if you do too much of it.
In the USA, I've had several charging stations where the CC terminal acts like it works, but it won't actually charge the vehicle.
However if you use the App, the charger works. This is in late 2025 and 2026, so it's better, but the non-app route is not fully fleshed out yet.
My worst experience was a charger near Yosemite ran by the local power utility. They required you use a website to turn the charger on, and the instructions were incomplete and it took me a half-dozen tries to figure out the right set of magic steps to get the dumb charger to work. Had it not been the only charger in like 100+ miles, I'm sure it would never ever get used due to how insanely broken it was.
At .49% expense ratio, plus whatever your cut is, it won't be a very cheap product. Even SPAXX, the default holding of cash at Fidelity is cheaper at .42% ER.
There is no free lunch in investing, so that extra yield comes with extra risk. Be that duration, credit, etc. That's not to say MBS's don't have their place, but I would never claim people's mortgages as equivalent to cash in any shape or form. Your website claims MBSF is safe for 3+ month durations, but that is not the avg duration of MBSF held securities, so you are encouraging duration risk.
I haven't read the full prospectus on MBSF, so I'm not an expert on that product, but it seems expensive and complicated, which is not what you want for cash and cash-like things. This should be a hard pass for literally everyone.
Meanwhile you can hold something like ICSH[0] or SGOV[1] with expense in the .09% or lower range(i.e. for every $10k we are talking $9/yr or less in fees). SGOV is 0-3 month max duration, so it's perfect for holdings in the 3 month time-frame. If you need longer time frames you can buy govt bond ladders in whatever time frame you want.
What your product should have been: You specify duration for each of your buckets, and then you pick appropriate, cheap index-based investments that are cheap and easy to reason about for each of the buckets.
The 4.5-5% yields we quote are net of expense ratio. Then our cut is 0.25%, comparable to the 0.15% to 0.6% charged by Mercury, Rho, etc. And we're working on bringing that expense ratio down as we scale.
Functionally speaking, short-duration floating-rate agency MBS trade at such a stable NAV that they're perfectly sufficient for long-term cash, and many large companies trade these.
MBSF is complex in the way that basically any fund is complex, but the strategy it employs is actually quite simple since it only trades a single asset class. Yes the expense ratio is higher than some other funds but the additional yields more than make up for it.
ICSH and SGOV are great funds too, and make sense for shorter-term cash, but they pay significantly less than we do.
Broadly speaking, our product is meant for exactly the kind of cash strategy you're thinking about: multiple buckets with duration spread accordingly. At the moment, our platform is just for the long-term bucket. But in the future, we might add additional shorter-term buckets too (maybe even with ICSH or SGOV).
The risk you are hiding will eventually show up. Charging .25% ER means you probably can make some decent money in the meantime and since the customer holds MBSF directly, you aren't on the hook for the risk. Smart.
It's a great business for you, if you can get enough customers. That said, I would never ever recommend anyone invest with you, since you are either purposely withholding the risks or not smart enough about the product you are trying to sell to understand the risks involved and share them with your customers. Either way, people shouldn't use your service.
0: https://www.postgresql.org/docs/18/ddl-rowsecurity.html
reply