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.

1. Create a new PostgreSQL flexible server(With both authentication Microsoft Entra and PostgreSQL) and have two admin account one PostgreSQL and one Microsoft Entra

2. Create new Database by running below command

   CREATE DATABASE testdb WITH OWNER = postgresql_admin

3. Create new Role

   CREATE ROLE Test_db_user WITH nologin;

4. 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;

5. Grant Create on public schema

   GRANT CREATE ON SCHEMA public TO Test_db_user;

6. Create new user

   CREATE ROLE dev1 WITH login PASSWORD 'abc123' INHERIT;

7. Assign "Test_db_user " role to "dev1" user

   GRANT Test_db_user TO dev1;

8. Log in with newly created user

9. Run below command to create table

   CREATE TABLE tblByDev1(col1 text);

Received below error

Answer 1

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:

1. Login with admin to the newly created db.
2. 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;

3. 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:

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