Check pg_ident.conf User Maps with pg_ident_file_mappings

calendar Nov 3, 2026 / · 11 min read · postgresql pg_ident.conf authentication user name maps peer authentication ident authentication configuration files postgresql 16 pg_ident_file_mappings pg_username  ·
Share on: linkedin copy

Check pg_ident.conf User Maps with pg_ident_file_mappings

A deploy ships a new pg_ident.conf. The next morning, every peer login from the application host fails with no match in usermap. The server is up, pg_hba.conf looks right, and the map file is the suspect. The pg_ident_file_mappings view lists the map lines in that file, one row per line, and its error column says what is wrong with each bad line.

Purpose and Overview

pg_ident.conf maps an external user name, such as an operating system login, to a PostgreSQL role. A map has no effect until a pg_hba.conf line names it with map=map-name in the options field. That option works for every authentication method that receives an external user name: ident, peer, gssapi, sspi, cert, and from PostgreSQL 18, oauth. By default the file is named pg_ident.conf and sits with postgresql.conf in the directory that -D or PGDATA points to. In a default installation that is the data directory. When data_directory is set to move the data somewhere else, the configuration files stay where -D points.

The file fails quietly. If ident_file points at a file that does not exist, the server still starts. The first sign of trouble is that every connection relying on map= fails with no match in usermap. A reload of a malformed file is no louder. pg_reload_conf() logs a parse error and pg_ident.conf was not reloaded, and the previously loaded maps stay resident. The server keeps running on maps that no longer match the file on disk.

A row appears for each non-empty, non-comment line in the file, with a note on whether the map could be applied. On PostgreSQL 16 and later the columns are map_number, file_name, line_number, map_name, sys_name, pg_username and error. PostgreSQL 15 has line_number, map_name, sys_name, pg_username and error, with no map_number and no file_name.

Sample Code

 1SELECT map_number, file_name, line_number, map_name, sys_name, pg_username, error
 2FROM pg_ident_file_mappings
 3ORDER BY map_number;
 4
 5-- The PostgreSQL License
 6--
 7-- Copyright (c) 2025 - 2026, pgEdge, Inc.
 8--
 9-- Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
10--
11-- IN NO EVENT SHALL pgEdge, Inc. BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF pgEdge, Inc. HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
12--
13-- pgEdge, Inc. SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN “AS IS” BASIS, AND pgEdge, Inc. HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

Before you read the view, confirm which file the server reads. The configuration and file locations query covers config_file, hba_file and ident_file in full; the short form is:

1SELECT current_setting('config_file') AS "config_file",
2       current_setting('hba_file')    AS "hba_file",
3       current_setting('ident_file')  AS "ident_file";
4
5-- Copyright 2020 Hari Sekhon. MIT License:
6-- https://github.com/HariSekhon/SQL-scripts/blob/master/LICENSE

Notes: The main query needs PostgreSQL 16 or later, because map_number and file_name first appear there. The view itself needs PostgreSQL 15 or later. Run both queries as a superuser. By default the view can be read only by superusers, and the ident_file setting carries the superuser-only flag. To see only the problem lines, add WHERE error IS NOT NULL.

Code Breakdown

The query is a single SELECT, but each column and the sort order answer a different question about the file.

The column list

map_name is the name a pg_hba.conf line points to with map=. sys_name is the detected user name of the client, and pg_username is the requested PostgreSQL user name. Read a valid row with plain names as: a client that arrives as sys_name through a pg_hba.conf line using map_name is allowed to log in as pg_username. That literal reading does not hold when sys_name starts with a slash, which makes it a regular expression, or when pg_username is all or starts with +, which stand for a set of roles.

error is NULL on a good line and holds an error message on a line that could not be processed. Rows with a non-null error mark problems in the matching lines of the file. A row for an incorrect entry usually has values only in line_number and error, so do not filter on map_name when you are hunting errors.

ORDER BY map_number

map_number is the number of the map in priority order if the line is valid, and NULL otherwise. Sorting on it lists the valid lines in priority order. Invalid lines have no map number, so find them with WHERE error IS NOT NULL rather than by where they land in the sorted output.

file_name and line_number

On 16 and later, line_number is the line number of the map in file_name, not in pg_ident.conf. The view resolves include directives, so a map pulled in from an included file shows its own filename. A row that reads line 3 belongs to whatever file file_name names. Once maps are spread across an include_dir, read the two columns together.

Disk, not memory

The view reports on the current contents of the file, not on what was last loaded by the server. A clean result means the file parses. It does not mean the running server is using those maps, because the view checks the file, not the server. After a failed reload the two differ: the view shows the edited file while the old maps stay in force.

Key pg_ident.conf Concepts

