Postgresql: Can't access table created for application user

I’m working on a self education project. My current goal/blocker is to create PostgreSQL database setup with managed migrations that will have minimal required permissions for separate migration and application user. I’ve managed so far to get migration part working, but setup I’ve got is too restrictive for supposed application user. What I want to achieve is that application user can use tables while not being able to change schema.

I’m using PostgreSQL from Docker and Flyway for migrations in Spring Java Application.

Here’s my init script for database on container creation:

To run this I do docker compose up on terminal 1.

CREATE DATABASE hot_update;

CREATE USER flyway_migration WITH PASSWORD 'flyway_secret';
CREATE USER spring_application WITH PASSWORD 'spring_secret';

c hot_update

DROP SCHEMA public;

CREATE SCHEMA application_schema AUTHORIZATION spring_application;
ALTER ROLE spring_application SET search_path = application_schema;

GRANT USAGE, CREATE ON SCHEMA application_schema TO flyway_migration;
GRANT TEMPORARY ON DATABASE hot_update TO spring_application;

GRANT USAGE ON SCHEMA application_schema TO spring_application;

ALTER DEFAULT PRIVILEGES
  IN SCHEMA application_schema
  GRANT ALL PRIVILEGES ON TABLES TO spring_application;

Then content of 1st migration applied by flyway_user. To run this I execute mvn spring-boot:run on terminal 2.

CREATE TABLE simple_entity
(
    id UUID,
    created_at TIMESTAMP,
    text varchar(255)
);

And it doesn’t work, at first I’ve thought that somehow I’ve messed up my r2dbc driver config in spring app, but it doesn’t even work from psql. Example session bellow on terminal 3.

wiktor@desktop-bep0pt7-1:~/code/postgres-hot-update> psql -U spring_application -h localhost -d hot_update
Password for user spring_application: 
psql (15.2)
Type "help" for help.

hot_update=> SELECT * FROM simple_entity;
ERROR:  permission denied for table simple_entity

For reference output of information commands in psql

hot_update=> dn
             List of schemas
        Name        |       Owner        
--------------------+--------------------
 application_schema | spring_application
(1 row)

hot_update=> d+
                                                      List of relations
       Schema       |         Name          | Type  |      Owner       | Persistence | Access method |  Size   | Description 
--------------------+-----------------------+-------+------------------+-------------+---------------+---------+-------------
 application_schema | flyway_schema_history | table | flyway_migration | permanent   | heap          | 16 kB   | 
 application_schema | simple_entity         | table | flyway_migration | permanent   | heap          | 0 bytes | 
(2 rows)

hot_update=> dp
                                           Access privileges
       Schema       |         Name          | Type  | Access privileges | Column privileges | Policies 
--------------------+-----------------------+-------+-------------------+-------------------+----------
 application_schema | flyway_schema_history | table |                   |                   | 
 application_schema | simple_entity         | table |                   |                   | 
(2 rows)

hot_update=> ddp
                          Default access privileges
  Owner   |       Schema       | Type  |          Access privileges          
----------+--------------------+-------+-------------------------------------
 postgres | application_schema | table | spring_application=arwdDxt/postgres
(1 row)

In case anyone wished to try it out locally here’s link to branch having WIP state I had when writing this question.

https://gitlab.com/stmi-blog/postgres-hot-update/-/tree/5-add-spring-data-reactive-repository-over-dummy-table

I’ve cross-posted same question to https://dba.stackexchange.com