PostgreSQL

Covers PostgreSQL on your own servers, Amazon RDS and Aurora, Azure Database for PostgreSQL and Google Cloud SQL. Tested on PostgreSQL 16 and 17.

Before you start

• The pgcrypto extension in the database Portward will read. The gateway computes tokens inside PostgreSQL with it, so sensitive values never leave the database in plain text on the way to the AI.
• An administrator who can create a role and run the SQL below.
• The database’s host name on the gateway’s allowed hosts (Allow the host).

Network access

The gateway connects to the database’s port, 5432 by default, over TLS. Open that port to the gateway as described in Network access. The certificate and host name are always checked (sslmode=verify-full).

Create a read-only login

Connect to the database Portward will read as an administrator, and run this. Replace sales with your database’s name and <password> with a strong password. You type the same password into setup later (Connect it in setup). If someone else types it there, share it through your password manager, never in chat or email.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE ROLE portward_reader LOGIN PASSWORD '<password>';
GRANT CONNECT ON DATABASE sales TO portward_reader;
GRANT USAGE ON SCHEMA public TO portward_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO portward_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO portward_reader;

Run it as the owner of the tables (on RDS, usually the master user that created them). Another login can only grant what it owns or was granted with the right to pass on. To see who owns a table, run \dt public.* in psql. If another role owns them, run the GRANT lines as that role. The last line lets the login read tables that the administrator running it creates later. Leave it out to grant new tables one by one.

Portward reads the tables and views in the login’s current schema, which is the first schema on its search path. For tables in another schema, such as reporting, grant USAGE and SELECT on that schema instead and put it first. Keep public after it, because pgcrypto’s functions live there:

ALTER ROLE portward_reader SET search_path = reporting, public;

Hosted versions

Amazon RDS and Aurora

• Run the SQL above as the master user. The master user itself can’t be Portward’s login, because it holds rds_superuser. Any PostgreSQL client that can reach the instance on port 5432 works, such as psql on an EC2 instance in the same VPC, and allow port 5432 from that machine’s security group in the instance’s security group. Use Amazon’s CA bundle for the instance’s region. Amazon’s guide Connecting to a DB instance running the PostgreSQL database engine covers setting up such a machine. Remove that machine’s 5432 rule when you are done.

  curl -fsSO https://truststore.pki.rds.amazonaws.com/us-east-2/us-east-2-bundle.pem   # your region
  psql "host=<endpoint> dbname=sales user=<master user> sslmode=verify-full sslrootcert=us-east-2-bundle.pem"

• In setup, use the instance or cluster endpoint, such as sales.abc123xyz.us-east-1.rds.amazonaws.com. Add that exact name to the allowed hosts.
• You don’t need to paste a CA. The gateway checks the certificate against Amazon’s RDS CA bundle, which ships with it.
• Allow port 5432 from the gateway in the instance’s security group. The instance must be in the stack’s VPC, or in a VPC peered with it, for that rule to work. On AWS, the gateway’s security group is the stack’s TaskSecurityGroup: on the stack’s Resources tab, its Physical ID is the group’s id (sg-…). Its description starts with “Portward gateway”.

Azure Database for PostgreSQL (flexible server)

• Allow pgcrypto first: in the portal, open the server, then Server parameters, find azure.extensions, tick PGCRYPTO and save. Then run the SQL above.
• The admin login you created with the server can’t be Portward’s login, because it is a member of azure_pg_admin.
• You don’t need to paste a CA. Azure’s certificate comes from a public CA.
• Under Networking, allow the gateway’s address in the firewall rules, or reach the server through a private network the gateway can use.

Google Cloud SQL for PostgreSQL

Cloud SQL’s server certificates come from Google’s own CA. There are two ways to connect, and which ones work depends on where the gateway runs.

• Directly, with the shared CA (every install). This works when the instance uses shared CAs (--server-ca-mode=GOOGLE_MANAGED_CAS_CA). Their certificates carry the instance’s DNS name. Connect to that DNS name and paste the CA into Server CA certificate (PEM). Get it with gcloud sql instances describe <instance> --format="value(serverCaCert.cert)", or from Connections, then Security, then Download in the console. An instance on the default per-instance CA has certificates that name no host, so the host name check refuses them. Google lets you switch an existing instance to shared CAs when you edit it; see Manage SSL/TLS certificates.

• Through the Cloud SQL Auth Proxy (your own server or the Azure VM only). Run the Cloud SQL Auth Proxy as a service in the gateway’s compose.yaml, sharing the gateway’s network (network_mode: "service:gateway"), with a service account that has the Cloud SQL Client role. The proxy encrypts the connection to Cloud SQL itself and uses that IAM role instead of an address allow-list. In setup, connect host 127.0.0.1 with the read-only login, and add 127.0.0.1 to the allowed hosts. The proxy has to run on the gateway’s own machine. The gateway skips TLS only for 127.0.0.1, and every other host must present a certificate that names it.

  This doesn’t work on the Google Cloud or AWS installs. On Google Cloud, the startup script rewrites compose.yaml at every boot and removes services it didn’t write, so the proxy disappears at the next restart. On AWS the gateway runs on ECS Fargate, which has no Compose file. On your own server and the Azure VM, compose.yaml keeps your changes, and portward update changes only the gateway’s image line.

The postgres user can’t be Portward’s login, because it is a member of cloudsqlsuperuser. Create portward_reader with the SQL above.

Connect it in setup

Choose PostgreSQL and fill in Name (the name people and the AI see, such as sales), Host, Port (empty means 5432), Database, User (portward_reader) and Password. Paste Server CA certificate (PEM) only for a company CA or Cloud SQL’s CA.

What the setup check tests

Before it saves, setup connects with the login and checks, in this order:

1. The host is on the allowed hosts. If not, it says “isn’t in the hosts the gateway operator allowed for setup”. An RDS or Aurora name allowed only by a wildcard gets “is on a domain other customers share”.
2. It can connect. Each failure has its own message: “login failed: check the user and password”, “the server’s TLS certificate couldn’t be verified”, “that database doesn’t exist on the server”, “the server doesn’t accept this login from the gateway’s address”, “the host name can’t be found”, or “the server didn’t answer; check the host, port and its firewall”.
3. The connection is encrypted (“use TLS”).
4. The login is not a superuser (“connect with a read-only role, not a superuser”).
5. The login is not an administrator (“connect with a read-only role, not an administrator”). That covers CREATEROLE, BYPASSRLS, and membership in pg_write_all_data, pg_database_owner, azure_pg_admin, rds_superuser, cloudsqlsuperuser or alloydbsuperuser.
6. pgcrypto is installed (“run CREATE EXTENSION pgcrypto in this database first”).
7. The login can read at least one table or view in its current schema (“the role can’t read any tables in this database”). A table it could INSERT, UPDATE, DELETE or TRUNCATE is listed as one it could change, and can’t be exposed.

Each time the gateway starts, it checks again that the login is not a superuser and can’t change any table it exposes.