Standard practices for granting privileges

Hello,

I have experience with Microsoft SQL Server and trying to migrate to
PostGreSQL. Wanted to reach out to community experts to guide me define or
follow best practices when granting privileges to the database users.

We have a bunch of stored procedures which will be executed by the
application users. In SQL Server, we have granted only EXECUTE privilege to
the application user. But in PostGreSQL, I discovered that application
users need to be granted privileges on tables as well which are
referred inside the stored procedures.

On reading PostGreSql documentation, I found that we can use SECURITY
DEFINER so that application users get privileges of the object owner at run
time and execute the procedure without having privileges on
underlying tables. But using the SECURITY DEFINER option conflicts with
transaction control statements. Hence the only option left is to grant
privileges to application users on the underlying tables. Which seems to me
a risky option (especially as compared to SQL Server where only EXECUTE
permission was sufficient).

Can anyone please guide me what are the best security practices to follow
under such circumstances?

Thanks in advance.

Regards,
Lokesh Vij