Establish a private point-to-point connection to LLMs from anywhere

Secure & private connections to enterprise LLMs

Setup a WebDAV server inside Snowflake to access stage data with a point-to-point connection

Access a Snowflake stage with WebDAV

Make HTTP calls to a private API, from your Snowflake account

Call a private API from Snowflake

Setup a private point-to-point data stream from Kafka to Snowflake.

Real-Time Data Ingestion from Kafka to Snowflake

Setup a private point-to-point connection between Snowflake and a remote PostgreSQL database.

Run Queries on Remote PostgreSQL from Snowflake

Share data in Snowflake with your enterprise apps via a private API

Build completely private APIs in Snowflake

How to build in security as a SaaS feature

Ockam is networkless, so you can connect applications to data, without touching the network.

Ockam is a new type of abstraction for the Cloud era. We call it Networkless. Ockam enables developers to connect applications securely, without touching the underlying network infrastructure.

Episode 4: Introduction to Networkless

How a SaaS Product Manager should think about connections to customer data

There are various ways SaaS products integrate with their customers private systems: APIs, VPNs, Private Link, etc. However, SaaS companies end up with a cookbook of different integration methods depending on the customers needs and architecture. The result: sales are blocked or delayed, customers have to punch holes in their network, and no one wins. Through this podcast we presume a Product Manager point-of-view and then aim to show how Ockam might be the thing you have been looking for.

Episode 3: How a SaaS Product Manager should think about connections to customer data

The right way to use VPNs, Reverse Proxies, and Ockam.

At the end of this podcast you will know when to use, and how to pick between, a VPN (Cisco, Palo Alto Networks, ZScaler, Tailscale) , Reverse Proxy (Cloudflare, AWS, Azure, Google, ngrok), Virtual Adjacency (Ockam) for your distributed cloud infrastructure. .

Episode 2 - VPNs, Reverse Proxies, and Ockam

Why we built Ockam, and what Ockam means to us.

Ockam's Matthew Gregory (CEO), Mrinal Wadhwa (CTO) and Glenn Gillen (Head of Product) share how their careers led them to Ockam, and what excites them about the product we're building.

Episode 1 - The Founding Story of Ockam

How to use Ockam application & Okta workforce identities to provide policy driven control to your distributed apps

Authenticate & authorize every access decision

How to use Ockam Routing (with the Rust library) and its benefits

Ockam Routing

In the early days of Ockam we were developing a C library. This is the story of why we rewrote in Rust

Rewriting Ockam in Rust

Allow clients to read and write to an InfluxDB database that is on-prem or in a private VPC without exposing its ports to the public internet

Connect distributed clients to a private InfluxDB database

How we have managed to amplify the growth of our open source community

How to amplify the growth of your OSS community on GitHub

How to grow an OSS community on GitHub

Transparency is one of our virtues. That's why we share how The Team works with everyone. This includes what we value, what we do, how compensation is determined, and a lot more!

The Team Handbook

This is how we level engineers at Ockam. Transparency is one of our virtues so we share our level expectations openly.

Engineering Levels Ladder

There are a lot of ways to participate on The Ockam Team; from user, to OSS contributor, to full-time teammate. Join us!

How to Join The Ockam Team

High Performance Teams don't happen by accident. We've got a playbook to build one.

One of Ockam's core values is that we are a High Performance Team. Just like open source software builds on top of the shoulders of giants, so do my ideas around team building. Specifically about building a High Performance Team

How we build Ockam as a High Performance Team

Matthew Gregory discusses how his experiences in sailing inspired him to name Ockam as a tribute to one of his mentors.

What's the story behind the Ockam name?

{/*  */}

{/*  */}
{/*  */}
{/*  */}
{/*  */}
{/*  */}

Wouldn't it be nice to upload and manage files in a Snowflake stage the same way
you would if they were files on your local machine? Wouldn't it be even better if 
it wasn't necessary to manage IP allow lists, open firewall ports, or setup 
services like PrivateLink to make that happen? 

I'm about to walk you through setting this up, and it will take you less than 
15 minutes to have everything working.

Introducing the Snowflake Stage Data with WebDAV Connector!

<AspectRatio maxW="50%" ratio={16 / 9}>
  <iframe
    src="https://www.youtube.com/embed/8un_rsg7l84?si=xwAohiLRLgw_uu73"
    title="Mount your Snowflake Stage as a local filesystem"
    frameBorder="0"
    allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture"
    allowFullScreen
  ></iframe>
</AspectRatio>

## Quick links

