PostgreSQL数据库安全——用户标识和认证

一时失言乱红尘 2022-09-12 13:45 363阅读 0赞

PostgreSQL通过用户标识和认证为系统提供最外层的安全保护措施。在客户端访问数据库资源之前，服务器首先需要通过身份认证模块来验证用户的合法身份，从而在数据库系统的前后端之间建立安全的通信信道，防止非法用户连接数据库，保证只有合法的用户才能访问数据库资源。一次完整的客户端认证过程：

1.  客户端和服务器端的Postmaster进程建立连接
2.  客户端发送请求信息到守护进程Postmaster
3.  Postmaster根据请求信息检查配置文件pg\_hba.conf是否允许该客户端连接，并把认证方式和必要信息发送到客户端
4.  客户端根据收到的不同认证方式，发送相应的认证信息给Postmaster
5.  Postmaster调用认证模块对客户端送来的认证信息进行认证。如果认证通过，初始化一个Postgres进程与客户端进程通信；否则拒绝继续会话，关闭连接

## Postmaster根据请求信息检查配置文件pg\_hba.conf是否允许该客户端连接 ##

Postmaster根据请求信息检查配置文件pg\_hba.conf是否允许该客户端连接流程在ClientAuthentication函数（src/backend/utils/init/postinit.c）  
BackgroundWorkerInitializeConnection --> InitPostgres --> PerformAuthentication(Port \*port) --> ClientAuthentication(port)  
BackgroundWorkerInitializeConnectionByOid --> InitPostgres --> PerformAuthentication(Port \*port) --> ClientAuthentication(port)  
\**PostgresMain --> InitPostgres --> PerformAuthentication(Port port) --> ClientAuthentication(port)*  
因此检查配置文件pg\_hba.conf是否允许该客户端连接流程应该是相应服务进程postgres进行处理的，即这里的PostgresMain。

/* PostgresMain postgres main loop -- all backends, interactive or otherwise start here */
    void PostgresMain(int argc, char *argv[], const char *dbname, const char *username) { 
        ...
    	/* General initialization. * NOTE: if you are tempted to add code in this vicinity, consider putting * it inside InitPostgres() instead. In particular, anything that * involves database access should be there, not here. */
    	InitPostgres(dbname, InvalidOid, username, InvalidOid, NULL, false);
    	...
    }
    void InitPostgres(const char *in_dbname, Oid dboid, const char *username,
    			 Oid useroid, char *out_dbname, bool override_allow_connections) { 
    	bool		bootstrap = IsBootstrapProcessingMode();
    	bool		am_superuser;
    	char	   *fullpath;
    	char		dbname[NAMEDATALEN];
    	elog(DEBUG3, "InitPostgres");
    	/* Add my PGPROC struct to the ProcArray. Once I have done this, I am visible to other backends! */
    	InitProcessPhase2();	
    	...
    			 
    	else
    	{ 
    		/* normal multiuser case */
    		Assert(MyProcPort != NULL);
    		PerformAuthentication(MyProcPort);
    		InitializeSessionUserId(username, useroid);
    		am_superuser = superuser();
    	}
    }

PerformAuthentication的输入参数MyProcPort是定义在src/backend/utils/init/globals.c中的全局变量。会在fork出来服务客户端的子进程调用的BackendInitialize函数中使用postmaster父进程创建的port进行初始化。

