PostgreSQL Protocol: Overview

• RavenDB implements the PostgreSQL protocol, allowing applications and tools that use PostgreSQL, like Power BI and pgAdmin, to retrieve data from a RavenDB database.

• Clients can query the database in SQL, or in RavenDB's native RQL (Raven Query Language).

• To use RavenDB as a PostgreSQL server you need -

  • A license that enables the PostgreSQL Protocol.
  • PostgreSQL explicitly enabled in your settings.

• Installing RavenDB as a secure server allows you to authenticate PostgreSQL clients, granting access only to clients that provide the proper credentials.

• The PostgreSQL protocol is not supported for sharded databases.

• In this article:

  • Enabling PostgreSQL support
    • License
    • Settings
    • PostgreSQL Port
    • Platform support
  • Supported clients
  • Querying over the protocol
  • Security

Enabling PostgreSQL support

License

• PostgreSQL is included in all licenses.
• To connect Power BI to RavenDB, your license must explicitly enable Power BI.
  If your current license doesn't include Power BI Support, you can acquire one that does here.
• Visit Studio's About page to see the features included in your license.

Settings

PostgreSQL support is an experimental feature and must be explicitly enabled in your settings.
To enable the feature, add these two lines to your server's settings.json file and restart the server:

"Integrations.PostgreSQL.Enabled": true,
"Features.Availability": "Experimental"

When setting up a secure server using the setup wizard, turning on the wizard's Experimental features option enables PostgreSQL support and writes both keys to settings.json, so you do not need to add the keys manually.

PostgreSQL Port

• To access RavenDB, your clients need not only the server's URL but also the PostgreSQL Port number.
  By default, the port number is 5433.

• To use a different port, add the following line to your settings.json file, with a port number of your choice, and restart the server:

  "Integrations.PostgreSQL.Port": 5433

Platform support

• To parse incoming SQL, the PostgreSQL integration uses libpg_query, a library that is built separately for each platform (like Windows x64 or Linux ARM64) and ships with the RavenDB server.
• The library does not ship for 32-bit platforms: the Raspberry Pi and Windows x86 packages.
  When the RavenDB server runs on one of these platforms, the PostgreSQL integration will not start even if enabled in your settings, and the server will log a warning.

Supported clients

Any PostgreSQL client can connect to RavenDB over the protocol; the table below lists popular clients.
Power BI, pgAdmin, and Microsoft Fabric also send many PostgreSQL metadata queries of their own. RavenDB recognizes these queries, so you can use these clients' interfaces without repeated errors.

Client	Description
Power BI (the Power BI Desktop application and the online service)	Build reports and dashboards over RavenDB data.
pgAdmin	Browse a RavenDB database and run SQL or RQL queries using the Query Tool.
Microsoft Fabric	Copy data out of a RavenDB database using a Fabric Copy job.
Npgsql	The .NET data provider for PostgreSQL. Versions 6 through 10 are supported.

Querying over the protocol

Clients that connect to a RavenDB database over PostgreSQL see each document collection as a table in a public schema.
You can query in two languages:

• SQL
  The RavenDB server translates SQL SELECT queries to RQL and runs the translated queries on the database.
  See the supported SQL subset and its limitations in Querying with SQL.

• RQL
  RavenDB recognizes RQL input and runs the query as it is.
  e.g., from Orders where ShipTo.Country == 'France'.

Security

To allow RavenDB to authenticate PostgreSQL clients:

• Set RavenDB as a Secure Server.

  If RavenDB is not set as a secure server, the server will require no authentication over the PostgreSQL protocol.
  Allowing just any client to connect to your database (via PostgreSQL or otherwise) without authentication is risky, and should in general be avoided.

• Create PostgreSQL Credentials using Studio.
  PostgreSQL credentials are a user name and a password that a client must provide to access the database.

A secure server accepts PostgreSQL connections only over TLS.
The server refuses a non-TLS connection before the client sends its password, so the password never travels unencrypted.
To connect to a secure server, enable SSL in your client's connection settings.
e.g., in an Npgsql connection string, include SSL Mode=Require.