This is an interesting, detailed, and well-written article.<p>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.<p>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.
Very nice article.<p>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.<p>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?<p>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.
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.<p>0 - <a href="http://www.ibm.com/developerworks/library/l-async/" rel="nofollow">http://www.ibm.com/developerworks/library/l-async/</a>
Is there some kind of row-based security approach in postgres?<p>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?<p>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.<p>EDIT: Seems it's a work in progress: <a href="https://wiki.postgresql.org/wiki/Row-security" rel="nofollow">https://wiki.postgresql.org/wiki/Row-security</a>
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].<p>[1] You should really be running your web application in its own user account regardless of how you interface to your database.<p>[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.<p>[3] <a href="http://www.postgresql.org/docs/9.3/static/auth-methods.html#AUTH-PEER" rel="nofollow">http://www.postgresql.org/docs/9.3/static/auth-methods.html#...</a>
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
Are they seriously recommending the usage of unsalted md5?<p>Edit: Oh, the article is from 2009 (I'd say it was bad practice even back then though).
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.<p>Has anyone gotten it to work transparently?
Pretty good article but had to laugh when I read this:<p>> Common practice dictates that passwords have at least six characters and are changed frequently.