
Total security in a PostgreSQL database - amirmansour
http://www.ibm.com/developerworks/library/os-postgresecurity/
======
tptacek
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.

~~~
yummyfajitas
You are absolutely correct. But by properly locking things down, you can
reduce the probability of giving an attacker write privileges.

If you simply split your connections into readonly and read/write pools,
you've already drastically reduced your attack surface. (A third DDL role
exists for migrations only, and does not exist in your app.) Now SQL injection
on your homepage or "display content" page cannot give the attacker
INSERT/UPDATE/DELETE.

You can go a little further and split your tables/columns into public ("I
could reconstruct these by scraping the site") and private ("I might need to
login for these"). Then create a role with access to the public data only, and
use it for public views. Now an attack on your homepage/other public views can
give the attacker only a limited SELECT.

This is all pretty easy to do and it can drastically reduce your attack
surface. It also gives you a useful way to prioritize fixing vulnerabilities.
First lock down read/write views. Then lock down read. Lastly lock down
readpublic.

~~~
ams6110
When I'm completely in control (writing a totally custom app) I do all my DB
access with stored procedures. The application connection cannot see or access
any tables directly. It can execute stored procedures, and that's it. I
essentially build an API with stored procedures, and that's what the
applications use.

~~~
wglb
Stored procedures don't offer any protection against malicious input. An SQL
statement built up of concatenation including user input is just as bad in a
stored procedure as it is directly in a program.

~~~
einhverfr
> Stored procedures don't offer any protection against malicious input.

Not necessarily true. Parameterized queries generally do and most stored
procedure queries should be parameterized. Add good check constraints....

Now, the protection isn't perfect but it is well beyond no protection at all
and can be a good level.

> An SQL statement built up of concatenation including user input is just as
> bad in a stored procedure as it is directly in a program.

Yeah, and sometimes that's the only way to do things in PostgreSQL. So if you
find yourself having to do this (I find it most common in CREATE statements),
then heavily commend and make sure (via frequent audit) that all values from
the input are properly quoted by quote_ident and quote_literal functions.

~~~
dragonwriter
> Not necessarily true. Parameterized queries generally do and most stored
> procedure queries should be parameterized.

But that's not anything special about stored procs. Queries called from
application code should generally be parameterized, which provides the same
protection.

~~~
einhverfr
It is different because in SQL you can't do non-parameterized queries, and in
PLPGSQL you have to specify you are executing dynamic sql. This means that
since it is in the db, it is going to be parameterized by default.

So what is different is the fact that unless you use a non-SQL-derivative
stored proc language, you pretty much have to put up big warning signs any
time you put in place a possibly exploitable query.

Examples:

1\. This is not possibly exploitable:

    
    
        CREATE OR REPLACE FUNCTION foo(in_bar int) RETURNS setof foo
        LANGUAGE PLPGSQL AS
        $$
        BEGIN
            RETURN QUERY
                SELECT * FROM foo WHERE bar_id = in_bar;
        END;
        $$;
    

2\. This one is exploitable.

    
    
        CREATE OR REPLACE FUNCTION new_user(username text) 
        RETURNS BOOL LANGUAGE PLPGSQL AS
        $$
        BEGIN
           EXECUTE $E$CREATE USER $E$ || username;
           RETURN TRUE;
        END;
        $$;
    

3\. This one is not exploitable.

    
    
        CREATE OR REPLACE FUNCTION new_user(username text) 
        RETURNS BOOL LANGUAGE PLPGSQL AS
        $$
        BEGIN
           EXECUTE $E$CREATE USER $E$ || quote_ident(username);
           RETURN TRUE;
        END;
        $$;
    

The point is that it is crystal clear in these cases whether a query is
parameterized internally or not. If you don't see the combination of EXECUTE
and ||, there is nothing to worry about.

------
dizzystar
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.

~~~
rosser
_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.

------
rubiquity
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/)

------
perlgeek
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)

~~~
pharaohgeek
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.

------
csense
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#...](http://www.postgresql.org/docs/9.3/static/auth-
methods.html#AUTH-PEER)

------
angry_octet
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
The PDF is dated from 2009 maybe it was common at the time to rely on MD5.

~~~
einhverfr
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.

------
a1a
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).

------
nasalgoat
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?

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

------
yeukhon
Isn't default postgres user password authentication still MD5?

~~~
elchief
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.

------
sehrope
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.

