serverB:~$ sudo -i -u postgres
postgres@serverB:~$ ALTER SYSTEM SET listen_addresses TO '*';
ALTER: command not found
I would like to change some settings in PostgreSQL 15, but I am not sure how to use the ALTER SYSTEM command. I am apparently using the wrong program.
And
sudo su postgres
postgres@serverB:/home/ubuntu$ psql -c "ALTER SYSTEM SET listen_addresses TO '*'"
> could not change directory to "/home/ubuntu": Permission denied
You're trying to run PostgreSQL commands as shell commands, and the two are not the same. If you need to use peer authentication to connect as postgres, then run psql like this:
sudo -u postgres psql
Once connected, you can run your ALTER SYSTEM command, but for listen_addresses you will have to restart PostgreSQL (via service or systemctl).
I don't know for Ubuntu specifically, but sudo su is generally frowned upon for the reason you found (it does not reset the environment, so e.g. $HOME is not accessible). Use sudo -s if you need a shell.
It looks like the "postgres" user doesn't have permission to write to the postgresql.auto.conf file.
ALTER SYSTEM is designed to write changes to a configuration file named postgresql.auto.conf, you should never edit this file manually.
If you connect to postgres and run the following it should print the path to your configuration files, postgresql.conf and postgresql.auto.conf
psql
show config_file;
select current_user;
You could do it via:
psql -c "show config_file; select current_user;"
Once you have the path and confirm the user you are connecting to the database as you need to confirm that user has permission to the postgresql.auto.conf. You need to restart postgres when changing the listen_address parameter.
Also the special entry * corresponds to all available IP interfaces, it's usually a best practice to make this more restrictive.
HTH.
su postgres without a -, so that the home directory was not set properly.
postgresql.auto.conf manually just fine as long as PostgreSQL is not currently running. However, it will be fully overwritten by PostgreSQL so don't expect e.g. comments to be retained.
alter systemcan be executed on SQL connection only but the original author of the question tried to execute SQL command onbashcommand line. The solution is simple (just runpsqlfirst) but the more important lesson to learn is that you have to understand which program is active (e.g.bashvspsql) before entering any commands as superuser.