9 ms·

Total security in a PostgreSQL database

218 points by amirmansour 13y ago

kbar13 13y ago
Wow, this is very in-depth. Bookmarked for reading when I get home. Thanks for sharing!
rubiquity 13y ago
DeveloperWorks puts out some really great content from time to time. This article and their article on POSIX Asychronous I/O in Linux[0] are two of my favorites. 0 - http://www.ibm.com/developerworks/library/l-async/ http://www.ibm.com/developerworks/library/l-async/
dizzystar 13y ago
Very nice article. The section under "the ideal administrator" is quite eye-opening. I pretty much use PostgreSQL exclusively, and I've found that every time I learn something new, there is another mile of learning to go, and that feedback cycle never seems to end. I have a few PostgreSQL-specific book on admin and server programming, but I wonder where I would be able to go to really learn this stuff. Are there any classes or places to go for this sort of SQL training? How does one go about becoming a total master at this? I find that, out of all the programming that I do, I love working with SQL the most and I want to dive deeper into it.
- petit_robert 13y ago
  I would suggest subscribing to and reading the fine postgresql mailing lists (general and performance). It's amazing what you can learn from the questions and answers they get.
- rosser 13y ago
  How does one go about becoming a total master at this? I find that, out of all the programming that I do, I love working with SQL the most and I want to dive deeper into it. Exposure, exploration, and experience. I started my career in a role similar to the one it sounds like you're in today: at first, I was just another developer. Then I was the developer who knew and liked SQL pretty well. At some point, there was a shift where I started spending more time helping people do database-y things than I did developing. Subsequently, keeping our PostgreSQL instances happy was officially made my job. Then I got a job with the title DBA based on that experience. Though I've moved on a couple times since, that's the hat I've worn for the better part of a decade now. It's not always the most exciting work (and when it is exciting, it's often enough the wrong kind of exciting), but it's almost always interesting. It probably helped that I've always been a technologist for sake of fascination with technology, who happened to realize you could also make a decent living with those skills. I'd started playing with Linux in the late '90s, for example, out of a "What's this all about?" sort of curiosity. Net, I almost accidentally ended up checking nearly every box in that "ideal administrator" section. My C is pretty rusty, and my network admin-fu is weak, but I don't think the breadth of my skills are unrelated to people (management and technical folk alike) at my last several jobs telling me I'm the best DBA they've ever worked with.
- edwinnathaniel 13y ago
  EnterpriseDB offers training around their product (enterprise of PostgreSQL) but they might occasionally inject EnterpriseDB specific materials. Other than that, the knowledge is rather sporadic (books, documentations, mailing-lists, the actual source code itself, join PostgreSQL community, etc).
- einhverfr 13y ago
  Also keep in mind that PostgreSQL provides unparalleled programming possibilities. I don't think it is humanly possible to fully master every possible aspect of PostgreSQL. Consider what this means: 1. Mastering standard data modelling techniques 2. Mastering advanced, PostgreSQL-specific data modelling techniques 3. Mastering server-side procedure programming in a large number of languages (C, Perl, Lisp, TCL, PHP, Python, Java, ...) 4. Mastering server-side type/language handler programming 5. Mastering tuning PostgreSQL to the OS, and tuning the OS to PostgreSQL 6. etc. This list can probably go on forever. The best you can do is master a significant part of things and use that to carve out a niche.
tptacek 13y ago
This is an interesting, detailed, and well-written article. Let me caution you though: in most applications, if you concede to an attacker INSERT/UPDATE/SELECT (ie: if you have SQL Injection), even if you've locked down the rest of the database and minimized privileges, you're pretty much doomed. Most teams we work with don't take the time to thoroughly lock down their databases, and we don't blame them; it's much more important to be sure you don't give an attacker any control of the database to begin with.
- rpedela 13y ago
  Doomed in what way? That the SQL injection allows the attacker to see or mess with data? Or that the attacker somehow gains greater access to the database, server, OS, etc? Or both?
  - rubiquity 13y ago
    Most attackers won't be SSHing into your servers and firing up psql processes to mash your database as that would require gaining user access, SSH access and psql access. Most attackers will go the cheaper and more likely route of turning your application on your server by using SQL injection.
  - tptacek 13y ago
    The latter.
