Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
PG Doc comments form
Date:
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

From
Tom Lane
Date:
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

From
Dariusz Daćko
Date:
> 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