Separate owner and runtime roles

PostgreSQL uses roles for both users and groups. A role with LOGIN can authenticate. A role without it can own objects without becoming an application credential.

Create one role that owns the schema, and another that runs the application:

CREATE ROLE notes_owner NOLOGIN;
CREATE ROLE notes_app LOGIN;

Set the runtime password with \password notes_app in psql. This avoids putting the cleartext password in the command history.

Let your administrator become the owner role only while running migrations:

GRANT notes_owner TO CURRENT_USER
  WITH INHERIT FALSE, SET TRUE;

SET ROLE notes_owner now activates the owner deliberately. Role attributes such as LOGIN, CREATEDB, and CREATEROLE are never inherited like table privileges.