How do I troubleshoot issues when I connect to my Aurora PostgreSQL-Compatible DB cluster?

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Check that you configured the correct connection parameters

Complete the following steps:

1. Open the Amazon Relational Database Service (Amazon RDS) console.
2. In the navigation pane, choose Databases.
3. Select your Aurora PostgreSQL-Compatible DB cluster.
4. In the Connectivity & security tab, note the following values:
   Writer or reader endpoint
   Default port number 5432
5. Verify that you have the correct database name, username, and password.

To test your connection, run the following psql command:

psql -h your-cluster-endpoint -p 5432 -U your-username -d your-database-name

Note: Replace your-cluster-endpoint with your cluster endpoint, your-username with your database username, and your-database-name with your database name.

Test network connectivity

To verify that your client can reach the Aurora PostgreSQL-Compatible DB cluster endpoint, run the following commands.

Use telnet

Run the following telnet command:

telnet your-cluster-endpoint 5432

Note: Replace your-cluster-endpoint with your cluster endpoint.

If the connection succeeds, then the output is similar to the following one:

Trying xxx.xxx.xxx.xxx...
Connected to your-cluster-endpoint.

Use netcat

If telnet isn't available, then run the following netcat command:

nc -zv your-cluster-endpoint 5432

Note: Replace your-cluster-endpoint with your cluster endpoint.

If the connection succeeds, then the output is similar to the following one:

Connection to your-cluster-endpoint 5432 port [tcp/postgresql] succeeded!

If the preceding commands time out or fail, then you have a network connectivity issue. Check that you configured the correct security group inbound rules.

Check your security group's inbound rules

Complete the following steps:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Databases.
3. Select your Aurora PostgreSQL-Compatible DB cluster.
4. In the Connectivity & security section, note the security group name under VPC security groups.
5. Open the Amazon Virtual Private Cloud (Amazon VPC) console.
6. In the navigation pane, choose Security groups.
7. Select your security group.
8. Choose Inbound rules.
9. Confirm that there's a rule with the following settings:
   Type is PostgreSQL or Custom TCP.
   Port is 5432.
   Source is your client's IP address or appropriate CIDR block.

To use the Amazon VPC console to add an inbound rule to your security group, see Configure security group rules.

To use the AWS CLI to add an inbound rule, run the following authorize-security-group-ingress command:

aws ec2 authorize-security-group-ingress \
  --group-id your-group-id \
  --protocol tcp \
  --port 5432 \
  --cidr your-IP-address/32 \
  --region your-region

Note: Replace your-group-id with your security group ID, your-IP-address/32 with your IP address or CIDR block, and your-region with your AWS Region.

Check your VPC network ACL inbound rules

To check your network ACL rules, complete the following steps:

1. Open the Amazon VPC console.
2. In the navigation pane, choose Network ACLs.
3. Select the network ACL that's associated with your DB subnet.
4. Choose the Inbound rules tab.
5. Confirm that there's a rule with the following settings:
   Type is PostgreSQL (5432) or All Traffic.
   Source is your client's IP address range or 0.0.0.0/0.

To add a network ACL rule, see Add rules.

Check your subnet and routing configuration

To check your subnet configuration, complete the following steps:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Databases.
3. Select your Aurora PostgreSQL-Compatible DB cluster.
4. In the Connectivity & security tab, note the DB subnet group name.
5. Choose the subnet group link.
6. Verify that the subnets are in the correct Availability Zones and VPC.

To check your route table configuration, see Determine the route table for a subnet.

Check your database-level permissions

Verify that the database user has the necessary permissions to connect to the database.

Connect as the primary user

Run the following psql command:

psql -h your-cluster-endpoint -p 5432 -U primary-username -d postgres

Note: Replace your-cluster-endpoint with your cluster endpoint and primary-username with your primary username.

Check user permissions

After you connect as the primary user, run the following SQL queries to check user permissions.

To list all database users, run the following command:

SELECT usename, usesuper, usecreatedb, useconnlimit FROM pg_user ORDER BY usename;

To check specific user permissions, run the following command:

SELECT usename, usesuper, usecreatedb, useconnlimit FROM pg_user WHERE usename = 'your-username';

