Note that this only works with idempotent commands, like ALTER. Since the WITH LOGIN and WITH NOLOGIN attributes are supposed to change, we add the runOnChange attribute, which just runs the SQL command again. Liquibase generates a hash for every changeset and warns you, if a hash changed. ALTER ROLE my_app_tom WITH PASSWORD '$' REVOKE USAGE ON SCHEMA my_schema FROM my_app REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA my_schema FROM my_app REVOKE ALL ON SCHEMA my_schema FROM my_app The SQL statement to create these roles isĬREATE ROLE my_app_tom WITH NOLOGIN IN ROLE my_app ĪLTER ROLE my_app_tom SET ROLE TO my_app ĬREATE ROLE my_app_jerry WITH NOLOGIN IN ROLE my_app ĪLTER ROLE my_app_jerry SET ROLE TO my_app One that is not yet used to log in and assume the first role ( inactive sub-role).One that is used to log in and assume the first role ( active sub-role),.One that has all the permissions and objects attached to it ( main role),.The solution to our problem thus is to have three roles: First, you have to define it to be WITH LOGIN WITH PASSWORD. The difference: Without explicitly stating it, you cannot use a role to log in. Three Users #įirst of all: PostgreSQL doesn’t have users, it has roles. While scenario 3 sounds complicated at first, it is very easy to achieve in PostgreSQL. Scenario 1 is not an option for service accounts, and scenario 2 is complicated and still triggers your alarms for unsuccessful login attempts (you have one, don’t you?). Create a copy of the database user, update the application, delete the old user. ![]() Make both the old and new password known to the application and let it fall back on the new one once the old one fails.In between you’ll have a short time when the application can’t connect. ![]()
0 Comments
Leave a Reply. |