On 11/16/21 9:07 PM, Prathima Mulpuri wrote:
Hi all, I need some help regarding Postgres and I have checked and
tried many queries. I am working on RDS Postgres 13 and in the
process of preparing the script for auditing all the user privileges
. As a part of our auditing, I need a script to list down all the
privileges of all the users to each database and if any privilege
that I need is missing, it should automatically execute the grant
/revoke (for example account1 should have only select privileges. If
it is granted with anything else it should revoke the permissions and
if select is not granted it should grant the select privilege) .
This should be done for all the databases in an instance in 1
script. I want to use cursor to list the databases and to run the
check and execute queries using a function or a stored procedure. The
results of the script should be sent to an email.
Please share any ideas and solutions for my requirement.
I am not aware of an existing solution that does all of those things,
but you could probably build one starting with the "check_access" extension:
https://github.com/CrunchyData/crunchy_check_access
You can see examples of use of the extension here:
https://blog.crunchydata.com/blog/postgresql-defaults-and-impact-on-security-part-1
In particular, you could use check_access to enumerate all privileges
when in a known-good state, save that output somewhere as the required
baseline state (e.g. in a text file), and then compare later audit runs
against that baseline (e.g. using diff).
Automation of remediation is left as an exercise for you ;-)
HTH,
Joe
--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development
