0
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

2 Answers 2

1

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.

-1

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.

1
  • 1
    No. The problem is that OP ran su postgres without a -, so that the home directory was not set properly. Nov 3, 2022 at 7:51

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .