Re: Account privileges - Mailing list pgsql-general

From Prathima Mulpuri
Subject Re: Account privileges
Date
Msg-id 1591CD2C-63F9-414C-BD48-51BB52A37C7B@gmail.com
Whole thread Raw
In response to Re: Account privileges  (Joe Conway <mail@joeconway.com>)
List pgsql-general
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



pgsql-general by date:

Previous
From: Mladen Gogala
Date:
Subject: Re: Postgres Equivalent of Oracle Package
Next
From: Abdul Mohammed
Date:
Subject: Execute command in PL/pgSQL function not executing