未加星标

Jason O'Donnell: pgAudit: Auditing Database Operations Part 2

字体大小 | |
[系统(linux) 所属分类 系统(linux) | 发布者 店小二03 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

In thelast blog post, pgAudit was configured to audit entire classes of statements (session auditing). Session auditing works great, but it can generate a lot of logs and not every administrator needs all that information. In this blog postpgAudit will be configured to use an auditing role to watch only specific objects.

Getting Started

This guide assumes pgAudit has already been installed on the target DB server. For more instructions on installing pgAudit, see the official documentation here .

pgAudit auditor role only supports the following commands against objects:

SELECT INSERT UPDATE DELETE Setup

First, create a role to designate as the auditing role:

CREATE ROLE auditor NOLOGIN;

Next, configure pgAudit to use that role for auditing:

ALTER SYSTEM SET pgaudit.role TO 'auditor'; SELECT pg_reload_conf();

That's it! pgAudit is now configured to use an auditing role.

Auditor Example

With pgAudit configured to use an auditing role, auditor can be assigned objects to audit.

First, lets create a test table and insert some data:

CREATE TABLE public.pgauditExample(id SERIAL, name TEXT, secret TEXT, age INT); INSERT INTO public.pgauditExample(name, secret, age) VALUES ('crunchy', 'my-secret', 30);

Next, grant the operations to the auditing role that should be monitored:

GRANT SELECT (name, secret), UPDATE (secret) ON public.pgauditExample TO auditor;

Notice how SELECT is granted to name and secret , however, auditor is also granted UPDATE on secret .

Next, trigger some audit logging on the objects that were just configured:

SELECT name FROM public.pgauditExample; SELECT secret FROM public.pgauditExample; UPDATE public.pgauditExample SET secret = 'new-secret' WHERE 'name' = 'crunchy'; SELECT age FROM public.pgauditExample;

Finally, check pg_log for the audit entries:

$ grep AUDIT postgresql-Fri.log | grep OBJECT 2016-10-12 13:54:54.836 UTC postgres postgres LOG: AUDIT: OBJECT,5,1,READ,SELECT,TABLE,public.pgauditexample,SELECT name FROM pgauditExample;,<none> 2016-10-12 13:54:54.837 UTC postgres postgres LOG: AUDIT: OBJECT,6,1,READ,SELECT,TABLE,public.pgauditexample,SELECT secret FROM pgauditExample;,<none> 2016-10-12 13:54:54.838 UTC postgres postgres LOG: AUDIT: OBJECT,7,1,WRITE,UPDATE,TABLE,public.pgauditexample,UPDATE pgauditExample SET secret = 'new-secret' WHERE 'name' = 'crunchy';,<none>

Notice how there is no audit log for the SELECT on age . The auditor role has not been granted privileges on that column, thus it does not watch the object.

Multiple Auditor Roles

In the last example pgAudit was configured to use the auditor role to watch specific objects. Although pgAudit can only be assigned one master auditor role, multiple roles can be configured to audit objects by granting them to the master role.

First, create new roles to also be used for auditing:

CREATE ROLE read_auditor NOLOGIN; CREATE ROLE write_auditor NOLOGIN; CREATE ROLE delete_auditor NOLOGIN;

Next, grant these roles to the auditor role:

GRANT read_auditor TO auditor; GRANT write_auditor TO auditor; GRANT delete_auditor TO auditor;

With the auditor role configured to use multiple roles, create a test table:

CREATE TABLE public.pgauditAuditorExample(id SERIAL, name TEXT, secret TEXT, age INT); INSERT INTO public.pgauditAuditorExample(name, secret, age) VALUES ('crunchy', 'my-secret', 30);

Next, grant the new roles access to various operations on the test table:

GRANT SELECT (name, secret, age) ON public.pgauditAuditorExample TO read_auditor; GRANT INSERT(secret), UPDATE (secret) ON public.pgauditAuditorExample TO write_auditor; GRANT DELETE ON public.pgauditAuditorExample TO delete_auditor;

With the new roles assigned to their objects to audit, trigger the audit logging:

SELECT secret FROM public.pgauditAuditorExample; INSERT INTO public.pgauditAuditorExample(name, secret, age) VALUES ('postgres', 'new-secret', 100); UPDATE public.pgauditAuditorExample SET secret = 'new-secret' WHERE name = 'crunchy'; DELETE FROM public.pgauditAuditorExample WHERE age = 100;

Finally, check pg_log for the audit entries:

$ grep AUDIT postgresql-Wed.log | grep OBJECT 2016-10-12 14:22:01.416 UTC postgres postgres LOG: AUDIT: OBJECT,4,1,READ,SELECT,TABLE,public.pgauditauditorexample,SELECT secret FROM public.pgauditAuditorExample;,<none> 2016-10-12 14:22:05.447 UTC postgres postgres LOG: AUDIT: OBJECT,5,1,WRITE,INSERT,TABLE,public.pgauditauditorexample,"INSERT INTO public.pgauditAuditorExample(name, secret, age) VALUES ('postgres', 'new-secret', 100);",<none> 2016-10-12 14:22:12.299 UTC postgres postgres LOG: AUDIT: OBJECT,6,1,WRITE,UPDATE,TABLE

本文系统(linux)相关术语:linux系统 鸟哥的linux私房菜 linux命令大全 linux操作系统

主题: RIAAUUTUTC
分页:12
转载请注明
本文标题:Jason O'Donnell: pgAudit: Auditing Database Operations Part 2
本站链接:http://www.codesec.net/view/483138.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 系统(linux) | 评论(0) | 阅读(41)