Azure Database for PostgreSQL flexible server gives error permission denied for schema public

Question

I am using Azure Database for PostgreSQL flexible server.

Here is what I am doing. Please let me know where I am going wrong.

Create a new PostgreSQL flexible server(With both authentication Microsoft Entra and PostgreSQL) and have two admin account one PostgreSQL and one Microsoft Entra
Create new Database by running below command

CREATE DATABASE testdb WITH OWNER = postgresql_admin
Create new Role

CREATE ROLE Test_db_user WITH nologin;
Grant SELECT, INSERT, UPDATE, DELETE on all tables in public schema

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO Test_db_user;
Grant Create on public schema

GRANT CREATE ON SCHEMA public TO Test_db_user;
Create new user

CREATE ROLE dev1 WITH login PASSWORD 'abc123' INHERIT;
Assign "Test_db_user " role to "dev1" user

GRANT Test_db_user TO dev1;
Log in with newly created user
Run below command to create table

CREATE TABLE tblByDev1(col1 text);

Received below error

The error may occur due to running the query in different databases. And also occurs when you do not have USAGE permissions on the public schema. As per this, Make sure you selected the correct db. For more information, you can refer to this. — Balaji
– Balaji, Commented Nov 24, 2023 at 6:11
You are correct. On step#8 I need to change the database I created in step#2. I was running the query against the default postgres databse. — Ravi Khambhati
– Ravi Khambhati, Commented Nov 27, 2023 at 17:30

Balaji · Accepted Answer · 2023-11-28 04:22:08Z

ERROR: permission denied for schema public LINE 1: create table student(Id int,name varchar(20))
The error may occur due to running the query in different databases. And also occurs when you do not have USAGE permissions on the public schema. As per PostgreSql documentation,

PostgreSQL 15 revokes the
CREATE permission from all users except a database owner from the public (or default) schema.

You can follow the steps below to grant permissions to create a table in PostgreSQL:

Login with admin to the newly created db.
Create a user with login and grant permissions to the user using the script below:

CREATE ROLE New_Test_db_user2 WITH nologin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO New_Test_db_user2;
GRANT CREATE ON SCHEMA public TO New_Test_db_user2;
CREATE ROLE dev5 WITH login PASSWORD '*****' INHERIT;
GRANT New_Test_db_user2 TO dev5;

enter image description here

Login to the newly created db with the new user and try to create a table using the script; then, it will create successfully, as shown below:

enter image description here

Make sure you selected the correct db. For more information, you can refer to this.

Collectives™ on Stack Overflow

Azure Database for PostgreSQL flexible server gives error permission denied for schema public

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related