Solved

Creating an sp procedure in PostgreSQL database

Posted on 2014-10-05
10
343 Views
Last Modified: 2014-10-11
I'm attempting to audit a PostgreSQL database and found this blog here to accomplish this http://doublebyteblog.wordpress.com/2014/02/11/quick-guide-to-auditing-a-postgresql-database/comment-page-1/#comment-114

Everything works as per the post. The only thing missing is the query to create the sp procedure to monitor every table in the database. Towards the end the author has this


CREATE TRIGGER fr_frame_audit
AFTER INSERT OR UPDATE OR DELETE ON fr_frame
FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();

Open in new window

In the example above, I created a trigger for auditing table ‘fr_frame’ (of course you can create a sp procedure to generate these statements, if you want to generate a trigger for auditing every table in the database…

Can someone help me with creating the sp procedure to monitor every table?
0
Comment
Question by:Gerhardpet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40362273
Hi!

Look at the manual :)
https://wiki.postgresql.org/wiki/Audit_trigger

There you have the the trigger you want.

Regards,
     Tomas Helgi
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40362390
Yes I know, I have already implemented the trigger. I'm looking for help in creating the sp procedure to monitor every table.

The manual does not provide help for creating sp procedure.
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40362465
Hi!

What the author of the blog you posted is saying that you can create a SP that loops through the system catalog
and creates a audit trigger for all tables in the database or a particular schema.

http://www.postgresql.org/docs/9.1/static/view-pg-tables.html

Something like this

CREATE OR REPLACE PROCEDURE addAuditTrigger( myschema text ) 
DECLARE
  rec record;
  sql text;
  sqltrigg text;
BEGIN
 
  sql = 'select * from pg_tables where schemaname = '||myschema||'; ;
	
  for rec in EXECUTE sql loop
    sqltrigg = 'CREATE TRIGGER fr_frame_audit AFTER INSERT OR UPDATE OR DELETE ON '|| rec.schemaname||'.'||rec.tablename ||' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ';
    EXECUTE sqltrigg;
  end loop;
	  
END;
$$ LANGUAGE plpgsql;

Open in new window


Regards,
    Tomas Helgi
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:Gerhardpet
ID: 40362831
I hope you can bear with me for a bit longer. I'm just beginning to learn database functions. I know basic queries.

This created the gltrans_audit for the gltrans table which works fine
CREATE TRIGGER gltrans_audit
AFTER INSERT OR UPDATE OR DELETE ON gltrans
FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); 

Open in new window


I want to create the trigger for all tables in the public schema

I'm not sure what to add to the top row. I changed the myschema to public
CREATE OR REPLACE PROCEDURE addAuditTrigger ( public text ) 

Open in new window

but then get this error

ERROR:  syntax error at or near "PROCEDURE"
LINE 1: CREATE OR REPLACE PROCEDURE addAuditTrigger ( public text ) 

Open in new window


I hope you wont mind holding my hand a bit here :-)
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
ID: 40363143
Hi!

Sorry my mistake this should be like this

CREATE OR REPLACE FUNCTION addAuditTrigger( myschema varchar(30) ) 
RETURNS void AS $$
DECLARE
  rec record;
  sql text;
  sqltrigg text;
BEGIN
 
  sql = 'select * from pg_tables where schemaname = '||myschema||';' ;
	
  for rec in EXECUTE sql loop
    sqltrigg = 'CREATE TRIGGER fr_frame_audit AFTER INSERT OR UPDATE OR DELETE ON '|| rec.schemaname||'.'||rec.tablename ||' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ';
    EXECUTE sqltrigg;
  end loop;
	  
END;
$$ LANGUAGE plpgsql;

Open in new window


and called like this for example

 SELECT addAuditTrigger('public');

where public is the public schema where your tables are.

Be aware that you maybe don't want to have this trigger added to the system catalog.

Regards,
       Tomas Helgi
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40363458
Still not able to make it work.

I copied and pasted the code below from your post and when I execute it I don't get any errors but it does not create the triggers for any of the tables.

I'm not sure what you mean by calling like this SELECT addAuditTrigger('public');

How would that look like in one query. Sorry I'm very new at all of this.

CREATE OR REPLACE FUNCTION addAuditTrigger( public varchar(30) ) 
RETURNS void AS $$
DECLARE
  rec record;
  sql text;
  sqltrigg text;
BEGIN
 
  sql = 'select * from pg_tables where schemaname = '||myschema||';' ;
	
  for rec in EXECUTE sql loop
    sqltrigg = 'CREATE TRIGGER _audit AFTER INSERT OR UPDATE OR DELETE ON '|| rec.schemaname||'.'||rec.tablename ||' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ';
    EXECUTE sqltrigg;
  end loop;
	  
END;
$$ LANGUAGE plpgsql;

Open in new window

0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40363526
HI!

What do you get if you call this query ?
select * from pg_tables where schemaname = 'PUBLIC' ; 

Open in new window


To call/execute the trigger you can do it like this.
SELECT addAuditTrigger('public');

Open in new window

Where PUBLIC is the schemaname where your tables that you want to audit are.
It may be that the pg_tables table are under a schemaname different from what the function is created under ( I don't have access to postgresql right now ) so you will probably need to add the schemaname in front of the tablename.

Regards,
    Tomas Helgi
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 40363530
I got it now. It is working if I call the function like you said

This is how I called it
SELECT addAuditTrigger('public');

Open in new window


Learned something new. Thank you for your patients!
0
 
LVL 1

Assisted Solution

by:Gerhardpet
Gerhardpet earned 0 total points
ID: 40363532
This is my final working version. I had to add 2 more single ' to this part '''||myschema||''';' ;

Someone else figured that out for me

CREATE OR REPLACE FUNCTION addAuditTrigger( myschema varchar(30) ) 
RETURNS void AS $$
DECLARE
  rec record;
  sql text;
  sqltrigg text;
BEGIN
 
  sql = 'select * from pg_tables where schemaname = '''||myschema||''';' ;
	
  for rec in EXECUTE sql loop
    sqltrigg = 'CREATE TRIGGER fr_frame_audit AFTER INSERT OR UPDATE OR DELETE ON '|| rec.schemaname||'.'||rec.tablename ||' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ';
    EXECUTE sqltrigg;
  end loop;
	  
END;
$$ LANGUAGE plpgsql;

Open in new window


And then I called it with this query

SELECT addAuditTrigger('public');

Open in new window

0
 
LVL 1

Author Closing Comment

by:Gerhardpet
ID: 40374576
My comment is also selected as solution since that is the final working solution for me.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question