PostgreSQL資料庫安全——使用者辨別和認證

PostgreSQL通過使用者辨別和認證為系統提供最外層的安全保護措施。在用戶端通路資料庫資源之前，伺服器首先需要通過身份認證子產品來驗證使用者的合法身份，進而在資料庫系統的前後端之間建立安全的通信信道，防止非法使用者連接配接資料庫，保證隻有合法的使用者才能通路資料庫資源。一次完整的用戶端認證過程：

用戶端和伺服器端的Postmaster程序建立連接配接
用戶端發送請求資訊到守護程序Postmaster
Postmaster根據請求資訊檢查配置檔案pg_hba.conf是否允許該用戶端連接配接，并把認證方式和必要資訊發送到用戶端
用戶端根據收到的不同認證方式，發送相應的認證資訊給Postmaster
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);
}

PostgreSQL資料庫安全——使用者辨別和認證

Postmaster根據請求資訊檢查配置檔案pg_hba.conf是否允許該用戶端連接配接

加載配置檔案pg_hba.conf和pg_ident.conf

ClientAuthentication

SendAuthRequest函數

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

2021-2025年中國運動療法（KT）帶行業市場供需與戰略研究報告

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述