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.

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.

Nowadays I generally write raw SQL with the Tiramisu library (shameless plug: https://github.com/stucchio/Tiramisu ) I cooked up. It's basically writing straight SQL, but with the ability to safely do (almost) string concatenation to build queries.

http://thedailywtf.com/Articles/Database-Changes-Done-Right....

(Or if you're really lucky, even the current load on the database could make it fail... i.e. transaction serialization failure due to the random row order you attempted to lock)

So it should have ended with 'you're doomed'.

http://sqitch.org

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.

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

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;
    $$;

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

    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;
    $$;

Let's take an example: separation of duties in accounting. You want someone to enter transactions and you want someone else to approve them, and you want to enforce this through db permissions.

You might give insert permissions to user 1 but update permissions to user 2. Except that you don't want to allow updates. You only want to allow some updates, to a few columns.

So now you grant update permissions only on some columns. Except that you don't want to allow all updates on those columns. You only want to allow updating from, say, a null value to a definite value (say, indicating who and when).

You can't do this with standard permissions, so you now create another role with permission to update these columns, and require that mere mortals go through a stored procedure to update them, which will only allow those rows to be updated which had nulls in these values.

Now, I hear you say, you could do this with a join table called 'approval' with these columns so you don't have to worry about NULLs. Great, except that NOT EXISTS() queries tend to suck and you can't use partial indexes to ensure you can always quickly look up those transactions pending approval. So in the end NULLs, stored procedures, and an intermediate role end up winning out in terms of security.

    CREATE TABLE public_user_content (
      body VARCHAR(512) NOT NULL,
      user_id BIGINT REFERENCES users(id)
    )

    CREATE TABLE users (
      id BIGINT PRIMARY KEY,
      username VARCHAR(128) NOT NULL,
      email VARCHAR(256) NOT NULL,
      badly_hashed_password VARCHAR(8),
      social_security_number VARCHAR(9)
    )

    GRANT SELECT ON public_user_content TO public_readers;
    GRANT SELECT ON users (username) TO public_readers;

    @run_with_role('public_readers')
    def get_public_feed(request):
        ....

However, in the context of forms authentication, there comes a delicate moment where the user name and hashed password must be exposed to an unauthenticated user, no?

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.

If your application wasn't designed from the start to make its database connection "just another view onto data users would have access to anyways" (paraphrasing), locking down the database is closer to make-work than it is to critical security measures.

A security model which follows these processes merely has a database that doesn't trust the application. This has some significant costs and tradeoffs associated (you can't do connection pooling).

Almost all webapps we write, we write them this way. (on oracle, not postgres, but that makes no difference).

> your application needs to be designed for it from the very beginning.

That's true. As in "you can not add security later on".

I guess it all depends on using this feature as an additional layer of protection or using it as the only layer of protection.

Otherwise I don't know how you would be able to make the permissions fine-grained enough to be able to make these claims.

Now this has some significant tradeoffs. It is very much a good decision for an ERP app. However for customer portals we are looking at a different approach, namely:

1. A portal user account in the db with very restricted permissions (and no direct access to tables)

2. A portal back-end user account in the db with limited access to tables and which owns most of the security definer functions the portal uses. I see no reason why we might need dynamic SQL in such functions, so no avenues for SQL injection unless you can exploit a trigger or something. For fine-grained permissions, the functions could do additional permission checks based on an auth token.

This should effectively prevent SQL Injection from being particularly useful in this context.

This means, btw, that you can hook up LedgerSMB to use LDAP Auth simply by changing PostgreSQL's LDAP settings. Here's an example howto, using LDAP via Samba (I would prefer Kerberos, but this demonstrates what people can do with such a model):

http://cocnm.computerisms.ca/index.php/Install_Ledgersmb

Now, again, this is great for an internal business tool. It may not be what you want for a high-scaling public web app. We use a different model for customer portals (with security again encapsulated in the db, but through a different way).

It's worth noting btw that PostgreSQL allows you to specify auth method by connecting host and requesting user/database, so you have a high degree of control over what sorts of connections can be enabled or disabled in such an environment in the db backend.

I like how you qualified this with "in most applications." The huge mitigation factor would be if you restrict these heavily by user by using db users as application users, to enforce permissions. This has significant tradeoffs in a number of areas (particularly in scalability) but is very much worthwhile for internal business tools.

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

Well, beyond that, locking down the database is a lot of work. I am not sure of two presuppositions in your position though, namely:

1. That it is possible to fully lock down a database. There are tradeoffs that have to be made somewhere or shortcuts that get made, or new attack vectors that might develop over time....

2. That it is possible to guarantee that one denies an attacker any control over the database to begin with. Even if you encapsulate the db behind stored procedures (which we do in LedgerSMB) you have the issue that down the road, someone adds a trigger which is vulnerable to SQL injection and even if your higher application levels are perfectly secure.

The long run approach I think is to see security as something a team should set the bar very high for, but will inherently always be a work in progress.

For example, one thing not covered in this article is what happens when you run a function as security definer, as a superuser. This means that the function may do things that may fire triggers, and now those triggers also run as superuser, so eventually you get to a point where something in the pipeline is vulnerable. Fully locking down the database means, generally, that superuser security definer functions probably should not read/write rows, but that security definer functions that do read/write rows (i.e. non-system operations) should probably be assigned to nonsuperuser owners.

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.

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.

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.

It's amazing what you can learn from the questions and answers they get.

Other than that, the knowledge is rather sporadic (books, documentations, mailing-lists, the actual source code itself, join PostgreSQL community, etc).

0 - http://www.ibm.com/developerworks/library/l-async/

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

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.

[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#...

Edit: Oh, the article is from 2009 (I'd say it was bad practice even back then though).

Has anyone gotten it to work transparently?

For stronger password hashing, you can keep your user accounts in LDAP, and have PG authenticate with LDAP.

> Common practice dictates that passwords have at least six characters and are changed frequently.