[Bug]: For embedded postgres appsmith user is unable to create tables because of the missing permission

[abhvsn]

Is there an existing issue for this?

• I have searched the existing issues

Description

When user opt for postgres version, startup flow makes sure to create appropriate DB, user and schema. In the current implementation we are not granting any permission to the role that is being created explicitly and is dependent on inheriting the create table and related CRUD ops on these tables. This can lead to permission issues with the appsmith user which is being used by Appsmith instance.

Ref logs:

postgres stdout | 2024-10-02 09:26:09.381 UTC [1781] ERROR:  permission denied for schema appsmith at character 14
postgres stdout | 2024-10-02 09:26:09.381 UTC [1781] STATEMENT:  CREATE TABLE "appsmith"."flyway_schema_history" (
postgres stdout | 	    "installed_rank" INT NOT NULL,
postgres stdout | 	    "version" VARCHAR(50),
postgres stdout | 	    "description" VARCHAR(200) NOT NULL,
postgres stdout | 	    "type" VARCHAR(20) NOT NULL,
postgres stdout | 	    "script" VARCHAR(1000) NOT NULL,
postgres stdout | 	    "checksum" INTEGER,
postgres stdout | 	    "installed_by" VARCHAR(100) NOT NULL,
postgres stdout | 	    "installed_on" TIMESTAMP NOT NULL DEFAULT now(),
postgres stdout | 	    "execution_time" INTEGER NOT NULL,
postgres stdout | 	    "success" BOOLEAN NOT NULL
postgres stdout | 	)

To fix this we need to add the explicit permission for appsmith user:

psql -h "$PG_DB_HOST" -p "$PG_DB_PORT" -U postgres -d "$PG_DB_NAME" -c "GRANT ALL PRIVILEGES ON SCHEMA appsmith TO ${PG_DB_USER};"
psql -h "$PG_DB_HOST" -p "$PG_DB_PORT" -U postgres -d "$PG_DB_NAME" -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA appsmith TO ${PG_DB_USER};"
psql -h "$PG_DB_HOST" -p "$PG_DB_PORT" -U postgres -d "$PG_DB_NAME" -c "ALTER DEFAULT PRIVILEGES IN SCHEMA appsmith GRANT ALL PRIVILEGES ON TABLES TO ${PG_DB_USER};"
psql -h "$PG_DB_HOST" -p "$PG_DB_PORT" -U postgres -c "GRANT CONNECT ON DATABASE appsmith TO ${PG_DB_USER};"

Steps To Reproduce

1. Pull latest image
2. Update the env to use Postgres url
3. Restart the container

Public Sample App

No response

Environment

Production

Severity

High (Blocker to building or releasing)

Issue video log

No response

Version

v1.42