Solved

Creating an sp procedure in PostgreSQL database

Posted on 2014-10-05
10
327 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
  • 6
  • 4
10 Comments
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
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
Comment Utility
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 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
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
 
LVL 1

Author Comment

by:Gerhardpet
Comment Utility
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 24

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 total points
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 1

Author Comment

by:Gerhardpet
Comment Utility
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 24

Expert Comment

by:Tomas Helgi Johannsson
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
My comment is also selected as solution since that is the final working solution for me.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Read about achieving the basic levels of HRIS security in the workplace.
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…

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now