Re: proposal: schema variables

Hi

út 10. 12. 2024 v 4:32 odesílatel jian he <jian(dot)universality(at)gmail(dot)com>
napsal:

> hi.
>
> GRANT|REVOKE ALL VARIABLES IN SCHEMA schema_name [, ...] }
> seems to work.
> might be better to add tests.
>

done

>
> also src/bin/psql/tab-complete.in.c
> COMPLETE_WITH_SCHEMA_QUERY_PLUS(Query_for_list_of_grantables,
> we can also add "ALL VARIABLES IN SCHEMA "
>

done

>
> also need change this <para> in grant.sgml:
> <para>
> There is also an option to grant privileges on all objects of the same
> type within one or more schemas. This functionality is currently
> supported
> only for tables, sequences, functions, and procedures. <literal>ALL
> TABLES</literal> also affects views and foreign tables, just like the
> specific-object <command>GRANT</command> command. <literal>ALL
> FUNCTIONS</literal> also affects aggregate and window functions, but not
> procedures, again just like the specific-object <command>GRANT</command>
> command. Use <literal>ALL ROUTINES</literal> to include procedures.
> </para>
>

done

>
> revoke.sgml, we should use <replaceable
> class="parameter">role_specification</replaceable>?
> so it will become like:
>
> REVOKE [ GRANT OPTION FOR ]
> { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
> ON { VARIABLE variable_name [, ...]
> | ALL VARIABLES IN SCHEMA schema_name [, ...] }
> FROM role_specification [, ...]
>

done

>
> maybe also add
> [ GRANTED BY role_specification ]
> but I didn't test "REVOKE [ GRANTED BY role_specification ]".
>

It i working, so I enhanced doc and regress tests

>
> Speaking of acl tests,
> similar to has_table_privilege I am afraid we need to have a function
> like has_variable_privilege for acl tests.
> has_table_privilege has 6 function signatures. so there will be more code.
>

ok, I wrote these functions

> ------------------------------------------------------
> doc/src/sgml/ref/create_variable.sgml
> <synopsis> section:
> CREATE VARIABLE [ IF NOT EXISTS ] name [ AS ] data_type ] [ COLLATE
> collation ]
>
> redundant right square bracket after "data_type".
>

fixed

Regards

Pavel