* [Stage data with WebDAV Connector the Snowflake Marketplace](https://app.snowflake.com/marketplace/listing/GZTYZXRGOI0/ockam-stage-data-with-webdav-connector)

## Snowflake 💙 WebDAV

WebDAV is a protocol for authoring and exchanging documents, based on the 
HTTP protocol.

One way to import data into Snowflake is to use the [`snow` command line client](https://docs.snowflake.com/en/developer-guide/snowflake-cli/installation/installation) 
or the [`snowflake-connector-python` Python library](https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-install) to upload files and 
have them be mapped to tables for later processing. Unfortunately, both those 
tools require a specific installation, and an OAuth authentication,
whereas HTTP commands can be easily executed with an ubiquitous utility like `curl`.

In this post I'm going to show you how to securely access a Snowflake stage in 
your account with WebDAV. In just a few minutes we will:

* Create a stage associated to your Snowflake account.
* Create a WebDAV server on Snowflake with a private encrypted connection.
* Upload, list, download and remove files with WebDAV commands.
* Mount the Snowflake stage as a filesystem on your local workstation.

![Snowflake stage data with WebDAV](/blog/snowflake-stage-data-webdav/snowflake-stage-data-webdav.png)


## Create a stage

This can be done with just a few commands in the Snowsight editor:
```sql
-- create a test database
CREATE DATABASE IF NOT EXISTS WEBDAV_TEST_DATABASE;
USE DATABASE WEBDAV_TEST_DATABASE;

-- create a test schema
CREATE SCHEMA IF NOT EXISTS WEBDAV_TEST_SCHEMA;
USE SCHEMA WEBDAV_TEST_SCHEMA;

-- create an internal stage
CREATE STAGE IF NOT EXISTS SECURE ENCRYPTION = (type = 'SNOWFLAKE_SSE');
```

The files contained in that stage can be listed with:
```sql
LIST @"SECURE";
```

And the stage should be empty for now!

## Setup a WebDAV server inside Snowflake

We are now going to create a WebDAV server for the previous stage inside Snowflake.
This will create a point-to-point connection between the two systems &mdash; without the need to expose
any systems to the public internet!

<ImageTour>
### !!steps Get the app

The Snowflake stage data with WebDAV Connector by Ockam is [available in the Snowflake Marketplace](https://app.snowflake.com/marketplace/listing/GZTYZXRGOI0/ockam-stage-data-with-webdav-connector).

![!tour Get the app](/blog/snowflake-stage-data-webdav/app-store.png)

### !!steps Select a warehouse

The first screen you're presented with will ask you to select the warehouse to utilize to activate the app and can choose to change the application name.

![!tour Select a warehouse](/blog/snowflake-stage-data-webdav/select-warehouse.png)

### !!steps Grant account privileges

Click the `Grant` button to the right of this screen. The app will then be
automatically granted permissions to create a warehouse and create a compute
pool.

![!tour Grant account privileges](/blog/snowflake-stage-data-webdav/grant-account.png)

### !!steps Activate app

Once the permissions grants complete, an `Activate` button will appear. Click
it and the activation process will begin.

![!tour Activate app](/blog/snowflake-stage-data-webdav/activate-app.png)

### !!steps Launch app

After the app activates you'll see a page that summarizes the
privileges that the application now has. There's nothing we need
to review or update on these screens yet, so proceed by clicking the `Launch app` button.

![!tour Launch app](/blog/snowflake-stage-data-webdav/launch-app.png)

</ImageTour>

## Setup your administrator access

We are now going to configure the application. We first need to:

 - Create an Ockam project and issue an enrollment ticket
 - Configure the user name and password which will be used to access the server.


<CodeTour>

### !!steps Download Ockam Command

Run the following command on your local workstation:

```bash ! tour
curl --proto '=https' --tlsv1.2 -sSfL \
  https://install.command.ockam.io \
  | bash && source "$HOME/.ockam/env"
```

This will install the Ockam Command and source in the required environment
settings. If you've installed Ockam Command before you can skip this step.


### !!steps Setup admin account

Once Ockam Command installation is complete you can run:

```bash ! tour
ockam enroll
```

Wrapped up in this single `ockam enroll` command are several steps that will
bootstrap your first project and get you ready to go. It will:

* Generate an Ockam Identity and store its secret keys in a file system based Ockam Vault.
* Create an account with Ockam Orchestrator.
* Provision a trial Space and Project in the Orchestrator.
* Make your Ockam Identity the administrator of your new Project.

### !!steps Generate enrollment ticket for Snowflake

In this section we're going to provision an Ockam node that will run alongside
the WebDAV started by the "Stage data with WebDAV - Connector" application, and provide one of the ends of our point-to-point connection.

We need to generate an enrollment ticket to allow a new Ockam Node to
join the project that was just created. This node will run alongside the WebDAV server, inside your Snowflake account:

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 24h \
  --attribute webdav \
  --relay webdav > webdav.ticket
```

### !!steps

In this command we've set the new ticket to expire in `24 hours`, and a usage
count of `1`. This means the generated ticket is valid for a single use, and
that it's valid for 24 hours. A single usage ticket means there is low
risk associated with mishandling this ticket after use; there's no means
for an attacker to re-use it like an API token to access any system.

```shell ! tour
ockam project ticket \
# !focus(1:1)
  --usage-count 1 --expires-in 24h \
  --attribute webdav \
  --relay webdav > webdav.ticket
```

### !!steps

The Project Membership Credential that's issued will include  
attributes that will be cryptographically attested to by the Project's
Membership Authority. You can use these attributes to apply policies with
Attribute Based Access Controls (ABAC) to allow or restrict specific actions and
communication paths between nodes.

In this example we assign a single attribute of `webdav`.

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 24h \
# !focus(1:1)
  --attribute webdav \
  --relay webdav > webdav.ticket
```

### !!steps

The `--relay webdav` flag is a shortcut for creating a policy that allows this
node to create a relay at the address `webdav`.

This relay will allow a WebDAV client to establish a secure end-to-end
encrypted connection to the WebDAV server, running in your Snowflake account, without requiring you to expose any
endpoints to the public Internet.

As the final part of this command we pipe the generated ticket to a file named `webdav.ticket`.

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 24h \
  --attribute webdav \
# !focus(1:1)
  --relay webdav > webdav.ticket
```

</CodeTour>

## Configuring the Snowflake app

<ImageTour>

### !!steps Configure connection details

Click "Get started" to open the Snowflake setup screen.

Take the contents of the file `webdav.ticket` that we just created and paste
it into "Provide the above Enrollment Ticket" form field in the "Configure app"
setup screen in Snowflake.

![!tour Create Snowflake ticket](/blog/snowflake-stage-data-webdav/snowflake-ticket.png)

### !!steps Configure the stage access

In this screen, we need to enter:

 - The full name of the stage we want to access: `WEBDAV_TEST_DATABASE.WEBDAV_TEST_SCHEMA.SECURE`.

![!tour Configure the stage access](/blog/snowflake-stage-data-webdav/snowflake-configure-stage-access.png)

### !!steps Grant privileges

To be able to authenticate with Ockam Orchestrator and then
discover the route to our outlet, the Snowflake app needs to allow
outbound connections to your Ockam project.

Toggle the `Grant access to egress and reach your Project` button and approve the connection by
pressing `Connect`.

Toggle the `Grant access to your user` button, enter `user` for the user name and `password` for the user password.

![!tour Grant egress](/blog/snowflake-stage-data-webdav/snowflake-grant-privileges.png)


### !!steps Start the WebDAV server

Press "Next" and wait for the WebDAV server to start. Once started, you should be able to see the WebDAV server logs.

![!tour Start server](/blog/snowflake-stage-data-webdav/snowflake-start-server.png)

You can also notice a tab containing the logs for an Ockam node. That Ockam node is a portal outlet which:

  - Created a relay named `webdav` in the Ockam project.
  - Declares that only clients with the attribute `webdav-client` can access the relay.

</ImageTour>

## Connect the WebDAV client

<CodeTour>

### !!steps Generate an enrollment ticket for the WebDAV client

One end of our connection is now setup, it's time to connect the WebDAV server.
We need to generate an enrollment ticket to allow another Ockam Node to
join our project. This node will run on our local machine:

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 2h \
  --attribute webdav-client > webdav-client.ticket
```

### !!steps

As we did with the earlier ticket, we've reduced the risk associated with
mishandling a ticket by restricting it's permitted usage window. We've again set
a usage count of `1`, but with a tighter
expiry time of just `2 hours`.

```bash ! tour
ockam project ticket \
# !focus(1:1)
  --usage-count 1 --expires-in 2h \
  --attribute webdav-client > webdav-client.ticket
```

### !!steps

This credential will include an attested attribute of `webdav-client`, corresponding to the attribute expected by 
the Ockam outlet node started in your Snowflake account.

The ticket is then saved to a file called `webdav-client.ticket`.

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 2h \
# !focus(1:1)
  --attribute webdav-client > webdav-client.ticket
```

</CodeTour>

<CodeTour> 

### !!steps Create a configuration file

We're now going to pass the configuration for our local Ockam Node as a JSON file, so
save the configuration to the right into a file named `webdav.json`.

```javascript ! tour
{
    "identity": "webdav-client",
    "name": "webdav-client",
    "tcp-inlet": {
      "from": "0.0.0.0:8001",
      "via": "webdav",
      "allow": "webdav"
    }
}
```

### !!steps

Ockam Command will process the configuration file after the node starts, instructing the node to create a portal inlet. The node creates an identity named `webdav-client` and an ockam node named `webdav-client`.

```javascript ! tour
{
// !focus(1:1)
    "identity": "webdav-client",
    "name": "webdav-client",
    "tcp-inlet": {
      "from": "0.0.0.0:8001",
      "via": "webdav",
      "allow": "webdav"
    }
}
```

### !!steps

Ockam Command creates a portal inlet. An inlet receives message sent to the local `from` address, encrypts them and forwards them to the project relay named`webdav`.

```javascript ! tour
{
    "identity": "webdav-client",
    "name": "webdav-client",
// !focus(1:1)
    "tcp-inlet": {
      "from": "0.0.0.0:8001",
      "via": "webdav",
      "allow": "webdav"
    }
}
```

### !!steps

We also define a policy to restrict which are nodes that are expected to be connected to our inlet.

This policy applies Attribute Based Access Controls (ABAC) to allow
other nodes that have the attribute `webdav` as part of their attested
credentials. The system denies access to nodes without the `webdav` attribute.

```javascript ! tour
{
    "identity": "webdav-client",
    "name": "webdav-client",
    "tcp-inlet": {
      "from": "0.0.0.0:8001",
      "via": "webdav",
// !focus(1:1)
      "allow": "webdav"
    }
}
```

### !!steps Start the Ockam inlet node

We can now finally start the Ockam inlet node with this command

```shell ! tour
ockam node create webdav.json --enrollment-ticket webdav-client.ticket
```

</CodeTour> 


## Accessing the stage

### Mounting as a filesystem (macOS)

<ImageTour>
### !!steps 

This is where things get very cool, because we've got all the pieces in place 
to add our Snowflake Stage as though it's another drive/filesystem on our local
workstation. I'm going to walk through how to do that on a Mac, but if you're 
on Windows you can [follow these instructions](https://superuser.com/questions/254113/how-to-connect-to-an-https-webdav-folder-on-windows-7)
and supply the same values I suggest below.

To get started open Finder.app, and either use the shortcut to connect to a
server (`⌘ + K`) or select from the menu (`Go > Connect to Server`). In the
dialog box that appears enter the server address of `http://localhost:8001`.

You can safely ignore any warning you may get about the connection being
insecure. **The Ockam portal connecting your system to your Snowflake Stage
is secure and end-to-end encrypted**. The warning is because the interface 
on your local machine is exposed as a `http://` protocol rather than `https://`.

![!tour Connect to server](/blog/snowflake-stage-data-webdav/macos-mount-webdav.png)

### !!steps 

When presented with authentication screen enter in the name and password of
`user` and `password`, as we configured earlier when configuring the Snowflake
app.

![!tour Enter username & password](/blog/snowflake-stage-data-webdav/macos-mount-webdav-userpass.png)

### !!steps 

Your Snowflake Stage will now be mounted just like any other filesystem! You can
drag files from other folders, delete, or rename files just like you would 
any other files on your system. The changes will be reflected in your Snowflake
Stage immediately. 

![!tour Drag files](/blog/snowflake-stage-data-webdav/macos-mount-webdav-drag.png)
</ImageTour>

### Using the API (curl)

<CodeTour> 
If you don't want to access the Snowflake Stage like a filesystem, you can
test the connection by directly using the HTTP API. 

### !!steps Create a file

Before connecting we create a file which will be uploaded to Snowflake

```shell ! tour
echo 'Hello Snowflake!' > hello.txt
```

### !!steps Upload a file

The `hello.txt` file can now be uploaded with a simple `curl` command

```shell ! tour
curl -T hello.txt -u user:password http://localhost:8001
```

### !!steps List files on the Snowflake stage

We can check that the file has indeed be uploaded by listing the stage contents from inside Snowsight
```sql ! tour
LIST @"SECURE";
```

### !!steps List remote files

We can also list the contents of the stage with another `curl` command, using the WebDAV `PROPFIND` method
```shell ! tour
curl -X PROPFIND \
  -u user:password http://localhost:8001/ \
  -H "Depth: 1"
```

### !!steps Download a file

We can download that same file from the stage

```shell ! tour
curl -u user:password \
  -O http://localhost:8001/hello.txt
```

### !!steps Delete a file

And finally we can remove the file from the stage

```shell ! tour
curl -X DELETE \
  -u user:password http://localhost:8001/hello.txt
```

</CodeTour> 


## Next steps

With a few steps we've been able to mount our Snowflake Stage as a filesystem 
on our local workstate. Rather than connecting via a public interface, our 
client has connected over a private route using a mutually authenticated and 
end-to-end encrypted conenction. No ports have been exposed to the internet,
no firewall ingress have been changed to allow access in, and yet we have two
private systems now able to securely share files with each other.

To explore other capabilities that are possible with Ockam I'd suggest:

* [Connecting Snowflake to any system, in any network](/for/snowflake)
* [Creating private point-to-point connections with _any_ database](https://docs.ockam.io/portals/databases)
* [Adding security as a feature in your SaaS product](/blog/building-secure-saas-platforms)

{/*  */}

{/*  */}
{/*  */}
{/*  */}
{/*  */}
{/*  */}

Your data is business critical, and business critical data shouldn't be 
accessible via public API endpoints. Too often though we're forced to compromise
on security in the name of convenience. A common example of that is when we 
want to allow third-party services to access self-hosted APIs that exist
within our own private network. 

If you're a Snowflake customer you don't need to compromise! 

In under 10 minutes I'll show you how to setup a private point-to-point 
connection that doesn't require you to manage IP allow lists, open firewall 
ports, or setup services like PrivateLink.

<AspectRatio maxW="50%" ratio={16 / 9}>
  <iframe
    src="https://www.youtube.com/embed/JJ7wIzoJMRk?si=8CX_NPDjHwnp7Mz1"
    title="Access private API endpoints from Snowflake"
    frameBorder="0"
    allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture"
    allowFullScreen
  ></iframe>
</AspectRatio>

## Quick links

* [Call Private API Connector in the Snowflake Marketplace](https://app.snowflake.com/marketplace/listing/GZTYZ723Y3S8Y/ockam-call-private-api-connector)

## No more public APIs

Whether you're providing a custom built interface to your business systems via
an API or self-hosting a service with an accessible API (e.g., JIRA, GitHub, etc.)
there's a good reason these are deployed within a private subnet. Granting 
access to this "internal" network is often easy for team members, you get them
to connect first to a VPN and then they're able to access all of your corporate
assets. When you're only trying to connect two services
there's no need to be connecting two entire networks and trying to make 
them look like one. You can instead establish a point-to-point connection between 
just the services that need to connect.

Once a private point-to-point connection is established from Snowflake to your 
system(s), you'll be able to query your private data using SQL. Imagine being
able to join data in your Snowflake warehouse to data in your private APIs! You
could even join results from something like JIRA, or join data across multiple
APIs as though that were tables in your warehouse! Or you could send POST
requests to insert new data. All from Snowflake. All using SQL. All without 
exposing any of your systems to the public internet.

In this post I'm going to show you how to securely access a private API, hosted 
in a private network, in just a few minutes. We will:

* Install and configure the Call Private API connector.
* Make a stored procedure call to retrieve data as if it was a table `select`.
* Make a stored procedure call to post data to the API.

![Snowflake call private API](/blog/snowflake-call-private-api/snowflake-call-private-api.png)

## Install and configure the Call Private API Connector

<ImageTour>
### !!steps Get the app

The Call Private API Connector by Ockam is [available in the Snowflake Marketplace](https://app.snowflake.com/marketplace/listing/GZTYZ723Y3S8Y/ockam-call-private-api-connector).

![!tour Get the app](/blog/snowflake-call-private-api/app-store.png)

### !!steps Select a warehouse

The first screen you're presented with will ask you to select the warehouse to utilize to activate the app and can choose to change the application name.

![!tour Select a warehouse](/blog/snowflake-call-private-api/select-warehouse.png)

### !!steps Grant account privileges

Click the `Grant` button to the right of this screen. The app will then be
automatically granted permissions to create a warehouse and create a compute
pool.

![!tour Grant account privileges](/blog/snowflake-call-private-api/grant-account.png)

### !!steps Activate app

Once the permissions grants complete, an `Activate` button will appear. Click
it and the activation process will begin.

![!tour Activate app](/blog/snowflake-call-private-api/activate-app.png)

### !!steps Launch app

After the app activates you'll see a page that summarizes the
privileges that the application now has. There's nothing we need
to review or update on these screens yet, so proceed by clicking the `Launch app` button.

![!tour Launch app](/blog/snowflake-call-private-api/launch-app.png)

</ImageTour>

## Configuration

We are now going to configure the application. We first need to:

 - Create an Ockam project and issue an enrollment ticket
 - Configure the user name and password which will be used to access the server.


<CodeTour>

### !!steps Download Ockam Command

Run the following command on your local workstation:

```bash ! tour
curl --proto '=https' --tlsv1.2 -sSfL \
  https://install.command.ockam.io \
  | bash && source "$HOME/.ockam/env"
```

This will install the Ockam Command and source in the required environment
settings. If you've installed Ockam Command before you can skip this step.


### !!steps Setup admin account

Once Ockam Command installation is complete you can run:

```bash ! tour
ockam enroll
```

Wrapped up in this single `ockam enroll` command are several steps that will
bootstrap your first project and get you ready to go. It will:

* Generate an Ockam Identity and store its secret keys in a file system based Ockam Vault.
* Create an account with Ockam Orchestrator.
* Provision a trial Space and Project in the Orchestrator.
* Make your Ockam Identity the administrator of your new Project.

### !!steps Generate enrollment ticket for Snowflake

In this section we're going to provision an Ockam node that will allow you to connect to your private API.

We need to generate an enrollment ticket to allow a new Ockam Node to
join the project that was just created. This node will be used by the connector stored procedures to make HTTP calls
from inside your Snowflake account:

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 24h \
  --attribute snowflake > snowflake.ticket
```

### !!steps

In this command we've set the new ticket to expire in `24 hours`, and a usage
count of `1`. This means the generated ticket is valid for a single use, and
that it's valid for 24 hours. A single usage ticket means there is low
risk associated with mishandling this ticket after use; there's no means
for an attacker to re-use it like an API token to access any system.

```shell ! tour
ockam project ticket \
# !focus(1:1)
  --usage-count 1 --expires-in 24h \
  --attribute snowflake > snowflake.ticket
```

### !!steps

The Project Membership Credential that's issued will include  
attributes that will be cryptographically attested to by the Project's
Membership Authority. You can use these attributes to apply policies with
Attribute Based Access Controls (ABAC) to allow or restrict specific actions and
communication paths between nodes.

In this example we assign a single attribute of `snowflake`.

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 24h \
# !focus(1:1)
  --attribute snowflake \
  --relay snowflake > snowflake.ticket
```

</CodeTour>

## Configuring the application

<ImageTour>

### !!steps Configure connection details

Click "Get started" to open the Snowflake setup screen.

Take the contents of the file `snowflake.ticket` that we just created and paste
it into "Provide the above Enrollment Ticket" form field in the "Configure app"
setup screen in Snowflake.

![!tour Create Snowflake ticket](/blog/snowflake-call-private-api/snowflake-ticket.png)

### !!steps Configure API access

In this screen we need to select an authorization method. For this tutorial you 
can select `Digest` and continue. The other options available to use are:

 - `None` No credentials are required to access the private API.
 - `Basic` The private API is accessible with a user name and password.
 - `Digest` The private API is accessible with a user name and password, but they are not transmitted in clear text.
 - `Bearer token` The private API is accessible with a token passed with an `Authorization` header.
 - `API Key` The private API is accessible with an API Key passed with a specific extension header (for example `X-api-key`).


![!tour Configure the stage access](/blog/snowflake-call-private-api/snowflake-configure-api-access.png)

### !!steps Grant privileges

To be able to authenticate with Ockam Orchestrator and then
discover the route to our outlet, the Snowflake app needs to allow
outbound connections to your Ockam project.

Toggle the `Grant access to your credentials` button, enter `user` for the user name and `password` for the user password.

Toggle the `Grant access to your Ockam Project` button and approve the connection by
pressing `Connect`.


![!tour Grant egress](/blog/snowflake-call-private-api/snowflake-grant-privileges.png)


### !!steps Start the application

Press "Next" and wait for the application to start. Once started, you should be able to see the server logs.

![!tour Start server](/blog/snowflake-call-private-api/snowflake-start-server.png)

You can also notice a tab containing the logs for an Ockam node. That Ockam node is a portal inlet:

  - Which accesses a relay named `api` in the Ockam project.
  - Declares that only services with the attribute `api` can be connected to the inlet.

</ImageTour>

## Setup an Ockam outlet on the API side

<CodeTour>

### !!steps Generate an enrollment ticket for the Ockam outlet

One end of our connection is now setup, it's time to connect the private API
We need to generate an enrollment ticket to allow another Ockam Node to
join our project. This node will run in the same network as your private API:

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 2h \
  --attribute api \
  --relay api > api.ticket
```

### !!steps

As we did with the earlier ticket, we've reduced the risk associated with
mishandling a ticket by restricting it's permitted usage window. We've again set
a usage count of `1`, but with a tighter
expiry time of just `2 hours`.

```bash ! tour
ockam project ticket \
# !focus(1:1)
  --usage-count 1 --expires-in 2h \
  --attribute api \
  --relay api > api.ticket
```

### !!steps

This credential will include an attested attribute of `api`, corresponding to the attribute expected by 
the Ockam inlet node started in your Snowflake account.

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 2h \
  # !focus(1:1)
  --attribute api \
  --relay api > api.ticket
```

### !!steps

This credential declares the creation of a relay named `api` to make the service accessible via your Ockam project. 

The ticket is then saved to a file called `api.ticket`.

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 2h \
  --attribute api \
  # !focus(1:1)
  --relay api > api.ticket
```

</CodeTour>

<CodeTour> 

### !!steps Create a configuration file

We're now going to pass the configuration for our local Ockam Node as a JSON file, so
save the configuration to the right into a file named `api.json`.

```javascript ! tour
{
  "identity": "api-outlet",
  "name": "api-outlet",
  "tcp-outlet": {
    "to": "private-network-host:8000",
    "allow": "snowflake"
  },
  "relay": "api"
}
```

### !!steps

Ockam Command will process the configuration file after the node starts, 
instructing the node to create a portal inlet. The node creates an identity 
named `api-outlet` and an Ockam node named `api-outlet`.

```javascript ! tour
{
// !focus(1:1)
  "identity": "api-outlet",
  "name": "api-outlet",
  "tcp-outlet": {
    "to": "private-network-host:8000",
    "allow": "snowflake"
  },
  "relay": "api"
}
```

### !!steps

Ockam Command creates a portal outlet. An outlet receives message sent to the 
project relay named `api`, decrypts them, and 
forwards them to the service located at `private-network-host:8000` (replace 
this with the internal hostname and port of the service you want to access).

```javascript ! tour
{
  "identity": "api-outlet",
  "name": "api-outlet",
// !focus(1:1)
  "tcp-outlet": {
    "to": "private-network-host:8000",
    "allow": "snowflake"
  },
  "relay": "api"
}
```

### !!steps

We also define a policy to restrict which are nodes that are expected to be connected to our outlet.

This policy applies Attribute Based Access Controls (ABAC) to allow
other nodes that have the attribute `snowflake` as part of their attested
credentials. The system denies access to nodes without the `snowflake` attribute.

```javascript ! tour
{
  "identity": "api-outlet",
  "name": "api-outlet",
  "tcp-outlet": {
    "to": "private-network-host:8000",
// !focus(1:1)
    "allow": "snowflake"
  },
  "relay": "api"
}
```

### !!steps Start the Ockam outlet node

We can now finally start the Ockam outlet node with this command. Run this 
command on a machine that is already able to access your private API. Depending
on your infrastructure setup this could be as another process on the API
server itself, or as a sidecar/container running in the same network.

```shell ! tour
ockam node create api.json --enrollment-ticket api.ticket
```

</CodeTour> 

## Testing the connection

We are now ready to test access to the private API, via a fully secure 
point-to-point connection!

### Querying data

<CodeTour> 

### !!steps

Let's assume that your HTTP API provides an endpoint named `/customers` to 
get a list of customers. You can make a `GET` query to retrieve all the 
customers as a table:

```sql ! tour
CALL CALL_PRIVATE_API__CONNECTOR.external.get('/customers');
```

### !!steps Getting data with query parameters

You can also parametrize your query with query parameters, provided as a 
comma-separated list of `key:value`s

```sql ! tour
CALL CALL_PRIVATE_API__CONNECTOR.external.get('/customers', 'min-age:20,max-age:50');
```

### !!steps Getting data with query parameters and headers

Some API also use headers to control the API behaviour. If you need to pass a 
specific set of headers, you can specify them in addition to the query parameters:

```sql ! tour
CALL CALL_PRIVATE_API__CONNECTOR.external.get('/customers', 'min-age:20,max-age:50', 'Content-Language:en-US,X-start-date:2023-01-01');
```
</CodeTour>

### Post / Put / Patch data

<CodeTour>

### !!steps 
It is also possible to send data to your API with a `POST` query. This data needs to be sent as a JSON payload:

```sql ! tour
CALL CALL_PRIVATE_API__CONNECTOR.external.POST('/customers', '{"name":"john", "age":50}');
```
</CodeTour>

### `JOIN` with other data in Snowflake

<CodeTour>

### !!steps

You can even join the response from your API with other data that is available
within Snowflake!

```sql ! tour
CALL CALL_PRIVATE_API__CONNECTOR.external.get('/demo.json')

SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) as api_names
left join
employees
ON api_names.id = employees.employee_id
```

### !!steps

First run the `CALL CALL_PRIVATE_API__CONNECTOR` stored procedure, as we did 
before, to query our private API.

```sql ! tour
-- !focus(1:1)
CALL CALL_PRIVATE_API__CONNECTOR.external.get('/demo.json')

SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) as api_names
left join
employees
ON api_names.id = employees.employee_id
```

### !!steps

Once that completes we can then use the `RESULT_SCAN` function, and the 
`LAST_QUERY_ID()`, to turn the response into a table.

```sql ! tour
CALL CALL_PRIVATE_API__CONNECTOR.external.get('/demo.json')

-- !focus(1:1)
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) as api_names
left join
employees
ON api_names.id = employees.employee_id
```

### !!steps

From there the regular SQL join syntax can join the API results with any
other data.

```sql ! tour
CALL CALL_PRIVATE_API__CONNECTOR.external.get('/demo.json')

SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) as api_names
-- !focus(1:3)
left join
employees
ON api_names.id = employees.employee_id
```

### !!steps Delete data

Finally you can delete data with a `DELETE` query to the appropriate endpoint:

```sql ! tour
CALL CALL_PRIVATE_API__CONNECTOR.external.DELETE('/customers/241031');
```

</CodeTour> 


## Next steps

In summary, we are now able to securely access a private API, using Snowflake 
stored procedures, without exposing any ports to the
internet.

If you'd like to explore some other capabilities of Ockam I'd recommend:

* [Creating private point-to-point connections with _any_ database](https://docs.ockam.io/portals/databases)
* [Adding security as a feature in your SaaS product](/blog/building-secure-saas-platforms)

{/*  */}
{/*  */}
{/*  */}
{/*  */}

You've got a bunch of data living in a PostgreSQL database, and you'd
love to query that data from Snowflake. You don't want to replicate everything.
You just want to be able to query that PostgreSQL data ad-hoc, but being able to 
access that database from Snowflake can be problematic. It's in a private subnet
and it's blocked off from the rest of the internet for good reason.

How do you do it?

Today I'm going to show you having less than 15 minutes, you can set up a
secure point-to-point connection that allows you to access your PostgreSQL
database from your Snowflake warehouse. And I'm going to show you why
Ockam is the most secure and best solution for doing that.

<AspectRatio maxW="50%" ratio={16 / 9}>
  <iframe
    src="https://www.youtube.com/embed/rKLx2tJf8AA?si=m1LXXN1wRduWXuj0"
    title="Query your private & remote Postgres from Snowflake"
    frameBorder="0"
    allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture"
    allowFullScreen
  ></iframe>
</AspectRatio>

In just a few minutes. We will:

* Setup a PostgreSQL database in AWS
* Connect Snowflake to PostgreSQL with a private encrypted connection
* Run a query from Snowflake against the remote PostgreSQL database

![Snowflake push to PostgreSQL](/blog/snowflake-query-postgres/snowflake-query-postgres.png)

## Quick links

* [Query Remote PostgreSQL Connector in the Snowflake Marketplace](https://app.snowflake.com/marketplace/listing/GZTYZXRGOHB/ockam-query-remote-postgresql-connector)
* [Ockam Node for Amazon RDS PostgreSQL in the AWS Marketplace](https://aws.amazon.com/marketplace/pp/prodview-zjugwdx5hp2le)

## Amazon Relational Database Service (RDS) for PostgreSQL

We're going to provision an Amazon RDS PostgreSQL Database so we can see an end-to-end 
experience of data moving from Snowflake to PostgreSQL. If you have an existing 
PostgreSQL database you're able to use you can skip this step.

### Create a PostgreSQL database

<ImageTour>

### !!steps

Within your [AWS Console](https://console.aws.amazon.com/) search for 
`RDS` in the search field at the top and select the matching result. Visit the 
`Databases` screen, and then click `Create Database`.

The `Standard Create` option provides a good set of defaults for creating a
RDS Database, so unless you've previous knowledge or experience to know you
might want something different I'd suggest choosing "PostgreSQL" and confirming the details and 
then clicking `Create database` at the bottom of the screen.

Once you've started the database creation it may take about 15 minutes for
provisioning to complete and for your database to be available.

![!tour Create a database](/blog/snowflake-query-postgres/create-rds.png)

</ImageTour>

## Connect Snowflake to PostgreSQL

We've created a PostgreSQL database, now it's time to connect it to Snowflake! The
next stage is going to complete the picture below, by creating a point-to-point
connection between the two systems &mdash; without the need to expose any systems
to the public internet!

![Snowflake push to Amazon RDS](/blog/snowflake-query-postgres/aws-complete.png)

### Launch the Snowflake Native App

<ImageTour>
### !!steps

The Snowflake Query Remote PostgreSQL Connector by Ockam is [available in the Snowflake Marketplace](https://app.snowflake.com/marketplace/listing/GZTYZXRGOHB/ockam-query-remote-postgresql-connector).

![!tour Get the app](/blog/snowflake-query-postgres/app-store.png)

### !!steps Select a warehouse

The first screen you're presented with will ask you to select the warehouse to utilize to activate the app.

![!tour Select a warehouse](/blog/snowflake-query-postgres/select-warehouse.png)

### !!steps Grant account privileges

Click the `Grant` button to the right of this screen. The app will then be
automatically granted permissions to create a warehouse and create a compute
pool. 

![!tour Grant account privileges](/blog/snowflake-query-postgres/grant-account.png)

### !!steps Activate app

Once the permissions grants complete, an `Activate` button will appear. Click
it and the activation process will begin.

![!tour Activate app](/blog/snowflake-query-postgres/activate-app.png)

### !!steps Launch app

After the app activates you'll see a page that summarizes the 
privileges that the application now has. There's nothing we need 
to review or update on these screens yet, so proceed by clicking the `Launch app` button.

![!tour Launch app](/blog/snowflake-query-postgres/launch-app.png)

</ImageTour>

### Setup Ockam

<CodeTour>
### !!steps Download Ockam Command

Run the following command on your local workstation:

```bash ! tour
curl --proto '=https' --tlsv1.2 -sSfL \
  https://install.command.ockam.io \
  | bash && source "$HOME/.ockam/env"
```

This will install the Ockam Command and source in the required environment 
settings. If you've installed Ockam Command before you can skip this step.


### !!steps Setup admin account

Once Ockam Command installation is complete you can run:

```bash ! tour
ockam enroll
```

Wrapped up in this single `ockam enroll` command are several steps that will 
bootstrap your first project and get you ready to go. It will:

* Generate an Ockam Identity and store its secret keys in a file system based Ockam Vault.
* Create an account with Ockam Orchestrator.
* Provision a trial Space and Project in the Orchestrator.
* Make your Ockam Identity the administrator of your new Project.

### !!steps Generate enrollment ticket for PostgreSQL

In this section we're going to provision an Ockam node that will run alongside
our PostgreSQL database, and provide one of the ends of our point-to-point connection.

![Amazon RDS PostgreSQL setup screen](/blog/snowflake-query-postgres/aws-rds-setup.png)

We need to generate an enrollment ticket to allow a new Ockam Node to 
join the project that was just created. This node will run alongside the 
Amazon RDS PostgreSQL Database, in the network where the Amazon RDS is running:

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 24h \
  --attribute postgres \
  --relay postgres > postgres.ticket
```

### !!steps 

In this command we've set the new ticket to expire in `24 hours`, and a usage 
count of `1`. This means the generated ticket is valid for a single use, and 
that it's valid for 24 hours. A single usage ticket means there is low 
risk associated with mishandling this ticket after use; there's no means 
for an attacker to re-use it like an API token to access any system. 

```shell ! tour
ockam project ticket \
# !focus(1:1)
  --usage-count 1 --expires-in 24h \
  --attribute postgres \
  --relay postgres > postgres.ticket
```

### !!steps 

The Project Membership Credential that's issued will include  
attributes that will be cryptographically attested to by the Project's 
Membership Authority. You can use these attributes to apply policies with 
Attribute Based Access Controls (ABAC) to allow or restrict specific actions and 
communication paths between nodes.

In this example we assign a single attribute of `postgres`. 

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 24h \
# !focus(1:1)
  --attribute postgres \
  --relay postgres > postgres.ticket
```

### !!steps 

The `--relay postgres` flag is a shortcut for creating a policy that allows this 
node to create a relay at the address `postgres`. 

This relay will allow your Snowflake warehouse to establish a secure end-to-end 
encrypted connection to your PostgreSQL database, without requiring you to expose any 
endpoints to the public Internet.

As the final part of this command we pipe the generated ticket to a file named `postgres.ticket`.

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 24h \
  --attribute postgres \
# !focus(1:1)
  --relay postgres > postgres.ticket
```

### !!steps Create a configuration file

We're going to pass the configuration for our Ockam Node as a JSON file, so 
save the configuration to the right into a file named `postgres.json`.

```javascript ! tour
{
  "relay": "postgres",
  "tcp-outlet": {
    "to": "$POSTGRES_ENDPOINT:5432",
    "allow": "snowflake"
  }
}
```

### !!steps

Ockam Command will process the configuration file after the node starts, with the
first instruction being to create a relay with the name `postgres`. This relay will 
allow Snowflake to establish a secure end-to-end encrypted connection to your
RDS Database. It provides the ability to have entirely isolated infrastructure 
running within a private network, without requiring you to expose any endpoints
to the public Internet and no configuring of allow lists to custom network routes.

```javascript ! tour
{
// !focus(1:1)
  "relay": "postgres",
  "tcp-outlet": {
    "to": "$POSTGRES_ENDPOINT:5432",
    "allow": "snowflake"
  }
}
```

### !!steps

An outlet decrypts any messages received by the node and 
then forwards them to the specified broker address.

```javascript ! tour
{
  "relay": "postgres",
// !focus(1:4)  
  "tcp-outlet": {
    "to": "$POSTGRES_ENDPOINT:5432",
    "allow": "snowflake"
  }
}
```

### !!steps

In this example we're giving the interpolated value from the environment
variable `POSTGRES_ENDPOINT`, which we'll set in a later step when we
start the node.

```javascript ! tour
{
  "relay": "postgres",
  "tcp-outlet": {
// !focus(1:1)  
    "to": "$POSTGRES_ENDPOINT:5432",
    "allow": "snowflake"
  }
}
```

### !!steps

We will setup the PostgreSQL Inlet that runs alongside our Snowflake 
warehouse in a moment. Before we do that though, we can define a policy to restrict what 
nodes can connect to our outlet.

This policy applies Attribute Based Access Controls (ABAC) to allow 
other nodes that have the attribute `snowflake` as part of their attested 
credentials. The system denies access to nodes without the `snowflake` attribute.

```javascript ! tour
{
  "relay": "postgres",
  "tcp-outlet": {
    "to": "$POSTGRES_ENDPOINT:5432",
// !focus(1:1)      
    "allow": "snowflake"
  }
}
```

</CodeTour> 

### Option 1 - Run an Ockam node next to PostgreSQL via the AWS Marketplace

<ImageTour>

### !!steps 

The Ockam Node for Amazon RDS is a streamlined way to provision a managed Ockam Node
within your private AWS VPC. If you'd prefer the ability to deploy your Ockam Node
yourself (for example on an existing machine or in a container) jump to our 
instructions on how to [run an Ockam Node manually](#option-2---run-an-ockam-node-next-to-postgresql-manually).

To deploy the node that will allow Snowflake to reach your Amazon RDS PostgreSQL database visit
the [Ockam Node for Amazon RDS PostgreSQL listing in the AWS Marketplace](https://aws.amazon.com/marketplace/pp/prodview-zjugwdx5hp2le), and click the `Continue to Subscribe` button, and then
`Continue to Configuration`. 

On the configuration page choose the region that your Amazon RDS cluster is
running in, and then click `Continue to Launch` followed by `Launch`.

![!tour Ockam node for Amazon RDS PostgreSQL](/blog/snowflake-query-postgres/aws-marketplace-rds.png)

### !!steps Enter stack details

The initial Create Stack screen pre-fills the fields with the correct
information for your node, so you can press `Next` to proceed.

![!tour Ockam node for Amazon RDS - create stack screen](/blog/snowflake-query-postgres/create-stack.png)

### !!steps Enter node configuration

This screen has important details to you need to fill in:

* **Stack name:** Give this stack a recognisable name, you'll see this in 
various locations in the AWS Console. It'll also make it easier to clean these 
resources up later if you wish to remove them.
* **VPC ID:** The ID of the Virtual Private Cloud network to deploy the node in. 
Make sure it's the same VPC where you've deployed your RDS instance.
* **Subnet ID:** Choose same subnet where your RDS instance is deployed.
* **Enrollment ticket:** Copy the contents of the `postgres.ticket` file we 
created earlier and paste it in here.
* **RDS PostgreSQL Database Endpoint:** In the _Connectivity & security_ for 
your Amazon RDS Database you will find Endpoint details. Copy the `Endpoint` 
value for the Private RDS Database that's in the same subnet you chose above.
* **JSON Node Configuration:** Copy the contents of the `postgres.json` file we 
created earlier and paste it in here.

Once the stack has completed, you will need to modify the security group on your
RDS instance to allow access on port `5432` from the instance that was just
created.

We've now completed the highlighted part of the diagram below, and our Amazon 
RDS PostgreSQL node is waiting for another node to connect.

![Amazon RDS PostgreSQL setup](/blog/snowflake-query-postgres/aws-rds-setup.png)

![!tour Ockam node for Amazon RDS PostgreSQL - node configuration screen](/blog/snowflake-query-postgres/stack-details.png)

</ImageTour>

### Option 2 - Run an Ockam Node next to PostgreSQL manually

<CodeTour>

### !!steps Install Ockam Command

The system where you plan to run your Ockam Node will need to be able to reach
your PostgreSQL server (so ensure your network access rules allow it) and needs to
have Ockam Command installed. Run the following command to install and setup the 
environment.

```bash ! tour
curl --proto '=https' --tlsv1.2 -sSfL \
  https://install.command.ockam.io \
  | bash && source "$HOME/.ockam/env"
```

### !!steps Start the PostgreSQL node

Now that Ockam Command is available, move the `postgres.json` and `postgres.ticket`
files we created earlier onto this system. The `postgres.json` file references 
an environment variable named `POSTGRES_ENDPOINT`. Be sure to set the 
environment variable by either exporting it or defining it inline when
starting `ockam`. You also need to replace the `postgres-address.here` value 
with the hostname of your actual PostgreSQL server.

You can now start your Ockam Node with the following command:

```javascript ! tour
POSTGRES_ENDPOINT="postgres-address.here" \
  ockam node create \
  postgres.json \
  --enrollment-ticket postgres.ticket
```

</CodeTour>

### Connect the Ockam node in Snowflake

<CodeTour>

### !!steps Generate enrollment ticket for Snowflake

![Setting up Ockam Node as native Snowflake app](/blog/snowflake-query-postgres/snowflake-setup.png)

One end of our connection is now setup, it's time to connect the Snowflake end. 
We need to generate an enrollment ticket to allow another Ockam Node to
join our project. This node will run in our Snowflake warehouse:

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 2h \
  --attribute snowflake > snowflake.ticket
```

### !!steps

As we did with the earlier ticket, we've reduced the risk associated with
mishandling a ticket by restricting it's permitted usage window. We've again set 
a usage count of `1`, but with a tighter
expiry time of just `2 hours`.

```bash ! tour
ockam project ticket \
# !focus(1:1)
  --usage-count 1 --expires-in 2h \
  --attribute snowflake > snowflake.ticket
```

### !!steps

This credential will include an attested attribute of `snowflake`. You'll 
recall in an earlier step we applied a policy to say that nodes in our 
project must have an attribute of `snowflake` to connect to 
the outlet. The node that enrolls with this ticket will also have the
attribute `snowflake`, and so it will be able to communicate with our PostgreSQL 
node. 

The ticket is then saved to a file called `snowflake.ticket`.

```bash ! tour
ockam project ticket \
  --usage-count 1 --expires-in 2h \
# !focus(1:1)
  --attribute snowflake > snowflake.ticket
```

</CodeTour>


<ImageTour>

### !!steps Configure connection details

Click "Get started" to open the Snowflake setup screen.

![!tour Create Snowflake ticket](/blog/snowflake-query-postgres/snowflake-get-started-app.png)

Take the contents of the file `snowflake.ticket` that we just created and paste
it into "Provide the above Enrollment Ticket" form field in the "Configure app"
setup screen in Snowflake.

![!tour Create Snowflake ticket](/blog/snowflake-query-postgres/snowflake-ticket.png)

### !!steps Grant privileges

To be able to authenticate with Ockam Orchestrator and then 
discover the route to our outlet, the Snowflake app needs to allow 
outbound connections to your Ockam project. 

Toggle the `Grant access to egress and reach your Project` and approve the connection by
pressing `Connect`. 

Toggle the `Grant access to your postgre database` and enter the username and password for your 
PostgreSQL database and store it as a secret in snowflake.


![!tour Grant egress](/blog/snowflake-query-postgres/grant-privileges.png)

### !!steps Configure PostgreSQL database 

Enter the name of the PostgreSQL database.

![!tour Map streams to PostgreSQL tables](/blog/snowflake-query-postgres/configure-database.png)


</ImageTour>


With that, we've completed the last step in the setup. We've now got a complete
point-to-point connection that allows our Snowflake warehouse to securely
query private PostgreSQL database.

![Snowflake push to PostgreSQL setup complete](/blog/snowflake-query-postgres/aws-snowflake-complete.png)

## Running a Query

Now that we have our secure connection set up between Snowflake and PostgreSQL, let's run some queries!

<CodeTour>

### !!steps Create and populate a table in PostgreSQL

First, let's create a table in our PostgreSQL database and insert some data. We can do this directly from Snowflake using the `OCKAM_POSTGRES_EXECUTE` procedure:

```sql ! tour
CALL QUERY_REMOTE_POSTGRESQL__CONNECTOR.EXTERNAL.OCKAM_POSTGRES_EXECUTE($$
  DROP TABLE IF EXISTS PETS;
  CREATE TABLE IF NOT EXISTS PETS (NAME VARCHAR(100), BREED VARCHAR(100));
  INSERT INTO PETS VALUES ('Max', 'Golden Retriever');
  INSERT INTO PETS VALUES ('Bella', 'Poodle');
$$);
```

This query does the following:
1. Drops the `PETS` table if it exists
2. Creates a new `PETS` table with `NAME` and `BREED` columns
3. Inserts two rows of data into the table

### !!steps Query the PostgreSQL table

Now that we have data in our PostgreSQL table, let's query it from Snowflake:

```sql ! tour
CALL QUERY_REMOTE_POSTGRESQL__CONNECTOR.EXTERNAL.OCKAM_POSTGRES_QUERY(
  'SELECT * FROM PETS;');
```

This query will return all rows from the `PETS` table in our PostgreSQL database.

</CodeTour>

The result of this query should be a table with two rows:

| NAME  | BREED             |
|-------|-------------------|
| Max   | Golden Retriever  |
| Bella | Poodle            |

As you can see, we've successfully created a table in PostgreSQL and queried it directly from Snowflake, all through our secure, point-to-point connection!

## Next steps

You now have a secure, point-to-point connection that allows your Snowflake warehouse to
query your private PostgreSQL database directly. This opens up a world of possibilities
for data integration and analysis.

From here you can:

* Join data from Snowflake and PostgreSQL in a single query
* Use PostgreSQL data to enrich your Snowflake datasets
* Perform real-time queries on your operational PostgreSQL database from within Snowflake

If you'd like to explore some other capabilities of Ockam I'd recommend:

* [Replicate tables from PostgreSQL to Snowflake](/blog/snowflake-pull-postgres)
* [Sync tables from Snowflake to PostgreSQL](/blog/snowflake-push-postgres)
* [Creating private point-to-point connections with _any_ database](https://docs.ockam.io/portals/databases)
* [Adding security as a feature in your SaaS product](/blog/building-secure-saas-platforms)

follow or visit us on

Quickstarts

Call a private API from Snowflake

Glenn Gillen

VP of Product, GTM

Call a private API from Snowflake

Glenn Gillen

VP of Product, GTM

Run Queries on Remote PostgreSQL from Snowflake

Glenn Gillen

VP of Product, GTM