> 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
