migrate fails due to permission issues despite being able to run SQL manually using same account

[chaseweaver]

I am working in a locked-down database. I have full permission on public schema. I have migrations.schema set to public in drizzle.config.ts. I am getting a permission denied error when running drizzle-kit migrate. However, I can run the SQL manually. drizzle-kit push also works without error.

// drizzle.config.ts

// ...
verbose: true,
migrations: {
  table: 'migrations',
  schema: 'public',
},
// ...

Error in question

Using 'pg' driver for database querying
[⣯] applying migrations...error: permission denied for database DATABASE_NAME

// ...

{
  length: 110,
  severity: 'ERROR',
  code: '42501',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'aclchk.c',
  line: '3652',
  routine: 'aclcheck_error'
}

"drizzle-orm": "^0.31.0",
"drizzle-kit": "^0.22.1",

My initial thought is there might be a check against the default drizzle schema prior to using the config one which is causing the command to fail since I do not have permissions for it.

Running this on a non-locked down database (where I can create schemas) works fine as you'd expect.

[RestartDK]

I also have the same issue when trying to use a migration against a supbase db

[Tiffceet]

Facing the same issue with postgres deplpoyed onto northflank using their add-on feature, not sure whats going on under drizzle-kit, if only there is a way to look at what sql command is sent by drizzle-kit under the hood like --verbose flag or whatsoever

"drizzle-kit": "^0.22.8",
"drizzle-orm": "^0.31.2",

import { defineConfig } from "drizzle-kit";
import "dotenv/config";

import { env } from "./env";

export default defineConfig({
  schema: [
    "./src/schema/comprehensive/*",
    "./src/schema/global/*",
    "./src/schema/org/*",
    "./src/schema/site/*",
    "./src/schema/tenant/*",
  ],
  out: "./migrations",
  dialect: "postgresql", // "mysql" | "sqlite" | "postgresql"
  dbCredentials: {
    url: env.DATABASE_URL,
  },
  strict: true,
  verbose: true,
  breakpoints: true, // add breakpoint to SQL statements
});

update: I was able to identify that this was caused by drizzle tries to run CREATE SCHEMA IF NOT EXISTS "drizzle" at the start of migration

just wanted to share how I troubleshoot this, I configured my local postgres to logs all SQL command with log_statement = 'all' in postgresql.conf and ran drizzle migration against it to see what commands it was running, I was able to identify which sql statement is giving the permission denied error this way

[Lavriz]

fyi: this issue seems to be a duplicate of #2161

[AndriiSherman]

@chaseweaver
if you specified this part

migrations: {
  table: 'migrations',
  schema: 'public',
},

then it would try to create __drizzle_migrations table inside public schema and not drizzle schema. Do you have any repro example, so I can check?

[kiranpradeep]

@AndriiSherman An example DB that reproduces this error can be created from this Managing PostgreSQL users and roles page from the AWS team. If you go directly to the summary section at the end, quote below

-- Revoke privileges from 'public' role
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

That removes all permission and then asks to assign permission as necessary. I had followed that best practices page from AWS and manually created schema: drizzle. But it fails as drizzle migrate attempts to create schema drizzle or as per above example public.migrations. It works only if I grant GRANT CREATE ON DATABASE

[Tigatok]

I have this issue using Drizzle with Supabase (but only on one of my supabase instances). I've given all sort of permissions to the postgres user in Supabase, with nothing making a difference.

To make matters more interesting, I did a supabase db reset --linked on the problem instance, and reset the DB. The first time I ran p drizzle-kit migrate on the empty instance, the migrations ran fine, and the drizzle schema was created. Now, however, when I try to run another migration, I get the permissions denied issue.

[
  {
    "file": null,
    "host": "db-adzsgftku",
    "metadata": [],
    "parsed": [
      {
        "application_name": "Supavisor",
        "backend_type": "client backend",
        "command_tag": "CREATE SCHEMA",
        "connection_from": "26002:58928",
        "context": null,
        "database_name": "postgres",
        "detail": null,
        "error_severity": "ERROR",
        "hint": null,
        "internal_query": null,
        "internal_query_pos": null,
        "leader_pid": null,
        "location": null,
        "process_id": 1735880,
        "query": "CREATE SCHEMA IF NOT EXISTS \"drizzle\"",
        "query_id": -6717465473777588000,
        "query_pos": null,
        "session_id": "6797cc8",
        "session_line_num": 21,
        "session_start_time": "2025-02-01 19:41:02 UTC",
        "sql_state_code": "42501",
        "timestamp": "2025-02-01 20:25:19.393 UTC",
        "transaction_id": 0,
        "user_name": "postgres",
        "virtual_transaction_id": "11/30520"
      }
    ],
    "parsed_from": null,
    "project": null,
    "source_type": null
  }
]

In the problem instance, I ran

GRANT CREATE DATABASE ON postgres TO postgres

Which completed successfully. All my connection string stuff is accurate.

I see there is an open MR for #4025 but I am not sure if I have a separate issue?

E: Drizzle-kit command output:

[⣟] applying migrations...error: permission denied for database postgres
    at /Users/tmarshall/clients/node_modules/.pnpm/[email protected][email protected]/node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
    at async PgDialect.migrate (/Users/tmarshall/clients/node_modules/.pnpm/[email protected]_@[email protected][email protected][email protected][email protected]/node_modules/src/pg-core/dialect.ts:85:3)
    at async migrate (/Users/tmarshall/clients/node_modules/.pnpm/[email protected]_@[email protected][email protected][email protected][email protected]/node_modules/src/node-postgres/migrator.ts:10:2) {
  length: 99,
  severity: 'ERROR',
  code: '42501',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'aclchk.c',
  line: '3650',
  routine: 'aclcheck_error'
}