1. Create a specific database role called rds_pgaudit:
CREATE ROLE rds_pgaudit;
2. Modify the DB parameter group by setting the following parameters
pgaudit.role = rds_pgaudit
rds.restrict_password_commands=1
shared_preload_libraries=pg_stat_statements,pgaudit
pgaudit.log= role
3. Reboot the RDS instance.
Validate if the parameter group is updated otherwise reboot the RDS.
4. Confirm that pgaudit is initialized by running the following command:
show shared_preload_libraries;
show pgaudit.role;
show rds.restrict_password_commands;
show pgaudit.log;
5. Create the pgaudit extension by running the following command:
CREATE EXTENSION pgaudit;
\dx
6. Confirm that pgaudit.role is set to rds_pgaudit by running the following command:
show pgaudit.role;
7. Configure the pgaudit.log parameter to audit any of the following:
ROLE audits statements related to roles and privileges, such as GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
ALL audits the following commands.
MISC audits miscellaneous commands, such as DISCARD, FETCH, CHECKPOINT, VACUUM, SET.
DDL audits all data description language (DDL) that is not included in the ROLE class.
ROLE audits statements related to roles and privileges, such as GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
FUNCTION audits function calls and DO blocks.
WRITE audits INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.
READ audits SELECT and COPY when the source is a relation or a query.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8