Hash generate and find tampered tables in PostgreSQL

gtk has asked for the wisdom of the Perl Monks concerning the following question:

Hi All, I am in a process of writing a perl script to hash tables in a schema to track any changes done in table definition level. This is thoroughly for the security purpose and to minimize the unauthorized changes in the DB. So the plan is to generate a Hash/Checksum key for each table and store in a temporary table after any deployment. Then generate the Hash/Checksum key value again before the next deployment and compare the previous Hash/Checksum values. Please let me know a correct way to do this in PostgreSQL. I did the same thing Oracle DB by using DBMS_CRYPTO function and couldn't able to find a way in PostgreSQL. So please suggest me a way to handle this hashing tables method and relevant functions/utilities? Thanks.

Comment on Hash generate and find tampered tables in PostgreSQL

Replies are listed 'Best First'.
Re: Hashing temporal tables in PostgreSQL by erix (Prior) on Sep 22, 2017 at 05:38 UTC
PostgreSQL has an md5 function, and I'd have a look at pgcrypto (pgcrypto is one of Postgres' contrib modules). (By the way, don't call it 'Postgre'. That's as forbidden as 'PERL'. The database is called PostgreSQL or postgres. Many people will understand 'Pg'.)	[reply]
Re^2: Hashing temporal tables in PostgreSQL by gtk (Acolyte) on Sep 22, 2017 at 05:48 UTC
Hi Erix, Thanks for the note. I have corrected the name as PostgreSQL. Yes I tried with md5() function but it seems like it is just taking the given string and created the hash value. I think it is impossible to get the meta_data hash value at once in PostgreSQL like in Oracle. Because oracle does have a function to call metadata "dbms_metadata.get_ddl()".	[reply]
Re^3: Hashing temporal tables in PostgreSQL by Corion (Patriarch) on Sep 22, 2017 at 06:28 UTC
If you want the schema information to hash it, the most generic way would be the catalog methods of DBI: `my $sth = $dbh->table_info(); my $tables = $sth->fetchall_arrayref(); print Dumper @$tables;` [download]	[reply] [d/l]
Re^4: Hashing temporal tables in PostgreSQL by gtk (Acolyte) on Sep 25, 2017 at 04:13 UTC
Re^5: Hashing temporal tables in PostgreSQL by erix (Prior) on Sep 25, 2017 at 06:25 UTC
Some notes below your chosen depth have not been shown here
Re^3: Hashing temporal tables in PostgreSQL by erix (Prior) on Sep 22, 2017 at 06:20 UTC
I don't really see the problem. That oracle get_dll() just gets you the metadata. Every database can give you metadata. An easy way in postgres is to use the views in schema information_schema. To see a list in psql: `\dv information_schema.` [download] `select table_schema , table_name , data_type from information_schema.columns where table_schema = 'public' and table_name= 'pgbench_accounts' order by ordinal_position;` [download] and use something like that to md5 (or another checksum), either as a whole or on a row-by-row basis, or both.	[reply] [d/l] [select]