1.133.0 SQL `PostgresError: syntax error at or near "."` Database name is not escaped properly
How can I create the role in sql?
51 Replies
:wave: Hey @raldone01,
Thanks for reaching out to us. Please carefully read this message and follow the recommended actions. This will help us be more effective in our support effort and leave more time for building Immich :immich:.
References
- Container Logs:
docker compose logs docs
- Container Status: docker ps -a docs
- Reverse Proxy: https://immich.app/docs/administration/reverse-proxy
- Code Formatting https://support.discord.com/hc/en-us/articles/210298617-Markdown-Text-101-Chat-Formatting-Bold-Italic-Underline#h_01GY0DAKGXDEHE263BCAYEGFJA
Checklist
I have...
1. :ballot_box_with_check: verified I'm on the latest release(note that mobile app releases may take some time).
2. :ballot_box_with_check: read applicable release notes.
3. :ballot_box_with_check: reviewed the FAQs for known issues.
4. :ballot_box_with_check: reviewed Github for known issues.
5. :ballot_box_with_check: tried accessing Immich via local ip (without a custom reverse proxy).
6. :ballot_box_with_check: uploaded the relevant information (see below).
7. :ballot_box_with_check: tried an incognito window, disabled extensions, cleared mobile app cache, logged out and back in, different browsers, etc. as applicable
(an item can be marked as "complete" by reacting with the appropriate number)
Information
In order to be able to effectively help you, we need you to provide clear information to show what the problem is. The exact details needed vary per case, but here is a list of things to consider:
- Your docker-compose.yml and .env files.
- Logs from all the containers and their status (see above).
- All the troubleshooting steps you've tried so far.
- Any recent changes you've made to Immich or your system.
- Details about your system (both software/OS and hardware).
- Details about your storage (filesystems, type of disks, output of commands like fdisk -l and df -h).
- The version of the Immich server, mobile app, and other relevant pieces.
- Any other information that you think might be relevant.
Please paste files and logs with proper code formatting, and especially avoid blurry screenshots.
Without the right information we can't work out what the problem is. Help us help you ;)
If this ticket can be closed you can use the /close command, and re-open it later if needed.
Successfully submitted, a tag has been added to inform contributors. :white_check_mark:I am using
ghcr.io/immich-app/postgres:14-vectorchord0.3.0-pgvectors0.2.0Please post more detail. Is this a new install or an update? If the latter, what did you change?
Update
I just pulled and changed:
to
ghcr.io/immich-app/postgres:14-vectorchord0.3.0-pgvectors0.2.0
I am still on redis:6.2-alpine@sha256:148bb5411c184abd288d9aaed139c98123eeb8824c5d3fce03cf721db58066d8 if that matters.
I still had command: ["postgres", "-c" ,"shared_preload_libraries=vectors.so", "-c", 'search_path="$$user", public, vectors', "-c", "logging_collector=on", "-c", "max_wal_size=2GB", "-c", "shared_buffers=512MB", "-c", "wal_compression=on"] from the previous production yaml.
I removed that and the db logs now look more reasonable.
I get no output at all from immich-server though which is very weird.
Also immich-server refuses to stop. All other containers are fine. (via compose) I can force it with the normal docker cli.
It might not be immichs fault.
I found segfaults in dmesg.
Nvidia broke the container toolkit on arch linux with their latest release: https://github.com/NVIDIA/nvidia-container-toolkit/issues/1101
I have now downgraded and will try again.
Okay it was nvidias fault.
There is some postgres escaping mistake in the migration I assume.
Seems like the database name is not properly escaped.
What is your database name for immich?
2Izuz2a_X)F}~8H<86*R~L_r`sx<<Yf.
Why...
🙃
Are you like, trying to cause problems? :KEKW:
It's funny I actually never had any issues with all the other apps I selfhost.
When I first started selfhosting I thought that having a difficult to guess database name was good security practise.
We should handle it, and we will make a fix probably to support it
But my god
Thanks you're the best.
Also
ghcr.io/immich-app/postgres:14-vectorchord0.3.0-pgvectors0.2.0 is pinned to posgres-14-vectorchord0.3.0. Updating postgres in docker between releases is a huge pain for users. Are there plans to support auto updates to the db? I switched to pgautoupgrade/pgautoupgrade:17-debian to update all may dbs scattered from version 13-16 to one unifed pg version.
Should i wait for the fix or connect manually and run some commands?Yes, we plan to update postgres to pg18 when that releases, and we plan to have an automated postgres update flow for people that follow our default installation
LOL
Patch release will probably be today, not sure if this is my highest priority for a fix right now though 🤣
If you rename your database you could get it working right now 😉
Just curious if it will rerun when the patch comes.
https://github.com/immich-app/immich/blob/bc8e08f5e89f533cee2218759f09d8a574f3c5df/server/src/repositories/database.repository.ts#L122
https://github.com/immich-app/immich/blob/bc8e08f5e89f533cee2218759f09d8a574f3c5df/server/src/repositories/database.repository.ts#L124
sql.raw missing :D
Btw, given you are getting "role "root" does not exist", do you not have a POSTGRES_PASSWORD env set on the database container?
Still getting it like clockwork. (Discord is very annyoing with their short message limits.)
Btw base service is just
Yea it'll be because you use the USER_FILE and such
its the damn heath check again
The healthcheck specifically subs in the username from the POSTGRES_USER env var
You can just override the healthcheck if you want those errors to go away
https://github.com/pgautoupgrade/docker-pgautoupgrade/issues/93
And here I was excited for:
We can probably add support to the _FILE style vars in future
wait is your new image not supporting them at all?
It'll support whatever the base postgres container supports
But the healthcheck doesn't support them
:phew:
Actually working healthcheck:
That was painful.
Double $ is needed for docker compose
So when I try to reproduce this locally, the command fails the first time it runs, but when I restart the service, it succeeds the second time 🤔
I don't know if I would be a great help but I can try to help you figure it out.
I’m not fond of merging this
PostgreSQL Documentation
4.1. Lexical Structure
4.1. Lexical Structure # 4.1.1. Identifiers and Key Words 4.1.2. Constants 4.1.3. Operators 4.1.4. Special Characters 4.1.5. Comments 4.1.6. Operator Precedence …
“SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($)”
So per the Postgres docs your DB starting with a number is not lexically correct
Ah, I see
“Quoted identifiers can contain any character, except the character with code zero. (To include a double quote, write two double quotes.) This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands. The length limitation still applies.”
Still feels like a lot for such an edge case to me
That’s weird, sql.table is supposed to escape this already
💀
Might be something to raise with kysely
What was happening before was it seemed that it got confused by the period
So it was putting extra quotes around that, I think it was treating it like a reference?
So like you would have tableName.columnName
Yeah I think that’s the issue
So it is escaping it, it’s just not handling the period correctly
Which would need to turn into "tableName"."columnName"
So it's adding that extra "." In this case, which is incorrect
But also nobody should have periods in their damn database names 🤣
Kysely after they patch this: https://xkcd.com/1172/
xkcd: Workflow
There are probably children out there holding down spacebar to stay warm in the winter! YOUR UPDATE MURDERS CHILDREN.
For now we could probably change it to sql.raw surrounded by double quotes instead of using sql.table
I'd rather just not
And have it fixed upstream
Maybe, Im just wondering if it might be difficult for them to fix it if there are valid uses of sql.table that have the period that’s supposed to work like that. I guess we can make an issue and see where it goes
That'd be my preference
It only affects people who have put a period in their database name
Of which we currently have one user
We can just direct people to not do that lol
Yeah that’s fair lmao
Hhahah an issue is fine. I have since renamed the db anyways. :D
When you create one link it here. I am curious.
https://github.com/kysely-org/kysely/issues/1468
I create a minimal reproducer. Sorry for bugging you with this.