- y0ghur7_xxx 13y ago
  > if you concede to an attacker INSERT/UPDATE/SELECT (ie: if you have SQL Injection), even if you've locked down the rest of the database and minimized privileges, you're pretty much doomed. Well... no. If you lock down your db correctly, using the correct privileges for the users accessing the data, and using veil or rls, the sql executed on the db is just an other way of accessing the data you can see anyway through the webapp. The webapp becomes just a nice frontend so the user does not need to execute sql directly, and if there is an sql injection vulnerability the attacker would still be able to only see the data he has the right to see anyway. He is just accessing it with sql instead of the webapp.
angry_octet 13y ago
Great read. But I was disappointed that it didn't mention other password encryption schemes, i.e. Blowfish. www.postgresql.org/docs/8.4/static/pgcrypto.html
- jmnicolas 13y ago
  The PDF is dated from 2009 maybe it was common at the time to rely on MD5.
  - einhverfr 13y ago
    MD5 challenge/response auth today would typically be used in short-hop logins. It might be further protected with SSL on longer hops. For larger networks, I would assume you'd want to use Kerberos auth.
yeukhon 13y ago
Isn't default postgres user password authentication still MD5?
- elchief 13y ago
  Yes, it is. Though it is salted (but not randomly sadly). For stronger password hashing, you can keep your user accounts in LDAP, and have PG authenticate with LDAP.
