ALTER DEFAULT PRIVILEGES

alter_default_privileges ::= ALTER DEFAULT PRIVILEGES
                             [ FOR ROLE role_name ]
                             IN SCHEMA schema_name
                             grant_or_revoke_clause

grant_or_revoke_clause ::= GRANT privilege_list ON object_type TO grantee [ WITH GRANT OPTION ]
                         | REVOKE [ GRANT OPTION FOR ] privilege_list ON object_type FROM grantee

privilege_list ::= privilege [, ...]

privilege ::= SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER   -- for TABLES
            | USAGE | SELECT | UPDATE                                                 -- for SEQUENCES
            | EXECUTE                                                                 -- for FUNCTIONS/ROUTINES
            | USAGE                                                                   -- for TYPES

object_type ::= TABLES | SEQUENCES | FUNCTIONS | ROUTINES | TYPES

grantee ::= role_name | PUBLIC

-- Grant SELECT on all future tables to PUBLIC
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;

-- Grant multiple privileges to a role
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE ON TABLES TO app_user;

-- Grant with ability to re-grant
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT ON TABLES TO admin_user WITH GRANT OPTION;

-- Grant EXECUTE on all future functions
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO api_user;

-- Grant USAGE and SELECT on all future sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO app_user;

-- Grant USAGE on all future types
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON TYPES TO app_user;

-- Expand table privileges and remove sequence privileges
-- Before:
--   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user;
--   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO app_user;
-- After:
--   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO app_user;
-- Migration generates:
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE USAGE ON SEQUENCES FROM app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE ON TABLES TO app_user;

-- Before: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user;
-- After:  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO app_user WITH GRANT OPTION;
-- Migration generates:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE ON TABLES TO app_user WITH GRANT OPTION;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user WITH GRANT OPTION;

-- Remove all default privileges
-- Before:
--   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;
--   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO app_user;
-- After: (no default privileges)
-- Migration generates:
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE DELETE, INSERT, UPDATE ON TABLES FROM app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM readonly_user;

-- For granting
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT privilege_list ON object_type TO grantee[ WITH GRANT OPTION];

-- For revoking
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name REVOKE privilege_list ON object_type FROM grantee;