Postgres error after upgrade to 1.7.6

[kingfisher77]

Describe the problem/question

After upgrade to privatebin 1.7.6 i cannot save an paste anymore. The database log shows this:

postgres-1 | 2025-03-06 21:58:16.809 CET [34] ERROR: invalid input syntax for type integer: "{"salt":"XXX"}"
postgres-1 | 2025-03-06 21:58:16.809 CET [34] CONTEXT: unnamed portal parameter $6 = '...'
postgres-1 | 2025-03-06 21:58:16.809 CET [34] STATEMENT: INSERT INTO "privatebin_paste" VALUES($1,$2,$3,$4,$5,$6,$7,$8)
postgres-1 | 2025-03-06 22:01:02.638 CET [26] LOG: checkpoint starting: time
postgres-1 | 2025-03-06 22:01:02.777 CET [26] LOG: checkpoint complete: wrote 2 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.101 s, sync=0.015 s, total=0.139 s; sync files=2, longest=0.014 s, average=0.008 s; distance=5 kB, estimate=5 kB

Did you use the FAQ section?

• Yes, I have read the FAQ and I found no solution/answer there.

What you did?

1. Upgrade Privatbin to version 1.7.6

...

What happens

Error message see above when saving a paste

What should happen

The paste should be saved

Additional information

Postgres 15
Privatebin 1.7.6
Docker

Server address

https://crypto.shift.agency

Server OS

privatebin/pdo:1.7.6

Webserver

privatebin/pdo:1.7.6

PrivateBin version

privatebin/pdo:1.7.6

Browser and version

Chrome

Local operating system and version

macOS 15.3.1

Issue reproducibility

No, I cannot reproduce it on https://privatebin.net.

[elrido]

In release 1.7.2 we dropped the unused postdate column from the paste table schema. If the permissions of the postgres user allow for this, the schema should get automatically upgraded for you, otherwise you will have to manually adjust the schema:

PrivateBin/lib/Data/Database.php

Lines 908 to 924 in dd6537b

	if (version_compare($oldversion, '1.7.1', '<=')) {
	$supportsDropColumn = true;
	if ($this->_type === 'sqlite') {
	try {
	$row = $this->_select('SELECT sqlite_version() AS "v"', array(), true);
	$supportsDropColumn = version_compare($row['v'], '3.35.0', '>=');
	} catch (PDOException $e) {
	$supportsDropColumn = false;
	}
	}
	if ($supportsDropColumn) {
	$this->_db->exec(
	'ALTER TABLE "' . $this->_sanitizeIdentifier('paste') .
	'" DROP COLUMN "postdate"'
	);
	}
	}

I don't remember the column order before the change, but would guess that postdate column was of an integer type and in the 6th position, hence the new versions failure to write a JSON string with the meta data into the still existing integer column.

Edit: corrected the version number that introduced the change from 1.7.1 to 1.7.2

[kingfisher77]

Thank you! The ALTER TABLE statement fixed the issue for us. Now i will have a look why the schema was not automatically changed.

[kingfisher77]

I run this SQL in Postgres 15

-- Grant CREATE, INDEX, and ALTER privileges on the schema
GRANT CREATE ON SCHEMA public TO privatebin;

-- Grant INSERT and UPDATE privileges on the config table
GRANT INSERT, UPDATE ON TABLE config TO privatebin;

We now expect the next upgrade to be performed automatically with schema changes. Thanks again!

Incidentally, would this type of per-version change be important enough to be included in the changelog?

[rugk]

Good point, feel free to propose a PR for a changelog entry! (or @elrido?)

And BTW, you can also mark code snippets with the language, which adds syntax highlighting to them, in the Markdown you write on GitHub.

[rugk]

Or maybe some other doc (installation guide? Okay, no it's rather about upgrades hmm?) or FAQ could be needed, as IIRC we receive this question quite often?

[elrido]

The change is documented in the https://github.com/PrivateBin/PrivateBin/blob/master/CHANGELOG.md#172-2024-05-05 (#1290) and the GRANTs are listed for MariaDB/MySQL in the https://github.com/PrivateBin/PrivateBin/blob/master/doc/Installation.md#using-a-database-instead-of-flat-files - do feel free to raise a pull request with different GRANT statements for Postgres or confirming them to apply there as well, if they do.