Thread: Worst case scenario of a compromised non super-user PostgreSQL user account

We are trying to determine the possible side effects of a rouge user account.

A web application requires a dedicated PostgreSQL database in which to
create tables and other database objects and manipulate data within
this single database. So I have created a database and made the
application's PostgreSQL login role be the owner of this database.
They however have no CREATEDB, CREATEROLE privileges and so on.

If this web application is compromised in one way or another such as
SQL injection (assuming the web application doesn't strictly make use
of parametrized queries). Or somehow the username and password stored
in the application's code are stolen. What extend of damage would the
hacker inflict.

I am assuming:
1) data and database objects may only be deleted, dropped or stolen
from only this single database as the given user role may only login
into this single database. New unwelcome database objects may also be
created in this database.
2) Possible denial of service attack where such user may run expensive
SELECT or data modification queries.

Are there other problems we may expect. Can they run any OS programs
or install any such tools, induce buffer overflows and so on.?

Regards,
Allan.

On 21/02/2011 3:44 PM, Allan Kamau wrote:

> Are there other problems we may expect. Can they run any OS programs
> or install any such tools, induce buffer overflows and so on.?

So long as your webapp user and database owner is a regular user
(non-superuser) without CREATE ROLE or CREATE DATABASE rights, they
should be pretty well sandboxed, though as you point out they can
attempt to affect the rest of the system by running expensive queries.

If your webapp user is a superuser and the webapp gets cracked, you're
screwed. Don't do it. Ever.

To be even safer, you should consider not even making your webapp user
the database owner. Have a different user create the database, own it,
and run the DDL to create and maintain its tables. Explicitly GRANT the
webapp user the rights it requires on the tables, views and functions it
uses, and no more. This may not be practical if your webapp likes to run
its own generated DDL during upgrades (like Rails, Drupal, etc) or lacks
any sort of documentation on what access rights it needs.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

On Mon, Feb 21, 2011 at 10:44:05AM +0300, Allan Kamau wrote:

> A web application requires a dedicated PostgreSQL database in which to
> create tables and other database objects and manipulate data within
> this single database.

Why does the web application need to create tables?

I usually prefer to have two accounts: one owns the objects, and
another that has INSERT/DELETE/UPDATE and so on permissions.

If the application is creating tables, you might want to ask yourself why.

Other than that, what others said.

A


--
Andrew Sullivan
ajs@crankycanuck.ca