Node后端无法连接本地PostgreSQL数据库求助

报错信息：

Error: connect ECONNREFUSED 127.0.0.1:5400
    at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1247:16) {
  errno: -4078,
  code: 'ECONNREFUSED',
  syscall: 'connect',
  address: '127.0.0.1',
  port: 5400
}

本地pgAdmin访问地址为http://localhost:5432，但运行Node后端app.js时持续触发上述连接拒绝错误，相关代码如下：

import('express').then((express) => {
    import('got').then((g) => {
        import('pg').then((p) => {

            var cors = require('cors');
            const got = g.default
            const pg = p.default
            const app = express.default()
            app.use(cors())
            const rtr = express.Router()
            const masterRouter = express.Router()
            const colorRouter = express.Router()
            const bomRouter = express.Router()
            const userRouter = express.Router()
            const cdtRouter = express.Router()
            const historyRouter = express.Router()

            const { Pool } = pg

            const baseUrl = 'https://xxxx.com/csi-requesthandler/api/v2'
            const login = `${baseUrl}/session`
            const customers = `${baseUrl}/customers`
            const suppliers = `${baseUrl}/suppliers`
            const styles = `${baseUrl}/styles`
            const color_ways = `${baseUrl}/colorways`
            const materials = `${baseUrl}/materials`
            const boms = `${baseUrl}/apparel_boms`
            const bom_revs = `${baseUrl}/apparel_bom_revisions`
            const part_materials = `${baseUrl}/part_materials`    
            const db_user = 'admin'
            const db_password = 'admin'
            const db_host = 'localhost'
            const db_catalog = 'postgres'
            const db_port = '5400'
            if (!db_user || !db_password || !db_host || !db_catalog) {
                console.error('Database configuration params are missing from environment!')
                process.exit(-1)
            }

            const pool = new Pool({
                user: db_user,
                host: db_host,
                database: db_catalog,
                password: db_password,
                port: db_port
            })


            /**
             * 
             * @param {*} req 
             * @param {*} res 
             * @param {()} next 
             * @returns call to next
             */
            function tokenValidator(req, res, next) {
                if (!req.headers.token) {
                    return res.status(400).json({ error: "Token must required" })
                }
                req.tokenCookie = req.headers.token
                next()
            }

            rtr.use(express.json({ limit: '50mb' }))
            rtr.use('/master', tokenValidator)
            rtr.use('/master', masterRouter)
            rtr.use('/color', tokenValidator)
            rtr.use('/color', colorRouter)
            rtr.use('/bom', tokenValidator)
            rtr.use('/bom', bomRouter)
            rtr.use('/user', tokenValidator)
            rtr.use('/user', userRouter)
            rtr.use('/cdt', cdtRouter)
            rtr.use('/history', historyRouter)

            app.use('/api', rtr)



            app.listen(PORT, () => {
                console.log(`Server is Listening of port ${PORT}`)
            })

            const cdt_map = []

            rtr.get('/connection', (req, res) => {
                return res.status(200).json({ success: 'Api Connected' });

            })
               
            rtr.post('/login', (req, res) => {
                
                const rbody = req.body;
                if (!rbody.username) {
                    return res.status(400).json({ error: 'Username not specified' })
                }
                if (!rbody.password) {
                    return res.status(400).json({ error: 'Password not specified' })
                }


                pool.query(`SELECT users.id, roles.id AS role, roles.role AS role_desc  FROM users INNER JOIN roles ON roles.id = users.role WHERE LOWER(users.username) = LOWER('${rbody.username.toLowerCase().trim()}')`, (err, dbr) => {
                    if (err) {
                        return res.status(500).json({ error: 'Unable to query existance of the user', db: err })
                    }
                    if (dbr.rowCount == 0) {
                        return res.status(401).json({ error: 'User does not exist!' })
                    }
                    const uid = dbr.rows[0].id
                    const role = dbr.rows[0].role
                    const role_desc = dbr.rows[0].role_desc

                    got.post(login, { json: rbody })
                        .then((success) => {
                            const respBody = JSON.parse(success.body)
                            const cookie = { cookie: respBody.token, user_id: uid, type: role, desc: role_desc }
                            return res.contentType('application/json').send(cookie)
                        }, reject => {
                            if (reject.response.statusCode == 400) {
                                return res.status(400).json({ "error": "Invalid username or password" })
                            }

                        })
                        .catch((err) => { console.error(err) })

                })


            })
})})})

排查与解决方案 #

• 修正端口配置：代码中db_port设置为5400，但实际PostgreSQL服务运行在默认端口5432，将db_port修改为5432即可解决核心连接问题：

  const db_port = '5432'
  

• 确认PostgreSQL服务状态：检查本地PostgreSQL服务是否正常运行。Windows可通过服务管理器查看，macOS/Linux执行命令：

  systemctl status postgresql
  

• 验证数据库 credentials：确认db_user（admin）、db_password（admin）是否正确，且该用户拥有访问postgres数据库的权限。可通过pgAdmin登录验证账号有效性。
• 检查端口阻塞：确保本地防火墙或安全软件未阻止Node应用访问127.0.0.1:5432端口。
• 修复SQL注入漏洞：当前登录接口的SQL查询直接拼接用户输入，存在严重安全风险，替换为参数化查询：

  pool.query(
    `SELECT users.id, roles.id AS role, roles.role AS role_desc 
     FROM users INNER JOIN roles ON roles.id = users.role 
     WHERE LOWER(users.username) = LOWER($1)`,
    [rbody.username.toLowerCase().trim()],
    (err, dbr) => {
      // 原有处理逻辑
    }
  )
  

内容的提问来源于stack exchange，提问作者Ryan Fonseka