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
.
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
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"}]';
SELECT
*
FROM
sites
WHERE
environment @> ANY (ARRAY['[{"key": "STRIPE_SECRET", "value": "sk_live_DUMMY"}]']::jsonb[]);
SELECT
*
FROM
sites
WHERE
environment @> ANY (ARRAY['[{"key": "STRIPE_SECRET"}]']::jsonb[]);
SELECT
*
FROM
sites
WHERE
environment @> ANY (ARRAY['[{"value": "sk_live_DUMMY"}]']::jsonb[]);
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[]);
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.