Обсуждение: The synopsis of "ALTER DEFAULT PRIVILEGES" statement is too difficult to understand

Поиск
Список
Период
Сортировка

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

От
PG Doc comments form
Дата:
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/sql-alterdefaultprivileges.html
Description:

The syntax/synopsis of the "ALTER DEFAULT PRIVILEGES" statement on the
https://www.postgresql.org/docs/13/sql-alterdefaultprivileges.html page can
be simplified to the below form:
------------------------------------------------------------------------------------------
ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke

where abbreviated_grant_or_revoke is one of:

GRANT privileges TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT
OPTION ]
REVOKE [ GRANT OPTION FOR ] privileges FROM { [ GROUP ] role_name | PUBLIC }
[, ...] [ CASCADE | RESTRICT ]

and privileges is one of:

{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [,
...] | ALL [ PRIVILEGES ] } ON TABLES
{ { USAGE | SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } ON SEQUENCES
{ EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTIONS | ROUTINES }
{ USAGE | ALL [ PRIVILEGES ] } ON TYPES
{ USAGE | CREATE | ALL [ PRIVILEGES ] } ON SCHEMAS
------------------------------------------------------------

This form is easier to understand.

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

От
Tom Lane
Дата:
PG Doc comments form <noreply@postgresql.org> writes:
> The syntax/synopsis of the "ALTER DEFAULT PRIVILEGES" statement on the
> https://www.postgresql.org/docs/13/sql-alterdefaultprivileges.html page can
> be simplified to the below form:
> [ factor out privileges and object type ]

Hmm.  I can't get too excited about doing that unless we refactor
the GRANT and REVOKE synopses similarly.  The factorization seems
a little weird too --- the sub-production is not just privileges,
but privileges and target object type (or in GRANT/REVOKE, it'd
be specific target object(s)).  Maybe that's okay, but I'm not 100%
sold on this way being any clearer.

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 ]

where privilege_and_object_type is one of:

{ SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON TABLES
... etc etc ...

            regards, tom lane

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

От
Dariusz Daćko
Дата:
> 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