how to use system's environnement variables in sql script

Question

i want to use several linux environment variables inside a sql script that i run with this command :

psql -h XXX -p 5432 -U XXX -d XXX -f ./script.sql

the content of the script is a succession of this line, but the referencing doesn't work, i.e. it doesn't replace the value, even though env variables exist

alter user airflow with password '$PASSWORD';

do you have a way ?

I tried to run a script with references to linux environment variables, but it interprets it as a string

Answer 1

You can use envsubst to substitute environment variables :

psql -h XXX -p 5432 -U XXX -d XXX -f <(envsubst < ./script.sql)