Bug#919385: postgresql-common: alter system set port ignored by pg-commands
Re: To wim.bert...@ucll.be 2019-02-27 <20190227143731.gc25...@msg.df7cb.de> > 4. Patch PostgreSQL such that it puts the port into external_pid_file, > i.e. into /var/run/postgresql/11-main.pid > > The bottom line is that #4 looks most attractive, but I'm not fixing > this in postgresql-common now. I will try sending a patch for #4 to > upstream and see if it gets accepted for PG 12+. If so, we can still > patch the older versions on our side. I've had another look into this, and as the patch hasn't happened. I'm documenting this as a configuration to avoid now. Christoph
Bug#919385: postgresql-common: alter system set port ignored by pg-commands
Hallo, in my opinion the cleanest patch would be to change the file location of postgresql.auto.conf to the /etc/postgresql/version/cluster directory. But a you pointed out, this is hardcoded into postgresql. So a patch to postgresql for enabling this, means making this a compile time option instead of being hardcoded. This option eg --location_of_postgresql_auto could then be set at compile time. hth, Wim Van: Christoph Berg Verzonden: woensdag 27 februari 2019 15:37 Aan: Wim Bertels; 919...@bugs.debian.org Onderwerp: Re: Bug#919385: postgresql-common: alter system set port ignored by pg-commands I was looking into several ideas that all have some problems. 0. Look into pg_settings Doesn't work because we can only query the database if we already know the port 1. Put the port on the postgres command line (postgres -p ) Works, but then restarting the server is hard once the port got changed. `pg_ctl restart` will even preserve the last command line. 2. Put the port into a file next to /var/run/postgresql/11-main.pid Has several consistency issues. If the port is changed, the file is not updated immediately. If the cluster is shut down, should the file be removed? 3. Write a suid wrapper that read postgresql.auto.conf suid programs are a security nightmare. 4. Patch PostgreSQL such that it puts the port into external_pid_file, i.e. into /var/run/postgresql/11-main.pid Patching the server is evil, and it breaks applications reading the external file that are not prepared to ignore extra information The bottom line is that #4 looks most attractive, but I'm not fixing this in postgresql-common now. I will try sending a patch for #4 to upstream and see if it gets accepted for PG 12+. If so, we can still patch the older versions on our side. Christoph
Bug#919385: postgresql-common: alter system set port ignored by pg-commands
I was looking into several ideas that all have some problems. 0. Look into pg_settings Doesn't work because we can only query the database if we already know the port 1. Put the port on the postgres command line (postgres -p ) Works, but then restarting the server is hard once the port got changed. `pg_ctl restart` will even preserve the last command line. 2. Put the port into a file next to /var/run/postgresql/11-main.pid Has several consistency issues. If the port is changed, the file is not updated immediately. If the cluster is shut down, should the file be removed? 3. Write a suid wrapper that read postgresql.auto.conf suid programs are a security nightmare. 4. Patch PostgreSQL such that it puts the port into external_pid_file, i.e. into /var/run/postgresql/11-main.pid Patching the server is evil, and it breaks applications reading the external file that are not prepared to ignore extra information The bottom line is that #4 looks most attractive, but I'm not fixing this in postgresql-common now. I will try sending a patch for #4 to upstream and see if it gets accepted for PG 12+. If so, we can still patch the older versions on our side. Christoph
Bug#919385: postgresql-common: alter system set port ignored by pg-commands
Re: Wim Bertels 2019-02-26 <1551198329.5922.23.ca...@ucll.be> > why are they different? if it's important that postgresql.auto.conf > isn't readable by other, then why is postgresql.conf, The problem is the PGDATA directory permissions, not so much the permissions of the file. PG checks if the permissions of that directory are 700, and refuses to start otherwise. (In PG11, the check got relaxed a bit so 750 is also ok now, but that won't change our problem much in practice.) > if it is an option: storing the postgresql.auto.conf in the > /etc/postgresql/11/oefensets/ dir would open the option to set the > permissions to -rw-r--r-- > Or is there a good reason not to store this file here? PG insists that this file is located in PGDATA. There is no option to move it elsewhere like there is for postgresql.conf. Christoph
Bug#919385: postgresql-common: alter system set port ignored by pg-commands
> But in actual practise, there is a problem, postgresql.auto.conf is > stored inside PGDATA, which is not readable by anyone except postgres > and root, so settings modified that way are only visible (and used) > for these users. ok, thank you for the clarification Christoph, root and postgres do indeed give the correct information, other users don't. > > I don't see how we can sanely get out of this problem. The idea of > connecting to the server to query pg_settings has the problem that > it's more heavyweight, and of course we need to know the port to be > able to connect... Storing the port in yet another location doesn't > seem attractive either. if i compare the permission settings of the 2 conf files: -rw-r--r-- 1 postgres postgres 22983 feb 14 12:45 /etc/postgresql/11/oefensets/postgresql.conf -rw--- 1 postgres postgres 385 mrt 2 2018 /var/lib/postgresql/11/oefensets/postgresql.auto.conf why are they different? if it's important that postgresql.auto.conf isn't readable by other, then why is postgresql.conf, if it is an option: storing the postgresql.auto.conf in the /etc/postgresql/11/oefensets/ dir would open the option to set the permissions to -rw-r--r-- Or is there a good reason not to store this file here? > > Christoph -- mvg, Wim Bertels -- If more of us valued food and cheer and song above hoarded gold, it would be a merrier world. -- J.R.R. Tolkien
Bug#919385: postgresql-common: alter system set port ignored by pg-commands
Control: tag -1 moreinfo Re: wim 2019-01-15 <154755510710.20704.3906668621163200728.reportbug@zwerfkat> > 1. connect with psql to your local instance > 2. use alter system to set the port to a non default port > 3. restart postgresql > 4. you will notice that pg_lsclusters still lists the old port instead of the > changed port by alter system > 5. you cannot connect with psql unless with specifying the port Hi Wim, this has actually been fixed in postgresql-common 170 in October 2015, postgresql.auto.conf is now also read. But in actual practise, there is a problem, postgresql.auto.conf is stored inside PGDATA, which is not readable by anyone except postgres and root, so settings modified that way are only visible (and used) for these users. I don't see how we can sanely get out of this problem. The idea of connecting to the server to query pg_settings has the problem that it's more heavyweight, and of course we need to know the port to be able to connect... Storing the port in yet another location doesn't seem attractive either. Christoph
Bug#919385: postgresql-common: alter system set port ignored by pg-commands
Source: postgresql-common Severity: normal Hallo, this report is long overdue, but i tested i today and still exists, probably debian specific for --cluster version/db option how to reproduce: 1. connect with psql to your local instance 2. use alter system to set the port to a non default port 3. restart postgresql 4. you will notice that pg_lsclusters still lists the old port instead of the changed port by alter system 5. you cannot connect with psql unless with specifying the port alter scenario that does work: do the same but not using alter system, but by editing the port in postgresql.conf likely cause: postgresql.auto.conf in the data_directory is not read or ignored by commands as psql, pg_lsclusters,.. a short recording about this behaviour(2 minutes) using psql: https://u0082489.webontwerp.ucll.be/opname.ogv pg_lscluster (and psql) example: " $ pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 11 old 5432 online 11 /var/log/postgresql/postgresql-11-old.log [wim] # ALTER SYSTEM SET port TO 5433; $ pg_ctlcluster 11 old restart $ pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 11 old 5432 online 11 /var/log/postgresql/postgresql-11-old.log $ psql -h /var/run/postgresql/ psql: could not connect to server: Bestand of map bestaat niet Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql//.s.PGSQL.5432"? $ psql --cluster 11/old psql: could not connect to server: Bestand of map bestaat niet Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql//.s.PGSQL.5432" $ psql -h /var/run/postgresql/ -p 5433 psql (11.1 (Debian 11.1-1.pgdg90+1)) [wim] # \conninfo You are connected to database "wim" as user "wim" via socket in "/var/run/postgresql/" at port "5433". " vs " $ pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 11 old 5432 online 11 /var/log/postgresql/postgresql-11-old.log $ change port in postgresql.conf using editor to 5433 $ pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 11 old 5433 online 11 /var/log/postgresql/postgresql-11-old.log older ignored report since it's debian specific: https://www.postgresql.org/message-id/7d92073a-524c-2bad-af9c-0e00256619a4%40ucll.be $ psql -h /var/run/postgresql/ psql: could not connect to server: Bestand of map bestaat niet Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql//.s.PGSQL.5432"? $ psql --cluster 11/old psql (11.1 (Debian 11.1-1.pgdg90+1)) [wim] # \conninfo You are connected to database "wim" as user "wim" via socket in "/var/run/postgresql" at port "5433". " So the undesirable behaviour is (using alter system set port): * pg_lsclusters is listing the wrong port (not good) * psql --cluster version/clustername is not working (annoying) ** while it does work not using alter system but editing postgresql.conf as usual Probably affects all debian versions. (debian and apt.postgresql.org) hth, Wim -- System Information: Debian Release: 9.6 APT prefers stable-updates APT policy: (500, 'stable-updates'), (500, 'stable') Architecture: amd64 (x86_64) Kernel: Linux 4.9.0-8-amd64 (SMP w/8 CPU cores) Locale: LANG=nl_BE.utf8, LC_CTYPE=nl_BE.utf8 (charmap=UTF-8), LANGUAGE=nl_BE.utf8 (charmap=UTF-8) Shell: /bin/sh linked to /bin/dash Init: systemd (via /run/systemd/system) # dpkg -l | grep postgresql ii libreoffice-sdbc-postgresql 1:5.2.7-1+deb9u4 amd64PostgreSQL SDBC driver for LibreOffice ii pgdg-keyring 2018.2 all keyring for apt.postgresql.org ii postgresql-10 10.6-1.pgdg90+1 amd64object-relational SQL database, version 10 server ii postgresql-11 11.1-1.pgdg90+1 amd64object-relational SQL database, version 11 server ii postgresql-11-pldebugger 1:1.0-10-g2a298eb-1.pgdg90+2 amd64PostgreSQL pl/pgsql Debugger API ii postgresql-9.49.4.20-1.pgdg90+1 amd64object-relational SQL database, version 9.4 server ii postgresql-9.59.5.15-1.pgdg90+1 amd64object-relational SQL database, version 9.5 server ii postgresql-9.69.6.11-1.pgdg90+1 amd64object-relational SQL database, version 9.6 server ii postgresql-9.6-ip4r 2.4-1.pgdg90+1 amd64IPv4 and IPv6 types for PostgreSQL 9.6 ii postgresql-client 11+197.pgdg90+1 all front-end prog
