Journal Entry, February 24th to March 3rd 2023

Postgres

I have a project that compliments Laravel Forge by storing a centralized copy of the .env file for all sites and servers in the fleet of Digital Ocean droplets.

There is an environment_raw text column for the .env file with an embedded environment jsonb column that has an id, key, and value tuple when we look at the field in something like TablePlus.

Get a distinct list of environment variable keys

WITH fields_rowset AS (
    SELECT
        jsonb_array_elements(environment) AS fields
    FROM
        sites
) SELECT DISTINCT
    fields -> 'key' AS key_name
    -- fields -> 'value' AS key_value
FROM
    fields_rowset
WHERE
    fields -> 'value' IS NOT NULL
ORDER BY
    key_name ASC

Get a list of sites where STRIPE_SECRET is the specified value

The following queries utilize a few different methodologies for searching the embedded schema. We start with a singular key-value pair.

SELECT
    *
FROM
    sites
WHERE
    environment @> '[{"key": "STRIPE_SECRET", "value": "sk_test_DUMMY"}]';

Get a list of sites where STRIPE_SECRET is one of the specified values

SELECT
    *
FROM
    sites
WHERE
    environment @> ANY (ARRAY['[{"key": "STRIPE_SECRET", "value": "sk_live_DUMMY"}]']::jsonb[]);

Get a list of sites where STRIPE_SECRET is any value

SELECT
    *
FROM
    sites
WHERE
    environment @> ANY (ARRAY['[{"key": "STRIPE_SECRET"}]']::jsonb[]);

Get a list of sites where any variable has the specified value

SELECT
    *
FROM
    sites
WHERE
    environment @> ANY (ARRAY['[{"value": "sk_live_DUMMY"}]']::jsonb[]);

Get a list of sites where STRIPE_KEY or STRIPE_SECRET are their specified values

The array and any keywords are more versatile approaches but require casting to jsonb explicitly. There is another syntax like environment -> 'key' @> '"APP_DEBUG"' but I can't produce the expected result. It's an empty result despite being configured in just about every Laravel environment.

SELECT
    *
FROM
    sites
WHERE
    environment @> ANY (ARRAY['[{"key": "STRIPE_KEY", "value": "pk_test_DUMMY"}]', '[{"key": "STRIPE_SECRET", "value": "sk_test_DUMMY"}]']::jsonb[]);

Conclusion

While it is redundant for an embedded schema of key/value pairs, it makes the data model easier to query than guessing which variables are present on any given system. Some machines have WordPress installed with totally different settings than Laravel. There are also multiple Laravel versions where certain variables have changed names throughout the years.

There are shortcomings to the approach as this is separate data to Forge or the servers themselves, but it is a much faster interface than making API calls to every server to find this information. One shortcoming was not having a versioned schema for historical data for auditing reasons.

After creating a very rudimentary version of this, I realized tools like Doppler or Infisical offer a more robust centralized place for secrets management far beyond what my initial version could deliver.