0

Aurora Postgres 11.8

Is there any way possible that a non-superadmin user can run pg_stat_statements_reset()?

Details:

Have to schedule pg_stat_statements_reset() on an hourly basis, since there is no internal scheduler available in Aurora Postgres 11.8, I want to go for lambda/cronjob as only the superadmin can run it so its a security risk in my environment to expose superadmin password in a lambda/cronjob. So is there any way out in my case? can there be an sp that starts execution from non-superuser and then switch user within etc?

Thanks

user3706888
  • 159
  • 2
  • 11

1 Answers1

0

The documentation for pg_stat_statements_reset says:

pg_stat_statements_reset discards statistics gathered so far by pg_stat_statements corresponding to the specified userid, dbid and queryid. If any of the parameters are not specified, the default value 0(invalid) is used for each of them and the statistics that match with other parameters will be reset. If no parameter is specified or all the specified parameters are 0(invalid), it will discard all statistics. By default, this function can only be executed by superusers. Access may be granted to others using GRANT.

Let me repeat that: Access may be granted to others using GRANT.

Andreas
  • 154,647
  • 11
  • 152
  • 247
  • Thanks for the reply, didn't work for me. doc you refer to is of v13, I am working on v11 that don't have any parameter with said function. Moreover, I have granted the following to my user but still got permission denied error GRANT EXECUTE ON FUNCTION public.pg_stat_statements_reset() TO my_user; GRANT ALL ON FUNCTION public.pg_stat_statements_reset() TO my_user; – user3706888 Nov 30 '20 at 07:50
  • @a-horse-with-no-name I'm quoting the text of a specific version of the documentation (version 13, currently the latest). In the future, the `current` version of the document may say something different, so I deliberately linked to the version I'm quoting. Reverted your change. – Andreas Nov 30 '20 at 08:22