PG17.6数据库如何将一个操作系统用户映射为数据库用户

mahan

首先我们先建立一下操作系统用户和数据库用户，然后修改一下配置文件。

[root@mytest ~]# useradd testpg

[root@mytest ~]# passwd testpg

更改用户 testpg 的密码 。

新的密码：

无效的密码： 密码是一个回文

重新输入新的密码：

passwd：所有的身份验证令牌已经成功更新。

[root@mytest ~]# su -l postgres

上一次登录： 三 10月 29 16:09:58 CST 2025 pts/0 上

[postgres@mytest ~]$ psql scott

psql (17.6)

输入 "help" 来获取帮助信息.

下面创建一个数据库用户，用户名同操作系统用户名。

scott=# create user testpg with superuser password '111';

CREATE ROLE

下面我们看一下用户映射文件的配置，内容如下，特别是最后一行配置才是重点。

[postgres@mytest data]$ cat pg_ident.conf

# PostgreSQL User Name Maps

# =========================

#

# ---------------

# Mapping Records

# ---------------

#

# Refer to the PostgreSQL documentation, chapter "Client

# Authentication" for a complete description.  A short synopsis

# follows.

#

# This file controls PostgreSQL user name mapping.  It maps external

# user names to their corresponding PostgreSQL user names.  Records

# are of the form:

#

# MAPNAME  SYSTEM-USERNAME  PG-USERNAME

#

# (The uppercase quantities must be replaced by actual values.)

#

# MAPNAME is the (otherwise freely chosen) map name that was used in

# pg_hba.conf.  SYSTEM-USERNAME is the detected user name of the

# client.  PG-USERNAME is the requested PostgreSQL user name.  The

# existence of a record specifies that SYSTEM-USERNAME may connect as

# PG-USERNAME.

#

# If SYSTEM-USERNAME starts with a slash (/), it will be treated as a

# regular expression.  Optionally this can contain a capture (a

# parenthesized subexpression).  The substring matching the capture

# will be substituted for \1 (backslash-one) if present in

# PG-USERNAME.

#

# PG-USERNAME can be "all", a user name, a group name prefixed with "+", or

# a regular expression (if it starts with a slash (/)).  If it is a regular

# expression, the substring matching with \1 has no effect.

#

# Multiple maps may be specified in this file and used by pg_hba.conf.

#

# No map names are defined in the default configuration.  If all

# system user names and PostgreSQL user names are the same, you don't

# need anything in this file.

#

# ---------------

# Include Records

# ---------------

#

# This file allows the inclusion of external files or directories holding

# more records, using the following keywords:

#

# include           FILE

# include_if_exists FILE

# include_dir       DIRECTORY

#

# FILE is the file name to include, and DIR is the directory name containing

# the file(s) to include.  Any file in a directory will be loaded if suffixed

# with ".conf".  The files of a directory are ordered by name.

# include_if_exists ignores missing files.  FILE and DIRECTORY can be

# specified as a relative or an absolute path, and can be double-quoted if

# they contain spaces.

#

# -------------------------------

# Miscellaneous

# -------------------------------

#

# This file is read on server startup and when the postmaster receives

# a SIGHUP signal.  If you edit the file on a running system, you have

# to SIGHUP the postmaster for the changes to take effect.  You can

# use "pg_ctl reload" to do that.



# Put your actual configuration here

# ----------------------------------



# MAPNAME       SYSTEM-USERNAME         PG-USERNAME

  testpgmap     testpg                   testpg

从配置文件的注释信息看，如果用户配置了此文件，用户要执行pg_ctl reload才能生效配置文件。作为开源数据库的PG，我觉得其数据库配置文件内容所写的注释说明是非常权威的，那些内容是广大开源社区参与者心血和辛勤付出，这些内容值得初学者好好研读学习。

testpgmap是一个用户映射名，内容是数据库用户和操作系统用户的对应关系，这个映射关系在其它配置文件将会用到。

下面看一下用户配置文件中所做的设置，如下所示。

[postgres@mytest data]$ cat pg_hba.conf

# PostgreSQL Client Authentication Configuration File

# ===================================================

#

# Refer to the "Client Authentication" section in the PostgreSQL

# documentation for a complete description of this file.  A short

# synopsis follows.

#

# ----------------------

# Authentication Records

# ----------------------

#

# This file controls: which hosts are allowed to connect, how clients

# are authenticated, which PostgreSQL user names they can use, which

# databases they can access.  Records take one of these forms:

#

