Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For many of my tables, I've added auditing triggers (based on this wiki page). It works very well and has helped me several times figure out who changed what in the system. We have a Python/Django application that sits on top of the data and that code is tracked in Git. However, there seems to be one area of our system where changes are not tracked very well. And that's the function/triggers, etc in PostgreSQL. I wish there was a way I could add a similar audit capability to the schema as I have with the data itself.

How do DBA track these changes? Note: I'm in a position where there is more than one person with sufficient privileges to "CREATE OR REPLACE" a function, so I can't necessarily count on a person to write a script and check it into Git and I certainly can't force them. If possible, it needs to be automatic.

I've thought about writing a python script to "create" a script file and write it to a file and then programmatically commit changes to a git repo.

Another option would be to add some tables to my audit schema and just query each night and look for changes and right them to a table. Certainly could work, but not quite as nice as being able to do a diff/blame via git.

This may not be the greatest question because I'm not exactly sure what I'm looking for and there may not be an exact "right" answer, but I would like to know what people do. I've done some "googling" on the topic, but I'm not finding a lot (maybe I'm just using the wrong terms).

Finally, I'm currently using PostgreSQL 9.1.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Right now there isn't really a single wonderful option for this.

You need to make sure people use revision control properly and consistently.

Some after-the-fact checks can be used to query for unexpected procedure changes and alert if the pg_proc.prosrc column doesn't match what you have in revision control.

If necessary you can force all changes through a single person's approval. Alternately you can deny access to create and update procedures to all accounts except one - and have that account automatically apply changes that're committed to git via a hook. I don't recommend this though - it'll make operations very restrictive and make it hard to fix unexpected issues.

share|improve this answer
    
Appreciate the answer. Yeah, I'm afraid you are correct. There is not a single solution. :\ Unfortunately the company I work for uses Heroku for hosting it's PostgreSQL instances. So, there's only one user/password. And you can't create a ROLE (which is complete worthless in my opinion). So, every developer has the right to create/change a function. I can scream/preach all I want that they need to create a script and add it to version control and track it, but as you can imagine, that often gets ignored and I have no idea who changed something. –  David S Jun 6 '13 at 3:28
    
@DavidS Hopefully PostgreSQL 9.4 will have full event trigger support, allowing you to create triggers for things like function creation/replacement. It might be possible in 9.3 (soon to be released) if you can install a custom compiled C extension, but that won't do you any good if you're on Heroku. –  Craig Ringer Jun 6 '13 at 3:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.