Map line and include forms

Each line that is not blank or a comment takes one of four forms: map-name system-username database-username, include file, include_if_exists file, or include_dir directory. The three include forms arrived in PostgreSQL 16.

Many-to-many mapping

An operating system user can correspond to any number of database users, and the reverse also holds. One map name can carry several lines, so one login can reach several roles and several logins can share one role. To see everything a map allows, filter the view on map_name.

all and + in the database-username field

If the database-username is all and the system-username matches, the user can log in as any existing database user. With a leading + on a role name, the operating system user can login as any user belonging to that role. Both came in PostgreSQL 16, along with leading-slash regular expressions in the user column. From 16, a literal user name that matches one of these patterns must be double-quoted.

Regex system-username and \1

When the system-username field starts with a slash, the rest of the field is treated as a regular expression. The part that matched a capture group can be referenced in the database-username field as \1. For example, a system-username of /^(.*)@example\.com$ with a database-username of \1 maps app_ro@example.com to the role app_ro. Use ^ and $ to force the match to cover the entire system user name. Without them, the pattern can match part of a longer name.

Reload, not restart

The file is read at start-up and when the main server process receives SIGHUP. Any of pg_ctl reload, pg_reload_conf() or kill -HUP picks up edits. The ident_file parameter is different: it can only be set at server start. Moving the file to a new path therefore takes a restart, while editing it in place takes only a reload.

Practical Applications

The view is most useful around a change to the file: before the reload, after a login fails, and as a standing check that notices when the file has changed.

Pre-testing an edit before pg_reload_conf()

The view helps pre-test changes to pg_ident.conf. Because it reads the file on disk, you can save the edit, run the main query with WHERE error IS NOT NULL, and reload only when it returns zero rows.

Diagnosing "no match in usermap"

Work in this order: SHOW ident_file first, then SELECT * FROM pg_ident_file_mappings. The first query catches the edit made to the wrong file. On Debian and Ubuntu, ident_file is already set to /etc/postgresql/NN/main/pg_ident.conf, so a copy in the data directory is never read. Then check the view for a row whose map_name matches the pg_hba.conf line, whose sys_name matches the client login, and whose pg_username is the role being requested. If no row has all three, that is the missing line, unless a row under the same map_name uses a /regex in sys_name or all or a +role in pg_username; test the login against those rows before adding a new one.

Catching a failed reload

When a reload fails, the server log shows pg_ident.conf was not reloaded and the old maps keep serving logins. Run the view to get the line number and the error message for each bad line. Fix the file, run the view again until no error rows are left, then reload. The view cannot confirm what the server loaded, so check the log after the second reload as well.

Change tracking in monitoring

A collector can run the main query on a schedule and store data only when ident mapping configuration changes are detected. Because the view reads the file on disk, an edit shows up in the history before anyone reloads the server.

Version Compatibility

pg_ident_file_mappings was added in PostgreSQL 15. On 15 the columns are line_number, map_name, sys_name, pg_username and error, and line_number is the line number of the map in pg_ident.conf. Older releases do not have the view.

PostgreSQL 16 allowed include files in pg_hba.conf and pg_ident.conf, and both pg_hba_file_rules and pg_ident_file_mappings now display the file name. The 16 view also has map_number, which is absent from the 15 column list. The same release aligned the pg_ident.conf user column with pg_hba.conf, adding all, + role membership and leading-slash regular expressions.

On PostgreSQL 15, use this fallback query:

 1SELECT line_number AS map_number, NULL::text AS file_name, line_number, map_name, sys_name, pg_username, error
 2FROM pg_ident_file_mappings
 3ORDER BY line_number;
 4
 5-- The PostgreSQL License
 6--
 7-- Copyright (c) 2025 - 2026, pgEdge, Inc.
 8--
 9-- Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
10--
11-- IN NO EVENT SHALL pgEdge, Inc. BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF pgEdge, Inc. HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
12--
13-- pgEdge, Inc. SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN “AS IS” BASIS, AND pgEdge, Inc. HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

It returns the same seven columns as the 16 query. line_number stands in for map_number, and file_name is a typed NULL, so one table can store output from both versions. On 15 the map_number it returns is a line number, not a priority.

Best Practices

  • Treat the output as access data — the view is superuser-only by default and lists which logins can reach which roles, so keep monitoring copies under the same control.
  • Set ident_file to an absolute path — a relative path is read against the directory postgres was started in.
  • Check for error rows before every reload — WHERE error IS NOT NULL should return zero rows before pg_reload_conf().
  • Double-quote literal names that look like patterns — on 16 and later, a user name that matches all, + or a leading slash must be quoted.

References

Posts in this series