🐛 [Bug]: Storage user MUST have create permissions, even if using existing table

[bryanvaz]

Bug Description

Driver: PostgreSQL
Category: Security
Severity: MEDIUM

Problem:
It is currently not possible to use the postgres storage driver without elevated user permissions that allow the user to arbitrarily create tables. This is even true if you already have a table that matches the desired schema. The behaviour is different than valkey, redis, and a number of other drivers where table/namespace create permissions are considered elevated permissions that a webserver should not posses for normal operations.

Always runs init query when initializing the storage:

storage/postgres/postgres.go

Lines 73 to 78 in fb997fd

	for _, query := range initQuery {
	if _, err := db.Exec(context.Background(), fmt.Sprintf(query, cfg.Table)); err != nil {
	db.Close()
	panic(err)
	}
	}

init query will fail if user does not have CREATE ON SCHEMA permission, even if table already exists:

storage/postgres/postgres.go

Lines 32 to 39 in fb997fd

	initQuery = []string{
	`CREATE TABLE IF NOT EXISTS %s (
	k VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT '',
	v BYTEA NOT NULL,
	e BIGINT NOT NULL DEFAULT '0'
	);`,
	`CREATE INDEX IF NOT EXISTS e ON %s (e);`,
	}

Root Cause:
The issue seems to mainly arise because of the way that the PostgreSQL query engine(QE) is built. The QE seems to preflight the query to ensure the user has the permissions to perform all of the actions in the query, even if a logic branch may never be taken (i.e. QE will verify user has CREATE permission even though query has the IF NOT EXISTS predicate and table already exists). This behaviour is expected and pretty good as it minimizes the need for the QE to do permission checks during row-locking/table-locking operations.

Potential Fixes:

Bare minimum fix:
Run two separate queries, (1) first check if table exists, (2) if table does not exists, then create table

Slightly better fix:
Add Config flag that disables all Create/Drop operations, and panics/raises error if table does not exist, schema does not match, or reset via DROP is set to true

Tangential fixes:
Reset function should TRUNCATE instead of DROP to accomplish the goal of:

storage/postgres/config.go

Lines 59 to 62 in fb997fd

	// Reset clears any existing keys in existing Table
	//
	// Optional. Default is false
	Reset bool

How to Reproduce

Steps to reproduce the behaviour:

1. Create a user in postgres that has full table and usage permissions, but no create permissions:

CREATE DATABASE fiber_store;

CREATE ROLE fiber_users;

GRANT CONNECT ON DATABASE fiber_user TO fiber_users;
GRANT USAGE ON SCHEMA public TO fiber_users;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO fiber_users;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO fiber_users;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO fiber_users;

CREATE USER fiber_users WITH PASSWORD 'password';

GRANT fiber_users TO fiber_user;

2. Connect to database using the admin user in psql or SQL IDE
3. Create the table expected by Storage

CREATE TABLE IF NOT EXISTS sessions (
	k  VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT '',
	v  BYTEA NOT NULL,
	e  BIGINT NOT NULL DEFAULT '0'
);
CREATE INDEX IF NOT EXISTS e ON sessions (e);

4. Create a new barebones Go program
5. Create a github.com/gofiber/storage/postgres/v3 storage connection using the newly created fiber_user
6. go run program
7. Program will panic with the following error:panic: ERROR: permission denied for schema public (SQLSTATE 42501)
8. Change user to admin user (usually postgres)
9. go run updated program
10. Program will proceed normally.
11. Check db schema, table will be unaltered

Expected Behavior

If table already exists, matches desired schema and Config{ Reset: false }, store should connect successfully and continue normally.

Storage package Version

v3.0.1

Code Snippet (optional)

See reproduction

Checklist:

• I agree to follow Fiber's Code of Conduct.
• I have checked for existing issues that describe my problem prior to opening this one.
• I understand that improperly formatted bug reports may be closed without explanation.

[bryanvaz]

I'm happy to patch with a PR if the error makes sense, just let me know if there is a specific branch I should be basing on/merging into