Querying my Cloud Sql DB from my Node.js backend

Question

Recently I've been learning google cloud sql it took a little but I was able to connect my cloud sql auth proxy to my postgres client. However I'm not sure how to query or make post request to my cloud sql. Originally I was just doing

const Pool = require("pg").Pool;

const pool = new Pool({
    user: "postgres",
    password: "****",
    host: "localhost",
    port: 5432,
    database: "somedb"
});

I'm not sure how to convert this over to try and query the cloud sql db. I did try converting it and got.

const Pool = require("pg").Pool;

const pool = new Pool({
    user: "postgres",
    password: "****",
    host: "[cloud sql ip]",
    port: 5432,
    database: "[pg/gc db]"
});

I end up getting the error [pg_hba.conf rejects connection for host "[ipv4 ip]", user "postgres", database "[pg/gc db]", no encryption]. I know that the documentation has a code sample but I don't understand it and cant really find any resources on explaining it.

Edit: I am uploading files to a bucket in cloud storage which I was successfully able to do. I plan on mapping out all these files onto a webpage. However I would like to filter them by certain features so I am making a second request after I store the file. My second request will store attributes into a database that I can then relate to the files for filtering.

Gabe Weiss · Accepted Answer · 2022-07-22 17:43:33Z

1

If you're running the auth proxy from your local machine where you're running your application, then the code will be the same from your application's code perspective. You'll still connect to localhost (although you may need to connect to 127.0.0.1 depending on how you have hosts set up on the machine).

The database field will depend on how you've set up the database in Cloud SQL, but it should be the same as your local database. E.g. if you created a database named "somedb" in Cloud SQL, you don't have to change anything to connect to it in Cloud SQL. The proxy running locally will make everything behave as if you're running the database locally from the application's perspective.

Edit: This particular answer wasn't the issue they were having, but in the comments it came up that both the Proxy and SSL-only was being used, which is (generally) less recommended as it doubles up the SSL/TLS usage because the Proxy also uses generated SSL certificates to connect to Cloud SQL so the database-level SSL connectivity is a redundancy that's likely not needed. There are some edge cases where you may want both, but broadly speaking one or the other is recommended.

edited Jul 22, 2022 at 17:43

answered Jul 20, 2022 at 17:40

Gabe Weiss

3,3621 gold badge15 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

boredProjects Over a year ago

I'm pretty sure I got everything working except I run into the error saying its refusing my request because there's no encryption so I loaded up SSL stuff into an env file and called them. Unfortunately now I'm getting [ 0B080074:x509 certificate routines:X509_check_private_key:key values mismatch ]. Any suggestions? Thanks for the help by the way.

Gabe Weiss Over a year ago

Oof yeah, I always get that wrong. Do you have SSL-only connections turned on for the Cloud SQL instance? By default it's not required for Cloud SQL so that SHOULDN'T be an issue, but if you have that turned on it could be tripping up on this.

boredProjects Over a year ago

I do have it turned on and its causing the issue. I can turn it off but I've been reading it makes my data less secure. However since I'm passing through the Cloud SQL Auth Proxy and that should wrap the connection in an SSL-cert would it be an issue to turn it off?

Gabe Weiss Over a year ago

Totally depends on your risk tolerance, generally speaking. Just to confirm, if you want it to be secure, Public IP should be turned on (because otherwise you can't talk to it from outside GCP) but NO networks should be authorized. As long as that's true, and you're using the Auth Proxy it's safe to turn off SSL, yes. Because the ONLY way to talk to the instance is through the auth proxy, and you have to have a service account to do that. Behind the scenes, the auth proxy is also using SSL, so essentially you're doubling the SSL cost.

boredProjects Over a year ago

Awesome thanks for answering all my questions!

Collectives™ on Stack Overflow

Querying my Cloud Sql DB from my Node.js backend

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related