siikr_db_setup.sql has hardcoded username for function permission grants

[alekratz]

See: https://github.com/EGjoni/Siikr/blob/main/siikr/siikr_db_setup.sql#L942

Since the siikr DB user is specified in the siikr.conf file, it should be reflected in the SQL as well. To fix this, I think it may be worth splitting up the SQL into a static file that has all of the database creation stuff and function definitions, and then a "template" SQL file that uses the environment variables defined in siikr.conf.

So the SQL template file might look something like this:

GRANT ALL ON FUNCTION public.vector_out(public.vector) TO ${pg_user};
GRANT ALL ON FUNCTION public.vector_recv(internal, oid, integer) TO ${pg_user};
-- ...etc

and then doing an envsubst to create this custom file somewhere (and hopefully cleaning it up afterwards)

source "$script_dir/siikr.conf"
template_out="$(mktemp)"
envsubst "$script_dir/siikr/siikr_db_setup_nonstatic.sql" > "$template_out"
psql -U "$pg_user" -d "$siikr_db" -f "$template_out"
rm "$template_out"

Unless there's an easier way to do it.

[alekratz]

Also, it looks like your local username got included too. I think this line and the next one can be deleted: https://github.com/EGjoni/Siikr/blob/main/siikr/siikr_db_setup.sql#L1264

[EGjoni]

Sounds reasonable.

Though easier alternatives might include:

1. hardcoding the GRANT commands into the setup script and having them run after the .sql file (which avoids the cleanup).
2. removing the GRANT commands entirely, and changing the default db_user in the .conf file to "postgres", which has all permissions to everything by default.