From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Prathima Mulpuri <prathima(dot)mulpuri(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Account privileges |
Date: | 2021-11-17 14:01:53 |
Message-ID: | 35b78957-8229-8cb7-ea34-0167131356c4@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2021-11-17 15:54:47 | Re: How is JSON stored in PG ? |
Previous Message | Akheel Ahmed | 2021-11-17 07:18:57 | Re: create all indexes and triggers in a schema after bulk load |