Oracle Cloud Infrastructure Documentation

Connecting to a Database

OCI Database with PostgreSQL database system endpoints aren't directly accessible from the internet. Applications that need to connect to the database require connectivity to the VCN and private subnet of the database system.

Use one of the following methods to connect to a database in OCI Database with PostgreSQL for data management tasks:

Using a Bastion Port Forwarding Session

If you're connecting to the database using a Bastion port forwarding session, you can connect directly from a local network to the database. You don't need to configure a Compute instance to connect from.

Use the following instructions to get started:

  1. Verifying Prerequisites
  2. Installing the PostgreSQL Client
  3. Connecting to the Database
Note

Bastion sessions aren't permanently available. SSH port forwarding sessions use only one IP address at a time, and require a new session for each private IP address. For more information, see Session Types.

Verifying Prerequisites

Verify that the required IAM resources and OCI network are in place. For more information, see Getting Started with OCI Database with PostgreSQL.

You can use Bastion to securely connect to a database for data management tasks. A bastion exists in the public subnet of the same VCN that contains the database system. Bastions establish the network infrastructure needed to connect a user to a target resource in the private subnet, such as a database system.

If there isn't a bastion in the VCN, create one and ensure that it has access to the private subnet of the database system. For more information, see Allowing Network Access From a Bastion.

When you create the session, specify port 5432.

Installing the PostgreSQL Client

On a local machine, install a version of the PostgreSQL client that's compatible with the PostgreSQL version of the database system by following the instructions at https://www.postgresql.org/download/.

Connecting to the Database

Sign in to the database using the PostgreSQL client installed on a local machine.

  1. Open the navigation menu and select Databases. Under PostgreSQL, select DB Systems.
  2. (Optional) In the List scope section, under Compartment, select a compartment.
  3. Click the name of the database system.
  4. On the database system details page, note the FQDN under Connection details.
  5. Download the CA certificate from the Connection details section and save it to the following location: <parent_directory>/<dbsystem.pub>
  6. Connect to the port forwarding session.
  7. From the command line on a local machine, run the following commands to sign in to the database: 
    1. rm -rf ~/.pgpass

      This command removes the default password file, which has an incorrect password.

    2. Use this command to sign in as a database user:

      psql "sslmode=verify-full sslrootcert=<parent_directory>/<dbsystem.pub> host=<endpoint_fqdn> hostaddr=127.0.0.1 dbname=<database_name> user=<user_name>"

You're now signed in to the PostgreSQL database.

Tip

For details on running PostgreSQL client commands, see the PostgreSQL reference manual.