Thread: Standard practices for granting privileges

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