{"id":28658,"date":"2023-10-18T07:53:01","date_gmt":"2023-10-18T05:53:01","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=28658"},"modified":"2023-10-18T07:53:04","modified_gmt":"2023-10-18T05:53:04","slug":"postgresql-17-login-event-triggers","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/","title":{"rendered":"PostgreSQL 17: login event triggers"},"content":{"rendered":"\n

This is a feature I am sure a lot of people have waited for and finally it was committed for PostgreSQL 17. Back in my Oracle days it was quite common to have a trigger firing once a user logged into the database. This is e.g. useful to setup some logging or deny logins based on the day of the week or the time of the day. Starting with PostgreSQL 17 you can do the same.<\/p>\n\n\n\n

The new on login trigger in PostgreSQL is an event trigger<\/a>. In addition to ddl_command_start<\/code>, ddl_command_end<\/code>, table_rewrite<\/code> and sql_drop<\/code> an event trigger can now fire on the “login” event.<\/p>\n\n\n\n

Let’s do a simple example to see how this works. An event trigger requires a function which return “event trigger”, so we need to create this first:<\/p>\n\n\n

\ncreate or replace function deny_session_daniel()\n  returns event_trigger security definer\n  as\n$$\n  declare\n  begin\n    if ( session_user = 'daniel' )\n    then\n      raise exception 'user % is not allowed to login', 'daniel';\n    end if;\n  end\n$$ language plpgsql;  \n<\/pre><\/div>\n\n\n
This is a pretty simple and useless function: It will deny all logins from user “daniel” by raising an exception. This can of course be done more elegant by not creating this user at all or deny connections for this user in pg_hba.conf<\/a>. Anyway, for the purpose of this post, it demonstrates what can be done. The next step is to create the event trigger:<\/p>\n\n\n
\npostgres=# create event trigger my_login_trg\n  on login\n  execute function deny_session_daniel();\nCREATE EVENT TRIGGER\npostgres=# alter event trigger my_login_trg enable always;\nALTER EVENT TRIGGER\n<\/pre><\/div>\n\n\n
As this event trigger was created in the “postgres” database it only should fire there, and it should only raise an exception if the user who is trying to connect is named “daniel”:<\/p>\n\n\n
\npostgres=# \\c postgres postgres\nYou are now connected to database "postgres" as user "postgres".\npostgres=# \\c template1 postgres\nYou are now connected to database "template1" as user "postgres".\ntemplate1=# \\c postgres postgres\nYou are now connected to database "postgres" as user "postgres".\n<\/pre><\/div>\n\n\n
Doing the same as the newly created “daniel” user should raise an exception when we try connect to the “postgres” database, but not to the “template1” database:<\/p>\n\n\n
\npostgres=# create user daniel with login password 'daniel';\nCREATE ROLE\npostgres=# \\c postgres daniel\nconnection to server on socket "\/tmp\/.s.PGSQL.5432" failed: FATAL:  user daniel is not allowed to login\nCONTEXT:  PL\/pgSQL function deny_session_daniel() line 6 at RAISE\nPrevious connection kept\n<\/pre><\/div>\n\n\n
This works as expected, but you need to be careful. If there is a bug on your event trigger function this will not allow any logins at all, e.g.:<\/p>\n\n\n
\npostgres=# create or replace function deny_session_daniel()\n  returns event_trigger security definer\n  as\n$$\n  declare\n  begin\n      raise exception 'user %1 is not allowed to login', session_user;\n  end;\n$$ language plpgsql; \nCREATE FUNCTION\n<\/pre><\/div>\n\n\n
From now on, nobody will be able to connect to the “postgres” database, not even the superuser “postgres”:<\/p>\n\n\n
\npostgres=# \\c postgres daniel\nconnection to server on socket "\/tmp\/.s.PGSQL.5432" failed: FATAL:  user daniel1 is not allowed to login\nCONTEXT:  PL\/pgSQL function deny_session_daniel() line 4 at RAISE\nPrevious connection kept\npostgres=# \\c postgres postgres\nconnection to server on socket "\/tmp\/.s.PGSQL.5432" failed: FATAL:  user postgres1 is not allowed to login\nCONTEXT:  PL\/pgSQL function deny_session_daniel() line 4 at RAISE\nPrevious connection kept\n<\/pre><\/div>\n\n\n
To workaround this, you need to temporarily disable event triggers at all:<\/p>\n\n\n
\npostgres=# \\c template1 postgres\nYou are now connected to database "template1" as user "postgres".\ntemplate1=# alter system set event_triggers TO off;\nALTER SYSTEM\ntemplate1=# select pg_reload_conf();\n pg_reload_conf \n----------------\n t\n(1 row)\n\ntemplate1=# \\c postgres postgres\nYou are now connected to database "postgres" as user "postgres".\n<\/pre><\/div>\n\n\n
After that, fix the issue and enable event triggers again:<\/p>\n\n\n
\npostgres=# create or replace function deny_session_daniel()\n  returns event_trigger security definer\n  as\n$$\n  declare\n  begin\n    if ( session_user = 'daniel' )\n    then\n      raise exception 'user %1 is not allowed to login', 'daniel';\n    else \n      raise notice 'current user is %1', session_user;\n    end if;\n  end\n$$ language plpgsql;  \nCREATE FUNCTION\npostgres=# alter system set event_triggers TO on;\nALTER SYSTEM\npostgres=# select pg_reload_conf();\n pg_reload_conf \n----------------\n t\n(1 row)\n\npostgres=# \\c postgres postgres\nYou are now connected to database "postgres" as user "postgres".\npostgres=# \\c postgres daniel\nconnection to server on socket "\/tmp\/.s.PGSQL.5432" failed: FATAL:  user daniel1 is not allowed to login\nCONTEXT:  PL\/pgSQL function deny_session_daniel() line 6 at RAISE\nPrevious connection kept\n<\/pre><\/div>\n\n\n
What you also need to check is, if you are on a replica because writing anything to a replica will fail as it is read only. If it fails, then the login fails as well.<\/p>\n\n\n\n
A nice feature, thanks to all involved in this.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
This is a feature I am sure a lot of people have waited for and finally it was committed for PostgreSQL 17. Back in my Oracle days it was quite common to have a trigger firing once a user logged into the database. This is e.g. useful to setup some logging or deny logins based […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[],"type_dbi":[2749],"class_list":["post-28658","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","type-postgresql"],"acf":[],"yoast_head":"\nPostgreSQL 17: login event triggers - 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\/postgresql-17-login-event-triggers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 17: login event triggers\" \/>\n<meta property=\"og:description\" content=\"This is a feature I am sure a lot of people have waited for and finally it was committed for PostgreSQL 17. Back in my Oracle days it was quite common to have a trigger firing once a user logged into the database. This is e.g. useful to setup some logging or deny logins based […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-10-18T05:53:01+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-18T05:53:04+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=\"2 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\/postgresql-17-login-event-triggers\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 17: login event triggers\",\"datePublished\":\"2023-10-18T05:53:01+00:00\",\"dateModified\":\"2023-10-18T05:53:04+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/\"},\"wordCount\":373,\"commentCount\":0,\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/\",\"name\":\"PostgreSQL 17: login event triggers - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2023-10-18T05:53:01+00:00\",\"dateModified\":\"2023-10-18T05:53:04+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 17: login event triggers\"}]},{\"@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":"PostgreSQL 17: login event triggers - 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\/postgresql-17-login-event-triggers\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 17: login event triggers","og_description":"This is a feature I am sure a lot of people have waited for and finally it was committed for PostgreSQL 17. Back in my Oracle days it was quite common to have a trigger firing once a user logged into the database. This is e.g. useful to setup some logging or deny logins based […]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/","og_site_name":"dbi Blog","article_published_time":"2023-10-18T05:53:01+00:00","article_modified_time":"2023-10-18T05:53:04+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 17: login event triggers","datePublished":"2023-10-18T05:53:01+00:00","dateModified":"2023-10-18T05:53:04+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/"},"wordCount":373,"commentCount":0,"articleSection":["Database Administration & Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/","name":"PostgreSQL 17: login event triggers - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-10-18T05:53:01+00:00","dateModified":"2023-10-18T05:53:04+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-17-login-event-triggers\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 17: login event triggers"}]},{"@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\/28658","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=28658"}],"version-history":[{"count":11,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/28658\/revisions"}],"predecessor-version":[{"id":28669,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/28658\/revisions\/28669"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=28658"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=28658"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=28658"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=28658"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}