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"

2 + 2 * 4 = 16?

Jens Kubieziel recently posted a Twitter poll: 2 + 2 * 4

The available options:

  • 16
  • 15
  • 14
  • 13

Notably absent is the correct result (at least for our ordinary mathematics). This poll is a No-Win Situation: you can't win, because the correct result is not available as an option.

Nevertheless I had an idea, but for this I had to wait until the poll closes.

2 + 2 <strong /> 4 = 16

 

Continue reading "2 + 2 * 4 = 16?"

ctid and other PostgreSQL table internals

While attending a conference (yay) the discussion turned to system columns, and especially ctid. This was in context of backups, and the physical position of data on disk. The question was if that can be used to determine if an incremental backup is required, and if it is enough to just copy that field where the ctid points to. Not quite, but I’d like to use this question to describe the system columns more in detail.

In a PostgreSQL table one may find - depending on the server version - the following additional system columns:

  • tableoid
  • xmin
  • xmax
  • cmin
  • cmax
  • ctid
  • oid

Most of the columns exist in every table, but are excluded when doing a SELECT *. However one can include the column(s) in a query, and then PostgreSQL will return the values for the columns as well.

 

 

Continue reading "ctid and other PostgreSQL table internals"

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!"

PostgreSQL Project @ GSoC 2022

As is a good tradition, the PostgreSQL Project participates in Google Summer of Code (GSoC). Last year we submitted 7 projects for 7 students - and got all 7 projects accepted. This year we got quite a few more good proposals from students, and more mentors are helping. Guess what? Google accepted all 12 proposals!

Google modified the program again. For 2021 they cut the time for every project in half, to accommodate for the at-home work during the Covid-19 pandemic. This turned out to be suboptimal, and many larger projects need more time. This year students can choose between “medium” (175 hours) and “large” (350 hours) projects. This gives everyone a chance to scope the project accordingly.

 

 

Continue reading "PostgreSQL Project @ GSoC 2022"

My PostgreSQL database is empty!

The one thing I like to do is challenge PostgreSQL - and readers - to the extreme. A while ago I posted about the Beer o'clock. Building Zoé was another interesting example. And at some point I blog about Dancing with the Stars as well. Today it's all about nothing. Empty. Where is all the data gone?

A while ago I posted this screenshot on Twitter, and as far as I can tell no one found the correct answer.

The screenshot looks like there's nothing. Empty names, schema name, role name, database name, even no data. And yet this all comes together and somehow works. Let's dig in.

 

Continue reading "My PostgreSQL database is empty!"

Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way

Christophe Pettus posted an interesting challenge here. Two strings which on the surface look equal, but if you ask PostgreSQL to compare them, they are not equal.

Now let me start with a note: Twitter totally screws this challenge up.

How so? Although the two strings are different in the original, when posting this to Twitter the strings are made equal. Where is the fun in that?

I asked Christophe for the original query:

INSERT INTO t VALUES (E'Zo\u0065\u0301', E'Zo\u00e9');

And you end up with the following texts in the table:

SELECT * FROM t;
  a  |  b  
-----+-----
 Zoé | Zoé
(1 row)

If you translate the UTF-8 strings into hex, you get "0x5a 0x6f 0x65 0xcc 0x81" and "0x5a 0x6f 0xc3 0xa9". Clearly they are different.

However if you convert the two strings from the Tweet, you get "0x5a 0x6f 0xc3 0xa9" and "0x5a 0x6f 0xc3 0xa9". Same string. Poor Twitter.

Checking the hex values was actually one of my first ideas when I spotted this challenge. But nevertheless based on my experience from my "Data Types in PostgreSQL" and "Advanced Data Types in PostgreSQL" talks, I figured it should be possible to "solve" this puzzle even if the strings are in fact equal.

Buckle up! We are about to dive deep into how extendible PostgreSQL really is!

 

Continue reading "Not so equal texts in PostgreSQL - and how to compare texts in a more elaborate way"

GSoC 2021 completed

The Google Summer of Code 2021 for the PostgreSQL Project is wrapped up. The timeline this year was shortened to half, compared to previous years. That’s good, because smaller projects can be worked on, and students have a chance to cope with a changing environment at home and university. On the other hand, the shorter time doesn’t allow diving into more complex projects. Nevertheless, with the help of all mentors, six students successfully concluded their projects.

 

Continue reading "GSoC 2021 completed"

PostgreSQL Project @ GSoC 2021

Wow! The PostgreSQL Project got all 7 proposals accepted into Google Summer of Code 2021!

This year Google changed the participation terms a bit, and cut the time for the students in half. This is supposed to help students who can’t work full-time from home, especially in light of the global pandemic situation. It also means smaller projects, which are easier to handle even for students new to the project.

The PostgreSQL project got a great number of initial applications (29), and we talked with many of the students about refining their proposals. 27 out of the 29 applications were finally submitted by the students. Some are duplicates, some are clearly just copied from somewhere, but many propose good ideas.

After talking with available mentors, and “recruiting” a few more, we settled on 7 final applications, and submitted them to Google.

As usual many of the proposals are not directly developing code for core PostgreSQL, but work on tools and applications from the PostgreSQL ecosystem. Expect some great output over the following months.