Re: Re: user/grant - best practices handling permission in production system

On Jul 24, 2009, at 1:11 AM, Stefano Nichele wrote:

> Hi Greg,
> thanks for your suggestions. See below for my comments.
>
> Greg Stark wrote:
>> Well there isn't a way to do step 2 in one shot either.
> Maybe my explanation was not clear. Step2 means run the DDL/
> DMLscript to create and populate all the tables.
>
>> You'll have to
>> issue a CREATE statement for each object, it's no extra work to issue
>> a GRANT for each object with the specific rights the application
>> should have at that time. Think of it as an important part of the
>> process of creating a new object.
>>
> You are right but I don't like so much this approach since I'd want
> to provide the DDL/DML script and let the DBA to decide database
> name and user name (ie, I don't want to put the username in the DDL/
> DML script file).

It's not necessary that you give the DDL/DML script to anyone to run
or that you embed any user specific info in any of your DDL. An
alternative approach would be to store the DDL that you intend to be
run for others in a locked down schema and then create a function, or
functions depending on how you implement it all, with an admin role
(one that has permissions to create whatever's needed) as SECURITY
INVOKER. In this way you always maintain control of who, what, when,
and where things are created and, more importtantly, dropped.

>> Note that it's probably not necessary to grant all rights to every
>> table. Most applications have some tables that are read-only or
>> insert-only from the point of view of the application. Your system
>> will be more secure if the application does not have unnecessary
>> privileges. So thinking about what rights to grant to the application
>> for each object when it's created is not a bad thing.
>>
> You are right also about that, but maybe it's too much for me. But
> to have a real secure DB this should be taken in account.
>
>
> At the end, these are the steps
> 1. using postgres user (or another user with grant for creating
> database) create the database
> 2. using the user used in the previous step, create a new user (the
> one the webapp will use)
> 3. give to the new user all permission on the database
> 4. using the new user, create and populate the tables with the DDL/
> DML script.
>
> In this way the user is not the db owner but is the owner of all
> tables. Do you think it's acceptable ?

I don't think it's a good idea to have the webapp user creating
database objects like tables and such as it would then have the
ability to drop said objects. Picture a developer thinking they want
to log in and run some queries to check out some data in order to
decide how better to implement some data crunching application level
algorithms and thinking, "Hey! I've got the app user's login right
here!". All it would then take is them not paying attention to what
they're doing for one second and BAM! you're getting a call from the
poor sap sounding like she's about to get sick, asking you when the
last backup was...

Thinking through the necessary permissions the app user and each table
is worth it for a number of reasons. One good one, in addition to
security, is that it defines a data level access interface that can
guide application db access APIs, discouraging ad-hoc SQL in the app.
It's also another way to force yourself to think about your design
from another angle. Another way to look at permissions is that if you
give too much you're just creating the risk of more work for yourself
if someone later abuses them as you'll be the one asked to fix their
mess.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k