Versioned views, materialized views or procedures are the solution to this. It is frequent that even internally, companies don't give access to their raw data but rather to a restricted schema containing a formated subset of it.
Stored procedures technically can do anything, I guess, but at that point you would be better with traditional services which will give you more flexibility.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Postg...
Also, using views and stored procedures with source control is a pain.
Deploying these into prod is also much more cumbersome than just normal backend code.
Accessing a view will also be slower than accessing an “original” table since the view needs to be aggregated.
Where does it say anything needs aggregating. You can have a view that exists just for security.
> Also, using views and stored procedures with source control is a pain. Deploying these into prod is also much more cumbersome than just normal backend code.
Uh? This is normal backend code.
Are modern developers allergic to SQL or what is the issue?
versioned views and materialized views are essentially api endpoints in this context. just developed in sql instead of some sane language.
SQL is a legendary language, it's powerful enough to build entire APIs out of (check out PostGraphile, PostgREST, and Hasura) but also somehow simple enough that non-technical business analysts can use it. It's definitely worth spending time on.
Wouldn't it be far simpler to just create a service providing access to those views with something like OData?