Shared Postgresql connection fails #16

New issue

Open

opened 2026-04-22 16:45:53 +02:00 by tux · 1 comment

tux commented

2026-04-22 16:45:53 +02:00

Owner

On a postgresql database following the instructions, an admin user with full permissions was created, and a normal user with the permissions:

GRANT CONNECT ON DATABASE shellstation TO linux;
GRANT USAGE ON SCHEMA public TO linux;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO linux;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO linux;

ALTER DEFAULT PRIVILEGES FOR ROLE shellstation_admin IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO linux;
ALTER DEFAULT PRIVILEGES FOR ROLE shellstation_admin IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO linux;

Now with the normal user the test for the database connection succeeds, but upon restart the app cannot connect to the database and this is seen in the postgresql log:

2026-04-22 16:41:51.477 CEST [689996] linux@shellstation ERROR:  permission denied for schema public at character 29
2026-04-22 16:41:51.477 CEST [689996] linux@shellstation STATEMENT:  
	CREATE TABLE IF NOT EXISTS _sqlx_migrations (
	    version BIGINT PRIMARY KEY,
	    description TEXT NOT NULL,
	    installed_on TIMESTAMPTZ NOT NULL DEFAULT now(),
	    success BOOLEAN NOT NULL,
	    checksum BYTEA NOT NULL,
	    execution_time BIGINT NOT NULL
	);

On a postgresql database following the instructions, an admin user with full permissions was created, and a normal user with the permissions: ``` GRANT CONNECT ON DATABASE shellstation TO linux; GRANT USAGE ON SCHEMA public TO linux; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO linux; GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO linux; ALTER DEFAULT PRIVILEGES FOR ROLE shellstation_admin IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO linux; ALTER DEFAULT PRIVILEGES FOR ROLE shellstation_admin IN SCHEMA public GRANT USAGE ON SEQUENCES TO linux; ``` Now with the normal user the test for the database connection succeeds, but upon restart the app cannot connect to the database and this is seen in the postgresql log: ``` 2026-04-22 16:41:51.477 CEST [689996] linux@shellstation ERROR: permission denied for schema public at character 29 2026-04-22 16:41:51.477 CEST [689996] linux@shellstation STATEMENT: CREATE TABLE IF NOT EXISTS _sqlx_migrations ( version BIGINT PRIMARY KEY, description TEXT NOT NULL, installed_on TIMESTAMPTZ NOT NULL DEFAULT now(), success BOOLEAN NOT NULL, checksum BYTEA NOT NULL, execution_time BIGINT NOT NULL ); ```