{"id":8814,"date":"2016-09-02T14:51:54","date_gmt":"2016-09-02T12:51:54","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/"},"modified":"2016-09-02T14:51:54","modified_gmt":"2016-09-02T12:51:54","slug":"auditing-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/","title":{"rendered":"Auditing in PostgreSQL"},"content":{"rendered":"

Today, especially in the Pharma and Banking sectors, sooner or later you will be faced with the requirement of auditing. Detailed requirements will vary but usually at least tracking logons to the database is a must. Some companies need more information to pass their internal audits such as: Who created which objects, who fired which sql against the database, who was given which permissions and so on. In this post we’ll look at what PostgreSQL can offer here.<\/p>\n

<\/p>\n

PostgreSQL comes with a comprehensive logging system<\/a> by default. In my 9.5.4 instance there are 28 parameters related to logging:<\/p>\n

\n(postgres@[local]:5438) [postgres] > select count(*) from pg_settings where name like 'log%';\n count \n-------\n    28\n(1 row)\n\n<\/pre>\n
Not all of them are relevant when it comes to auditing but some can be used for a minimal auditing setup. For logons and loggoffs there are “log_connections” and “log_disconnections”:<\/p>\n
\n(postgres@[local]:5438) [postgres] > alter system set log_connections=on;\nALTER SYSTEM\n(postgres@[local]:5438) [postgres] > alter system set log_disconnections=on;\nALTER SYSTEM\n(postgres@[local]:5438) [postgres] > select context from pg_settings where name in ('log_dicconnections','log_connections');\n      context      \n-------------------\n superuser-backend\n(1 row)\n(postgres@[local]:5438) [postgres] > select pg_reload_conf();\n pg_reload_conf \n----------------\n t\n(1 row)\n<\/pre>\n
From now on, as soon as someone connects to or disconnects from the instance it is reported in the logfile:<\/p>\n
\n2016-09-02 10:35:56.983 CEST - 2 - 13021 - [local] - postgres@postgres LOG:  connection authorized: user=postgres database=postgres\n2016-09-02 10:36:04.820 CEST - 3 - 13021 - [local] - postgres@postgres LOG:  disconnection: session time: 0:00:07.837 user=postgres database=postgres host=[local]\n<\/pre>\n
Another parameter that might be useful for auditing is “log_statement”. When you set this to “ddl” all DDLs are logged, when you set it to “mod” all DDLs plus all statements that modify data will be logged. To log all statements there is the value of “all”.<\/p>\n
\n(postgres@[local]:5438) [postgres] > alter system set log_statement='all';\nALTER SYSTEM\n<\/pre>\n
For new session all statements will be logged from now on:<\/p>\n
\n2016-09-02 10:45:15.859 CEST - 3 - 13086 - [local] - postgres@postgres LOG:  statement: create table t ( a int );\n2016-09-02 10:46:44.064 CEST - 4 - 13098 - [local] - postgres@postgres LOG:  statement: insert into t values (1);\n2016-09-02 10:47:00.162 CEST - 5 - 13098 - [local] - postgres@postgres LOG:  statement: update t set a = 2;\n2016-09-02 10:47:10.606 CEST - 6 - 13098 - [local] - postgres@postgres LOG:  statement: delete from t;\n2016-09-02 10:47:22.012 CEST - 7 - 13098 - [local] - postgres@postgres LOG:  statement: truncate table t;\n2016-09-02 10:47:25.284 CEST - 8 - 13098 - [local] - postgres@postgres LOG:  statement: drop table t;\n<\/pre>\n
Be aware that your logfile can grow significantly if you turn this on and especially if you set the value to “all”.<\/p>\n
That’s it more or less when it comes to auditing: You can audit logons, logoffs and SQL statements. This might be sufficient for your requirements but this also might not be sufficient for requirements. What do you do if you need e.g. to audit on object level? With the default logging parameters you can not do this. But, as always in PostgreSQL, there is an extension: pgaudit<\/a>. <\/p>\n
If you want to install this extension you’ll need the PostgreSQL source code. To show the complete procedure here is a PostgreSQL setup from source. Obiously the first step is to download and extract the source code:<\/p>\n
\npostgres@pgbox:\/u01\/app\/postgres\/software\/ [PG953] cd \/u01\/app\/postgres\/software\/\npostgres@pgbox:\/u01\/app\/postgres\/software\/ [PG953] wget https:\/\/ftp.postgresql.org\/pub\/source\/v9.5.4\/postgresql-9.5.4.tar.bz2\n--2016-09-02 09:39:29--  https:\/\/ftp.postgresql.org\/pub\/source\/v9.5.4\/postgresql-9.5.4.tar.bz2\nResolving ftp.postgresql.org (ftp.postgresql.org)... 213.189.17.228, 217.196.149.55, 87.238.57.227, ...\nConnecting to ftp.postgresql.org (ftp.postgresql.org)|213.189.17.228|:443... connected.\nHTTP request sent, awaiting response... 200 OK\nLength: 18496299 (18M) [application\/x-bzip-compressed-tar]\nSaving to: \u2018postgresql-9.5.4.tar.bz2\u2019\n\n100%[==================================================================================>] 18'496'299  13.1MB\/s   in 1.3s   \n\n2016-09-02 09:39:30 (13.1 MB\/s) - \u2018postgresql-9.5.4.tar.bz2\u2019 saved [18496299\/18496299]\n\npostgres@pgbox:\/u01\/app\/postgres\/software\/ [PG953] tar -axf postgresql-9.5.4.tar.bz2 \npostgres@pgbox:\/u01\/app\/postgres\/software\/ [PG953] cd postgresql-9.5.4\n<\/pre>\n
Then do the usual configure, make and make install:<\/p>\n
\npostgres@pgbox:\/u01\/app\/postgres\/software\/ [PG953] PGHOME=\/u01\/app\/postgres\/product\/95\/db_4\npostgres@pgbox:\/u01\/app\/postgres\/software\/ [PG953] SEGSIZE=2\npostgres@pgbox:\/u01\/app\/postgres\/software\/ [PG953] BLOCKSIZE=8\npostgres@pgbox:\/u01\/app\/postgres\/software\/ [PG953] .\/configure --prefix=${PGOME} \n            --exec-prefix=${PGHOME} \n            --bindir=${PGOME}\/bin \n            --libdir=${PGOME}\/lib \n            --sysconfdir=${PGOME}\/etc \n            --includedir=${PGOME}\/include \n            --datarootdir=${PGOME}\/share \n            --datadir=${PGOME}\/share \n            --with-pgport=5432 \n            --with-perl \n            --with-python \n            --with-tcl \n            --with-openssl \n            --with-pam \n            --with-ldap \n            --with-libxml \n            --with-libxslt \n            --with-segsize=${SEGSIZE} \n            --with-blocksize=${BLOCKSIZE} \n            --with-wal-segsize=16  \n            --with-extra-version=\" dbi services build\"\npostgres@pgbox:\/u01\/app\/postgres\/software\/postgresql-9.5.4\/ [PG953] make world\npostgres@pgbox:\/u01\/app\/postgres\/software\/postgresql-9.5.4\/ [PG953] make install\npostgres@pgbox:\/u01\/app\/postgres\/software\/postgresql-9.5.4\/ [PG953] cd contrib\npostgres@pgbox:\/u01\/app\/postgres\/software\/postgresql-9.5.4\/ [PG953] make install\n<\/pre>\n
Once this is done you can continue with the installation of the pgaudit extension:<\/p>\n
\npostgres@pgbox:\/u01\/app\/postgres\/software\/postgresql-9.5.4\/ [PG953] git clone https:\/\/github.com\/pgaudit\/pgaudit.git\npostgres@pgbox:\/u01\/app\/postgres\/software\/postgresql-9.5.4\/contrib\/ [PG953] cd pgaudit\/\npostgres@pgbox:\/u01\/app\/postgres\/software\/postgresql-9.5.4\/contrib\/pgaudit\/ [PG953] make -s check\n============== creating temporary instance            ==============\n============== initializing database system           ==============\n============== starting postmaster                    ==============\nrunning on port 57736 with PID 8635\n============== creating database \"contrib_regression\" ==============\nCREATE DATABASE\nALTER DATABASE\n============== running regression test queries        ==============\ntest pgaudit                  ... ok\n============== shutting down postmaster               ==============\n============== removing temporary instance            ==============\n\n=====================\n All 1 tests passed. \n=====================\n\npostgres@pgbox:\/u01\/app\/postgres\/software\/postgresql-9.5.4\/contrib\/pgaudit\/ [PG953] make install\n\/usr\/bin\/mkdir -p '\/u01\/app\/postgres\/product\/95\/db_4\/lib'\n\/usr\/bin\/mkdir -p '\/u01\/app\/postgres\/product\/95\/db_4\/share\/extension'\n\/usr\/bin\/mkdir -p '\/u01\/app\/postgres\/product\/95\/db_4\/share\/extension'\n\/usr\/bin\/install -c -m 755  pgaudit.so '\/u01\/app\/postgres\/product\/95\/db_4\/lib\/pgaudit.so'\n\/usr\/bin\/install -c -m 644 .\/pgaudit.control '\/u01\/app\/postgres\/product\/95\/db_4\/share\/extension\/'\n\/usr\/bin\/install -c -m 644 .\/pgaudit--1.0.sql  '\/u01\/app\/postgres\/product\/95\/db_4\/share\/extension\/'\n<\/pre>\n
That’s it. Initialize a new cluster:<\/p>\n
\npostgres@pgbox:\/u01\/app\/postgres\/software\/ [PG954] initdb -D \/u02\/pgdata\/PG954 -X \/u03\/pgdata\/PG954\nThe files belonging to this database system will be owned by user \"postgres\".\nThis user must also own the server process.\n\nThe database cluster will be initialized with locales\n  COLLATE:  en_US.UTF-8\n  CTYPE:    en_US.UTF-8\n  MESSAGES: en_US.UTF-8\n  MONETARY: de_CH.UTF-8\n  NUMERIC:  de_CH.UTF-8\n  TIME:     en_US.UTF-8\nThe default database encoding has accordingly been set to \"UTF8\".\nThe default text search configuration will be set to \"english\".\n\nData page checksums are disabled.\n\ncreating directory \/u02\/pgdata\/PG954 ... ok\ncreating directory \/u03\/pgdata\/PG954 ... ok\ncreating subdirectories ... ok\nselecting default max_connections ... 100\nselecting default shared_buffers ... 128MB\nselecting dynamic shared memory implementation ... posix\ncreating configuration files ... ok\ncreating template1 database in \/u02\/pgdata\/PG954\/base\/1 ... ok\ninitializing pg_authid ... ok\ninitializing dependencies ... ok\ncreating system views ... ok\nloading system objects' descriptions ... ok\ncreating collations ... ok\ncreating conversions ... ok\ncreating dictionaries ... ok\nsetting privileges on built-in objects ... ok\ncreating information schema ... ok\nloading PL\/pgSQL server-side language ... ok\nvacuuming database template1 ... ok\ncopying template1 to template0 ... ok\ncopying template1 to postgres ... ok\nsyncing data to disk ... ok\n\nWARNING: enabling \"trust\" authentication for local connections\nYou can change this by editing pg_hba.conf or using the option -A, or\n--auth-local and --auth-host, the next time you run initdb.\n\nSuccess. You can now start the database server using:\n\n    pg_ctl -D \/u02\/pgdata\/PG954 -l logfile start\n<\/pre>\n
… and install the extension:<\/p>\n
\npostgres@pgbox:\/u02\/pgdata\/PG954\/ [PG954] psql postgres\npsql (9.5.4 dbi services build)\nType \"help\" for help.\n\n(postgres@[local]:5438) [postgres] > create extension pgaudit;\nERROR:  pgaudit must be loaded via shared_preload_libraries\nTime: 2.226 ms\n\n(postgres@[local]:5438) [postgres] > alter system set shared_preload_libraries='pgaudit';\nALTER SYSTEM\nTime: 18.236 ms\n\n##### Restart the PostgreSQL instance\n\n(postgres@[local]:5438) [postgres] > show shared_preload_libraries ;\n shared_preload_libraries \n--------------------------\n pgaudit\n(1 row)\n\nTime: 0.278 ms\n(postgres@[local]:5438) [postgres] > create extension pgaudit;\nCREATE EXTENSION\nTime: 4.688 ms\n\n(postgres@[local]:5438) [postgres] > dx\n                   List of installed extensions\n  Name   | Version |   Schema   |           Description           \n---------+---------+------------+---------------------------------\n pgaudit | 1.0     | public     | provides auditing functionality\n plpgsql | 1.0     | pg_catalog | PL\/pgSQL procedural language\n(2 rows)\n<\/pre>\n
Ready. So, what can you do with it? As the documentation<\/a> is quite well here are just a few examples.<\/p>\n
To log all statements against a role:<\/p>\n
\n(postgres@[local]:5438) [postgres] > alter system set pgaudit.log = 'ROLE';\n<\/pre>\n
Altering or creating roles from now on is reported in the logfile as:<\/p>\n
\n2016-09-02 14:50:45.432 CEST - 9 - 13353 - [local] - postgres@postgres LOG:  AUDIT: SESSION,2,1,ROLE,CREATE ROLE,,,create user uu login password ,\n2016-09-02 14:52:03.745 CEST - 16 - 13353 - [local] - postgres@postgres LOG:  AUDIT: SESSION,3,1,ROLE,ALTER ROLE,,,alter user uu CREATEDB;,\n2016-09-02 14:52:20.881 CEST - 18 - 13353 - [local] - postgres@postgres LOG:  AUDIT: SESSION,4,1,ROLE,DROP ROLE,,,drop user uu;,\n<\/pre>\n
Object level auditing can be implemented like this (check the documentation for the meaning of the pgaudit.role parameter):<\/p>\n
\n(postgres@[local]:5438) [postgres] > create user audit;\nCREATE ROLE\n(postgres@[local]:5438) [postgres] > create table taudit ( a int );\nCREATE TABLE\n(postgres@[local]:5438) [postgres] > insert into taudit values ( 1 );\nINSERT 0 1\n(postgres@[local]:5438) [postgres] > grant select,delete on taudit to audit;\nGRANT\n(postgres@[local]:5438) [postgres] > alter system set pgaudit.role='audit';\nALTER SYSTEM\n(postgres@[local]:5438) [postgres] > select pg_reload_conf();\n pg_reload_conf \n----------------\n t\n(1 row)\n<\/pre>\n
Once we touch the table:<\/p>\n
\n(postgres@[local]:5438) [postgres] > select * from taudit;\n a \n---\n 1\n(1 row)\n(postgres@[local]:5438) [postgres] > update taudit set a = 4;\n<\/pre>\n
… the audit information appears in the logfile:<\/p>\n
\n2016-09-02 14:57:10.198 CEST - 5 - 13708 - [local] - postgres@postgres LOG:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.taudit,select * from taudit;,\n2016-09-02 15:00:59.537 CEST - 9 - 13708 - [local] - postgres@postgres LOG:  AUDIT: OBJECT,2,1,WRITE,UPDATE,TABLE,public.taudit,update taudit set a = 4;,\n<\/pre>\n
Have fun with auditing …<\/p>\n","protected":false},"excerpt":{"rendered":"
Today, especially in the Pharma and Banking sectors, sooner or later you will be faced with the requirement of auditing. Detailed requirements will vary but usually at least tracking logons to the database is a must. Some companies need more information to pass their internal audits such as: Who created which objects, who fired which […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[667,77],"type_dbi":[],"class_list":["post-8814","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-audit","tag-postgresql"],"acf":[],"yoast_head":"\nAuditing in PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Auditing in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"Today, especially in the Pharma and Banking sectors, sooner or later you will be faced with the requirement of auditing. Detailed requirements will vary but usually at least tracking logons to the database is a must. Some companies need more information to pass their internal audits such as: Who created which objects, who fired which […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-09-02T12:51:54+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Auditing in PostgreSQL\",\"datePublished\":\"2016-09-02T12:51:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/\"},\"wordCount\":449,\"commentCount\":0,\"keywords\":[\"audit\",\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/\",\"name\":\"Auditing in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-09-02T12:51:54+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Auditing in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Auditing in PostgreSQL - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Auditing in PostgreSQL","og_description":"Today, especially in the Pharma and Banking sectors, sooner or later you will be faced with the requirement of auditing. Detailed requirements will vary but usually at least tracking logons to the database is a must. Some companies need more information to pass their internal audits such as: Who created which objects, who fired which […]","og_url":"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2016-09-02T12:51:54+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Auditing in PostgreSQL","datePublished":"2016-09-02T12:51:54+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/"},"wordCount":449,"commentCount":0,"keywords":["audit","PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/","name":"Auditing in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-09-02T12:51:54+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/auditing-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Auditing in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8814","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=8814"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8814\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8814"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8814"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8814"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8814"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}