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

[Andrey Kostin]

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