{"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

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":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133],"tags":[667],"class_list":["post-8814","post","type-post","status-publish","format-standard","hentry","category-database","tag-audit"],"yoast_head":"\nAuditing in PostgreSQL - dbi services | 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 services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2016-09-02T12:51:54+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\"},\"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,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"keywords\":[\"audit\"],\"articleSection\":[\"Database\"],\"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 services | Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2016-09-02T12:51:54+00:00\",\"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 services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"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\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"caption\":\"Open source Team\"},\"description\":\"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Auditing in PostgreSQL - dbi services | 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 services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2016-09-02T12:51:54+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a"},"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,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"keywords":["audit"],"articleSection":["Database"],"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 services | Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-09-02T12:51:54+00:00","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 services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","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":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","caption":"Open source Team"},"description":"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.","url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"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"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}