Running process as another user causes permission denied error when setting up database connection.

[eseglem]

• asyncpg version: 0.25.0
• PostgreSQL version: 13
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: Both RDS and Docker. Not a PG related issue.
• Python version: 3.8.11 (RedHat)
• Platform: CentOS 7
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?: n/a
• Can the issue be reproduced under both asyncio and
  uvloop?: Untested, but shouldn't be relevant.

And I ran into this issue when upgrading from 0.23.0 to 0.25.0, but I believe it was introduced in 0.25.0 based on my reading of the git history.

I am using supervisord to run an app as a different user. When it gets to https://github.com/MagicStack/asyncpg/blob/master/asyncpg/connect_utils.py#L542 when setting up a connection. That resolves the path to /root/.postgresql/postgresql.key and results in an a Permission denied error when it calls .exists() on a file within /root/ as a non root user.

I believe this is because http://supervisord.org/subprocess.html#subprocess-environment doesn't change HOME in the path, and pathlib still sees HOME="/root". So I will be tinkering with unsetting that.

Even if changing the environment fixes my issue, it may be worth adding exception handling for when those paths resolve to a directory the user running asyncpg cannot access.

[lqhuang]

Thanks, I have suffered totally same issue where I'm using docker + supervisord + asyncpg.

Through I found this is an expected situation documented in supervisord docs, it may not only specify to supervisord. AsyncPg will also fail for every systems which environments have been messed up intentionally or unintentionally.

[tarsil]

Any news how to fix this? I'm having the same issue with docker, nginx, supervisor and asyncpg and the version is the 0.26.0

[eseglem]

@tarsil I believe I set environment=HOME="" within the supervisor config. If that doesn't work I'll go dig up what I did.

[tarsil]

@eseglem home as empty string? Because if not, I tried and it doesn't work

[eseglem]

@tarsil yeah, effectively unset it so asyncpg doesn't try to look in it.

[tarsil]

@eseglem did you add it to the program? If yes, I will give it a try tomorrow and let you know :)

[tarsil]

@eseglem under program it still fails.

[aemr3]

Setting HOME to a different path inside Dockerfile fixed this problem for us. Our celery worker was running as nobody user but HOME was set to /root.

[tarsil]

@eseglem after 300 tries, your solution worked :)

[verhovensky]

I also faced the issue. For me it was a big surprise.
Solved by upgrading postgresql from 15 to 15.1 version and setting HOME env variable in docker-compose.yml file

environment:
    - HOME=""

Also take into consideration docker volumes if you use docker.
For me it was crucial to actually RENAME volume with postgresql data, because deleting them was not working.

[himalczyk]

I faced this issue as well. In my case the container was missing the HOME path to be set to the directory in which the apps is contained.
Eg.
HOME: /home/app_name

This changes where the application looks for ssl keys (such as postgresql.key).

Default Behavior: When the application inside the container runs and tries to access /root/.postgresql/postgresql.key, it encounters a "Permission denied" error because it likely does not have the necessary permissions to access files in /root/.

Custom HOME Directory: By setting the HOME environment variable to /home/app_name, you are telling the operating system and your application to treat /home/app_name as the home directory. As a result, the application will now look for the SSL key files in /home/app_name/.postgresql/ instead of /root/.postgresql/.