Introduction
Security typically refers to preventing unauthorized access to your data. Consequently, this indicates that only authorized users can access the system and its data. Which user is allowed access to the database is determined through authentication. The user who can "log in" to the database system, to put it in another way.
As PostgreSQL offers a variety of authentication techniques, they can be categorized into three groups as follows.
- PostgreSQL Internal Authentication
- Operating System Authentication
- External Server Authentication
The table below shows all the supported authentication methods sorted by categories.
| PostgreSQL Internal Authentication | Operating System Authentication | External Server Authentication |
| Trust | Peer | LDAP |
| Md5 | Ident | RADIUS |
| Scram-sha-256 | PAM | SSPI |
| Reject | - | GSSAPI |
| Cert | - | - |
PAM (Pluggable Authentication Modules) is one of the authentication methods categorized in Operating System Authentication. This mechanism is used to verify each user’s identity through a process called authentication. I'll demonstrate how to set up PAM in Fujitsu Enterprise Postgres in this blog.
PAM authentication
With the exception of the fact that PAM is used as the authentication mechanism, this authentication technique functions identically to password.
PAM checks the linked remote host name or IP address as well as the user name and password pairs. Therefore, before PAM can be used for authentication purposes, we must ensure that the user exists in the database.
The following PAM configuration options are supported.
- pamservice
Specify PAM service name. In OSS PostgreSQL, "postgresql" is the default PAM service name; however, in FEP, you must manually construct the service with a meaningful name, such as "fep".
The PAM service is located at "/etc/pam.d/.". - pam_use_hostname
This option determines whether the remote IP address or the host name is provided to PAM modules through the PAM_RHOST item.
Let's now see the procedures for setting up PAM authentication in Fujitsu Enterprise Postgres.
Steps to configure PAM authentication in Fujitsu Enterprise Postgres
- Before configuring PAM authentication, make sure that the Fujitsu Enterprise Postgres server supports PAM authentication. It must have been set at compile time when the server binaries were created. Verify with the following command.
[fsepuser@pam ~]$ pg_config | grep with-pam
CONFIGURE = '--with-libraries=/file1/FEP/BUILD/buildwork/ORG/pclmods/pclmods_rhel7_64/lib:/filel/FEP/BUILD/buildwork/OUT/1ibedit/64/li
b:/filel/FEP/BUILD/buildwork/OUT/OpenLDAP/64/lib:/filel/FEP/BUILD/buildwork/OUT/UUID/64/lib:/filel/FEP/BUILD/buildwork/OUT/libxml2/64/1
ib:/filel/FEP/BUILD/buildwork/OUT/libxslt/64/lib:/filel/FEP/BUILD/buildwork/OUT/libevent/64/lib:/filel/FEP/BUILD/buildwork/OUT/OpenSSL/
64/lib:/file1/FEP/BUILD/buildwork/OUT/Kerberos5/64/lib' '--with-includes=/file1/FEP/BUILD/buildwork/ORG/pclmods/pclmods_rhel7_64/includ
e:/filel/FEP/BUILD/buildwork/OUT/libedit/64/include:/filel/FEP/BUILD/buildwork/OUT/OpenLDAP/64/include:/filel/FEP/BUILD/buildwork/OUT/U
UID/64/include:/filel/FEP/BUILD/buildwork/OUT/libxml2/64/include/libxml2:/filel/FEP/BUILD/buildwork/OUT/libxslt/64/include:/filel/FEP/B
UILD/buildwork/OSS/_changes/libiconv/include:/file1/FEP/BUILD/buildwork/OUT/libevent/64/include:/filel/FEP/BUILD/buildwork/OUT/OpenSSL/
64/include:/filel/FEP/BUILD/buildwork/OUT/Kerberos5/64/include --with-icu' '--enable-nls' '--with-pgport-27500' '--with-libedit-prefe
rred' '--with-openssl' '--with-krb-srvnam-postgres' '--with-gssapi' '--with-ldap' '--with-ossp-uuid"--with-libxml' '--with-libxslt'
--with-perl' '--with-python' '--with-tel' '--with-telconfig /usr/lib64' '--with-pam' '--with-1z4' '--enable-tap-tests' '--with-selinux'
'TCLSH=/usr/bin/telsh' 'CC-gcc 'CFLAGS=-02 -D DO CHECKLICENSE CPPFLAGS=-DLINUX_OOM_SCORE_ADJ=0 -DLINUX_OOM_ADJ' *LDFLAGS=-L/opt/rh
/11vm-toolset-10.0/root/lib64 -Wl,-R/opt/rh/llvm-toolset-10.0/root/lib64 -Wl,-rpath,S$ORIGIN/../lib'''--enable-new-dtags --prefix
=/filel/FEP/BUILD/buildwork/OUT/postgres/64'--with-11vm' LLVM_CONFIG=/opt/rh/11vm-toolset-10.0/root/usr/bin/11vm-config' CLANG=/opt *PKG_CONFIG_PATH=/opt
/rh/11vm-toolset-10.0/root/usr/lib64/pkgconfig:/opt/rh/devtoolset-8/root -I
/usr/include 'ICU_LIBS--licui18n -licuuc -licudata 'PYTHON /usr/bin/python3.6" /th/11vm-toolset-10.0/root/usr/bin/clang' /usr/lib64/pkgconfig' 'ICU_CFLAGS=-D_REENTRANT
[fsepuser@pam ~]$ - Since it is verified that FEP supports PAM, the next step is to create a service file for PAM inside of "/etc/pam.d" with a meaningful name, in this example "fep". The file includes the essential information as shown below, then save the file.
[root@pam ~]# vi /etc/pam.d/fep
auth include system-auth
account include system-auth
password include system-auth
session include system-auth - Change the permission to shadow as below. “shadow” file is located inside the “/etc”.
[root@pam etc]# chmod 444 /etc/shadow
[root@pam etc]# 11 -1rt /etc/shadow
-r--r--r--. 1 root root 2168 Oct 26 00:10 /etc/shadow
[root@pam etc]# - Modify the "pg_hba.conf" file as below. It is also possible to specify the specific user by entering the user name in the user field.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
#host all all 127.0.0.1/32 trust
#host all all 0.0.0.0/0 trust
host all all 192.168.10.0/24 pam pamservice=fep
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust - The OS user and the database user must be the same. Therefore, before using PAM for authentication, ensure that the user exists in the database.
In this example, the OS user "pamtest" was created.
[root@pam ~]# ll -lrt /home/
total 4
drwx------. 16 joy joy 4096 Oct 25 13:39 joy
drwx------. 6 fsepuser fsepuser 179 Dec 1 10:34 fsepuser
[root@pam ~]# useradd pamtest
[root@pam ~]# passwd pamtest
Changing password for user pamtest.
New password:
BAD PASSWORD: The password is shorter than 7 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@pam ~]# ll -lrt /home/
total 4
drwx------. 16 joy joy 4096 Oct 25 13:39 joy
drwx------. 6 fsepuser fsepuser 179 Dec 1 10:34 fsepuser
drwx------. 3 pamtest pamtest 78 Dec 1 10:54 pamtest
[root@pam ~]# - Now, create the database user with the same name as the OS user. Create it without any password.
[fsepuser@pam ~]$ psql postgres
psql (14.0)
Type "help" for help.
postgres= \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
fep | | {}
fsepuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# create user pamtest with login;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
fep | | {}
fsepuser | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
pamtest | | {}
postgres=# - Connect to the database with the database user "pamtest" created above.
[fsepuser@pam ~]$ psql -h 192.168.10.109 -U pamtest –d postgres
It is verified that "pamtest" user is able to log in to the database by entering the same OS user password.
Password for user pamtest:
psql (14.0)
Type "help" for help.
postgres=>
Conclusion
By leveraging PostgreSQL's effective PAM authentication features, you may improve the security of your database system by adding an extra layer of authentication and access control.
Furthermore, for organizations seeking a centralized and scalable approach to user authentication, my previous technical blog Connecting Fujitsu Enterprise Postgres to Active Directory for Authentication using LDAP will assist you in discovering the benefits and implementation of LDAP authentication in the Fujitsu Enterprise Postgres database.
leverages and extends the strength and reliability of PostgreSQL with additional enterprise features.
