While PostgreSQL's GRANT ALL ON ALL TABLES IN SCHEMA command is powerful for managing existing tables, it has a significant limitation: it doesn't automatically apply to tables created after the grant statement executes. This creates security gaps in production environments where new tables may be added through migrations or application updates.

The proper solution is to use PostgreSQL's ALTER DEFAULT PRIVILEGES command:

ALTER DEFAULT PRIVILEGES 
    IN SCHEMA public
    GRANT ALL PRIVILEGES ON TABLES TO u;

This ensures that any tables created in the future will automatically have the specified privileges applied.

For a more complete setup, consider these variations:

-- Grant all privileges on future tables to a specific user
ALTER DEFAULT PRIVILEGES 
    FOR ROLE admin_user
    IN SCHEMA public
    GRANT ALL ON TABLES TO app_user;

-- Grant specific privileges to a role
ALTER DEFAULT PRIVILEGES
    IN SCHEMA public
    GRANT SELECT, INSERT ON TABLES TO readonly_role;

-- Apply to all schemas (PostgreSQL 9.6+)
ALTER DEFAULT PRIVILEGES
    GRANT ALL ON TABLES TO u;

To check existing default privileges:

SELECT * FROM pg_default_acl;

To modify existing default privileges:

-- First revoke existing defaults
ALTER DEFAULT PRIVILEGES
    IN SCHEMA public
    REVOKE ALL ON TABLES FROM u;

-- Then set new defaults
ALTER DEFAULT PRIVILEGES
    IN SCHEMA public
    GRANT SELECT ON TABLES TO u;

1. Default privileges only affect objects created after setting them
2. Different default privileges can be set for different object types (TABLES, SEQUENCES, FUNCTIONS, etc.)
3. The command must be executed by a superuser or the role that will own the future objects

For complete coverage, combine both approaches:

-- For existing tables
GRANT ALL ON ALL TABLES IN SCHEMA public TO u;

-- For future tables
ALTER DEFAULT PRIVILEGES
    IN SCHEMA public
    GRANT ALL ON TABLES TO u;

While PostgreSQL's GRANT ALL ON ALL TABLES IN SCHEMA public syntax works perfectly for existing tables, it doesn't automatically apply to tables created after the grant statement executes. This creates a maintenance headache where permissions must be reapplied whenever new tables are added.

PostgreSQL provides ALTER DEFAULT PRIVILEGES to solve exactly this problem:

ALTER DEFAULT PRIVILEGES 
    IN SCHEMA public
    GRANT ALL ON TABLES 
    TO username;

This command ensures all future tables created in the public schema will automatically have the specified privileges assigned.

Here are some common use cases with specific examples:

-- Grant SELECT only to readonly_user
ALTER DEFAULT PRIVILEGES 
    IN SCHEMA analytics
    GRANT SELECT ON TABLES
    TO readonly_user;

-- Grant all privileges to multiple roles
ALTER DEFAULT PRIVILEGES
    FOR ROLE dba
    IN SCHEMA public
    GRANT ALL ON TABLES
    TO admin_user, app_user;

To check existing default privileges:

SELECT 
    defaclrole::regrole AS role,
    defaclnamespace::regnamespace AS schema,
    defaclobjtype AS object_type,
    defaclprivileges AS privileges
FROM pg_default_acl;

1. Default privileges only affect objects created after the statement executes

2. The command must be run by a superuser or the schema owner

3. You can specify privileges for specific roles only:

ALTER DEFAULT PRIVILEGES
    FOR ROLE creator_role
    IN SCHEMA public
    GRANT SELECT ON TABLES
    TO viewer_role;

For comprehensive coverage, combine default privileges with initial grants:

-- Grant on existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user;

-- Set default for future tables
ALTER DEFAULT PRIVILEGES
    IN SCHEMA public
    GRANT SELECT ON TABLES
    TO app_user;