struct Port *MyProcPort;
    static void BackendInitialize(Port *port) { 
    	int			status;
    	int			ret;
    	char		remote_host[NI_MAXHOST];
    	char		remote_port[NI_MAXSERV];
    	char		remote_ps_data[NI_MAXHOST];
    	/* Save port etc. for ps status */
    	MyProcPort = port;

PerformAuthentication函数认证远程客户端，开启statement\_timeout，调用ClientAuthentication函数，关闭statement\_timeout。

/* PerformAuthentication -- authenticate a remote client * returns: nothing. Will not return at all if there's any failure. */
    static void PerformAuthentication(Port *port) { 
    	/* This should be set already, but let's make sure */
    	ClientAuthInProgress = true;	/* limit visibility of log messages */
    	/* In EXEC_BACKEND case, we didn't inherit the contents of pg_hba.conf * etcetera from the postmaster, and have to load them ourselves. * FIXME: [fork/exec] Ugh. Is there a way around this overhead? */
    #ifdef EXEC_BACKEND
    	/* load_hba() and load_ident() want to work within the PostmasterContext, * so create that if it doesn't exist (which it won't). We'll delete it * again later, in PostgresMain. */
    	if (PostmasterContext == NULL)
    		PostmasterContext = AllocSetContextCreate(TopMemoryContext, "Postmaster", ALLOCSET_DEFAULT_SIZES);
    	if (!load_hba()) { 
    		/* It makes no sense to continue if we fail to load the HBA file, since there is no way to connect to the database in this case. */
    		ereport(FATAL, (errmsg("could not load pg_hba.conf")));
    	}
    	if (!load_ident()) { 
    		/* It is ok to continue if we fail to load the IDENT file, although it * means that you cannot log in using any of the authentication * methods that need a user name mapping. load_ident() already logged * the details of error to the log. */
    	}
    #endif
    
    	/* Set up a timeout in case a buggy or malicious client fails to respond * during authentication. Since we're inside a transaction and might do * database access, we have to use the statement_timeout infrastructure. */
    	enable_timeout_after(STATEMENT_TIMEOUT, AuthenticationTimeout * 1000);
    	/* Now perform authentication exchange. */
    	ClientAuthentication(port); /* might not return, if failure */
    	/* Done with authentication. Disable the timeout, and log if needed. */
    	disable_timeout(STATEMENT_TIMEOUT, false);
    	if (Log_connections){ 
    		StringInfoData logmsg;
    		initStringInfo(&logmsg);
    		if (am_walsender)
    			appendStringInfo(&logmsg, _("replication connection authorized: user=%s"), port->user_name);
    		else
    			appendStringInfo(&logmsg, _("connection authorized: user=%s"), port->user_name);
    		if (!am_walsender)
    			appendStringInfo(&logmsg, _(" database=%s"), port->database_name);
    		if (port->application_name != NULL)
    			appendStringInfo(&logmsg, _(" application_name=%s"), port->application_name);
    #ifdef USE_SSL
    		if (port->ssl_in_use)
    			appendStringInfo(&logmsg, _(" SSL enabled (protocol=%s, cipher=%s, bits=%d, compression=%s)"), be_tls_get_version(port), be_tls_get_cipher(port), be_tls_get_cipher_bits(port), be_tls_get_compression(port) ? _("on") : _("off"));
    #endif
    #ifdef ENABLE_GSS
    		if (port->gss) { 
    			const char *princ = be_gssapi_get_princ(port);
    			if (princ)
    				appendStringInfo(&logmsg, _(" GSS (authenticated=%s, encrypted=%s, principal=%s)"), be_gssapi_get_auth(port) ? _("yes") : _("no"), be_gssapi_get_enc(port) ? _("yes") : _("no"), princ);
    			else
    				appendStringInfo(&logmsg,_(" GSS (authenticated=%s, encrypted=%s)"), be_gssapi_get_auth(port) ? _("yes") : _("no"), be_gssapi_get_enc(port) ? _("yes") : _("no"));
    		}
    #endif
    		ereport(LOG, errmsg_internal("%s", logmsg.data));
    		pfree(logmsg.data);
    	}
    	set_ps_display("startup", false);
    	ClientAuthInProgress = false;	/* client_min_messages is active now */
    }

### 加载配置文件pg\_hba.conf和pg\_ident.conf ###

typedef struct Port { 
        ...
        /* Information that needs to be held during the authentication cycle. */
    	HbaLine    *hba;
    	/* GSSAPI structures. */
    #if defined(ENABLE_GSS) || defined(ENABLE_SSPI)
    	/* If GSSAPI is supported and used on this connection, store GSSAPI * information. Even when GSSAPI is not compiled in, store a NULL pointer * to keep struct offsets the same (for extension ABI compatibility). */
    	pg_gssinfo *gss;
    #else
    	void	   *gss;
    #endif
    	/* SSL structures. */
    	bool		ssl_in_use;
    	char	   *peer_cn;
    	bool		peer_cert_valid;
    	/* OpenSSL structures. (Keep these last so that the locations of other fields are the same whether or not you build with OpenSSL.) */
    #ifdef USE_OPENSSL
    	SSL		   *ssl;
    	X509	   *peer;
    #endif
    } Port;

Port结构体存储着客户端的相关信息（如客户端主机名、端口、用户名、数据库名等），与HBA相关的结构体成员`HbaLine *hba`，load\_hba()和load\_ident()负责加载hba成员。

### ClientAuthentication ###

函数的执行流程如下：  
调用**hba\_getauthmethod**，检查客户端地址、所连接数据库、用户名在文件HBA中是否有能匹配的HBA记录。*如果能找到匹配的HBA记录，则将Port结构中的相关认证方法的字段设置为HBA记录中的参数*，同时返回状态值STATUS\_OK.

/* Client authentication starts here. If there is an error, this * function does not return and the backend process is terminated. */
    void ClientAuthentication(Port *port) { 
    	int			status = STATUS_ERROR;
    	char	   *logdetail = NULL;
    	/* Get the authentication method to use for this frontend/database * combination. Note: we do not parse the file at this point; this has * already been done elsewhere. hba.c dropped an error message into the * server logfile if parsing the hba config file failed. */
    	hba_getauthmethod(port);
    	CHECK_FOR_INTERRUPTS();

基于hba选项进行初步检测，如果编译时选择了使用SSL，在这里先要检查客户端是否已提供一个有效的证书（通过Port结构中hba字段的clientcert字段的值来判断）

/* This is the first point where we have access to the hba record for the * current connection, so perform any verifications based on the hba * options field that should be done *before* the authentication here. */
    	if (port->hba->clientcert != clientCertOff) { 
    		/* If we haven't loaded a root certificate store, fail */
    		if (!secure_loaded_verify_locations())
    			ereport(FATAL, (errcode(ERRCODE_CONFIG_FILE_ERROR), errmsg("client certificates can only be checked if a root certificate store is available")));
    		/* If we loaded a root certificate store, and if a certificate is * present on the client, then it has been verified against our root * certificate store, and the connection would have been aborted * already if it didn't verify ok. */
    		if (!port->peer_cert_valid)
    			ereport(FATAL, (errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION), errmsg("connection requires a valid client certificate")));
    	}

根据不同的认证方法，进行相应的认证过程

/* Now proceed to do the actual authentication check */
    	switch (port->hba->auth_method) { 
    		case uaReject: { 
    			/* An explicit "reject" entry in pg_hba.conf. This report exposes * the fact that there's an explicit reject entry, which is * perhaps not so desirable from a security standpoint; but the * message for an implicit reject could confuse the DBA a lot when * the true situation is a match to an explicit reject. And we * don't want to change the message for an implicit reject. As * noted below, the additional information shown here doesn't * expose anything not known to an attacker. */		
    				char		hostinfo[NI_MAXHOST];
    				const char *encryption_state;
    				pg_getnameinfo_all(&port->raddr.addr, port->raddr.salen,hostinfo, sizeof(hostinfo),NULL, 0,NI_NUMERICHOST);
    				encryption_state =
    #ifdef ENABLE_GSS
    					(port->gss && port->gss->enc) ? _("GSS encryption") :
    #endif
    #ifdef USE_SSL
    					port->ssl_in_use ? _("SSL on") :
    #endif
    					_("SSL off");
    				if (am_walsender)
    					ereport(FATAL,(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),/* translator: last %s describes encryption state */errmsg("pg_hba.conf rejects replication connection for host \"%s\", user \"%s\", %s",hostinfo, port->user_name,encryption_state)));
    				else
    					ereport(FATAL, (errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION), /* translator: last %s describes encryption state */ errmsg("pg_hba.conf rejects connection for host \"%s\", user \"%s\", database \"%s\", %s", hostinfo, port->user_name, port->database_name, encryption_state)));
    				break;
    			}
    		case uaImplicitReject: { 
    			/* No matching entry, so tell the user we fell through. * NOTE: the extra info reported here is not a security breach, * because all that info is known at the frontend and must be * assumed known to bad guys. We're merely helping out the less * clueful good guys. */
    			
    				char		hostinfo[NI_MAXHOST];
    				const char *encryption_state;
    				pg_getnameinfo_all(&port->raddr.addr, port->raddr.salen,hostinfo, sizeof(hostinfo),NULL, 0,NI_NUMERICHOST);
    				encryption_state =
    #ifdef ENABLE_GSS
    					(port->gss && port->gss->enc) ? _("GSS encryption") :
    #endif
    #ifdef USE_SSL
    					port->ssl_in_use ? _("SSL on") :
    #endif
    					_("SSL off");
    #define HOSTNAME_LOOKUP_DETAIL(port) (port->remote_hostname ? (port->remote_hostname_resolv == +1 ? errdetail_log("Client IP address resolved to \"%s\", forward lookup matches.", port->remote_hostname) : port->remote_hostname_resolv == 0 ? errdetail_log("Client IP address resolved to \"%s\", forward lookup not checked.", port->remote_hostname) : port->remote_hostname_resolv == -1 ? errdetail_log("Client IP address resolved to \"%s\", forward lookup does not match.", port->remote_hostname) : port->remote_hostname_resolv == -2 ? errdetail_log("Could not translate client host name \"%s\" to IP address: %s.", port->remote_hostname, gai_strerror(port->remote_hostname_errcode)) : 0) : (port->remote_hostname_resolv == -2 ? errdetail_log("Could not resolve client IP address to a host name: %s.", gai_strerror(port->remote_hostname_errcode)) : 0))
    				if (am_walsender)
    					ereport(FATAL,(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),/* translator: last %s describes encryption state */errmsg("no pg_hba.conf entry for replication connection from host \"%s\", user \"%s\", %s",hostinfo, port->user_name,encryption_state),HOSTNAME_LOOKUP_DETAIL(port)));
    				else
    					ereport(FATAL,(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),/* translator: last %s describes encryption state */errmsg("no pg_hba.conf entry for host \"%s\", user \"%s\", database \"%s\", %s",hostinfo, port->user_name,port->database_name,encryption_state),HOSTNAME_LOOKUP_DETAIL(port)));
    				break;
    			}
    		case uaGSS:
    #ifdef ENABLE_GSS
    			/* We might or might not have the gss workspace already */
    			if (port->gss == NULL)
    				port->gss = (pg_gssinfo *)MemoryContextAllocZero(TopMemoryContext,sizeof(pg_gssinfo));
    			port->gss->auth = true;
    			/* If GSS state was set up while enabling encryption, we can just * check the client's principal. Otherwise, ask for it. */
    			if (port->gss->enc) status = pg_GSS_checkauth(port);
    			else{ 
    				sendAuthRequest(port, AUTH_REQ_GSS, NULL, 0);
    				status = pg_GSS_recvauth(port);
    			}
    #else
    			Assert(false);
    #endif
    			break;
    
    		case uaSSPI:
    #ifdef ENABLE_SSPI
    			if (port->gss == NULL)
    				port->gss = (pg_gssinfo *)MemoryContextAllocZero(TopMemoryContext,sizeof(pg_gssinfo));
    			sendAuthRequest(port, AUTH_REQ_SSPI, NULL, 0);
    			status = pg_SSPI_recvauth(port);
    #else
    			Assert(false);
    #endif
    			break;
    
    		case uaPeer:
    #ifdef HAVE_UNIX_SOCKETS
    			status = auth_peer(port);
    #else
    			Assert(false);
    #endif
    			break;
    		case uaIdent:
    			status = ident_inet(port);
    			break;
    		case uaMD5:
    		case uaSCRAM:
    			status = CheckPWChallengeAuth(port, &logdetail);
    			break;
    		case uaPassword:
    			status = CheckPasswordAuth(port, &logdetail);
    			break;
    		case uaPAM:
    #ifdef USE_PAM
    			status = CheckPAMAuth(port, port->user_name, "");
    #else
    			Assert(false);
    #endif /* USE_PAM */
    			break;
    		case uaBSD:
    #ifdef USE_BSD_AUTH
    			status = CheckBSDAuth(port, port->user_name);
    #else
    			Assert(false);
    #endif /* USE_BSD_AUTH */
    			break;
    		case uaLDAP:
    #ifdef USE_LDAP
    			status = CheckLDAPAuth(port);
    #else
    			Assert(false);
    #endif
    			break;
    		case uaRADIUS:
    			status = CheckRADIUSAuth(port);
    			break;
    		case uaCert:
    			/* uaCert will be treated as if clientcert=verify-full (uaTrust) */
    		case uaTrust:
    			status = STATUS_OK;
    			break;
    	}
    
    	if ((status == STATUS_OK && port->hba->clientcert == clientCertFull) || port->hba->auth_method == uaCert) { 
    		/* Make sure we only check the certificate if we use the cert method * or verify-full option. */
    #ifdef USE_SSL
    		status = CheckCertAuth(port);
    #else
    		Assert(false);
    #endif
    	}
    	if (ClientAuthentication_hook)
    		(*ClientAuthentication_hook) (port, status);
    	if (status == STATUS_OK)
    		sendAuthRequest(port, AUTH_REQ_OK, NULL, 0);
    	else
    		auth_failed(port, status, logdetail);
    }

## SendAuthRequest函数 ##

SendAuthRequest函数向客户端发送认证请求