Website Database Security (2018)(int64software.com)
int64software.com
Website Database Security (2018)
https://int64software.com/blog/2018/11/21/hardening-website-security-part-3-website-database-security/
20 comments
I've seen plenty of web sites running 10+ year-old code written by someone's neigbor's kid with an ancient PHP handbook in hand. Luckily, deprecating the mysql extension in PHP in favor of the mysqli extension has broken a _lot_ of ancient code like that.
Yeah PHP has been a rich source of SQLi issues in the past, but it can crop up anywhere.
I've seen it most languages/database engines and even where an ORM is in play (e.g. ActiveRecord in rails) you can get it with bugs in the ORM implementations.
I've seen it most languages/database engines and even where an ORM is in play (e.g. ActiveRecord in rails) you can get it with bugs in the ORM implementations.
It's not only mysql though, our first system was mssql with asp and the database was cleared or filled with spam several times per month until we made a new one with php and mysql. About 15 years ago I guess.
You can see SQLi in pretty much every system that supports SQL (even including MS Access!), although the impact tends to be worse where the Database engine has more functionality for things like running operating system commands, where it can lead to total system compromise.
This might be the first time I've ever read an article like this and thought "I actually do all of this". Perhaps the "Senior" in front of my job title isn't as undeserved as it can sometimes feel :)
It amazes me how many systems (coughHeroku) leave database access open to the Internet. Is that actually acceptable in most people’s threat model? There’s no way I’d let my team deploy something like that to production.
If you have a standard CRUD API service that exposes endpoints for reading and writing effectively all the tables in your DB (presumably with varying levels of API-layer auth on each such action), then what is the difference between securing and then exposing that API service layer, and securing and then exposing the DBMS backing that API service layer?
(Where by "securing the DBMS" I mean the same thing as I do by "securing the API": issuing each authorized client their own access credentials; giving each such auth role proper ACLs to every resource; and using quotas and resource-encapsulation to prevent client accounts from DoSing the system as a whole.)
---
Or, let me put that another way. Consider http://postgrest.org/ — it's an automatic RESTful API gateway for PostgreSQL. Essentially, PostgREST is nothing more than a wire-protocol translator. Rather than standing as a separate daemon on the network, it could just be built into the DBMS itself—i.e. your DBMS could speak REST just as well as it can speak SQL. (And some DBMSes do speak REST. Mongo, CouchDB, ElasticSearch, etc.)
If you expose the "REST API of the DBMS" to the world, rather than the SQL API of the DBMS, this doesn't change the DBMS's security story. You'd still be exposing your DBMS, and leaving it vulnerable unless you explicitly take steps to secure it for public access.
Which implies, I think, that if you have a public API layer—authed or not—that can be reduced down to the "CRUD of every table the connected role can touch" API that PostgREST exposes, then you're already exposing your DBMS to the world. You're obfuscating the DBMS's wire protocol, but you're not changing its security story: you still have a data store that can be talked to (and attacked) by arbitrary clients. They just have to translate their attacks to REST/GraphQL/whatever instead of SQL. But the queries will still end up at the DBMS, and the attack surface of such queries is still pretty much identical to the attack surface you get by speaking SQL directly to the DBMS (insofar as what you care about in your DBMS is your data and the ability to query it.)
---
One more thought:
There is a difference between "public access" and "shared access."
• Shared access is what an "open but authenticated" API layer gives you: you can freely sign up to get your own set of API credentials, and then use them to make queries.
• Public access is what an unauthenticated API layer gives you: anyone can just submit a query at random without introducing themselves, and there's no way other than heuristics to limit a user from DoSing you just by using a million IPs to issue one concurrent query apiece.
DBMSes were, and are, designed for shared access. They have all the security features you need to secure them for shared access.
DBMSes don't give much thought to public access. They are perfectly fine at delivering the security story you'd expect of a "guest" role, but they don't have the same decades of engineering put into things like IP rate-limiting, grey-listing, etc. that web servers do. So the security story is good, but the DDoSing story is less so.
Thankfully, though, most such DDoS-protection logic doesn't live in web servers, but in load balancers. Some load balancers are just for HTTP, but TCP load balancers like HAProxy can configured protect your DBMS against DDoS attacks just fine, just as well as they can protect a web-app.
(Where by "securing the DBMS" I mean the same thing as I do by "securing the API": issuing each authorized client their own access credentials; giving each such auth role proper ACLs to every resource; and using quotas and resource-encapsulation to prevent client accounts from DoSing the system as a whole.)
---
Or, let me put that another way. Consider http://postgrest.org/ — it's an automatic RESTful API gateway for PostgreSQL. Essentially, PostgREST is nothing more than a wire-protocol translator. Rather than standing as a separate daemon on the network, it could just be built into the DBMS itself—i.e. your DBMS could speak REST just as well as it can speak SQL. (And some DBMSes do speak REST. Mongo, CouchDB, ElasticSearch, etc.)
If you expose the "REST API of the DBMS" to the world, rather than the SQL API of the DBMS, this doesn't change the DBMS's security story. You'd still be exposing your DBMS, and leaving it vulnerable unless you explicitly take steps to secure it for public access.
Which implies, I think, that if you have a public API layer—authed or not—that can be reduced down to the "CRUD of every table the connected role can touch" API that PostgREST exposes, then you're already exposing your DBMS to the world. You're obfuscating the DBMS's wire protocol, but you're not changing its security story: you still have a data store that can be talked to (and attacked) by arbitrary clients. They just have to translate their attacks to REST/GraphQL/whatever instead of SQL. But the queries will still end up at the DBMS, and the attack surface of such queries is still pretty much identical to the attack surface you get by speaking SQL directly to the DBMS (insofar as what you care about in your DBMS is your data and the ability to query it.)
---
One more thought:
There is a difference between "public access" and "shared access."
• Shared access is what an "open but authenticated" API layer gives you: you can freely sign up to get your own set of API credentials, and then use them to make queries.
• Public access is what an unauthenticated API layer gives you: anyone can just submit a query at random without introducing themselves, and there's no way other than heuristics to limit a user from DoSing you just by using a million IPs to issue one concurrent query apiece.
DBMSes were, and are, designed for shared access. They have all the security features you need to secure them for shared access.
DBMSes don't give much thought to public access. They are perfectly fine at delivering the security story you'd expect of a "guest" role, but they don't have the same decades of engineering put into things like IP rate-limiting, grey-listing, etc. that web servers do. So the security story is good, but the DDoSing story is less so.
Thankfully, though, most such DDoS-protection logic doesn't live in web servers, but in load balancers. Some load balancers are just for HTTP, but TCP load balancers like HAProxy can configured protect your DBMS against DDoS attacks just fine, just as well as they can protect a web-app.
PostgREST doesn't expose PostgreSQL full SQL capabilities. The clients can't do at will JOINs, DISTINCT, GROUP BYs, CALL arbitrary functions, etc, through its REST interface. It only exposes fast/safe operations and a single isolated schema of your choosing.
In a way you could think of PostgREST as a sandbox on top of PostgreSQL. DDoS protection has been thought since PostgREST inception and it has influenced its design.
You can see more details of PostgREST design in this article: https://medium.freecodecamp.org/stop-calling-postgrest-magic...
In a way you could think of PostgREST as a sandbox on top of PostgreSQL. DDoS protection has been thought since PostgREST inception and it has influenced its design.
You can see more details of PostgREST design in this article: https://medium.freecodecamp.org/stop-calling-postgrest-magic...
Also, for even more protection against expensive queries we recommend using this extension: https://github.com/pgexperts/pg_plan_filter
You can filter queries by their plan cost before they are executed.
You can filter queries by their plan cost before they are executed.
Sometimes I feel in development corners are cut. You get what you pay for. Also, people lie on their CV’s.
The risk is not completely unmitigated in that they generate random database, user, and passwords automatically. These are not the 'appname' password your security-clueless developer chose. The question is what is the risk that PostgreSQL has an authentication bypass vulnerability. Compared to the risk that your web app developers got authentication and authorization at the web app layer wrong, this is very unlikely. And given such a web application vector, your database behind the firewall is just as vulnerable to attack through the web app.
> The question is what is the risk that PostgreSQL has an authentication bypass vulnerability.
Well there was the whole OpenSSL Heartbleed issue that would only be an issue if the DB is network accessible: https://blog.hagander.net/postgresql-and-the-openssl-heartbl...
> Compared to the risk that your web app developers got authentication and authorization at the web app layer wrong, this is very unlikely. And given such a web application vector, your database behind the firewall is just as vulnerable to attack through the web app.
More layers and defense in depth is always a win for security. If nothing else, it limits your attack surface and gives you time to respond to an issue in one your layers.
Well there was the whole OpenSSL Heartbleed issue that would only be an issue if the DB is network accessible: https://blog.hagander.net/postgresql-and-the-openssl-heartbl...
> Compared to the risk that your web app developers got authentication and authorization at the web app layer wrong, this is very unlikely. And given such a web application vector, your database behind the firewall is just as vulnerable to attack through the web app.
More layers and defense in depth is always a win for security. If nothing else, it limits your attack surface and gives you time to respond to an issue in one your layers.
Oh I agree on the layers. I'm all about the defense in depth.
If it was locked down presumably all you would need to connect to the DB host is a Heroku account. I’d rather have it intentionally open with complimenting security controls.
This doesn't make any sense. There's no scenario that keeping the DB limited to being accessed by Heroku dynos would make it less secure than leaving it open to the internet AND all of the other ways you can access it currently.
Beyond that, if you leave it accessible via the internet, you are banking hard on postgres not having a zero-day that lets someone get through without brute-forcing the password (or heroku being slow to update your software).
Beyond that, if you leave it accessible via the internet, you are banking hard on postgres not having a zero-day that lets someone get through without brute-forcing the password (or heroku being slow to update your software).
It's interesting that it's managed to persist as a serious issue for over 20 years now.