[stork-users] Postgres issues with new install on Rocky8

[Slawek Figiel]

Hello Andrey!

Thanks for your report! I have good news. The described problems have 
already been fixed, and they are waiting for release.

Postgres team introduced some breaking-compatibility changes in the last 
releases. In Postgres 14, they modified the default permissions of the 
public schema, as you mentioned. We assumed these permissions are always 
granted. In Postgres 16, they introduced a new "SYSTEM_USER" keyword. 
Unfortunately, one of the Stork tables is called the same. It broke some 
essential queries, and Stork could not initialize the database and log 
users.

The above problems were described and solved in 
https://gitlab.isc.org/isc-projects/stork/-/issues/1148. The fix will be 
included in next week's release.

If you need to set up Stork before the release date, the only workaround 
is to downgrade Postgres.

Best regards
Slawek

On 05/10/2023 22:54, Andrey Kostin wrote:
> Hello all Stork users,
> 
> After my sysadmin gave up and after spending a day myself trying to 
> start stork-server I made some progress but stuck with this:
> 
> [root at storkserver-rocky ~]# stork-tool db-init --db-password="stork" 
> --db-user="stork" --db-host=127.0.0.1
> INFO[2023-10-05 16:19:04]             main.go:154   SQL queries tracing 
> set to none
> INFO[2023-10-05 16:19:04]       connection.go:90    Checking connection 
> to database
> INFO[2023-10-05 16:19:04]       connection.go:155   Connected to 
> database 127.0.0.1:5432
> INFO[2023-10-05 16:19:04]             main.go:177   Database schema is 
> empty (version 0)
> [root at storkserver-rocky ~]# stork-tool db-up --db-password="stork" 
> --db-user="stork" --db-host=127.0.0.1
> INFO[2023-10-05 16:19:20]             main.go:154   SQL queries tracing 
> set to none
> INFO[2023-10-05 16:19:20]       connection.go:90    Checking connection 
> to database
> INFO[2023-10-05 16:19:20]       connection.go:155   Connected to 
> database 127.0.0.1:5432
> FATA[2023-10-05 16:19:20]             main.go:168   problem migrating 
> database: ERROR #42601 syntax error at or near "system_user"
> 
> As I understand, system_user is a reserved word in postgres but it seems 
> that stork creates a table with the same name:
> [root at storkserver-rocky ~]# psql -h 127.0.0.1 -U stork
> Password for user stork:
> psql (16.0)
> Type "help" for help.
> 
> stork=> \dt+
>                                                                   List 
> of relations
>   Schema |      Name       | Type  | Owner | Persistence | Access method 
> |    Size    |                         Description
> --------+-----------------+-------+-------+-------------+---------------+------------+--------------------------------------------------------------
>   public | gopg_migrations | table | stork | permanent   | heap | 8192 
> bytes |
>   public | sessions        | table | stork | permanent   | heap | 8192 
> bytes | Table storing sessions according for scs.
>   public | system_user     | table | stork | permanent   | heap | 8192 
> bytes | Table holding a list of users which are known to the system.
> (3 rows)
> 
> Don't know, is it's something new or well-known issue, at least search 
> in maillist archive returns nothing for "system_user".
> The system is:
> [root at storkserver-rocky ~]# cat /etc/redhat-release
> Rocky Linux release 8.8 (Green Obsidian)
> 
> and Postgres version is 16.
> 
> Another problem that I seem solved before hitting the wall described 
> above, but want to report:
> 
> [root at storkserver-rocky ~]# stork-tool db-init --db-password="stork" 
> --db-user="stork" --db-host=127.0.0.1
> INFO[2023-10-05 15:47:17]             main.go:154   SQL queries tracing 
> set to none
> INFO[2023-10-05 15:47:17]       connection.go:90    Checking connection 
> to database
> INFO[2023-10-05 15:47:17]       connection.go:155   Connected to 
> database 127.0.0.1:5432
> FATA[2023-10-05 15:47:17]             main.go:168   problem migrating 
> database: ERROR #42501 permission denied for schema public
> 
> I spent several hours until I found this article:
> https://www.cybertec-postgresql.com/en/error-permission-denied-schema-public/
> "In PostgreSQL 14 and in prior versions, by default anybody can create a 
> table. The new table will simply end up in the PUBLIC schema."
> "The public schema is the perfect vector for such a privilege escalation 
> attack. Therefore PostgreSQL has made a major leap forward and changed 
> this behavior. In version 15, only the database owner can create objects 
> in the public schema."
> 
> The problem "permission denied for schema public" can be avoided by 
> running "stork-tool db-init" as user postgres, but without parameters it 
> uses user stork, so fails by default.
> 
> Kind regards,
> Andrey