PostgreSQL password security

postgres@db-172-16-3-221-> cd
postgres@db-172-16-3-221-> less .psql_history
alter role postgres encrypted password 'a_b_cpostgreHAHAHAHAHAH';
\q

postgres@db-172-16-3-221-> cd $PGDATA/pg_log
2014-10-09 09:30:53.277 CST,"postgres","digoal",36441,"[local]",5435e54c.8e59,3,"idle",2014-10-09 09:30:52 CST,2/76,0,LOG,00000,"statement: alter role postgres encrypted password 'a_b_cpostgreHAHAHAHAHAH';",,,,,,,,"exec_simple_query, postgres.c:890","psql"

同时还可能被记录到审计工具中, 例如堡垒机.

解决办法 : 

1. 使用pwd+user的md5值设置密码, 

2. 或者 创建用户时使用createuser命令行工具-W选项提示输入密码. 

例如 : 

我们看到日志中记录的密码已经是加密后的密码.

2014-10-09 09:36:31.491 CST,"postgres","postgres",36499,"127.0.0.1:10919",5435e69f.8e93,3,"idle",2014-10-09 09:36:31 CST,2/100,0,LOG,00000,"statement: CREATE ROLE newrole ENCRYPTED PASSWORD 'md5e8541c3402dc262583fc1b0980b013df' SUPERUSER CREATEDB CREATEROLE NOINHERIT LOGIN NOREPLICATION;

对应的代码 : 

src/bin/scripts/createuser.c

                if (encrypted != TRI_NO)
                {
                        char       *encrypted_password;
 
                        encrypted_password = PQencryptPassword(newpassword,
                                                                                                   newuser);
                        if (!encrypted_password)
                        {
                                fprintf(stderr, _("Password encryption failed.\n"));
                                exit(1);
                        }
                        appendStringLiteralConn(&sql, encrypted_password, conn);
                        PQfreemem(encrypted_password);
                }

src/interfaces/libpq/fe-auth.c

/*
 * PQencryptPassword -- exported routine to encrypt a password
 *
 * This is intended to be used by client applications that wish to send
 * commands like ALTER USER joe PASSWORD 'pwd'.  The password need not
 * be sent in cleartext if it is encrypted on the client side.  This is
 * good because it ensures the cleartext password won't end up in logs,
 * pg_stat displays, etc.  We export the function so that clients won't
 * be dependent on low-level details like whether the enceyption is MD5
 * or something else.
 *
 * Arguments are the cleartext password, and the SQL name of the user it
 * is for.
 *
 * Return value is a malloc'd string, or NULL if out-of-memory.  The client
 * may assume the string doesn't contain any special characters that would
 * require escaping.
 */
char *
PQencryptPassword(const char *passwd, const char *user)
{
        char       *crypt_pwd;
 
        crypt_pwd = malloc(MD5_PASSWD_LEN + 1);
        if (!crypt_pwd)
                return NULL;
 
        if (!pg_md5_encrypt(passwd, user, strlen(user), crypt_pwd))
        {
                free(crypt_pwd);
                return NULL;
        }
 
        return crypt_pwd;
}

src/backend/libpq/md5.c

/*
 * Computes MD5 checksum of "passwd" (a null-terminated string) followed
 * by "salt" (which need not be null-terminated).
 *
 * Output format is "md5" followed by a 32-hex-digit MD5 checksum.
 * Hence, the output buffer "buf" must be at least 36 bytes long.
 *
 * Returns TRUE if okay, FALSE on error (out of memory).
 */
bool
pg_md5_encrypt(const char *passwd, const char *salt, size_t salt_len,
                           char *buf)
{
        size_t          passwd_len = strlen(passwd);
 
        /* +1 here is just to avoid risk of unportable malloc(0) */
        char       *crypt_buf = malloc(passwd_len + salt_len + 1);
        bool            ret;
 
        if (!crypt_buf)
                return false;
 
        /*
         * Place salt at the end because it may be known by users trying to crack
         * the MD5 output.
         */
        memcpy(crypt_buf, passwd, passwd_len);
        memcpy(crypt_buf + passwd_len, salt, salt_len);
 
        strcpy(buf, "md5");
        ret = pg_md5_hash(crypt_buf, passwd_len + salt_len, buf + 3);
 
        free(crypt_buf);
 
        return ret;
}