Handling access privilege in liquibase

General Discussion
1

I am new to liquibase. I am using the sql formatted changelogs 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 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 way? We only have two users in database as mentioned here.

2

Are you sure your tables being created in the public schema?

3

Yes the tables are created in public schema.

Everything is working as expected just that I have to execute the GRANT command everytime a new table is created via liquibase.

4

I have also tried the below commands as masteruser on 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.

5

I don’t think this privilege issue has anything to do with Liquibase. This is purely a PostgreSQL privilege setup issue. I’d suggest working this out just inside the database, without Liquibase.

6

As suggested I have set the access privilege in postgres outside liquibase like below.

I executed the below command with masteruser on the appDB

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