DEV Community

Dmitry Romanoff
Dmitry Romanoff

Posted on • Edited on

9

PostgreSQL version 15+ ERROR: permission denied for schema public

PostgreSQL version 15+ will error out and tell you that you don't have permission to create something inside the public schema without explicitly specifying who is allowed to do that beforehand. It is now necessary to grant permissions to a user explicitly.

What should be changed?

If it’s Postgres version 15+ right after the DB_name has been created and DB_user has been created, and before any object creation, connect to the as user admin and run this:

GRANT ALL ON SCHEMA public TO <DB_user>;
Enter fullscreen mode Exit fullscreen mode

Detailed Explanation.

Reconstruction:

psql -h my_db_host -U dima_test -d dima_test -W
Password:

dima_test=> create table xyz(a varchar(100));
ERROR:  permission denied for schema public
Enter fullscreen mode Exit fullscreen mode

Workaround:

psql -h my_db_host -U root -d dima_test -W
Password: 

dima_test=> GRANT ALL ON SCHEMA public TO dima_test;
GRANT
dima_test=> \q


psql -h my_db_host -U dima_test -d dima_test -W
Password: 

dima_test=> create table xyz(a varchar(100));
CREATE TABLE
dima_test=>
Enter fullscreen mode Exit fullscreen mode

ask_dima@yahoo.com

Image of Datadog

Create and maintain end-to-end frontend tests

Learn best practices on creating frontend tests, testing on-premise apps, integrating tests into your CI/CD pipeline, and using Datadog’s testing tunnel.

Download The Guide

Top comments (1)

Collapse
 
aarne_avialaynen_376c1e4d profile image

Not works.

GRANT  ALL  ON SCHEMA public TO postgres;
Enter fullscreen mode Exit fullscreen mode
GRANT
Query returned successfully in 55 msec.
Enter fullscreen mode Exit fullscreen mode
update user_table set status_id = 1 where status_id = 2
Enter fullscreen mode Exit fullscreen mode
ERROR:  permission denied for schema public
LINE 1: SELECT 1 FROM ONLY "public"."status" x WHERE "id" OPER...
Enter fullscreen mode Exit fullscreen mode

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more