Handling access privilege in liquibase with postgres-CodePudding

I am new to liquibase. I am using the sql formatted changelogs 1 for postgres RDS. We run liquibase using masteruser in postgres and the application accesses the database using a different user in postgres, appsuer.

If I am creating a new table in database via liquibase. The appuser do not have the privilege to access the new table. So every time, I have to execute the below command as masteruser on the app DB after a new table is created.

GRANT SELECT, INSERT, UPDATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO appsuer;

Can someone let me know how to handle the GRANT also via liquibase SQL formatted changelogs or there are some other simpler and recommended ways? We only have two users in the database as mentioned here.

I executed the below command as masteruser on the app DB.

GRANT appuser to masteruser;
ALTER DEFAULT PRIVILEGES
    FOR USER appuser
    IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO appsuer ;

But then each time a new table is created via liquibase I have to execute the GRANT command once again inspite of ALTER DEFAULT PRIVILEGE command being executed.

CodePudding user response：

Just create some changelog that will be executed every time with runAlways and put it at the end of the list of your changelogs/files so it will be executed every time as the latest one.

maybe something like this (I'm not familiar with formatted sql)

--changeset author:you id:someid runAlways:true dbms:postgresql
GRANT appuser to masteruser;
ALTER DEFAULT PRIVILEGES
    FOR USER appuser
    IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO appsuer ;