Rob Sargent wrote: > On 10/2/19 5:27 PM, raf wrote: > > > > > I can't help with questions about scale but I like to give roles/users > > almost no permissions at all. i.e. They can't select, insert, update > > or delete anything. All they have permission to do is to execute stored > > functions that were installed by a role with the necessary permissions > > and they are security defining functions so the permissions of the role > > that created them apply when the functions are called. This means that > > there will never be any successful SQL injection, even if the application > > code is buggy, so it's more important for web applications, but I apply > > this method to internal systems as well. This approach might help with > > scaling because fewer users might be needed but I'm not sure. > > > > cheers, > > raf > > > How easy is it to introduce an new function call all the way up to the app > user? It's easy when you have the right tools to make it easy: i.e. you write the stored function, then run a tool to generate the python class for the result set and the python function that the client applications can then call to execute the stored function and return its results. > Does this approach preclude making use of any query generation > techniques available? Yes, it does. I'm happy to write my own plpgsql and sql. I find that usually results in faster results (mainly by reducing the temptation to process data outside the database) as well as being more secure. I once worked in a job were I couldn't go home until some program had finished and it was sucking data out of the database just to summarise it and insert the summaries. It tooks hours. I replaced it with a stored procedure that took two minutes and I started going home much earlier. Rightly or wrongly, that made me prefer prcessing data inside the database. cheers, raf
