Re: Users, Roles and Connection Pooling

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