Re: [GENERAL] Worst case scenario of a compromised non super-user PostgreSQL user account

2011-02-21 Thread Andrew Sullivan 
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
a...@crankycanuck.ca

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Worst case scenario of a compromised non super-user PostgreSQL user account

2011-02-21 Thread Craig Ringer 

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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general