{"id":12175,"date":"2018-12-21T14:52:31","date_gmt":"2018-12-21T13:52:31","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/"},"modified":"2023-06-09T17:20:25","modified_gmt":"2023-06-09T15:20:25","slug":"pam-authentication-with-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/","title":{"rendered":"PAM Authentication With PostgreSQL"},"content":{"rendered":"

By Mouhamadou Diaw<\/strong><\/p>\n

PostgreSQl supports many authentication methods. The PAM authentication method operates similarly to password except that it uses PAM (Pluggable Authentication Modules) as the authentication mechanism. The user must exist in the database before PAM can be used for authentication.
\nIn this blog I will configure PAM authentication for a PostgreSQL cluster 11 running on a centos 7.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n
13<\/div>\n
14<\/div>\n
15<\/div>\n<\/td>\n
\n
\n
postgres=# <\/code>select<\/code> version();<\/code><\/div>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/code>version<\/code><\/div>\n
<\/div>\n
--------------------------------------------------------------------------------<\/code><\/div>\n
-------------------------<\/code><\/div>\n
\u00a0<\/code>PostgreSQL 11.1 <\/code>on<\/code> x86_64-pc-linux-gnu, compiled <\/code>by<\/code> gcc (GCC) 4.8.5 20150623 (R<\/code><\/div>\n
ed Hat 4.8.5-28), 64-<\/code>bit<\/code><\/div>\n
(1 row)<\/code><\/div>\n
<\/div>\n
postgres=#<\/code><\/div>\n
<\/div>\n
[root@dbi-pg-essentials ~]# cat \/etc\/centos-release<\/code><\/div>\n
CentOS Linux release 7.3.1611 (Core)<\/code><\/div>\n
[root@dbi-pg-essentials ~]# hostname<\/code><\/div>\n
dbi-pg-essentials<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

We suppose that PostgreSQL is already installed with the PAM module. This should be the case if the installation was done with yum. If you decide to install using the sources, be sure to configure with the option \u2013with-pam
\nWith the installation this should exist a service named postgresql in the \/etc\/pam.d directory. If not you have to create a service for postgresql.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n<\/td>\n
\n
\n
[postgres@dbi-pg-essentials pam.d]<\/code># pwd<\/code><\/div>\n
\/etc\/pam<\/code>.d<\/code><\/div>\n
[root@dbi-pg-essentials pam.d]<\/code># ls -l postgresql<\/code><\/div>\n
-rw-r--r--. 1 root root 71 Nov\u00a0 7 12:37 postgresql<\/code><\/div>\n
[root@dbi-pg-essentials pam.d]<\/code>#<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

The first step is then to configure PostgreSQL to accept PAM authentication. Like other authentication methods, we have to add the corresponding entries in the pg_hba.conf file<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
[postgres@dbi-pg-essentials data]$ <\/code>grep<\/code> pamservice pg_hba.conf<\/code><\/div>\n
host\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 192.168.22.0<\/code>\/24<\/code>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 pam pamservice=postgresql<\/code><\/div>\n
[postgres@dbi-pg-essentials data]$<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

We can note the option pamservice=postgresql. Don\u2019t forget to reload or to restart your cluster after modifying the pg_hba.conf file.
\nIn my case I also had to change the permissions of \/etc\/shadow file to following<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
[postgres@dbi-pg-essentials data]$ <\/code>ls<\/code> -l <\/code>\/etc\/shadow<\/code><\/div>\n
-r--r--r-- 1 root root 988 Dec 21 11:20 <\/code>\/etc\/shadow<\/code><\/div>\n
[postgres@dbi-pg-essentials data]$<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

And the configuration is done. For the test let\u2019s create a linux user named for example usrpam in the server<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n<\/td>\n
\n
\n
[root@dbi-pg-essentials ~]<\/code># useradd -m usrpam<\/code><\/div>\n
[root@dbi-pg-essentials ~]<\/code># passwd usrpam<\/code><\/div>\n
Changing password <\/code>for<\/code> user usrpam.<\/code><\/div>\n
New password:<\/code><\/div>\n
BAD PASSWORD: The password is shorter than 8 characters<\/code><\/div>\n
Retype new password:<\/code><\/div>\n
passwd<\/code>: all authentication tokens updated successfully.<\/code><\/div>\n
[root@dbi-pg-essentials ~]<\/code>#<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

As specified earlier, the user should exist in the database before PAM authentication can be used. So let\u2019s create the same user in PostgreSQL but without password<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
postgres=# <\/code>create<\/code> user<\/code> usrpam <\/code>with<\/code> login;<\/code><\/div>\n
CREATE<\/code> ROLE<\/code><\/div>\n
postgres=#<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

And now the usrpam should be able to connect from any client in the network 192.168.22.0.<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n
4<\/div>\n
5<\/div>\n
6<\/div>\n
7<\/div>\n
8<\/div>\n
9<\/div>\n
10<\/div>\n
11<\/div>\n
12<\/div>\n<\/td>\n
\n
\n
[usr2@dbi-pg-essentials_2 ~]$\u00a0 psql -h dbi-pg-essentials -U usrpam -d postgres\u00a0 <\/code><\/div>\n
Password <\/code>for<\/code> user usrpam:<\/code><\/div>\n
psql (11.1)<\/code><\/div>\n
Type <\/code>\"help\"<\/code> for<\/code> help.<\/code><\/div>\n
<\/div>\n
postgres=> <\/code>select<\/code> user;<\/code><\/div>\n
\u00a0\u00a0<\/code>user<\/code><\/div>\n
--------<\/code><\/div>\n
\u00a0<\/code>usrpam<\/code><\/div>\n
(1 row)<\/code><\/div>\n
<\/div>\n
postgres=><\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

By Mouhamadou Diaw PostgreSQl supports many authentication methods. The PAM authentication method operates similarly to password except that it uses PAM (Pluggable Authentication Modules) as the authentication mechanism. The user must exist in the database before PAM can be used for authentication. In this blog I will configure PAM authentication for a PostgreSQL cluster 11 […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,83],"tags":[1527,77,1323],"type_dbi":[],"class_list":["post-12175","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-postgresql","tag-authentication-pam","tag-postgresql","tag-postgresql-11"],"acf":[],"yoast_head":"\nPAM Authentication With PostgreSQL - dbi Blog<\/title>\n<meta name=\"description\" content=\"postgreSQL, PostgreSQL 11, Authentication PAM\" \/>\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\/pam-authentication-with-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PAM Authentication With PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"postgreSQL, PostgreSQL 11, Authentication PAM\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-12-21T13:52:31+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-06-09T15:20:25+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 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\/pam-authentication-with-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"PAM Authentication With PostgreSQL\",\"datePublished\":\"2018-12-21T13:52:31+00:00\",\"dateModified\":\"2023-06-09T15:20:25+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/\"},\"wordCount\":260,\"commentCount\":0,\"keywords\":[\"Authentication PAM\",\"PostgreSQL\",\"PostgreSQL 11\"],\"articleSection\":[\"Database Administration & Monitoring\",\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/\",\"name\":\"PAM Authentication With PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-12-21T13:52:31+00:00\",\"dateModified\":\"2023-06-09T15:20:25+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"postgreSQL, PostgreSQL 11, Authentication PAM\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PAM Authentication With 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\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PAM Authentication With PostgreSQL - dbi Blog","description":"postgreSQL, PostgreSQL 11, Authentication PAM","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\/pam-authentication-with-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"PAM Authentication With PostgreSQL","og_description":"postgreSQL, PostgreSQL 11, Authentication PAM","og_url":"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2018-12-21T13:52:31+00:00","article_modified_time":"2023-06-09T15:20:25+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"PAM Authentication With PostgreSQL","datePublished":"2018-12-21T13:52:31+00:00","dateModified":"2023-06-09T15:20:25+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/"},"wordCount":260,"commentCount":0,"keywords":["Authentication PAM","PostgreSQL","PostgreSQL 11"],"articleSection":["Database Administration & Monitoring","PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/","name":"PAM Authentication With PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-12-21T13:52:31+00:00","dateModified":"2023-06-09T15:20:25+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"postgreSQL, PostgreSQL 11, Authentication PAM","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/pam-authentication-with-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PAM Authentication With 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\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12175","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\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=12175"}],"version-history":[{"count":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12175\/revisions"}],"predecessor-version":[{"id":25797,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12175\/revisions\/25797"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12175"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12175"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}