Обсуждение: user/grant - best practices handling permission in production system
Hi All, I have some questions for you about the best way to handle permission on a database in a production system. The final goal is to have a web application connected to the db using a single user that must run select/delete/insert/update (and maybe truncate) In my opinion that user should NOT own the db and the db itself should NOT be created using that user. Of course that user should NOT be able to create database or other users. The steps could be: 1. using postgres user (or another user with grant for creating database) create the database 2. using the user used in step 1, create the schema and populate tables with initial data 3. using the user used in the previous step, create a new user (the one the webapp will use) 4. give to the new user the grant on all database objects for select/delete/insert/update At this point the webapp should work correctly. The main missing point for me is how to perform step 4 in a simple way since it seems there is not a way to give the right grants to all db objects in one shot. What do you think about that ? What are the common practices for a production system ? thanks a lot ste
On Thu, Jul 23, 2009 at 10:09 PM, Stefano Nichele<stefano.nichele@gmail.com> wrote: > 2. using the user used in step 1, create the schema and populate tables with > At this point the webapp should work correctly. > The main missing point for me is how to perform step 4 in a simple way since > it seems there is not a way to give the right grants to all db objects in > one shot. Well there isn't a way to do step 2 in one shot either. 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. 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. -- greg http://mit.edu/~gsstark/resume.pdf
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). > 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 ? Ste
Stefano Nichele schrieb: > Hi All, > I have some questions for you about the best way to handle permission on > a database in a production system. > The final goal is to have a web application connected to the db using a > single user that must run select/delete/insert/update (and maybe truncate) > > In my opinion that user should NOT own the db and the db itself should > NOT be created using that user. Of course that user should NOT be able > to create database or other users. > > The steps could be: > 1. using postgres user (or another user with grant for creating > database) create the database > 2. using the user used in step 1, create the schema and populate tables > with initial data > 3. using the user used in the previous step, create a new user (the one > the webapp will use) > 4. give to the new user the grant on all database objects for > select/delete/insert/update I totally agree with Greg's answer but just want to give a hint for granting privileges to several objects in one shot as in step 4. pgAdmin III is giving this ability with the grant wizard ... this may help if you don't want to put all the steps in a "init script" for automatic db setup. Cheers Andy
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