A Guide to Cross-Database Queries: PostgreSQL FDW & DbLink

John Negoita

1 year ago

When building a multi-tenant application with PostgreSQL, you might choose to store your tenant-specific data in separate schemas within the same database. This is a common and efficient approach.

However, what if you need to access information like tenant credentials that are stored in a completely separate database, possibly even on a different server? For example when using something like Keycloak.

This is where the power of PostgreSQL’s cross-database querying capabilities comes into play.

What You’ll Learn:

Understand the challenges of accessing data across PostgreSQL databases.
Learn about Foreign Data Wrappers (FDW) and how they simplify postgres query across databases.
Explore using postgres_fdw, a specialized FDW for connecting to other PostgreSQL databases.
Discover how to create and utilize foreign tables to access remote data.
Explore dblink as an alternative for older PostgreSQL versions (pre-9.3).
Learn how to enhance security and optimize performance for cross-database queries.

Possible Solutions:

Let’s explore the methods to seamlessly connect your multi-tenant application to external credential data:

Method 1 – Using Foreign Data Wrappers (FDW):

Foreign Data Wrappers are a powerful feature in PostgreSQL that allows you to access data residing in external sources, including other PostgreSQL databases, as if they were local tables. This approach offers a transparent and efficient way to query and manipulate data across databases.

Step 1: Enable postgres_fdwBefore using postgres_fdw, you need to enable it in your PostgreSQL database:
```
CREATE EXTENSION postgres_fdw;
```
Step 2: Create a Foreign ServerNext, you need to define a foreign server that represents your external credentials database. This involves providing connection details such as the host, database name, and port. For this example, let’s assume your credentials database is named ‘keycloak_db’ and resides on host ‘192.168.1.100’ listening on the standard PostgreSQL port ‘5432’. You would execute the following SQL command:
```
CREATE SERVER keycloak_server FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '192.168.1.100', port '5432', dbname 'keycloak_db');
```
Step 3: Create User MappingYou need to establish a user mapping to allow your PostgreSQL user to connect to the foreign server. This step involves specifying the username and password for authentication with the ‘keycloak_db’ database:
```
CREATE USER MAPPING FOR current_user SERVER keycloak_server
OPTIONS (user 'keycloak_user', password 'keycloak_password');
```
Replace ‘keycloak_user’ and ‘keycloak_password’ with the actual credentials.
Step 4: Create a Foreign TableNow, you can create a foreign table in each tenant schema that points to the credentials table in your ‘keycloak_db’ database. Let’s assume your credentials table is named ‘credentials’. Here’s how you would create the foreign table:
```
-- Within each tenant schema
CREATE FOREIGN TABLE tenant_credentials (
    tenant_id INT,
    username TEXT,
    password TEXT
) SERVER keycloak_server OPTIONS (schema_name 'public', table_name 'credentials');
```
Adjust ‘public’ and ‘credentials’ if your table is in a different schema or has a different name.
Step 5: Create ViewsFinally, you can create views within each tenant schema that combine data from the tenant’s tables with the credentials fetched from the foreign table. For instance:
```
-- Within each tenant schema
CREATE VIEW tenant_data_with_credentials AS
SELECT
    td.*,
    tc.username,
    tc.password
FROM
    tenant_data td -- Replace 'tenant_data' with your actual tenant table
JOIN
    tenant_credentials tc ON td.tenant_id = tc.tenant_id;
```
This view joins a hypothetical ‘tenant_data’ table with the ‘tenant_credentials’ foreign table, giving you a unified view of tenant data and credentials.

Method 2 – Using dblink

For those using PostgreSQL versions prior to 9.3, FDW might not be an option. Thankfully, PostgreSQL provides dblink, a module enabling cross-database queries using function calls.

Step 1: Install dblink Dblink is part of the PostgreSQL contrib package, so you might need to install it separately.

Step 2: Create a Function to Retrieve CredentialsYou can define a function that establishes a connection to the ‘keycloak_db’ database and fetches the user credentials. Below is an example:

CREATE OR REPLACE FUNCTION get_tenant_credentials(tenant_id INT)
RETURNS TABLE (username TEXT, password TEXT) AS $$
BEGIN
    RETURN QUERY SELECT c.username, c.password
    FROM dblink('host=192.168.1.100 port=5432 dbname=keycloak_db user=keycloak_user password=keycloak_password',
           'SELECT username, password FROM credentials WHERE tenant_id = ' || tenant_id)
         AS c(username TEXT, password TEXT);
END;
$$ LANGUAGE plpgsql;

Replace the connection details and table/column names with your actual values.

Step 3: Create Views Using the FunctionYou can then create views in each tenant schema that leverage this function to retrieve and join credentials:
```
-- Within each tenant schema
CREATE VIEW tenant_data_with_credentials AS
SELECT
    td.*,
    gc.username,
    gc.password
FROM
    tenant_data td
JOIN
    get_tenant_credentials(td.tenant_id) gc ON TRUE; -- Assuming 'tenant_data' has a 'tenant_id' column
```
This view uses the get_tenant_credentials function to fetch the username and password for the corresponding tenant ID from ‘keycloak_db’.

Conclusion

Accessing data across different PostgreSQL databases doesn’t have to be a complex task. By using features like Foreign Data Wrappers and dblink, you can efficiently query data across databases, simplifying the development of applications like your multi-tenant app.

Important: While the provided SQL code examples offer a good starting point, you may need to modify them based on your specific database and schema structures. For the most accurate and up-to-date information, always consult the official PostgreSQL documentation. Additionally, carefully consider security best practices when managing credentials across databases.