Security Definer Functions - Custom Roles
I may be overthinking this. But I have a function that I want to allow authenticated users to be able to execute without access to the underlying table. I was able to do this with Security Definer and restricting access to all except allowing execute for authenticated users. However, from my understanding security definer functions can be a risk if not carefully handled.
However, I may need to do a few of these types of functions. That being said, the risk comes since they inherit the permissions of the owner, so could I create a custom Database Role that only has access to the underlying table and create the function under that role while revoking access to all other tables and functions and then that way if somehow I did mess something up, the worst thing they would be able to do is utilize the privileges of that particular role which is only reading data from those specific tables and not all of the permissions the superuser role has?
However, I may need to do a few of these types of functions. That being said, the risk comes since they inherit the permissions of the owner, so could I create a custom Database Role that only has access to the underlying table and create the function under that role while revoking access to all other tables and functions and then that way if somehow I did mess something up, the worst thing they would be able to do is utilize the privileges of that particular role which is only reading data from those specific tables and not all of the permissions the superuser role has?
