Skip to content

Changes to the public schema in PostgreSQL 15 and how to handle upgrades

In September 2021, a patch for the upcoming PostgreSQL version 15 was committed which introduces a visible change for users: the CREATE privilege for the public schema is no longer set by default. This is a recommendation from CVE-2018-1058.

What does that mean for the average (non-superuser) user?

In PostgreSQL 14:

postgres=# SELECT version();
                                                version                                                
-------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

postgres=# CREATE ROLE unprivileged WITH LOGIN;
CREATE ROLE

postgres=# CREATE DATABASE priv_test;
CREATE DATABASE

postgres=# \c priv_test 
You are now connected to database "priv_test" as user "ads".

priv_test=# \dn+ public
                       List of schemas
  Name  | Owner | Access privileges |      Description       
--------+-------+-------------------+------------------------
 public | ads   | ads=UC/ads       +| standard public schema
        |       | =UC/ads           | 
(1 row)

We see that PUBLIC (the second line in Access privileges) has USAGE (U) and CREATE (C) privileges. A regular user can create a table in the public schema:

priv_test=# SET SESSION ROLE unprivileged;
SET
priv_test=> SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

priv_test=> CREATE TABLE priv_test (id INT);
CREATE TABLE
priv_test=> \dp priv_test 
                               Access privileges
 Schema |   Name    | Type  | Access privileges | Column privileges | Policies 
--------+-----------+-------+-------------------+-------------------+----------
 public | priv_test | table |                   |                   | 
(1 row)

 

This is how the public schema privileges look in PostgreSQL 15:

postgres=# SELECT version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 15beta3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

postgres=# CREATE ROLE unprivileged WITH LOGIN;
CREATE ROLE

postgres=# CREATE DATABASE priv_test;
CREATE DATABASE

postgres=# \c priv_test 
You are now connected to database "priv_test" as user "ads".

priv_test=# \dn+ public
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   | 
(1 row)

The C (CREATE) is missing. And the CREATE TABLE for a regular user is no longer working by default:

priv_test=# SET SESSION ROLE unprivileged;
SET
priv_test=> SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

priv_test=> CREATE TABLE priv_test (id INT);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE priv_test (id INT);

But how is this change handled in upgrades?

 

 

Continue reading "Changes to the public schema in PostgreSQL 15 and how to handle upgrades"

PostgreSQL Upgrades are hard!

Together with Lætitia Avrot and Nikolay Samokhvalov I was invited to participate in a Community Panel (YouTube video) about PostgreSQL Upgradability at Postgres Vision 2022. The panel was moderated by Bruce Momjian and initiated and organized by Jimmy Angelakos. Bruce did talk with each of us before, which helped a lot to guide the discussion in the right direction. The recording of the panel discussion is available on the Postgres Vision website.

During this panel each of us provided examples for how easy or complicated PostgreSQL upgrades still are.
 

 

Continue reading "PostgreSQL Upgrades are hard!"

New OpenWeatherMap channels missing after openHAB upgrade from 2.4 to 2.5

A while ago I upgraded openHAB from version 2.4 to 2.5. That comes with the usual share of problems, but eventually I got it working.

For an unrelated project I wanted to use the OpenWeatherMap setup I have to improve the daily weather forecast with icons.

The binding provices ids for the current weather condition and the current weather icon. That can be used to load the icon from the OWM website. Went to my setup and added two new Items:

String	homeCurrentConditionID	"Current condition ID [%s]"	{ channel="openweathermap:weather-and-forecast:home:current#condition-id" }
String	homeCurrentIconID	"Current icon ID [%s]"		{ channel="openweathermap:weather-and-forecast:home:current#icon-id" }

Used Ansible to deploy everything to the Raspberry running openHAB. And then wondered because only the "homeCurrentConditionID" item came online, not the "homeCurrentIconID". After searching around and browsing the community forum, it turns out that the "icon-id" channel is new in 2.5, and will not automatically installed if the OpenWeatherMap binding was already used in 2.4. WT* ...

One has to remove all the OWM things ("openweathermap:weather-api:home" and "openweathermap:weather-and-forecast:home") and reinstall everything, in order to get the new channels working. That is really unexpected, and not user friendly.

After mumbling a while over my options I went ahead, created a new backup, and then removed the two items. One did get stuck along the way, a long Java trace appeared in the logfile, and I had to force remove it. Then - for good measueres - I also uninstalled the binding, and restarted openHAB.

Afterwards I re-deployed my Ansible Playbook, which installs the binding, takes care of the binding configuration, and adds the Things again. After everything came online, the homeCurrentIconID Item is also populated. Why is everything in openHAB so complicated?

Restic upgrade on Debian Buster

A while ago I switched backups from "Duplicity" to "Restic". About time: I was using Duplicity for many years (I think I started using it around 2010, long before "Restic" became available) and it served me well. But recently I ran into more and more issues, especially with archives getting larger and larger. There is an 11 years old open bug in the Duplicity bugtracker, which describes a showstopper for backing up larger archives. And it doesn't look like this will be solved anytime soon. Therefore it was time for something new.

Since I'm rolling out my backups with Ansible, it was relatively easy to create a set of scripts for Restic which use almost the same infrastructure as the old Duplicity backups. That works as expected on all our laptops. But the Raspberry Pi, which does the fileserver backups, seem to had a problem. Backups took way longer than before, jumped from 30-60 minutes (depending on the amount of changes) to constantly around 10 hours.

After some investigation (means: --verbose --verbose --verbose debugging), it turns out that Restic identifies most of the files as new, even though they did not change at all. Some background information: the Raspberry mounts the QNAP fileserver using the SMB3 protocol. The "mount -t cifs" uses the "serverino" option, but apparently that is not enough to provide a stable inode number. And if the inode for a file changes, Restic assumes it is a new file.

On the bright side, because the content of the files do not change, the deduplication still works, and no additional content is added to the backup. The size of the backup does not increase. Still, Restic fetches all the data from the server, and that takes a long time.

 

Continue reading "Restic upgrade on Debian Buster"

How to upgrade a Ubuntu server over the network

Now that the shiny new Ubuntu 9.04 is available, the servers with packages for 7.10 are closed down. Ok, how to upgrade a server, if you can't even install the "update-manager-core" package - because of the missing repositories?

(Backup and) edit your /etc/apt/sources.list file, comment out any third-party software sources. Replace every occurence of the old distribution name ("gutsy" in question) with the new name ("hardy" for the latest Long-Time-Support release, or a newer release). Now start a screen in case your network connection breaks during the upgrade.

aptitude clean

aptitude update (make sure that you don't see any 404 errors here)

aptitude dist-upgrade (that's the old name, the new one is full-upgrade)

Reboot your box and voila you should have an upgraded system.