> ## Documentation Index
> Fetch the complete documentation index at: https://docs.squarecloud.app/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL on Square Cloud: Connection, Extensions and Limits

> Managed PostgreSQL on Square Cloud: connection string, TLS with a client certificate, user permissions, available extensions, pg_cron, memory settings and common errors.

A managed PostgreSQL instance with a client-certificate TLS connection, a database and a role that are yours, popular extensions ready to enable and `pg_cron` for scheduled jobs. Facts shared by every engine (limits, snapshots, Insights) are on the [Databases overview](https://docs.squarecloud.app/en/services/databases.md).

## Connect

Copy the connection URL from the database's page in the dashboard. It has this shape, with the `squarecloud` user and the `squarecloud` database created for you:

```
postgresql://squarecloud:PASSWORD@HOST:PORT/squarecloud
```

PostgreSQL requires **SSL/TLS and the client certificate**, besides the password. Download the `.pem` under **Connection** → **SSL/TLS certificate** and point the client to it:

| Parameter | Value |
| :- | :- |
| `sslmode` | `require`, or `verify-ca` / `verify-full` to also check the server |
| `sslcert` | the `.pem` |
| `sslkey` | the `.pem` |
| `sslrootcert` | the `.pem` (needed by `verify-ca` and `verify-full`) |

> [tab] Node.js
>
> ```javascript theme={"system"}
> import fs from "node:fs";
> import pg from "pg";
>
> const pem = fs.readFileSync("certificate.pem", "utf8");
>
> // One pool for the whole process, created once and reused.
> export const pool = new pg.Pool({
>   connectionString: process.env.DATABASE_URL,
>   ssl: { ca: pem, cert: pem, key: pem },
>   max: 10,
> });
>
> const { rows } = await pool.query("SELECT now()");
> ```

> [tab] Python
>
> ```python theme={"system"}
> import os
> import psycopg2
>
> # libpq refuses a key file that other users can read.
> os.chmod("certificate.pem", 0o600)
>
> conn = psycopg2.connect(
>     os.environ["DATABASE_URL"],
>     sslmode="verify-ca",
>     sslrootcert="certificate.pem",
>     sslcert="certificate.pem",
>     sslkey="certificate.pem",
> )
> ```

> [tab] psql
>
> ```bash theme={"system"}
> chmod 600 certificate.pem
> psql "postgresql://squarecloud:PASSWORD@HOST:PORT/squarecloud?sslmode=verify-ca&sslrootcert=certificate.pem&sslcert=certificate.pem&sslkey=certificate.pem"
> ```

> [!NOTE]
>
> Go clients reject `sslmode=verify-full` with this certificate (`x509: certificate relies on legacy Common Name field`): it names the host only in its Common Name, which Go no longer checks. Use `sslmode=verify-ca`, which still validates the certificate.

ORMs and other clients (Prisma, Drizzle, JDBC, GUI tools) are covered in [database connection errors](https://docs.squarecloud.app/en/platform/troubleshooting/database-connection-errors.md).

## Connections

Each database accepts up to 256 simultaneous connections, of which **253 are available to your clients**. Past that, new connections fail with `remaining connection slots are reserved for roles with the SUPERUSER attribute` (or `sorry, too many clients already`). Create **one pool per process** at startup and reuse it, sized so that every app, worker and shard together stays under the limit. A new connection per request or per query exhausts the limit quickly. The **Insights** tab shows who holds the open connections.

## Your user and permissions

The `squarecloud` role has `LOGIN`, `CREATEDB`, `CREATEROLE` and `BYPASSRLS`, and owns the `squarecloud` database. You can create databases, schemas, roles and grants, and change settings a regular user may change, for a session or as a default:

```sql theme={"system"}
ALTER ROLE squarecloud SET statement_timeout = '30s';
ALTER DATABASE squarecloud SET work_mem = '16MB';
```

The role is **not a superuser** and has no replication rights. That rules out:

* `ALTER SYSTEM` and settings reserved to superusers;
* server-side `COPY ... TO/FROM 'file'` (use `\copy` in `psql`, which reads and writes on your machine);
* untrusted languages such as `plpython3u`;
* logical replication and change data capture tools that need it.

## Extensions

Already installed: `plpgsql`, plus `pg_cron` and `pg_stat_statements` in the `postgres` database.

You can enable these in any of your databases with `CREATE EXTENSION`, in the database that will use them:

`vector` (pgvector), `pg_trgm`, `pgcrypto`, `uuid-ossp`, `citext`, `hstore`, `unaccent`, `ltree`, `btree_gin`, `btree_gist`, `cube`, `dict_int`, `fuzzystrmatch`, `intarray`, `isn`, `lo`, `seg`, `tablefunc`, `tcn`, `tsm_system_rows`, `tsm_system_time`.

```sql theme={"system"}
CREATE EXTENSION IF NOT EXISTS vector;
```

No other extension is available, and none can be added on request. PostGIS, for example, is not available.

## Scheduled jobs with pg\_cron

`pg_cron` lives in the `postgres` database. Connect to `postgres` (same URL, ending in `/postgres`) and schedule the job to run in your database. Schedules are in **UTC**:

```sql theme={"system"}
SELECT cron.schedule_in_database(
  'refresh-stats',          -- job name
  '*/5 * * * *',            -- every 5 minutes
  'REFRESH MATERIALIZED VIEW stats',
  'squarecloud'             -- database the job runs in
);

SELECT jobid, jobname, schedule, database FROM cron.job;
SELECT cron.unschedule('refresh-stats');
```

The run history in `cron.job_run_details` is never purged automatically. Schedule a cleanup of your old rows:

```sql theme={"system"}
SELECT cron.schedule_in_database('purge-cron-history', '0 3 * * *',
  $$DELETE FROM cron.job_run_details WHERE end_time < now() - interval '7 days'$$, 'postgres');
```

## Memory and configuration

* `shared_buffers` is **25% of the database's RAM** (at least 128 MB). Changing the RAM restarts the database to apply it.
* PostgreSQL relies on the operating system's file cache for the rest, so the RAM shown in the dashboard climbs and then stays high. That is normal, not a leak.
* A single query can write at most **1 GB of temporary files** (sorts, hashes). Past it the query fails with `temporary file size exceeds temp_file_limit`: add an index or process the data in batches.
* The write-ahead log takes 256 to 512 MB of the database's storage.
* The server configuration is the same on every database and can't be changed per database.

## Slow queries

Statements that take 100 ms or more are recorded, and the **Insights** tab lists them with their mean time. For your own analysis, connect to the `postgres` database and query `pg_stat_statements`, and use `EXPLAIN ANALYZE` on the statement:

```sql theme={"system"}
SELECT calls, round(mean_exec_time) AS mean_ms, query
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;
```

## Snapshots

A PostgreSQL snapshot holds every database and role of the instance. Besides the [general cases](https://docs.squarecloud.app/en/services/databases.md#snapshots-and-restore), it fails when a database has `ALLOW_CONNECTIONS false`, or when your own lock (`LOCK TABLE`, a long `ALTER TABLE`) blocks it for more than 5 minutes. The export is plain SQL text, so a database close to its 10 GB of storage may not fit in a snapshot.

## Common errors

| Message | What it means | What to do |
| :- | :- | :- |
| `no pg_hba.conf entry for host ... no encryption` | The client connected without SSL/TLS. | Enable `sslmode` and load the certificate. |
| `connection requires a valid client certificate` | SSL/TLS is on, but the client certificate was not sent. | Set `sslcert` and `sslkey` to the `.pem`. |
| `private key file ... has group or world access` | libpq (psql, psycopg2) refuses a readable key file. | `chmod 600` the `.pem` (in Python, `os.chmod(path, 0o600)`). |
| `remaining connection slots are reserved for roles with the SUPERUSER attribute` / `sorry, too many clients already` | All 253 connections are in use. | Use one shared pool per process; see [Connections](#connections). |
| `must be superuser` / `permission denied to set parameter` | The command needs a superuser. | Use a session or role setting instead, or another approach. |
| `extension "..." is not available` | The extension is not in the list above. | Use one of the available extensions. |
| `temporary file size exceeds temp_file_limit` | The query needed more than 1 GB of temporary files. | Add an index or work in batches. |
| `could not extend file ... No space left on device` | The 10 GB of storage is full: writes fail. | Delete data; see [disk full](https://docs.squarecloud.app/en/platform/troubleshooting/database-performance.md#the-disk-is-full). |
| `the database system is not yet accepting connections` | The database is recovering after a forced stop. | Wait: it accepts connections once the recovery ends. |

## Contact us

If you continue facing **technical difficulties**, our **specialized support team** is available to assist you. [**Contact us**](https://squarecloud.app/en/support) and we'll be happy to help you resolve any issue: support quality is a big part of why developers rate Square Cloud [**4.9/5 across 402 reviews**](https://www.trustpilot.com/review/squarecloud.app) on Google and Trustpilot.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.