Note: Replace your-username with the username of the user whose permissions you want to check.

To list databases and their owners, run the following command:

SELECT datname, datowner, datacl FROM pg_database WHERE datname NOT IN ('template0', 'template1');

Create or modify user permissions

If the user doesn't exist or doesn't have sufficient permissions, then run the following commands.

To create a new user, run the following command:

CREATE USER your_username WITH PASSWORD 'your_password';

Note: Replace your_username with the username of the user that you're creating and your_password with a secure password.

To grant the user permission to connect to a database, run the following command:

GRANT CONNECT ON DATABASE your_database_name TO your_username;

Note: Replace your_database_name with your database name and your_username with the username of user who you're granting permission to.

To grant the user permission to use a schema, run the following command:

GRANT USAGE ON SCHEMA public TO your_username;

Note: Replace your_username with the username of the use who you're granting permission to.

To grant the user permission to a table, run the following command:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO your_username;

Note: Replace your_username with the username of the user who you're granting permission to.

Monitor connection quotas

Check whether your DB cluster reached its maximum connection quota.

Check current connections

Connect to your DB cluster, and then run the following SQL queries.

To check current connection count, run the following command:

SELECT count(*) as current_connections FROM pg_stat_activity WHERE state = 'active';

To check the maximum connections setting, run the following command:

SHOW max_connections;

To view active connections by user, run the following command:

SELECT usename, count(*) as connection_count FROM pg_stat_activity GROUP BY usename ORDER BY connection_count DESC;

Modify the max_connections parameter

To modify the max_connections parameter, see Modifying parameters in a DB cluster parameter group in Amazon Aurora.

Test SSL/TLS connections

Aurora PostgreSQL-Compatible supports encrypted connections. Test both encrypted and unencrypted connections.

Test without SSL

To test your connection without SSL, run the following psql command:

psql "host=your-cluster-endpoint port=5432 dbname=your-database user=your-username sslmode=disable"

Note: Replace your-cluster-endpoint with your cluster endpoint, your-database with your database name, and your-username with your username.

Test with SSL

To test your connection with SSL, run the following psql command:

psql "host=your-cluster-endpoint port=5432 dbname=your-database user=your-username sslmode=require"

Note: Replace your-cluster-endpoint.cluster with your cluster endpoint, your-database with your database name, and your-username with your username.

Download and use RDS CA certificate

To download the RDS certificate authority (CA) certificate, run the following wget command:

wget https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem

To use the certificate, run the following psql command:

psql "host=your-cluster-endpoint port=5432 dbname=your-database user=your-username sslmode=verify-full sslrootcert=global-bundle.pem"

Note: Replace your-cluster-endpoint with your cluster endpoint, your-database with your database name, and your-username with your username.

Troubleshoot IAM database authentication

If you use AWS Identity and Access Management (IAM) database authentication, then complete the following steps.

Generate authentication token

To generate an authentication token, run the following generate-db-auth-token AWS CLI command:

aws rds generate-db-auth-token \
  --hostname your-cluster-endpoint \
  --port 5432 \
  --region your-region \
  --username your-iam-username

Note: Replace your-cluster-endpoint with your cluster endpoint, your-region with your Region, and your-iam-username with your IAM database username.

Use IAM authentication to connect

Run the following psql command:

psql "host=your-cluster-endpoint port=5432 dbname=your-database user=your-iam-username password=your-generated-token"

Note: Replace your-cluster-endpoint with your cluster endpoint, your-database with your database name, and your-iam-username with your IAM-enabled username. Also, replace your-generated-token with the token that you generated.

Verify IAM policy permissions

Make sure that you attached the following policy to the IAM user or role:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "rds-db:connect"
      ],
      "Resource": [
        "arn:aws:rds-db:region:account-id:dbuser:cluster-resource-id/your-iam-username"
      ]
    }
  ]
}

Note: Replace region with your Region, account-id with your AWS account ID, cluster-resource-id with your DB cluster resource ID, and your-iam-username with your IAM database username.

To further troubleshoot your connection, see Troubleshooting connections to your RDS for PostgreSQL instance.

Related information

Control traffic to your AWS resources using security groups

Control subnet traffic with network access control lists