Thank you for your suggestions. I want to use pg_cron extension to schedule the weekly job to capture the privileges of all the databases. I cannot use node.js as it needs to connect from an EC2 to make the connection. I have do do everything inside the RDS only. Sent from my iPhone > On 17 Nov 2021, at 10:01 PM, Joe Conway <mail@xxxxxxxxxxxxx> wrote: > > 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
