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.
I've cross-posted same question to https://dba.stackexchange.com
I see where the problem is, when you assigned the default privilege, it should have been done as flyway_migration.
hot_update=# \c - flyway_migration
You are now connected to database "hot_update" as user "flyway_migration".
hot_update=>
hot_update=> ALTER DEFAULT PRIVILEGES
hot_update-> IN SCHEMA application_schema
hot_update-> GRANT ALL PRIVILEGES ON TABLES TO spring_application;
ALTER DEFAULT PRIVILEGES
hot_update=>
hot_update=> CREATE TABLE application_schema.simple_entity
hot_update-> (
hot_update(> id UUID,
hot_update(> created_at TIMESTAMP,
hot_update(> text varchar(255)
hot_update(> );
CREATE TABLE
As you see now:
hot_update=> \c - spring_application
You are now connected to database "hot_update" as user "spring_application".
hot_update=> select * from simple_entity ;
id | created_at | text
----+------------+------
(0 rows)
And now for owner you have flyway_migration not postgres:
hot_update=> \ddp
Default access privileges
Owner | Schema | Type | Access privileges
------------------+--------------------+-------+---------------------------------------------
flyway_migration | application_schema | table | spring_application=arwdDxt/flyway_migration
(1 row)
OR you must create table as postgres CASE2:
hot_update=# \c - flyway_migration
You are now connected to database "hot_update" as user "flyway_migration".
hot_update=>
hot_update=> ALTER DEFAULT PRIVILEGES IN SCHEMA application_schema REVOKE ALL PRIVILEGES ON TABLES FROM spring_application;
ALTER DEFAULT PRIVILEGES
hot_update=# drop table application_schema.simple_entity ;
DROP TABLE
hot_update=> \c - postgres
You are now connected to database "hot_update" as user "postgres".
hot_update=# ALTER DEFAULT PRIVILEGES IN SCHEMA application_schema GRANT ALL PRIVILEGES ON TABLES TO spring_application;
ALTER DEFAULT PRIVILEGES
hot_update=#
hot_update=# CREATE TABLE application_schema.simple_entity
hot_update-# (
hot_update(# id UUID,
hot_update(# created_at TIMESTAMP,
hot_update(# text varchar(255)
hot_update(# );
CREATE TABLE
hot_update=# \c - spring_application
You are now connected to database "hot_update" as user "spring_application".
hot_update=> select * from simple_entity ;
id | created_at | text
----+------------+------
(0 rows)
And you have:
hot_update=> \ddp
Default access privileges
Owner | Schema | Type | Access privileges
----------+--------------------+-------+-------------------------------------
postgres | application_schema | table | spring_application=arwdDxt/postgres
(1 row)