nasalgoat 13y ago
An excellent article, but it brings up a question about authentication using the various load balancing tools out there, such as pgPool or pgBouncer. I've found the auth tools in them to be extremely poor, to the point that it's easier to just leave it off. Has anyone gotten it to work transparently?
sehrope 13y ago
Pretty good article but had to laugh when I read this: > Common practice dictates that passwords have at least six characters and are changed frequently.
a1a 13y ago
Are they seriously recommending the usage of unsalted md5? Edit: Oh, the article is from 2009 (I'd say it was bad practice even back then though).
perlgeek 13y ago
Is there some kind of row-based security approach in postgres? Let's say I run a hosting company, and when a user logs in, I want to limit DB access of this particular connection to rows that actually matter to the logged-in customer (like purchased services, associated accounts) and still allow access to general-purpose information (like list of available TLDs, stock prices for services etc.). Can I do that somehow? Then I'd use authentication outside of the database (like with LDAP), and only allow access to the database after login; that way information leaks should be pretty much contained to the logged-in customer. EDIT: Seems it's a work in progress: https://wiki.postgresql.org/wiki/Row-security https://wiki.postgresql.org/wiki/Row-security
- einhverfr 13y ago
  In addition to the work in progress, you could use security barrier views.
- pharaohgeek 13y ago
  The SE-PostgreSQL project provides row and column-level security. It's based off the work of SELinux, so it may be overkill for what you're looking for. Plus, it has all of the inherent complexities of SELinux, so that may be a turnoff for you. But, if you're REALLY paranoid about security, it's a great tool.
- elchief 13y ago
  WITH CHECK OPTION is coming in 9.4 . It's already done apparently. You can sorta do it now with security_barrier views, but they aren't auto-updatable, so you need to write more code, which is a PITA.
csense 13y ago
In the case where your application and database are running on the same server, you can eliminate the database password entirely by running the application in a separate user account [1] and running your database on a UNIX socket [2] with peer authentication [3]. [1] You should really be running your web application in its own user account regardless of how you interface to your database. [2] Running daemons on a UNIX socket is better security-wise than running on localhost, because you can protect the UNIX socket with filesystem permissions. [3] http://www.postgresql.org/docs/9.3/static/auth-methods.html#AUTH-PEER http://www.postgresql.org/docs/9.3/static/auth-methods.html#...

218 points by amirmansour 13y ago

Wow, this is very in-depth. Bookmarked for reading when I get home. Thanks for sharing!

DeveloperWorks puts out some really great content from time to time. This article and their article on POSIX Asychronous I/O in Linux[0] are two of my favorites. 0 - http://www.ibm.com/developerworks/library/l-async/ http://www.ibm.com/developerworks/library/l-async/

dizzystar 13y ago

Very nice article. The section under "the ideal administrator" is quite eye-opening. I pretty much use PostgreSQL exclusively, and I've found that every time I learn something new, there is another mile of learning to go, and that feedback cycle never seems to end. I have a few PostgreSQL-specific book on admin and server programming, but I wonder where I would be able to go to really learn this stuff. Are there any classes or places to go for this sort of SQL training? How does one go about becoming a total master at this? I find that, out of all the programming that I do, I love working with SQL the most and I want to dive deeper into it.

petit_robert 13y ago
I would suggest subscribing to and reading the fine postgresql mailing lists (general and performance). It's amazing what you can learn from the questions and answers they get.
rosser 13y ago
How does one go about becoming a total master at this? I find that, out of all the programming that I do, I love working with SQL the most and I want to dive deeper into it. Exposure, exploration, and experience. I started my career in a role similar to the one it sounds like you're in today: at first, I was just another developer. Then I was the developer who knew and liked SQL pretty well. At some point, there was a shift where I started spending more time helping people do database-y things than I did developing. Subsequently, keeping our PostgreSQL instances happy was officially made my job. Then I got a job with the title DBA based on that experience. Though I've moved on a couple times since, that's the hat I've worn for the better part of a decade now. It's not always the most exciting work (and when it is exciting, it's often enough the wrong kind of exciting), but it's almost always interesting. It probably helped that I've always been a technologist for sake of fascination with technology, who happened to realize you could also make a decent living with those skills. I'd started playing with Linux in the late '90s, for example, out of a "What's this all about?" sort of curiosity. Net, I almost accidentally ended up checking nearly every box in that "ideal administrator" section. My C is pretty rusty, and my network admin-fu is weak, but I don't think the breadth of my skills are unrelated to people (management and technical folk alike) at my last several jobs telling me I'm the best DBA they've ever worked with.
edwinnathaniel 13y ago
EnterpriseDB offers training around their product (enterprise of PostgreSQL) but they might occasionally inject EnterpriseDB specific materials. Other than that, the knowledge is rather sporadic (books, documentations, mailing-lists, the actual source code itself, join PostgreSQL community, etc).
einhverfr 13y ago
Also keep in mind that PostgreSQL provides unparalleled programming possibilities. I don't think it is humanly possible to fully master every possible aspect of PostgreSQL. Consider what this means: 1. Mastering standard data modelling techniques 2. Mastering advanced, PostgreSQL-specific data modelling techniques 3. Mastering server-side procedure programming in a large number of languages (C, Perl, Lisp, TCL, PHP, Python, Java, ...) 4. Mastering server-side type/language handler programming 5. Mastering tuning PostgreSQL to the OS, and tuning the OS to PostgreSQL 6. etc. This list can probably go on forever. The best you can do is master a significant part of things and use that to carve out a niche.

tptacek 13y ago

This is an interesting, detailed, and well-written article. Let me caution you though: in most applications, if you concede to an attacker INSERT/UPDATE/SELECT (ie: if you have SQL Injection), even if you've locked down the rest of the database and minimized privileges, you're pretty much doomed. Most teams we work with don't take the time to thoroughly lock down their databases, and we don't blame them; it's much more important to be sure you don't give an attacker any control of the database to begin with.

