Re: Re: user/grant - best practices handling permission in production system - Mailing list pgsql-general
From | Erik Jones |
---|---|
Subject | Re: Re: user/grant - best practices handling permission in production system |
Date | |
Msg-id | EABDEDC1-5B67-4292-B08A-33220FD0E736@engineyard.com Whole thread Raw |
In response to | Re: user/grant - best practices handling permission in production system (Stefano Nichele <stefano.nichele@gmail.com>) |
List | pgsql-general |
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
pgsql-general by date: