PostgreSQL local login trust, ident authentication permission Control

The logon method is similar to the sqlplusassysdba command in Oracle. The difference is that when non-installation users log on in this way, Oracle returns

The logon method is similar to the sqlplus/as sysdba command in Oracle. The difference is that when non-installation users log on in this way, Oracle returns

A few days ago, it was accidentally discovered that any OS user could obtain the PG database administrator privilege without a password. This is also a BUG. If DBA is improperly configured, it is not easy to disclose data.

You can configure pg_cmd.conf and pg_ident.conf to prohibit non-database OS users from logging on using trust, which may be useful to some students who need to support local authentication.

Prerequisites:

1. My PG database is a private database created by Linux User ithomer

2. In the database authentication method pg_mirror.conf, the local user logon authentication method is trust

# Type databaseuseraddressmethod # "local" is for Unix domain socket connections onlylocal alltrust

Problem reproduction:

1. log on to the OS as a Linux User such as homerwu (this user does not have any database permissions and is not in the same group as ithomer)

2. The homerwu user downloads the executable program of the PG client and logs on to PG through local socket authentication. the user is specified, and the password is not specified.

Psql (9.2.3) Type "help" for help. S = #

Login successful... The administrator privilege of the ithomer private database is obtained without a password.

When I thought that another major security vulnerability had occurred, I sent an email to the open-source PG community and got a reply: "This is the design of the trust certification, which is also described in the document"

The logon method is similar to the sqlplus/as sysdba command in Oracle. The difference is that when non-installation users log on in this way, Oracle returns an authentication error message, the request is rejected directly. In this way, without checking the OS user name and user group, PG passes authentication directly and feels quite insecure (the local user authentication method of the database is trust by default ).

Later, I read the PG document about authentication and changed it to the ident authentication method ("Identification Protocol" is described in RFC 1413 ), specify which OS users can log on with those PG usernames. It may be useful for users who need to log on directly in sysdba mode.

Solution:

1. modify the ident authentication file pg_ident.conf and set the OS ing between OS and PG user names in the local socket Authentication mode. If the OS user name is ithomer, you can use the PG user name ithomer to log on as the local socket authentication method. (Similarly, users not in the ing relationship are not allowed to log on. If pg_ident.conf is not set, the default PG ing relationship of PG is sameuser, that is, you can only log on with the PG user name with the same OS name)

# MAPNAME SYSTEM-USERNAME PG-USERNAME
Local_ident_user ithomer

2. Adjust the local authentication method of pg_mirror.conf to ident and map the previously set MAPNAME local_ident_user (the setting methods for different PG versions are slightly different. Please refer to the documentation)

# "Local" is for Unix domain socket connections only

Local all ident map = local_ident_user

3. Reload Database Configuration

Pg_ctl reload

4. OK. Now only the OS user ithomer is allowed to log on without a password ~

Source code for PostgreSQL client logon authentication:

Function: void ClientAuthentication (Port * port)

Source File: src \ backend \ libpg \ auth. c

PostgreSQL details: click here
PostgreSQL: click here

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

,