Total security in a PostgreSQL database

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&#x2F;UPDATE&#x2F;SELECT (ie: if you have SQL Injection), even if you&#x27;ve locked down the rest of the database and minimized privileges, you&#x27;re pretty much doomed.<p>Most teams we work with don&#x27;t take the time to thoroughly lock down their databases, and we don&#x27;t blame them; it&#x27;s much more important to be sure you don&#x27;t give an attacker any control of the database to begin with.

Very nice article.<p>The section under &quot;the ideal administrator&quot; is quite eye-opening. I pretty much use PostgreSQL exclusively, and I&#x27;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&#x2F;O in Linux[0] are two of my favorites.<p>0 - <a href="http://www.ibm.com/developerworks/library/l-async/" rel="nofollow">http:&#x2F;&#x2F;www.ibm.com&#x2F;developerworks&#x2F;library&#x2F;l-async&#x2F;</a>

Is there some kind of row-based security approach in postgres?<p>Let&#x27;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&#x27;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&#x27;s a work in progress: <a href="https://wiki.postgresql.org/wiki/Row-security" rel="nofollow">https:&#x2F;&#x2F;wiki.postgresql.org&#x2F;wiki&#x2F;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:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;9.3&#x2F;static&#x2F;auth-methods.html#...</a>

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

Are they seriously recommending the usage of unsalted md5?<p>Edit: Oh, the article is from 2009 (I&#x27;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&#x27;ve found the auth tools in them to be extremely poor, to the point that it&#x27;s easier to just leave it off.<p>Has anyone gotten it to work transparently?

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

Isn&#x27;t default postgres user password authentication still MD5?

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