Обсуждение: Account privileges
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 partof our auditing, I need a script to list down all the privileges of all the users to each database and if any privilegethat I need is missing, it should automatically execute the grant /revoke (for example account1 should have onlyselect privileges. If it is granted with anything else it should revoke the permissions and if select is not grantedit 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. Thank you Prathima
On Tuesday, November 16, 2021, Prathima Mulpuri <prathima.mulpuri@gmail.com> wrote:
Please share any ideas and solutions for my requirement.
Drop the “function or a stored procedure” requirement, use an external application language instead. Node.js for example.
Separate the listing of extra/missing privileges from the execution of code to fix those things.
You didn’t mention how the software is supposed to know what is the correct set of permissions. If you haven’t worked through that detail conceptually, and with some manual query execution to prove out that it works, that should be the first feature to design and implement.
David J.
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
Thank you for your suggestions. I want to use pg_cron extension to schedule the weekly job to capture the privileges ofall the databases. I cannot use node.js as it needs to connect from an EC2 to make the connection. I have do do everythinginside the RDS only. Sent from my iPhone > On 17 Nov 2021, at 10:01 PM, Joe Conway <mail@joeconway.com> 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 somewhereas 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