Re: The synopsis of "ALTER DEFAULT PRIVILEGES" statement is too difficult to understand

> Maybe it'd help to split the GRANT and REVOKE cases completely,
> along the lines of

> ALTER DEFAULT PRIVILEGES
>     [ FOR { ROLE | USER } target_role [, ...] ]
>     [ IN SCHEMA schema_name [, ...] ]
>     GRANT privilege_and_object_type
>     TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
>
> ALTER DEFAULT PRIVILEGES
>     [ FOR { ROLE | USER } target_role [, ...] ]
>     [ IN SCHEMA schema_name [, ...] ]
>     REVOKE [ GRANT OPTION FOR ] privilege_and_object_type
>     FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
>

I like this complete split.


The "privileges" name, which I have used, comes from the SQL standard: https://jakewheat.github.io/sql-overview/sql-2016-foundation-grammar.html#grant-statement
  <grant privilege statement> ::=
    GRANT <privileges>  TO <grantee> [ { <comma> <grantee> }... ]
        [ WITH HIERARCHY OPTION ]
        [ WITH GRANT OPTION ]
        [ GRANTED BY <grantor> ]
Also, maybe it's better to use privileges_on_object_type instead of privilege_and_object_type in ALTER DEFAULT PRIVILEGES, and privileges_on_objects instead of privilege_and_object in GRANT/REVOKE?


Refactoring GRANT/REVOKE also makes sense. Currently, synopses of the "ALTER DEFAULT PRIVILEGES" and GRANT/REVOKE statements are one of the longest. Besides the shorter text, the refactoring of the GRANT/REVOKE synopses has the following advantages:
 1. One can easily see that there are two versions of GRANT/REVOKE: one for assigning privileges and one for assigning roles.
 2. One can easily compare synopses of GRANT and REVOKE for example in diff or WinMerge, and notice that privileges_on_objects are the same in both cases.

The refactored synopses of GRANT/REVOKE can look like this:

------------------------------------------------------------------------------------------

GRANT role_name [, ...] TO role_specification [, ...]
    [ WITH ADMIN OPTION ]
    [ GRANTED BY role_specification ]

GRANT privileges_on_objects TO role_specification [, ...]
    [ WITH GRANT OPTION ]

where role_specification can be one of:

[ GROUP ] role_name
PUBLIC
CURRENT_USER
SESSION_USER

and privileges_on_objects is one of:

{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
     | ALL TABLES IN SCHEMA schema_name [, ...] }

{ { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]

{ { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
     | ALL SEQUENCES IN SCHEMA schema_name [, ...] }

{ { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]

{ USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]

{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]

{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]

{ EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
     | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }

{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]

{ { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]

{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]

{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]

{ USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]

------------------------------------------------------------------------------------------

REVOKE [ ADMIN OPTION FOR ] role_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ] privileges_on_objects
    FROM role_specification [, ...]
    [ CASCADE | RESTRICT ]

where role_specification can be one of:

    [ GROUP ] role_name
    PUBLIC
    CURRENT_USER
    SESSION_USER

and privileges_on_objects is one of:

<< paste here the exact copy from the synopsis of the GRANT statement >>

------------------------------------------------------------------------------------------


Best Regards,
Dariusz Dacko