Security is everybody’s concern when talking about data and information, and therefore it becomes the main foundation of every database. Security means protecting your data from unauthorized access. That means only authorized users can log in to a system called authentication; a user can only do what they are authorized to do (authorization) and log the user activity (accounting). I have explained these in my main security post, PostgreSQL Database Security: What You Need To Know.
When we are talking about security, authentication is the first line of defense. PostgreSQL provides various methods of authentication, which are categorized into three categories.
- PostgreSQL Internal Authentication
- OS-based Authentication
- External Server-Based Authentication
In most cases, PostgreSQL is configured to be used with internal authentication. Therefore I have discussed all internal authentication in the previous blog post I mentioned above. In this blog, we will discuss the operating system-based authentication methods for PostgreSQL. There are three methods to do OS-Based authentication.
Looking for more information on PostgreSQL security? Watch our on-demand webinar, PostgreSQL Security Missteps and Tips, to learn more!
Ident
Ident authentication only supports TCP/IP connections. Its ident server provides a mechanism to map the client’s operating system username onto the database username. It also has the option for username mapping.
1 2 3 4 5 6 7 8 | # 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 ident # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the |
1 2 | $ psql postgres -h 127.0.0.1 -U postgres psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: Ident authentication failed for user "postgres" |
If no ident server is installed, you will need to install the ident2 on your ubuntu box or oidentd on CentOS 7. Once you have downloaded and configured the ident server, it is now time to configure PostgreSQL. It starts with creating a user map in “pg_ident.conf” file.
1 2 3 4 | # Put your actual configuration here # ---------------------------------- # MAPNAME SYSTEM-USERNAME PG-USERNAME PG_USER vagrant postgres |
Here we have mapped our system user “vagrant” user with PostgreSQL’s “postgres.” Time to login using the user vagrant.
1 2 3 4 5 | $ psql postgres -h 127.0.0.1 -U postgres psql (15devel) Type "help" for help. postgres=# |
Note: The Identification Protocol is not intended as an authorization or access control protocol.
PAM (Pluggable Authentication Modules)
PAM (Pluggable Authentication Modules) authentication works similarly to “passwords.” You’d have to create a PAM service file that should enable PAM-based authentication. The service name should be set to “PostgreSQL.”
Once the service is created, PAM can now validate user name/password pairs and optionally the connected remote hostname or IP address. The user must already exist in the database for PAM authentication to work.
1 2 3 4 5 6 | $ psql postgres -h 127.0.0.1 -U postgres Password for user postgres: 2021-08-11 13:16:38.332 UTC [13828] LOG: pam_authenticate failed: Authentication failure 2021-08-11 13:16:38.332 UTC [13828] FATAL: PAM authentication failed for user "postgres" 2021-08-11 13:16:38.332 UTC [13828] DETAIL: Connection matched pg_hba.conf line 91: "host all all 127.0.0.1/32 pam" psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: PAM authentication failed for user "postgres" |
Ensure that the PostgreSQL server supports PAM authentication. It is a compile-time option that must be set when the server binaries were built. You can check if your PostgreSQL server supports PAM authentication using the following command.
1 2 3 | $ pg_config | grep with-pam CONFIGURE = '--enable-tap-tests' '--enable-cassert' '--prefix=/usr/local/pgsql/' '--with-pam' |
In case there is no PAM server file for PostgreSQL under /etc/pam.d, you’d have to create it manually. You may choose any name for the file; however, I prefer to name it “postgresql.”
1 2 3 4 5 6 | $ /etc/pam.d/PostgreSQL @include common-auth @include common-account @include common-session @include common-password |
Since the PostgreSQL user cannot read the password files, install sssd (SSSD – System Security Services Daemon) to bypass this limitation.
1 | sudo apt-get install sssd |
Add postgresql to the “ad_gpo_map_remote_interactive” to the “/etc/sssd/sssd.conf”
1 2 | $ cat /etc/sssd/sssd.conf ad_gpo_map_remote_interactive = +postgresql |
Start sssd service, and check the status that it has properly started.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | $ sudo systemctl start sssd $ sudo systemctl status sssd sssd.service - System Security Services Daemon Loaded: loaded (/lib/systemd/system/sssd.service; enabled; vendor preset: enabled) Active: active (running) since Wed 2021-08-11 16:18:41 UTC; 12min ago Main PID: 1393 (sssd) Tasks: 2 (limit: 1071) Memory: 5.7M CGroup: /system.slice/sssd.service ├─1393 /usr/sbin/sssd -i --logger=files └─1394 /usr/libexec/sssd/sssd_be --domain shadowutils --uid 0 --gid 0 --logger=files |
Time now to configure pg_hba.conf to use the PAM authentication. We need to specify the PAM service name (pamservice) as part of authentication options. This should be the same as the file you have created in the /etc/pam.d folder, which in my case is postgresql.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 pam pamservice=postgresql # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the |
We must now reload (or restart) the PostgreSQL server. After this, you can try to login into the PostgreSQL server.
1 2 3 4 | vagrant@ubuntu-focal:~$ psql postgres -h 127.0.0.1 -U postgres psql (15devel) Type "help" for help. postgres=> |
Note
Peer
Peer authentication is “ident”ical; i.e., Very much like the ident authentication! The only subtle differences are there are no ident servers, and this method works on local connections rather than over TCP/IP.
The peer authentication provides a mechanism to map the client’s operating system username onto the database username. It also has the option for username mapping. The configuration is very similar to how we configured for ident authentication except that the authentication method is specified as “peer” instead of “ident.”
$ cat $PGDATA/pg_hba.conf
1 2 3 4 5 6 | # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer map=PG_USER # IPv4 local connections: host all all 127.0.0.1/32 trust |
1 2 3 4 5 6 7 8 9 | $ psql postgres -U postgres 2021-08-12 10:51:11.855 UTC [1976] LOG: no match in usermap "PG_USER" for user "postgres" authenticated as "vagrant" 2021-08-12 10:51:11.855 UTC [1976] FATAL: Peer authentication failed for user "postgres" 2021-08-12 10:51:11.855 UTC [1976] DETAIL: Connection matched pg_hba.conf line 89: "local all all peer map=PG_USER" psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres" |
$PGDATA/pg_hba.conf configuration will look something like this:
1 2 3 4 5 6 | $ cat $PGDATA/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer map=PG_USER # IPv4 local connections: |
$PGDATA/pg_ident.conf
1 2 3 4 | # Put your actual configuration here # ---------------------------------- # MAPNAME SYSTEM-USERNAME PG-USERNAME PG_USER vagrant postgres |
1 2 3 4 | vagrant@ubuntu-focal:~$ psql postgres -U postgres psql (15devel) Type "help" for help. postgres=> |
Conclusion
We’ve covered several different authentication methods in this blog. These basic authentication methods involve the PostgreSQL server, kernel, and the ident server; options are available natively without any major external dependencies. It is, however, important that the database is secured properly to prevent unauthorized access to the data.
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
