RPostgres error with locale encoding language issue

Question

I am trying to connect a local PostgrelSQL database from Rstudio via the RPostgres package.

con <- dbConnect(RPostgres::Postgres(),
dbname="####",
host="127.0.0.1",
port=5432,
user="postgre",
password="####")

it returns unreadable characters:

Error: ��������:  �û� "postgre" Password ��֤ʧ��

I learned it might be a local encoding language issue.

Sys.getlocale()
[1] "LC_COLLATE=Chinese (Simplified)_China.utf8;LC_CTYPE=Chinese (Simplified)_China.utf8;LC_MONETARY=Chinese (Simplified)_China.utf8;LC_NUMERIC=C;LC_TIME=Chinese (Simplified)_China.utf8"

So I try setting the locale as:

Sys.setlocale(category = "LC_ALL",locale = "English_United States.1252")

Then the error becomes:

Error: <d6><c2><c3><fc><b4><ed><ce><f3>:  <d3><U+00FB><a7> "postgre" Password <c8><cf><U+05A4><U+02A7><b0><dc>

How to solve this issue?

The PostgrelSQL database on my Windows PC is encoded in Chinese (Simplified)_China.936. and the Rstudio sessioninfo are:

R version 4.2.0 (2022-04-22 ucrt)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 10 x64 (build 19044)

Matrix products: default

locale:
[1] LC_COLLATE=zh-cn.UTF-8  LC_CTYPE=zh-cn.UTF-8    LC_MONETARY=zh-cn.UTF-8 LC_NUMERIC=C            LC_TIME=zh-cn.UTF-8    

attached base packages:
[1] graphics  grDevices utils     datasets  stats     methods   base     

other attached packages:
 [1] DBI_1.1.3       RPostgres_1.4.4 forcats_0.5.1   stringr_1.4.0   dplyr_1.0.9     purrr_0.3.4     readr_2.1.2    
 [8] tidyr_1.2.0     tibble_3.1.7    ggplot2_3.3.6   tidyverse_1.3.2