rpedela 13y ago
Doomed in what way? That the SQL injection allows the attacker to see or mess with data? Or that the attacker somehow gains greater access to the database, server, OS, etc? Or both?
- rubiquity 13y ago
  Most attackers won't be SSHing into your servers and firing up psql processes to mash your database as that would require gaining user access, SSH access and psql access. Most attackers will go the cheaper and more likely route of turning your application on your server by using SQL injection.
- tptacek 13y ago
  The latter.
y0ghur7_xxx 13y ago
> if you concede to an attacker INSERT/UPDATE/SELECT (ie: if you have SQL Injection), even if you've locked down the rest of the database and minimized privileges, you're pretty much doomed. Well... no. If you lock down your db correctly, using the correct privileges for the users accessing the data, and using veil or rls, the sql executed on the db is just an other way of accessing the data you can see anyway through the webapp. The webapp becomes just a nice frontend so the user does not need to execute sql directly, and if there is an sql injection vulnerability the attacker would still be able to only see the data he has the right to see anyway. He is just accessing it with sql instead of the webapp.

angry_octet 13y ago

Great read. But I was disappointed that it didn't mention other password encryption schemes, i.e. Blowfish. www.postgresql.org/docs/8.4/static/pgcrypto.html

jmnicolas 13y ago
The PDF is dated from 2009 maybe it was common at the time to rely on MD5.
- einhverfr 13y ago
  MD5 challenge/response auth today would typically be used in short-hop logins. It might be further protected with SSL on longer hops. For larger networks, I would assume you'd want to use Kerberos auth.

yeukhon 13y ago

Isn't default postgres user password authentication still MD5?

elchief 13y ago
Yes, it is. Though it is salted (but not randomly sadly). For stronger password hashing, you can keep your user accounts in LDAP, and have PG authenticate with LDAP.

nasalgoat 13y ago

An excellent article, but it brings up a question about authentication using the various load balancing tools out there, such as pgPool or pgBouncer. I've found the auth tools in them to be extremely poor, to the point that it's easier to just leave it off. Has anyone gotten it to work transparently?

sehrope 13y ago

Pretty good article but had to laugh when I read this: > Common practice dictates that passwords have at least six characters and are changed frequently.

a1a 13y ago

Are they seriously recommending the usage of unsalted md5? Edit: Oh, the article is from 2009 (I'd say it was bad practice even back then though).

perlgeek 13y ago

Is there some kind of row-based security approach in postgres? Let's say I run a hosting company, and when a user logs in, I want to limit DB access of this particular connection to rows that actually matter to the logged-in customer (like purchased services, associated accounts) and still allow access to general-purpose information (like list of available TLDs, stock prices for services etc.). Can I do that somehow? Then I'd use authentication outside of the database (like with LDAP), and only allow access to the database after login; that way information leaks should be pretty much contained to the logged-in customer. EDIT: Seems it's a work in progress: https://wiki.postgresql.org/wiki/Row-security https://wiki.postgresql.org/wiki/Row-security

einhverfr 13y ago
In addition to the work in progress, you could use security barrier views.
pharaohgeek 13y ago
The SE-PostgreSQL project provides row and column-level security. It's based off the work of SELinux, so it may be overkill for what you're looking for. Plus, it has all of the inherent complexities of SELinux, so that may be a turnoff for you. But, if you're REALLY paranoid about security, it's a great tool.
elchief 13y ago
WITH CHECK OPTION is coming in 9.4 . It's already done apparently. You can sorta do it now with security_barrier views, but they aren't auto-updatable, so you need to write more code, which is a PITA.

csense 13y ago

In the case where your application and database are running on the same server, you can eliminate the database password entirely by running the application in a separate user account [1] and running your database on a UNIX socket [2] with peer authentication [3]. [1] You should really be running your web application in its own user account regardless of how you interface to your database. [2] Running daemons on a UNIX socket is better security-wise than running on localhost, because you can protect the UNIX socket with filesystem permissions. [3] http://www.postgresql.org/docs/9.3/static/auth-methods.html#AUTH-PEER http://www.postgresql.org/docs/9.3/static/auth-methods.html#...