# local         DATABASE  USER  METHOD  [OPTIONS]

# host          DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# hostssl       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# hostnossl     DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# hostgssenc    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

# hostnogssenc  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

#

# (The uppercase items must be replaced by actual values.)

#

# The first field is the connection type:

# - "local" is a Unix-domain socket

# - "host" is a TCP/IP socket (encrypted or not)

# - "hostssl" is a TCP/IP socket that is SSL-encrypted

# - "hostnossl" is a TCP/IP socket that is not SSL-encrypted

# - "hostgssenc" is a TCP/IP socket that is GSSAPI-encrypted

# - "hostnogssenc" is a TCP/IP socket that is not GSSAPI-encrypted

#

# DATABASE can be "all", "sameuser", "samerole", "replication", a

# database name, a regular expression (if it starts with a slash (/))

# or a comma-separated list thereof.  The "all" keyword does not match

# "replication".  Access to replication must be enabled in a separate

# record (see example below).

#

# USER can be "all", a user name, a group name prefixed with "+", a

# regular expression (if it starts with a slash (/)) or a comma-separated

# list thereof.  In both the DATABASE and USER fields you can also write

# a file name prefixed with "@" to include names from a separate file.

#

# ADDRESS specifies the set of hosts the record matches.  It can be a

# host name, or it is made up of an IP address and a CIDR mask that is

# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that

# specifies the number of significant bits in the mask.  A host name

# that starts with a dot (.) matches a suffix of the actual host name.

# Alternatively, you can write an IP address and netmask in separate

# columns to specify the set of hosts.  Instead of a CIDR-address, you

# can write "samehost" to match any of the server's own IP addresses,

# or "samenet" to match any address in any subnet that the server is

# directly connected to.

#

# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",

# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".

# Note that "password" sends passwords in clear text; "md5" or

# "scram-sha-256" are preferred since they send encrypted passwords.

#

# OPTIONS are a set of options for the authentication in the format

# NAME=VALUE.  The available options depend on the different

# authentication methods -- refer to the "Client Authentication"

# section in the documentation for a list of which options are

# available for which authentication methods.

#

# Database and user names containing spaces, commas, quotes and other

# special characters must be quoted.  Quoting one of the keywords

# "all", "sameuser", "samerole" or "replication" makes the name lose

# its special character, and just match a database or username with

# that name.

#

# ---------------

# Include Records

# ---------------

#

# This file allows the inclusion of external files or directories holding

# more records, using the following keywords:

#

# include           FILE

# include_if_exists FILE

# include_dir       DIRECTORY

#

# FILE is the file name to include, and DIR is the directory name containing

# the file(s) to include.  Any file in a directory will be loaded if suffixed

# with ".conf".  The files of a directory are ordered by name.

# include_if_exists ignores missing files.  FILE and DIRECTORY can be

# specified as a relative or an absolute path, and can be double-quoted if

# they contain spaces.

#

# -------------

# Miscellaneous

# -------------

#

# This file is read on server startup and when the server receives a

# SIGHUP signal.  If you edit the file on a running system, you have to

# SIGHUP the server for the changes to take effect, run "pg_ctl reload",

# or execute "SELECT pg_reload_conf()".

#

# ----------------------------------

# Put your actual configuration here

# ----------------------------------

#

# If you want to allow non-local connections, you need to add more

# "host" records.  In that case you will also need to make PostgreSQL

# listen on a non-local interface via the listen_addresses

# configuration parameter, or via the -i or -h command line switches.







# TYPE  DATABASE        USER            ADDRESS                 METHOD



# "local" is for Unix domain socket connections only

local   all             all                                     peer

local   all             all                                     peer map=testpgmap

# IPv4 local connections:

host    all             all             0.0.0.0/0            scram-sha-256

# IPv6 local connections:

host    all             all             ::1/128                 scram-sha-256

# Allow replication connections from localhost, by a user with the

# replication privilege.

local   replication     all                                     peer

host    replication     all             127.0.0.1/32            scram-sha-256

host    replication     all             ::1/128                 scram-sha-256

也就是下面这一行使用了MAP映射名

local   all             all                                     peer map=testpgmap

这样的话，切换为操作系统用户后，登录数据库时不用提供密码即可登录。

[root@mytest ~]# su -l testpg

上一次登录： 五 10月 31 10:59:40 CST 2025 pts/0 上

[testpg@mytest ~]$ psql -Utestpg -dscott

psql (17.6)

输入 "help" 来获取帮助信息.