Connect PostgreSQL to Plotly Studio

Step-by-step PostgreSQL data connection setup

Connecting PostgreSQL requires your host address, port, database name, and login credentials. Follow these steps to connect to your PostgreSQL data and visualize it with Plotly Studio:

Step 1: Confirm your PostgreSQL instance is running

PostgreSQL can be running locally on your machine, on a private server, or as a managed cloud service through AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL, Supabase, Neon, or dozens of other providers. It's also commonly the database running quietly behind production applications, with analytics teams connecting to a replica. Wherever your instance lives, confirm it's up and accessible before connecting from Plotly Studio.

Step 2: Allow remote connections (if needed)

Rather than connecting as the postgres superuser, it's best practice to create a scoped user with only the access Plotly Studio needs:

• Your server's firewall has port 5432 open to incoming connections
• The PostgreSQL config file pg_hba.conf is set to accept connections from the relevant IP address
• The postgresql.conf setting listen_addresses is not locked to localhost only

For cloud-hosted Postgres, this is typically handled through your provider's network access or security group settings.

Step 3: Create a dedicated database user (recommended)

Snowflake supports several optional connection arguments that may be required depending on your organization's setup:

CREATE USER plotly_user WITH PASSWORD '[YOUR_SECURE_PASSWORD]';
GRANT CONNECT ON DATABASE [YOUR_DATABASE] TO plotly_user;
GRANT USAGE ON SCHEMA public TO plotly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO plotly_user;

Step 4: Gather your connection details

Have these values ready before opening Plotly Studio. You'll find them in your PostgreSQL client (like pgAdmin or psql), your cloud provider's dashboard, or from the credentials used when the instance was provisioned.

Step 5: Connect in Plotly Studio

Open a new project and ask Plotly Studio to connect to Postgres. It will generate a credentials form. Fill in your Host, Port, Database, Username, and Password, then save. Plotly Studio will authenticate and return a Database Explorer showing all tables in your database.

Credentials needed

• Host: IP address, hostname, or localhost for local instances (e.g., db.company.com or 127.0.0.1)
• Port: typically 5432
• Database: the name of the target database (e.g., mydb)
• Username: your PostgreSQL user (e.g., plotly_user)
• Password: your PostgreSQL user password

Tip: contact us if you need help troubleshooting these steps.

LLM prompts for connecting to PostgreSQL

Plotly Studio uses an AI agent to generate and execute the data connection code for you. The prompts below are ready to copy and paste directly into Plotly Studio's data connection chat. Use these prompts to establish a connection, query your data, or do both in one shot. The global context rules are worth saving to your Plotly Studio global context to keep PostgreSQL connections consistent across projects.

Connection prompt

Connect to a PostgreSQL database using the psycopg2 library. Use psycopg2.connect() with
the provided host, port, dbname, user, and password parameters. Verify the connection and
then list all user-created tables in the public schema by querying
information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE'.

Query prompt

Using the established PostgreSQL connection, retrieve all rows from the table
[YOUR_SCHEMA].[YOUR_TABLE_NAME] in the [YOUR_DATABASE_NAME] database. Use pandas.read_sql()
with the connection object to return results as a pandas DataFrame. Use standard PostgreSQL
SQL syntax only.

Example one-shot prompt

Connect to a PostgreSQL database using psycopg2 with the following credentials:

Host: [YOUR_HOST_OR_IP]
Port: 5432
Database: [YOUR_DATABASE_NAME]
Username: [YOUR_USERNAME]
Password: [YOUR_PASSWORD]

Once connected, retrieve all rows from the table public.[YOUR_TABLE_NAME] and return the
result as a pandas DataFrame. Display a preview of the data.

Global context rules

- Always use psycopg2 as the primary connection library for PostgreSQL.
- Always use psycopg2.connect() with explicit host, port, dbname, user, and password
parameters.
- Do not attempt OAuth or certificate-based authentication unless explicitly instructed.
- Always use standard PostgreSQL SQL syntax. Do not use MySQL, SQL Server, or other SQL
dialects.
- Always return query results as a pandas DataFrame using pd.read_sql() or cursor +
fetchall().
- If the table schema is unknown, query information_schema.columns for the target table
first to confirm column names and types before building the main query.
- Default to LIMIT 10000 rows when querying large tables unless the user explicitly
requests a full pull.
- Always use schema-qualified table names (e.g., public.tablename) to avoid ambiguity.
- Do not log or expose raw password values in any output or error messages.

Troubleshooting and tips

• Permissions: The connecting user needs at minimum SELECT permission on the target schema. The USAGE permission on the schema is also required. Avoid using the postgres superuser in production. Create a scoped login instead.
• Default port: PostgreSQL's default port is 5432. Non-standard ports are common in enterprise environments, so always confirm with your DBA before connecting.
• Cloud providers: The connection process is identical whether pointing at localhost, a corporate on-prem server, or a cloud-managed instance. The only difference is your Host value and potentially whether SSL/TLS encryption is required (most cloud providers enforce it by default).