Default Privileges

DEFAULT PRIVILEGES FOR ROLE — automatically grant privileges on future objects created by a role.

Default privileges apply to objects created in the future by a specific role. They are declared inside a SCHEMA { } block.

Default privileges for tables and functions

SCHEMA public {
    DEFAULT PRIVILEGES FOR ROLE app_admin {
        GRANTS {
            SELECT   ON TABLES    TO app_readonly;
            EXECUTE  ON FUNCTIONS TO app_service;
            USAGE    ON SEQUENCES TO app_service;
        }
    }
}

-- emits
ALTER DEFAULT PRIVILEGES FOR ROLE "app_admin" IN SCHEMA "public"
    GRANT SELECT ON TABLES TO "app_readonly";

ALTER DEFAULT PRIVILEGES FOR ROLE "app_admin" IN SCHEMA "public"
    GRANT EXECUTE ON FUNCTIONS TO "app_service";

ALTER DEFAULT PRIVILEGES FOR ROLE "app_admin" IN SCHEMA "public"
    GRANT USAGE ON SEQUENCES TO "app_service";

Supported object types in ON clause

KeywordApplies to
TABLESSELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
SEQUENCESUSAGE, SELECT, UPDATE
FUNCTIONSEXECUTE
TYPESUSAGE
SCHEMASUSAGE, CREATE

Revoking default privileges

SCHEMA public {
    DEFAULT PRIVILEGES FOR ROLE app_admin {
        REVOKES {
            EXECUTE ON FUNCTIONS FROM PUBLIC;
        }
    }
}

-- emits
ALTER DEFAULT PRIVILEGES FOR ROLE "app_admin" IN SCHEMA "public"
    REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;