AlloyDB - Connecting with strapi Applications

[srinimk]

Hi,

I am currently exploring Alloydb for our strapi (CMS applications). 

I have created an Alloydb Cluster and primary instance in us-east1 Zone. I am planning to deploy my strapi as well in the same us-east1 zone.

I tried installing my strapi in the us-east1 zone (via Cloud Run). However,  I get ERRCONNREFUSED 127.0.0.1:5432. Is it possible for me to connect to the AlloyDb directly without the need of Authproxy.?

The environment variable for connection is listed here

module.exports = ({ env }) => ({
    defaultConnection: "default",
    connection: {
      client: "postgres",
      connection:{
        host: env.DATABASE_HOST,
        port: env.DATABASE_PORT,
        database: env.DATABASE_NAME,
        user: env.DATABASE_USERNAME,
        password: env.DATABASE_PASSWORD,
        schema: env('DATABASE_SCHEMA', 'public'),
        ssl: env.bool('DATABASE_SSL', false) && {
          key: env('DATABASE_SSL_KEY', undefined),
          cert: env('DATABASE_SSL_CERT', undefined),
          ca: env('DATABASE_SSL_CA', undefined),
          capath: env('DATABASE_SSL_CAPATH', undefined),
          cipher: env('DATABASE_SSL_CIPHER', undefined),
          rejectUnauthorized: env.bool(
            'DATABASE_SSL_REJECT_UNAUTHORIZED',
            true
          ),
        },
    },
  },
});

Show More

Can you guide me on some tutorials or step by step procedure to connect to the Alloydb with or without Authproxy 

Regards

Srini

[ms4446]

While the AlloyDB Auth Proxy is the recommended method for secure connections to AlloyDB, there are alternative methods available:

1. Using the AlloyDB Auth Proxy:

• Download the AlloyDB Auth Proxy client from the official AlloyDB documentation.
• Choose the IAM principal for the Auth Proxy's authorization, which can be a service account or a user account.
• Gather connection information for the AlloyDB instance(s) you wish to connect to, including the AlloyDB endpoint, port number, and database name.
• Start the Auth Proxy client and connect your application.

2. Connecting from within the same VPC:

• If your application resides in the same VPC as your AlloyDB instance, you can connect directly using the AlloyDB endpoint and port number. However, this method exposes your database to potential security vulnerabilities and is not recommended for production environments.

3. Using Open-Source Tools:

• Several open-source tools might allow connections to AlloyDB from outside its VPC. While these tools aren't officially supported by Google Cloud, they can be configured to establish a connection. Always ensure you understand the security implications when using third-party tools.

Tutorials and Resources:

• Connecting to AlloyDB with the Auth Proxy
• Troubleshooting AlloyDB Auth Proxy Connections

For any issues or further questions about connecting to AlloyDB, consult the official AlloyDB documentation

[srinimk]

Hi,

Thanks for your immediate reply. I am trying this on our development environment. So was more interested in connecting to the AlloyDB  Instance using the private ip endpoint and port number.

However, the strapi returns ERRCONNREFUSED 127.0.0.1:5432.

Not sure where the issue is.

Note: When I created a VM instance in the same VPC , installed psql client and tried connecting it worked like a charm. 

Any help is highly appreciated

Thanks and Regards

Srini

[ms4446]

There are several potential reasons for this issue:

• The AlloyDB instance might not be running.
• The AlloyDB instance might not be accessible from your Strapi application.
• There could be a misconfiguration in Strapi's database settings.

To troubleshoot:

1. Check if AlloyDB is Running: You can verify the AlloyDB instance's status through the AlloyDB console. Alternatively, from a VM instance in the same VPC, you can use the pg_isready command, which checks the connection status to a PostgreSQL server:

   php

   pg_isready -h <private_ip_endpoint> -p <port_number>

   If the command returns "accepting connections," the AlloyDB instance is operational.

2. Test AlloyDB Accessibility: Try connecting to the AlloyDB instance using a psql client from a VM in the same VPC. A successful connection indicates that the AlloyDB instance is accessible.

3. Review Strapi's Database Configuration: Ensure that the database host, port, and database name in Strapi's configuration match those of your AlloyDB instance.

If the issue persists, please provide additional details about your setup:

• Strapi version.
• AlloyDB instance version.
• VPC and subnet details for both Strapi and AlloyDB.
• Firewall rules applied to the Strapi application and AlloyDB instance.

[srinimk]

Hi, Thank you for your email.

1. Check AlloyDB is running - It looks perfect from the VM. Refer attachment

2. Test AlloyDB Accesibility - Tried connecting with AlloyDB private end point and looks good as well.

Environment Variables

The Strapi Database connection string is provided below for ref. 

module.exports = ({ env }) => ({
    defaultConnection: "default",
    connection: {
      client: "postgres",
      connection:{
        host: env.DATABASE_HOST,
        port: env.DATABASE_PORT,
        database: env.DATABASE_NAME,
        user: env.DATABASE_USERNAME,
        password: env.DATABASE_PASSWORD,
        schema: env('DATABASE_SCHEMA', 'public'),

    },
  },
});

Strapi Version - 4.12.7

AlloyDB Instance - Postgres 14

VPC - Default Network (us-central1 region)

Please do let me know if you need more details.

Regards

Srini

[ms4446]

Based on the information you have provided, it seems that the AlloyDB instance is running and accessible, and the Strapi database configuration is correct. However, Strapi is still returning an ERRCONNREFUSED error when you try to connect to the AlloyDB instance using the private IP endpoint and port number.

There are a few possible reasons for this:

• There may be a problem with the Strapi application's firewall rules.
• There may be a problem with the AlloyDB instance's firewall rules.
• There may be a problem with the network connection between the Strapi application and the AlloyDB instance.

Troubleshooting tips:

• Check the Strapi application's firewall rules. Make sure that the firewall rules allow Strapi to connect to the AlloyDB instance on port 5432.
• Check the AlloyDB instance's firewall rules. Make sure that the firewall rules allow connections from the Strapi application's IP address on port 5432.
• Check the network connection between the Strapi application and the AlloyDB instance. You can do this by running the following command from the Strapi application directory:

telnet <private_ip_endpoint> 5432

or

nc -zv <private_ip_endpoint> 5432

If the command returns a "Destination Host Unreachable" message, then there is a problem with the network connection between the Strapi application and the AlloyDB instance.

• Try connecting to the AlloyDB instance from a different machine in the same VPC.
• Try connecting to the AlloyDB instance using a different database client, such as pgAdmin or DBeaver.
• Try restarting the Strapi application.
• Try restarting the AlloyDB instance.

Additional notes:

• When checking the firewall rules,be sure to check both the Google Cloud Console (for VPC firewall rules) and the operating system of the Strapi server (if applicable).
• When testing the database client,be sure to conduct the tests from the same environment as Strapi to ensure consistency in the testing conditions.

[srinimk]

Hi,

Thanks for your email. I just debugged and found the error. It looks like the database configuration was having issues. I fixed it and seems to be ok.

The application is able to run now. 

Will do more test and get back to you incase of any issues.

Thanks again for your time.

Regards

/Srini