From 31eae6028eca4365e7165f5f33fee1ed0486aee0 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Mon, 9 Mar 2015 15:41:54 -0300 Subject: [PATCH] Allow CURRENT/SESSION_USER to be used in certain commands MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Commands such as ALTER USER, ALTER GROUP, ALTER ROLE, GRANT, and the various ALTER OBJECT / OWNER TO, as well as ad-hoc clauses related to roles such as the AUTHORIZATION clause of CREATE SCHEMA, the FOR clause of CREATE USER MAPPING, and the FOR ROLE clause of ALTER DEFAULT PRIVILEGES can now take the keywords CURRENT_USER and SESSION_USER as user specifiers in place of an explicit user name. This commit also fixes some quite ugly handling of special standards- mandated syntax in CREATE USER MAPPING, which in particular would fail to work in presence of a role named "current_user". The special role specifiers PUBLIC and NONE also have more consistent handling now. Also take the opportunity to add location tracking to user specifiers. Authors: Kyotaro Horiguchi. Heavily reworked by Álvaro Herrera. Reviewed by: Rushabh Lathia, Adam Brightwell, Marti Raudsepp. --- doc/src/sgml/ref/alter_aggregate.sgml | 3 +- doc/src/sgml/ref/alter_collation.sgml | 2 +- doc/src/sgml/ref/alter_conversion.sgml | 2 +- doc/src/sgml/ref/alter_database.sgml | 2 +- doc/src/sgml/ref/alter_domain.sgml | 2 +- doc/src/sgml/ref/alter_event_trigger.sgml | 2 +- .../sgml/ref/alter_foreign_data_wrapper.sgml | 2 +- doc/src/sgml/ref/alter_foreign_table.sgml | 2 +- doc/src/sgml/ref/alter_function.sgml | 2 +- doc/src/sgml/ref/alter_group.sgml | 10 +- doc/src/sgml/ref/alter_language.sgml | 2 +- doc/src/sgml/ref/alter_large_object.sgml | 2 +- doc/src/sgml/ref/alter_materialized_view.sgml | 2 +- doc/src/sgml/ref/alter_opclass.sgml | 11 +- doc/src/sgml/ref/alter_operator.sgml | 7 +- doc/src/sgml/ref/alter_opfamily.sgml | 19 +- doc/src/sgml/ref/alter_role.sgml | 35 +- doc/src/sgml/ref/alter_schema.sgml | 2 +- doc/src/sgml/ref/alter_sequence.sgml | 2 +- doc/src/sgml/ref/alter_server.sgml | 2 +- doc/src/sgml/ref/alter_table.sgml | 2 +- doc/src/sgml/ref/alter_tablespace.sgml | 2 +- doc/src/sgml/ref/alter_tsconfig.sgml | 2 +- doc/src/sgml/ref/alter_tsdictionary.sgml | 2 +- doc/src/sgml/ref/alter_type.sgml | 2 +- doc/src/sgml/ref/alter_user.sgml | 16 +- doc/src/sgml/ref/alter_user_mapping.sgml | 2 +- doc/src/sgml/ref/alter_view.sgml | 2 +- doc/src/sgml/ref/create_schema.sgml | 14 +- doc/src/sgml/ref/grant.sgml | 33 +- src/backend/catalog/aclchk.c | 54 +- src/backend/commands/alter.c | 2 +- src/backend/commands/extension.c | 2 +- src/backend/commands/foreigncmds.c | 58 +- src/backend/commands/policy.c | 18 +- src/backend/commands/schemacmds.c | 21 +- src/backend/commands/tablecmds.c | 4 +- src/backend/commands/tablespace.c | 2 +- src/backend/commands/user.c | 116 +-- src/backend/nodes/copyfuncs.c | 49 +- src/backend/nodes/equalfuncs.c | 45 +- src/backend/parser/gram.y | 228 +++-- src/backend/parser/parse_utilcmd.c | 4 +- src/backend/utils/adt/acl.c | 116 ++- src/include/commands/user.h | 2 +- src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 50 +- src/include/utils/acl.h | 8 +- src/test/regress/expected/rolenames.out | 940 ++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/rolenames.sql | 434 ++++++++ 52 files changed, 2000 insertions(+), 347 deletions(-) create mode 100644 src/test/regress/expected/rolenames.out create mode 100644 src/test/regress/sql/rolenames.sql diff --git a/doc/src/sgml/ref/alter_aggregate.sgml b/doc/src/sgml/ref/alter_aggregate.sgml index f92b8e5da3..3aa7c259da 100644 --- a/doc/src/sgml/ref/alter_aggregate.sgml +++ b/doc/src/sgml/ref/alter_aggregate.sgml @@ -22,7 +22,8 @@ PostgreSQL documentation ALTER AGGREGATE name ( aggregate_signature ) RENAME TO new_name -ALTER AGGREGATE name ( aggregate_signature ) OWNER TO new_owner +ALTER AGGREGATE name ( aggregate_signature ) + OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER AGGREGATE name ( aggregate_signature ) SET SCHEMA new_schema where aggregate_signature is: diff --git a/doc/src/sgml/ref/alter_collation.sgml b/doc/src/sgml/ref/alter_collation.sgml index 11ecd93c20..6708c7e10e 100644 --- a/doc/src/sgml/ref/alter_collation.sgml +++ b/doc/src/sgml/ref/alter_collation.sgml @@ -22,7 +22,7 @@ PostgreSQL documentation ALTER COLLATION name RENAME TO new_name -ALTER COLLATION name OWNER TO new_owner +ALTER COLLATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER COLLATION name SET SCHEMA new_schema diff --git a/doc/src/sgml/ref/alter_conversion.sgml b/doc/src/sgml/ref/alter_conversion.sgml index a3f68975bf..3514720d03 100644 --- a/doc/src/sgml/ref/alter_conversion.sgml +++ b/doc/src/sgml/ref/alter_conversion.sgml @@ -22,7 +22,7 @@ PostgreSQL documentation ALTER CONVERSION name RENAME TO new_name -ALTER CONVERSION name OWNER TO new_owner +ALTER CONVERSION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER CONVERSION name SET SCHEMA new_schema diff --git a/doc/src/sgml/ref/alter_database.sgml b/doc/src/sgml/ref/alter_database.sgml index 95c7ac9158..8b6fa5816f 100644 --- a/doc/src/sgml/ref/alter_database.sgml +++ b/doc/src/sgml/ref/alter_database.sgml @@ -31,7 +31,7 @@ ALTER DATABASE name [ [ WITH ] name RENAME TO new_name -ALTER DATABASE name OWNER TO new_owner +ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER DATABASE name SET TABLESPACE new_tablespace diff --git a/doc/src/sgml/ref/alter_domain.sgml b/doc/src/sgml/ref/alter_domain.sgml index d65d517bb5..f738b2948b 100644 --- a/doc/src/sgml/ref/alter_domain.sgml +++ b/doc/src/sgml/ref/alter_domain.sgml @@ -36,7 +36,7 @@ ALTER DOMAIN name ALTER DOMAIN name VALIDATE CONSTRAINT constraint_name ALTER DOMAIN name - OWNER TO new_owner + OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER DOMAIN name RENAME TO new_name ALTER DOMAIN name diff --git a/doc/src/sgml/ref/alter_event_trigger.sgml b/doc/src/sgml/ref/alter_event_trigger.sgml index 1bf9d8abb6..9d6c64ad52 100644 --- a/doc/src/sgml/ref/alter_event_trigger.sgml +++ b/doc/src/sgml/ref/alter_event_trigger.sgml @@ -23,7 +23,7 @@ PostgreSQL documentation ALTER EVENT TRIGGER name DISABLE ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ] -ALTER EVENT TRIGGER name OWNER TO new_owner +ALTER EVENT TRIGGER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER EVENT TRIGGER name RENAME TO new_name diff --git a/doc/src/sgml/ref/alter_foreign_data_wrapper.sgml b/doc/src/sgml/ref/alter_foreign_data_wrapper.sgml index 1c82db9e03..3f5fb0f77e 100644 --- a/doc/src/sgml/ref/alter_foreign_data_wrapper.sgml +++ b/doc/src/sgml/ref/alter_foreign_data_wrapper.sgml @@ -25,7 +25,7 @@ ALTER FOREIGN DATA WRAPPER name [ HANDLER handler_function | NO HANDLER ] [ VALIDATOR validator_function | NO VALIDATOR ] [ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) ] -ALTER FOREIGN DATA WRAPPER name OWNER TO new_owner +ALTER FOREIGN DATA WRAPPER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER FOREIGN DATA WRAPPER name RENAME TO new_name diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml b/doc/src/sgml/ref/alter_foreign_table.sgml index ff48ab8882..93f8743071 100644 --- a/doc/src/sgml/ref/alter_foreign_table.sgml +++ b/doc/src/sgml/ref/alter_foreign_table.sgml @@ -48,7 +48,7 @@ ALTER FOREIGN TABLE [ IF EXISTS ] nametrigger_name | ALL | USER ] ENABLE REPLICA TRIGGER trigger_name ENABLE ALWAYS TRIGGER trigger_name - OWNER TO new_owner + OWNER TO { new_owner | CURRENT_USER | SESSION_USER } OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ]) diff --git a/doc/src/sgml/ref/alter_function.sgml b/doc/src/sgml/ref/alter_function.sgml index ba879eb1ea..4e77962c88 100644 --- a/doc/src/sgml/ref/alter_function.sgml +++ b/doc/src/sgml/ref/alter_function.sgml @@ -26,7 +26,7 @@ ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) RENAME TO new_name ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) - OWNER TO new_owner + OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) SET SCHEMA new_schema diff --git a/doc/src/sgml/ref/alter_group.sgml b/doc/src/sgml/ref/alter_group.sgml index 143224286a..adf6f7e932 100644 --- a/doc/src/sgml/ref/alter_group.sgml +++ b/doc/src/sgml/ref/alter_group.sgml @@ -21,8 +21,14 @@ PostgreSQL documentation -ALTER GROUP group_name ADD USER user_name [, ... ] -ALTER GROUP group_name DROP USER user_name [, ... ] +ALTER GROUP role_specification ADD USER user_name [, ... ] +ALTER GROUP role_specification DROP USER user_name [, ... ] + +where role_specification can be: + + role_name + | CURRENT_USER + | SESSION_USER ALTER GROUP group_name RENAME TO new_name diff --git a/doc/src/sgml/ref/alter_language.sgml b/doc/src/sgml/ref/alter_language.sgml index 5c9ded6837..63d9ecd924 100644 --- a/doc/src/sgml/ref/alter_language.sgml +++ b/doc/src/sgml/ref/alter_language.sgml @@ -22,7 +22,7 @@ PostgreSQL documentation ALTER [ PROCEDURAL ] LANGUAGE name RENAME TO new_name -ALTER [ PROCEDURAL ] LANGUAGE name OWNER TO new_owner +ALTER [ PROCEDURAL ] LANGUAGE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } diff --git a/doc/src/sgml/ref/alter_large_object.sgml b/doc/src/sgml/ref/alter_large_object.sgml index a6dabca1b2..a0ed6c22f3 100644 --- a/doc/src/sgml/ref/alter_large_object.sgml +++ b/doc/src/sgml/ref/alter_large_object.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -ALTER LARGE OBJECT large_object_oid OWNER TO new_owner +ALTER LARGE OBJECT large_object_oid { OWNER TO new_owner | CURRENT_USER | SESSION_USER } diff --git a/doc/src/sgml/ref/alter_materialized_view.sgml b/doc/src/sgml/ref/alter_materialized_view.sgml index b0759fc5dc..8807e01c36 100644 --- a/doc/src/sgml/ref/alter_materialized_view.sgml +++ b/doc/src/sgml/ref/alter_materialized_view.sgml @@ -42,7 +42,7 @@ ALTER MATERIALIZED VIEW ALL IN TABLESPACE namestorage_parameter = value [, ... ] ) RESET ( storage_parameter [, ... ] ) - OWNER TO new_owner + OWNER TO { new_owner | CURRENT_USER | SESSION_USER } SET TABLESPACE new_tablespace diff --git a/doc/src/sgml/ref/alter_opclass.sgml b/doc/src/sgml/ref/alter_opclass.sgml index fc41d86661..2e561be8d7 100644 --- a/doc/src/sgml/ref/alter_opclass.sgml +++ b/doc/src/sgml/ref/alter_opclass.sgml @@ -21,9 +21,14 @@ PostgreSQL documentation -ALTER OPERATOR CLASS name USING index_method RENAME TO new_name -ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner -ALTER OPERATOR CLASS name USING index_method SET SCHEMA new_schema +ALTER OPERATOR CLASS name USING index_method + RENAME TO new_name + +ALTER OPERATOR CLASS name USING index_method + { OWNER TO new_owner | CURRENT_USER | SESSION_USER } + +ALTER OPERATOR CLASS name USING index_method + SET SCHEMA new_schema diff --git a/doc/src/sgml/ref/alter_operator.sgml b/doc/src/sgml/ref/alter_operator.sgml index 6075416320..bdb2d029b1 100644 --- a/doc/src/sgml/ref/alter_operator.sgml +++ b/doc/src/sgml/ref/alter_operator.sgml @@ -21,8 +21,11 @@ PostgreSQL documentation -ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } ) OWNER TO new_owner -ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } ) SET SCHEMA new_schema +ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } ) + { OWNER TO new_owner | CURRENT_USER | SESSION_USER } + +ALTER OPERATOR name ( { left_type | NONE } , { right_type | NONE } ) + SET SCHEMA new_schema diff --git a/doc/src/sgml/ref/alter_opfamily.sgml b/doc/src/sgml/ref/alter_opfamily.sgml index 55b912611d..b0942b6ea7 100644 --- a/doc/src/sgml/ref/alter_opfamily.sgml +++ b/doc/src/sgml/ref/alter_opfamily.sgml @@ -22,16 +22,25 @@ PostgreSQL documentation ALTER OPERATOR FAMILY name USING index_method ADD - { OPERATOR strategy_number operator_name ( op_type, op_type ) [ FOR SEARCH | FOR ORDER BY sort_family_name ] - | FUNCTION support_number [ ( op_type [ , op_type ] ) ] function_name ( argument_type [, ...] ) + { OPERATOR strategy_number operator_name ( op_type, op_type ) + [ FOR SEARCH | FOR ORDER BY sort_family_name ] + | FUNCTION support_number [ ( op_type [ , op_type ] ) ] + function_name ( argument_type [, ...] ) } [, ... ] + ALTER OPERATOR FAMILY name USING index_method DROP { OPERATOR strategy_number ( op_type [ , op_type ] ) | FUNCTION support_number ( op_type [ , op_type ] ) } [, ... ] -ALTER OPERATOR FAMILY name USING index_method RENAME TO new_name -ALTER OPERATOR FAMILY name USING index_method OWNER TO new_owner -ALTER OPERATOR FAMILY name USING index_method SET SCHEMA new_schema + +ALTER OPERATOR FAMILY name USING index_method + RENAME TO new_name + +ALTER OPERATOR FAMILY name USING index_method + OWNER TO { new_owner | CURRENT_USER | SESSION_USER } + +ALTER OPERATOR FAMILY name USING index_method + SET SCHEMA new_schema diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml index 0471daa1cc..e97bf4c9d2 100644 --- a/doc/src/sgml/ref/alter_role.sgml +++ b/doc/src/sgml/ref/alter_role.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -ALTER ROLE name [ [ WITH ] option [ ... ] ] +ALTER ROLE role_specification [ WITH ] option [ ... ] where option can be: @@ -39,10 +39,16 @@ ALTER ROLE name [ [ WITH ] name RENAME TO new_name -ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT } -ALTER ROLE { name | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT -ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET configuration_parameter -ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET ALL +ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT } +ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT +ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter +ALTER ROLE { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL + +where role_specification can be: + + [ GROUP ] role_name + | CURRENT_USER + | SESSION_USER @@ -128,6 +134,25 @@ ALTER ROLE { name | ALL } [ IN DATA + + CURRENT_USER + + + Alter the current user instead of an explicitely identified role. + + + + + + SESSION_USER + + + Alter the current session user instead of an explicitely identified + role. + + + + SUPERUSER NOSUPERUSER diff --git a/doc/src/sgml/ref/alter_schema.sgml b/doc/src/sgml/ref/alter_schema.sgml index 00395c02f8..dbc5c2d45f 100644 --- a/doc/src/sgml/ref/alter_schema.sgml +++ b/doc/src/sgml/ref/alter_schema.sgml @@ -22,7 +22,7 @@ PostgreSQL documentation ALTER SCHEMA name RENAME TO new_name -ALTER SCHEMA name OWNER TO new_owner +ALTER SCHEMA name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } diff --git a/doc/src/sgml/ref/alter_sequence.sgml b/doc/src/sgml/ref/alter_sequence.sgml index 9ba9bc4622..47d3c8291f 100644 --- a/doc/src/sgml/ref/alter_sequence.sgml +++ b/doc/src/sgml/ref/alter_sequence.sgml @@ -29,7 +29,7 @@ ALTER SEQUENCE [ IF EXISTS ] name [ [ RESTART [ [ WITH ] restart ] ] [ CACHE cache ] [ [ NO ] CYCLE ] [ OWNED BY { table_name.column_name | NONE } ] -ALTER SEQUENCE [ IF EXISTS ] name OWNER TO new_owner +ALTER SEQUENCE [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema diff --git a/doc/src/sgml/ref/alter_server.sgml b/doc/src/sgml/ref/alter_server.sgml index 68253b9166..e6cf511853 100644 --- a/doc/src/sgml/ref/alter_server.sgml +++ b/doc/src/sgml/ref/alter_server.sgml @@ -23,7 +23,7 @@ PostgreSQL documentation ALTER SERVER name [ VERSION 'new_version' ] [ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) ] -ALTER SERVER name OWNER TO new_owner +ALTER SERVER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER SERVER name RENAME TO new_name diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index b3a4970713..19baf2961a 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -73,7 +73,7 @@ ALTER TABLE ALL IN TABLESPACE name NO INHERIT parent_table OF type_name NOT OF - OWNER TO new_owner + OWNER TO { new_owner | CURRENT_USER | SESSION_USER } REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING} and table_constraint_using_index is: diff --git a/doc/src/sgml/ref/alter_tablespace.sgml b/doc/src/sgml/ref/alter_tablespace.sgml index 9d27173d71..d9b2a133b1 100644 --- a/doc/src/sgml/ref/alter_tablespace.sgml +++ b/doc/src/sgml/ref/alter_tablespace.sgml @@ -22,7 +22,7 @@ PostgreSQL documentation ALTER TABLESPACE name RENAME TO new_name -ALTER TABLESPACE name OWNER TO new_owner +ALTER TABLESPACE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] ) ALTER TABLESPACE name RESET ( tablespace_option [, ... ] ) diff --git a/doc/src/sgml/ref/alter_tsconfig.sgml b/doc/src/sgml/ref/alter_tsconfig.sgml index 1a40d88f6a..72a719b862 100644 --- a/doc/src/sgml/ref/alter_tsconfig.sgml +++ b/doc/src/sgml/ref/alter_tsconfig.sgml @@ -32,7 +32,7 @@ ALTER TEXT SEARCH CONFIGURATION name ALTER TEXT SEARCH CONFIGURATION name DROP MAPPING [ IF EXISTS ] FOR token_type [, ... ] ALTER TEXT SEARCH CONFIGURATION name RENAME TO new_name -ALTER TEXT SEARCH CONFIGURATION name OWNER TO new_owner +ALTER TEXT SEARCH CONFIGURATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER TEXT SEARCH CONFIGURATION name SET SCHEMA new_schema diff --git a/doc/src/sgml/ref/alter_tsdictionary.sgml b/doc/src/sgml/ref/alter_tsdictionary.sgml index 368f8ee135..7cecabea83 100644 --- a/doc/src/sgml/ref/alter_tsdictionary.sgml +++ b/doc/src/sgml/ref/alter_tsdictionary.sgml @@ -25,7 +25,7 @@ ALTER TEXT SEARCH DICTIONARY name ( option [ = value ] [, ... ] ) ALTER TEXT SEARCH DICTIONARY name RENAME TO new_name -ALTER TEXT SEARCH DICTIONARY name OWNER TO new_owner +ALTER TEXT SEARCH DICTIONARY name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER TEXT SEARCH DICTIONARY name SET SCHEMA new_schema diff --git a/doc/src/sgml/ref/alter_type.sgml b/doc/src/sgml/ref/alter_type.sgml index 7724c11c78..9789881a5c 100644 --- a/doc/src/sgml/ref/alter_type.sgml +++ b/doc/src/sgml/ref/alter_type.sgml @@ -24,7 +24,7 @@ PostgreSQL documentation ALTER TYPE name action [, ... ] -ALTER TYPE name OWNER TO new_owner +ALTER TYPE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER TYPE name RENAME ATTRIBUTE attribute_name TO new_attribute_name [ CASCADE | RESTRICT ] ALTER TYPE name RENAME TO new_name ALTER TYPE name SET SCHEMA new_schema diff --git a/doc/src/sgml/ref/alter_user.sgml b/doc/src/sgml/ref/alter_user.sgml index 58ae1da127..0ffaa16da2 100644 --- a/doc/src/sgml/ref/alter_user.sgml +++ b/doc/src/sgml/ref/alter_user.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -ALTER USER name [ [ WITH ] option [ ... ] ] +ALTER USER role_specification [ WITH ] option [ ... ] where option can be: @@ -38,10 +38,16 @@ ALTER USER name [ [ WITH ] name RENAME TO new_name -ALTER USER name SET configuration_parameter { TO | = } { value | DEFAULT } -ALTER USER name SET configuration_parameter FROM CURRENT -ALTER USER name RESET configuration_parameter -ALTER USER name RESET ALL +ALTER USER role_specification SET configuration_parameter { TO | = } { value | DEFAULT } +ALTER USER role_specification SET configuration_parameter FROM CURRENT +ALTER USER role_specification RESET configuration_parameter +ALTER USER role_specification RESET ALL + +where role_specification can be: + + [ GROUP ] role_name + | CURRENT_USER + | SESSION_USER diff --git a/doc/src/sgml/ref/alter_user_mapping.sgml b/doc/src/sgml/ref/alter_user_mapping.sgml index d0ddd1370b..3cd51b1413 100644 --- a/doc/src/sgml/ref/alter_user_mapping.sgml +++ b/doc/src/sgml/ref/alter_user_mapping.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -ALTER USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC } +ALTER USER MAPPING FOR { user_name | USER | CURRENT_USER | SESSION_USER | PUBLIC } SERVER server_name OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) diff --git a/doc/src/sgml/ref/alter_view.sgml b/doc/src/sgml/ref/alter_view.sgml index 3aef61b67e..00f4ecb9b1 100644 --- a/doc/src/sgml/ref/alter_view.sgml +++ b/doc/src/sgml/ref/alter_view.sgml @@ -23,7 +23,7 @@ PostgreSQL documentation ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT -ALTER VIEW [ IF EXISTS ] name OWNER TO new_owner +ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER VIEW [ IF EXISTS ] name RENAME TO new_name ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] ) diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml index 45e4637ab5..79305f15dd 100644 --- a/doc/src/sgml/ref/create_schema.sgml +++ b/doc/src/sgml/ref/create_schema.sgml @@ -21,10 +21,16 @@ PostgreSQL documentation -CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ] -CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ] -CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ] -CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name +CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ] +CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ] +CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ] +CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification + +where role_specification can be: + + [ GROUP ] user_name + | CURRENT_USER + | SESSION_USER diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index f2b25e546c..d9ac8d20f2 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -25,59 +25,66 @@ GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] ) [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) } ON [ TABLE ] table_name [, ...] - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { { USAGE | SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] } - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } ON DATABASE database_name [, ...] - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON DOMAIN domain_name [, ...] - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN DATA WRAPPER fdw_name [, ...] - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN SERVER server_name [, ...] - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...] | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE lang_name [, ...] - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } ON LARGE OBJECT loid [, ...] - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] } ON SCHEMA schema_name [, ...] - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespace_name [, ...] - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON TYPE type_name [, ...] - TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + TO role_specification [, ...] [ WITH GRANT OPTION ] + +where role_specification can be: + + [ GROUP ] role_name + | PUBLIC + | CURRENT_USER + | SESSION_USER GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ] diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index 3b456f97ef..6c8780f794 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -421,22 +421,25 @@ ExecuteGrantStmt(GrantStmt *stmt) istmt.behavior = stmt->behavior; /* - * Convert the PrivGrantee list into an Oid list. Note that at this point - * we insert an ACL_ID_PUBLIC into the list if an empty role name is - * detected (which is what the grammar uses if PUBLIC is found), so - * downstream there shouldn't be any additional work needed to support - * this case. + * Convert the RoleSpec list into an Oid list. Note that at this point + * we insert an ACL_ID_PUBLIC into the list if appropriate, so downstream + * there shouldn't be any additional work needed to support this case. */ foreach(cell, stmt->grantees) { - PrivGrantee *grantee = (PrivGrantee *) lfirst(cell); + RoleSpec *grantee = (RoleSpec *) lfirst(cell); + Oid grantee_uid; - if (grantee->rolname == NULL) - istmt.grantees = lappend_oid(istmt.grantees, ACL_ID_PUBLIC); - else - istmt.grantees = - lappend_oid(istmt.grantees, - get_role_oid(grantee->rolname, false)); + switch (grantee->roletype) + { + case ROLESPEC_PUBLIC: + grantee_uid = ACL_ID_PUBLIC; + break; + default: + grantee_uid = get_rolespec_oid((Node *) grantee, false); + break; + } + istmt.grantees = lappend_oid(istmt.grantees, grantee_uid); } /* @@ -904,22 +907,25 @@ ExecAlterDefaultPrivilegesStmt(AlterDefaultPrivilegesStmt *stmt) iacls.behavior = action->behavior; /* - * Convert the PrivGrantee list into an Oid list. Note that at this point - * we insert an ACL_ID_PUBLIC into the list if an empty role name is - * detected (which is what the grammar uses if PUBLIC is found), so - * downstream there shouldn't be any additional work needed to support - * this case. + * Convert the RoleSpec list into an Oid list. Note that at this point + * we insert an ACL_ID_PUBLIC into the list if appropriate, so downstream + * there shouldn't be any additional work needed to support this case. */ foreach(cell, action->grantees) { - PrivGrantee *grantee = (PrivGrantee *) lfirst(cell); + RoleSpec *grantee = (RoleSpec *) lfirst(cell); + Oid grantee_uid; - if (grantee->rolname == NULL) - iacls.grantees = lappend_oid(iacls.grantees, ACL_ID_PUBLIC); - else - iacls.grantees = - lappend_oid(iacls.grantees, - get_role_oid(grantee->rolname, false)); + switch (grantee->roletype) + { + case ROLESPEC_PUBLIC: + grantee_uid = ACL_ID_PUBLIC; + break; + default: + grantee_uid = get_rolespec_oid((Node *) grantee, false); + break; + } + iacls.grantees = lappend_oid(iacls.grantees, grantee_uid); } /* diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c index 59aacef7ea..3ddd7ec434 100644 --- a/src/backend/commands/alter.c +++ b/src/backend/commands/alter.c @@ -699,7 +699,7 @@ AlterObjectNamespace_internal(Relation rel, Oid objid, Oid nspOid) ObjectAddress ExecAlterOwnerStmt(AlterOwnerStmt *stmt) { - Oid newowner = get_role_oid(stmt->newowner, false); + Oid newowner = get_rolespec_oid(stmt->newowner, false); switch (stmt->objectType) { diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c index aa733575e4..5cc74d03c1 100644 --- a/src/backend/commands/extension.c +++ b/src/backend/commands/extension.c @@ -1371,7 +1371,7 @@ CreateExtension(CreateExtensionStmt *stmt) CreateSchemaStmt *csstmt = makeNode(CreateSchemaStmt); csstmt->schemaname = schemaName; - csstmt->authid = NULL; /* will be created by current user */ + csstmt->authrole = NULL; /* will be created by current user */ csstmt->schemaElts = NIL; csstmt->if_not_exists = false; CreateSchemaCommand(csstmt, NULL); diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c index bd4839189e..3b85c2c017 100644 --- a/src/backend/commands/foreigncmds.c +++ b/src/backend/commands/foreigncmds.c @@ -197,24 +197,6 @@ transformGenericOptions(Oid catalogId, } -/* - * Convert the user mapping user name to OID - */ -static Oid -GetUserOidFromMapping(const char *username, bool missing_ok) -{ - if (!username) - /* PUBLIC user mapping */ - return InvalidOid; - - if (strcmp(username, "current_user") == 0) - /* map to the owner */ - return GetUserId(); - - /* map to provided user */ - return get_role_oid(username, missing_ok); -} - /* * Internal workhorse for changing a data wrapper's owner. * @@ -1156,10 +1138,14 @@ CreateUserMapping(CreateUserMappingStmt *stmt) ObjectAddress referenced; ForeignServer *srv; ForeignDataWrapper *fdw; + RoleSpec *role = (RoleSpec *) stmt->user; rel = heap_open(UserMappingRelationId, RowExclusiveLock); - useId = GetUserOidFromMapping(stmt->username, false); + if (role->roletype == ROLESPEC_PUBLIC) + useId = ACL_ID_PUBLIC; + else + useId = get_rolespec_oid(stmt->user, false); /* Check that the server exists. */ srv = GetForeignServerByName(stmt->servername, false); @@ -1252,10 +1238,15 @@ AlterUserMapping(AlterUserMappingStmt *stmt) Oid umId; ForeignServer *srv; ObjectAddress address; + RoleSpec *role = (RoleSpec *) stmt->user; rel = heap_open(UserMappingRelationId, RowExclusiveLock); - useId = GetUserOidFromMapping(stmt->username, false); + if (role->roletype == ROLESPEC_PUBLIC) + useId = ACL_ID_PUBLIC; + else + useId = get_rolespec_oid(stmt->user, false); + srv = GetForeignServerByName(stmt->servername, false); umId = GetSysCacheOid2(USERMAPPINGUSERSERVER, @@ -1338,20 +1329,27 @@ RemoveUserMapping(DropUserMappingStmt *stmt) Oid useId; Oid umId; ForeignServer *srv; + RoleSpec *role = (RoleSpec *) stmt->user; - useId = GetUserOidFromMapping(stmt->username, stmt->missing_ok); - srv = GetForeignServerByName(stmt->servername, true); - - if (stmt->username && !OidIsValid(useId)) + if (role->roletype == ROLESPEC_PUBLIC) + useId = ACL_ID_PUBLIC; + else { - /* - * IF EXISTS specified, role not found and not public. Notice this and - * leave. - */ - elog(NOTICE, "role \"%s\" does not exist, skipping", stmt->username); - return InvalidOid; + useId = get_rolespec_oid(stmt->user, stmt->missing_ok); + if (!OidIsValid(useId)) + { + /* + * IF EXISTS specified, role not found and not public. Notice this + * and leave. + */ + elog(NOTICE, "role \"%s\" does not exist, skipping", + role->rolename); + return InvalidOid; + } } + srv = GetForeignServerByName(stmt->servername, true); + if (!srv) { if (!stmt->missing_ok) diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c index e86299781f..a3d840da5c 100644 --- a/src/backend/commands/policy.c +++ b/src/backend/commands/policy.c @@ -129,13 +129,7 @@ parse_policy_command(const char *cmd_name) /* * policy_role_list_to_array - * helper function to convert a list of role names in to an array of - * role ids. - * - * Note: If PUBLIC is provided as a role name, then ACL_ID_PUBLIC is - * used as the role id. - * - * roles - the list of role names to convert. + * helper function to convert a list of RoleSpecs to an array of role ids. */ static ArrayType * policy_role_list_to_array(List *roles) @@ -162,25 +156,25 @@ policy_role_list_to_array(List *roles) foreach(cell, roles) { - Oid roleid = get_role_oid_or_public(strVal(lfirst(cell))); + RoleSpec *spec = lfirst(cell); /* * PUBLIC covers all roles, so it only makes sense alone. */ - if (roleid == ACL_ID_PUBLIC) + if (spec->roletype == ROLESPEC_PUBLIC) { if (num_roles != 1) ereport(WARNING, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("ignoring roles specified other than public"), errhint("All roles are members of the public role."))); - - temp_array[0] = ObjectIdGetDatum(roleid); + temp_array[0] = ObjectIdGetDatum(ACL_ID_PUBLIC); num_roles = 1; break; } else - temp_array[i++] = ObjectIdGetDatum(roleid); + temp_array[i++] = + ObjectIdGetDatum(get_rolespec_oid((Node *) spec, false)); } role_ids = construct_array(temp_array, num_roles, OIDOID, sizeof(Oid), true, diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c index 722142e16e..c090ed220f 100644 --- a/src/backend/commands/schemacmds.c +++ b/src/backend/commands/schemacmds.c @@ -21,6 +21,7 @@ #include "catalog/dependency.h" #include "catalog/indexing.h" #include "catalog/namespace.h" +#include "catalog/pg_authid.h" #include "catalog/objectaccess.h" #include "catalog/pg_namespace.h" #include "commands/dbcommands.h" @@ -42,8 +43,7 @@ static void AlterSchemaOwner_internal(HeapTuple tup, Relation rel, Oid newOwnerI Oid CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString) { - const char *schemaName = stmt->schemaname; - const char *authId = stmt->authid; + const char *schemaName = stmt->schemaname; Oid namespaceId; OverrideSearchPath *overridePath; List *parsetree_list; @@ -58,11 +58,24 @@ CreateSchemaCommand(CreateSchemaStmt *stmt, const char *queryString) /* * Who is supposed to own the new schema? */ - if (authId) - owner_uid = get_role_oid(authId, false); + if (stmt->authrole) + owner_uid = get_rolespec_oid(stmt->authrole, false); else owner_uid = saved_uid; + /* fill schema name with the user name if not specified */ + if (!schemaName) + { + HeapTuple tuple; + + tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(owner_uid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for role %u", owner_uid); + schemaName = + pstrdup(NameStr(((Form_pg_authid) GETSTRUCT(tuple))->rolname)); + ReleaseSysCache(tuple); + } + /* * To create a schema, must have schema-create privilege on the current * database and must be able to become the target role (this does not diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 653677892d..623e6bfba8 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -3507,7 +3507,7 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, break; case AT_ChangeOwner: /* ALTER OWNER */ ATExecChangeOwner(RelationGetRelid(rel), - get_role_oid(cmd->name, false), + get_rolespec_oid(cmd->newowner, false), false, lockmode); break; case AT_ClusterOn: /* CLUSTER ON */ @@ -9388,7 +9388,7 @@ AlterTableMoveAll(AlterTableMoveAllStmt *stmt) HeapTuple tuple; Oid orig_tablespaceoid; Oid new_tablespaceoid; - List *role_oids = roleNamesToIds(stmt->roles); + List *role_oids = roleSpecsToIds(stmt->roles); /* Ensure we were not asked to move something we can't */ if (stmt->objtype != OBJECT_TABLE && stmt->objtype != OBJECT_INDEX && diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.c index 68b6917df5..fd226125a9 100644 --- a/src/backend/commands/tablespace.c +++ b/src/backend/commands/tablespace.c @@ -252,7 +252,7 @@ CreateTableSpace(CreateTableSpaceStmt *stmt) /* However, the eventual owner of the tablespace need not be */ if (stmt->owner) - ownerId = get_role_oid(stmt->owner, false); + ownerId = get_rolespec_oid(stmt->owner, false); else ownerId = GetUserId(); diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index 0ba7ba0c20..c14465eb87 100644 --- a/src/backend/commands/user.c +++ b/src/backend/commands/user.c @@ -49,10 +49,10 @@ extern bool Password_encryption; check_password_hook_type check_password_hook = NULL; static void AddRoleMems(const char *rolename, Oid roleid, - List *memberNames, List *memberIds, + List *memberSpecs, List *memberIds, Oid grantorId, bool admin_opt); static void DelRoleMems(const char *rolename, Oid roleid, - List *memberNames, List *memberIds, + List *memberSpecs, List *memberIds, bool admin_opt); @@ -443,10 +443,10 @@ CreateRole(CreateRoleStmt *stmt) * option, rolemembers don't. */ AddRoleMems(stmt->role, roleid, - adminmembers, roleNamesToIds(adminmembers), + adminmembers, roleSpecsToIds(adminmembers), GetUserId(), true); AddRoleMems(stmt->role, roleid, - rolemembers, roleNamesToIds(rolemembers), + rolemembers, roleSpecsToIds(rolemembers), GetUserId(), false); /* Post creation hook for new role */ @@ -478,7 +478,9 @@ AlterRole(AlterRoleStmt *stmt) TupleDesc pg_authid_dsc; HeapTuple tuple, new_tuple; + Form_pg_authid authform; ListCell *option; + char *rolename = NULL; char *password = NULL; /* user password */ bool encrypt_password = Password_encryption; /* encrypt password? */ char encrypted_password[MD5_PASSWD_LEN + 1]; @@ -647,33 +649,30 @@ AlterRole(AlterRoleStmt *stmt) pg_authid_rel = heap_open(AuthIdRelationId, RowExclusiveLock); pg_authid_dsc = RelationGetDescr(pg_authid_rel); - tuple = SearchSysCache1(AUTHNAME, PointerGetDatum(stmt->role)); - if (!HeapTupleIsValid(tuple)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("role \"%s\" does not exist", stmt->role))); - + tuple = get_rolespec_tuple(stmt->role); + authform = (Form_pg_authid) GETSTRUCT(tuple); + rolename = pstrdup(NameStr(authform->rolname)); roleid = HeapTupleGetOid(tuple); /* * To mess with a superuser you gotta be superuser; else you need * createrole, or just want to change your own password */ - if (((Form_pg_authid) GETSTRUCT(tuple))->rolsuper || issuper >= 0) + if (authform->rolsuper || issuper >= 0) { if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to alter superusers"))); } - else if (((Form_pg_authid) GETSTRUCT(tuple))->rolreplication || isreplication >= 0) + else if (authform->rolreplication || isreplication >= 0) { if (!superuser()) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), errmsg("must be superuser to alter replication users"))); } - else if (((Form_pg_authid) GETSTRUCT(tuple))->rolbypassrls || bypassrls >= 0) + else if (authform->rolbypassrls || bypassrls >= 0) { if (!superuser()) ereport(ERROR, @@ -718,11 +717,11 @@ AlterRole(AlterRoleStmt *stmt) * Call the password checking hook if there is one defined */ if (check_password_hook && password) - (*check_password_hook) (stmt->role, - password, - isMD5(password) ? PASSWORD_TYPE_MD5 : PASSWORD_TYPE_PLAINTEXT, - validUntil_datum, - validUntil_null); + (*check_password_hook)(rolename , + password, + isMD5(password) ? PASSWORD_TYPE_MD5 : PASSWORD_TYPE_PLAINTEXT, + validUntil_datum, + validUntil_null); /* * Build an updated tuple, perusing the information just obtained @@ -784,7 +783,7 @@ AlterRole(AlterRoleStmt *stmt) CStringGetTextDatum(password); else { - if (!pg_md5_encrypt(password, stmt->role, strlen(stmt->role), + if (!pg_md5_encrypt(password, rolename, strlen(rolename), encrypted_password)) elog(ERROR, "password encryption failed"); new_record[Anum_pg_authid_rolpassword - 1] = @@ -831,12 +830,12 @@ AlterRole(AlterRoleStmt *stmt) CommandCounterIncrement(); if (stmt->action == +1) /* add members to role */ - AddRoleMems(stmt->role, roleid, - rolemembers, roleNamesToIds(rolemembers), + AddRoleMems(rolename, roleid, + rolemembers, roleSpecsToIds(rolemembers), GetUserId(), false); else if (stmt->action == -1) /* drop members from role */ - DelRoleMems(stmt->role, roleid, - rolemembers, roleNamesToIds(rolemembers), + DelRoleMems(rolename, roleid, + rolemembers, roleSpecsToIds(rolemembers), false); /* @@ -860,13 +859,7 @@ AlterRoleSet(AlterRoleSetStmt *stmt) if (stmt->role) { - roletuple = SearchSysCache1(AUTHNAME, PointerGetDatum(stmt->role)); - - if (!HeapTupleIsValid(roletuple)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_OBJECT), - errmsg("role \"%s\" does not exist", stmt->role))); - + roletuple = get_rolespec_tuple(stmt->role); roleid = HeapTupleGetOid(roletuple); /* @@ -955,7 +948,8 @@ DropRole(DropRoleStmt *stmt) foreach(item, stmt->roles) { - const char *role = strVal(lfirst(item)); + RoleSpec *rolspec = lfirst(item); + char *role; HeapTuple tuple, tmp_tuple; ScanKeyData scankey; @@ -964,6 +958,12 @@ DropRole(DropRoleStmt *stmt) SysScanDesc sscan; Oid roleid; + if (rolspec->roletype != ROLESPEC_CSTRING) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("cannot use special role specifier in \"%s\"", "DROP ROLE"))); + role = rolspec->rolename; + tuple = SearchSysCache1(AUTHNAME, PointerGetDatum(role)); if (!HeapTupleIsValid(tuple)) { @@ -1233,11 +1233,11 @@ GrantRole(GrantRoleStmt *stmt) ListCell *item; if (stmt->grantor) - grantor = get_role_oid(stmt->grantor, false); + grantor = get_rolespec_oid(stmt->grantor, false); else grantor = GetUserId(); - grantee_ids = roleNamesToIds(stmt->grantee_roles); + grantee_ids = roleSpecsToIds(stmt->grantee_roles); /* AccessShareLock is enough since we aren't modifying pg_authid */ pg_authid_rel = heap_open(AuthIdRelationId, AccessShareLock); @@ -1286,7 +1286,7 @@ GrantRole(GrantRoleStmt *stmt) void DropOwnedObjects(DropOwnedStmt *stmt) { - List *role_ids = roleNamesToIds(stmt->roles); + List *role_ids = roleSpecsToIds(stmt->roles); ListCell *cell; /* Check privileges */ @@ -1312,7 +1312,7 @@ DropOwnedObjects(DropOwnedStmt *stmt) void ReassignOwnedObjects(ReassignOwnedStmt *stmt) { - List *role_ids = roleNamesToIds(stmt->roles); + List *role_ids = roleSpecsToIds(stmt->roles); ListCell *cell; Oid newrole; @@ -1328,7 +1328,7 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) } /* Must have privileges on the receiving side too */ - newrole = get_role_oid(stmt->newrole, false); + newrole = get_rolespec_oid(stmt->newrole, false); if (!has_privs_of_role(GetUserId(), newrole)) ereport(ERROR, @@ -1340,22 +1340,24 @@ ReassignOwnedObjects(ReassignOwnedStmt *stmt) } /* - * roleNamesToIds + * roleSpecsToIds + * + * Given a list of RoleSpecs, generate a list of role OIDs in the same order. * - * Given a list of role names (as String nodes), generate a list of role OIDs - * in the same order. + * ROLESPEC_PUBLIC is not allowed. */ List * -roleNamesToIds(List *memberNames) +roleSpecsToIds(List *memberNames) { List *result = NIL; ListCell *l; foreach(l, memberNames) { - char *rolename = strVal(lfirst(l)); - Oid roleid = get_role_oid(rolename, false); + Node *rolespec = (Node *) lfirst(l); + Oid roleid; + roleid = get_rolespec_oid(rolespec, false); result = lappend_oid(result, roleid); } return result; @@ -1366,7 +1368,7 @@ roleNamesToIds(List *memberNames) * * rolename: name of role to add to (used only for error messages) * roleid: OID of role to add to - * memberNames: list of names of roles to add (used only for error messages) + * memberSpecs: list of RoleSpec of roles to add (used only for error messages) * memberIds: OIDs of roles to add * grantorId: who is granting the membership * admin_opt: granting admin option? @@ -1375,15 +1377,15 @@ roleNamesToIds(List *memberNames) */ static void AddRoleMems(const char *rolename, Oid roleid, - List *memberNames, List *memberIds, + List *memberSpecs, List *memberIds, Oid grantorId, bool admin_opt) { Relation pg_authmem_rel; TupleDesc pg_authmem_dsc; - ListCell *nameitem; + ListCell *specitem; ListCell *iditem; - Assert(list_length(memberNames) == list_length(memberIds)); + Assert(list_length(memberSpecs) == list_length(memberIds)); /* Skip permission check if nothing to do */ if (!memberIds) @@ -1428,9 +1430,9 @@ AddRoleMems(const char *rolename, Oid roleid, pg_authmem_rel = heap_open(AuthMemRelationId, RowExclusiveLock); pg_authmem_dsc = RelationGetDescr(pg_authmem_rel); - forboth(nameitem, memberNames, iditem, memberIds) + forboth(specitem, memberSpecs, iditem, memberIds) { - const char *membername = strVal(lfirst(nameitem)); + RoleSpec *memberRole = lfirst(specitem); Oid memberid = lfirst_oid(iditem); HeapTuple authmem_tuple; HeapTuple tuple; @@ -1449,7 +1451,7 @@ AddRoleMems(const char *rolename, Oid roleid, ereport(ERROR, (errcode(ERRCODE_INVALID_GRANT_OPERATION), (errmsg("role \"%s\" is a member of role \"%s\"", - rolename, membername)))); + rolename, get_rolespec_name((Node *) memberRole))))); /* * Check if entry for this role/member already exists; if so, give @@ -1464,7 +1466,7 @@ AddRoleMems(const char *rolename, Oid roleid, { ereport(NOTICE, (errmsg("role \"%s\" is already a member of role \"%s\"", - membername, rolename))); + get_rolespec_name((Node *) memberRole), rolename))); ReleaseSysCache(authmem_tuple); continue; } @@ -1513,7 +1515,7 @@ AddRoleMems(const char *rolename, Oid roleid, * * rolename: name of role to del from (used only for error messages) * roleid: OID of role to del from - * memberNames: list of names of roles to del (used only for error messages) + * memberSpecs: list of RoleSpec of roles to del (used only for error messages) * memberIds: OIDs of roles to del * admin_opt: remove admin option only? * @@ -1521,15 +1523,15 @@ AddRoleMems(const char *rolename, Oid roleid, */ static void DelRoleMems(const char *rolename, Oid roleid, - List *memberNames, List *memberIds, + List *memberSpecs, List *memberIds, bool admin_opt) { Relation pg_authmem_rel; TupleDesc pg_authmem_dsc; - ListCell *nameitem; + ListCell *specitem; ListCell *iditem; - Assert(list_length(memberNames) == list_length(memberIds)); + Assert(list_length(memberSpecs) == list_length(memberIds)); /* Skip permission check if nothing to do */ if (!memberIds) @@ -1559,9 +1561,9 @@ DelRoleMems(const char *rolename, Oid roleid, pg_authmem_rel = heap_open(AuthMemRelationId, RowExclusiveLock); pg_authmem_dsc = RelationGetDescr(pg_authmem_rel); - forboth(nameitem, memberNames, iditem, memberIds) + forboth(specitem, memberSpecs, iditem, memberIds) { - const char *membername = strVal(lfirst(nameitem)); + RoleSpec *memberRole = lfirst(specitem); Oid memberid = lfirst_oid(iditem); HeapTuple authmem_tuple; @@ -1575,7 +1577,7 @@ DelRoleMems(const char *rolename, Oid roleid, { ereport(WARNING, (errmsg("role \"%s\" is not a member of role \"%s\"", - membername, rolename))); + get_rolespec_name((Node *) memberRole), rolename))); continue; } diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 9fe80080f3..ebb6f3a49b 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2504,6 +2504,18 @@ _copyXmlSerialize(const XmlSerialize *from) return newnode; } +static RoleSpec * +_copyRoleSpec(const RoleSpec *from) +{ + RoleSpec *newnode = makeNode(RoleSpec); + + COPY_SCALAR_FIELD(roletype); + COPY_STRING_FIELD(rolename); + COPY_LOCATION_FIELD(location); + + return newnode; +} + static Query * _copyQuery(const Query *from) { @@ -2650,6 +2662,7 @@ _copyAlterTableCmd(const AlterTableCmd *from) COPY_SCALAR_FIELD(subtype); COPY_STRING_FIELD(name); + COPY_NODE_FIELD(newowner); COPY_NODE_FIELD(def); COPY_SCALAR_FIELD(behavior); COPY_SCALAR_FIELD(missing_ok); @@ -2689,16 +2702,6 @@ _copyGrantStmt(const GrantStmt *from) return newnode; } -static PrivGrantee * -_copyPrivGrantee(const PrivGrantee *from) -{ - PrivGrantee *newnode = makeNode(PrivGrantee); - - COPY_STRING_FIELD(rolname); - - return newnode; -} - static FuncWithArgs * _copyFuncWithArgs(const FuncWithArgs *from) { @@ -2730,7 +2733,7 @@ _copyGrantRoleStmt(const GrantRoleStmt *from) COPY_NODE_FIELD(grantee_roles); COPY_SCALAR_FIELD(is_grant); COPY_SCALAR_FIELD(admin_opt); - COPY_STRING_FIELD(grantor); + COPY_NODE_FIELD(grantor); COPY_SCALAR_FIELD(behavior); return newnode; @@ -3038,7 +3041,7 @@ _copyAlterOwnerStmt(const AlterOwnerStmt *from) COPY_NODE_FIELD(relation); COPY_NODE_FIELD(object); COPY_NODE_FIELD(objarg); - COPY_STRING_FIELD(newowner); + COPY_NODE_FIELD(newowner); return newnode; } @@ -3424,7 +3427,7 @@ _copyCreateTableSpaceStmt(const CreateTableSpaceStmt *from) CreateTableSpaceStmt *newnode = makeNode(CreateTableSpaceStmt); COPY_STRING_FIELD(tablespacename); - COPY_STRING_FIELD(owner); + COPY_NODE_FIELD(owner); COPY_STRING_FIELD(location); COPY_NODE_FIELD(options); @@ -3561,7 +3564,7 @@ _copyCreateUserMappingStmt(const CreateUserMappingStmt *from) { CreateUserMappingStmt *newnode = makeNode(CreateUserMappingStmt); - COPY_STRING_FIELD(username); + COPY_NODE_FIELD(user); COPY_STRING_FIELD(servername); COPY_NODE_FIELD(options); @@ -3573,7 +3576,7 @@ _copyAlterUserMappingStmt(const AlterUserMappingStmt *from) { AlterUserMappingStmt *newnode = makeNode(AlterUserMappingStmt); - COPY_STRING_FIELD(username); + COPY_NODE_FIELD(user); COPY_STRING_FIELD(servername); COPY_NODE_FIELD(options); @@ -3585,7 +3588,7 @@ _copyDropUserMappingStmt(const DropUserMappingStmt *from) { DropUserMappingStmt *newnode = makeNode(DropUserMappingStmt); - COPY_STRING_FIELD(username); + COPY_NODE_FIELD(user); COPY_STRING_FIELD(servername); COPY_SCALAR_FIELD(missing_ok); @@ -3698,7 +3701,7 @@ _copyAlterRoleStmt(const AlterRoleStmt *from) { AlterRoleStmt *newnode = makeNode(AlterRoleStmt); - COPY_STRING_FIELD(role); + COPY_NODE_FIELD(role); COPY_NODE_FIELD(options); COPY_SCALAR_FIELD(action); @@ -3710,7 +3713,7 @@ _copyAlterRoleSetStmt(const AlterRoleSetStmt *from) { AlterRoleSetStmt *newnode = makeNode(AlterRoleSetStmt); - COPY_STRING_FIELD(role); + COPY_NODE_FIELD(role); COPY_STRING_FIELD(database); COPY_NODE_FIELD(setstmt); @@ -3769,7 +3772,7 @@ _copyCreateSchemaStmt(const CreateSchemaStmt *from) CreateSchemaStmt *newnode = makeNode(CreateSchemaStmt); COPY_STRING_FIELD(schemaname); - COPY_STRING_FIELD(authid); + COPY_NODE_FIELD(authrole); COPY_NODE_FIELD(schemaElts); COPY_SCALAR_FIELD(if_not_exists); @@ -3854,7 +3857,7 @@ _copyReassignOwnedStmt(const ReassignOwnedStmt *from) ReassignOwnedStmt *newnode = makeNode(ReassignOwnedStmt); COPY_NODE_FIELD(roles); - COPY_STRING_FIELD(newrole); + COPY_NODE_FIELD(newrole); return newnode; } @@ -4728,9 +4731,6 @@ copyObject(const void *from) case T_CommonTableExpr: retval = _copyCommonTableExpr(from); break; - case T_PrivGrantee: - retval = _copyPrivGrantee(from); - break; case T_FuncWithArgs: retval = _copyFuncWithArgs(from); break; @@ -4740,6 +4740,9 @@ copyObject(const void *from) case T_XmlSerialize: retval = _copyXmlSerialize(from); break; + case T_RoleSpec: + retval = _copyRoleSpec(from); + break; default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(from)); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index fe509b0ad1..8186e84d33 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -973,6 +973,7 @@ _equalAlterTableCmd(const AlterTableCmd *a, const AlterTableCmd *b) { COMPARE_SCALAR_FIELD(subtype); COMPARE_STRING_FIELD(name); + COMPARE_NODE_FIELD(newowner); COMPARE_NODE_FIELD(def); COMPARE_SCALAR_FIELD(behavior); COMPARE_SCALAR_FIELD(missing_ok); @@ -1008,14 +1009,6 @@ _equalGrantStmt(const GrantStmt *a, const GrantStmt *b) return true; } -static bool -_equalPrivGrantee(const PrivGrantee *a, const PrivGrantee *b) -{ - COMPARE_STRING_FIELD(rolname); - - return true; -} - static bool _equalFuncWithArgs(const FuncWithArgs *a, const FuncWithArgs *b) { @@ -1041,7 +1034,7 @@ _equalGrantRoleStmt(const GrantRoleStmt *a, const GrantRoleStmt *b) COMPARE_NODE_FIELD(grantee_roles); COMPARE_SCALAR_FIELD(is_grant); COMPARE_SCALAR_FIELD(admin_opt); - COMPARE_STRING_FIELD(grantor); + COMPARE_NODE_FIELD(grantor); COMPARE_SCALAR_FIELD(behavior); return true; @@ -1295,7 +1288,7 @@ _equalAlterOwnerStmt(const AlterOwnerStmt *a, const AlterOwnerStmt *b) COMPARE_NODE_FIELD(relation); COMPARE_NODE_FIELD(object); COMPARE_NODE_FIELD(objarg); - COMPARE_STRING_FIELD(newowner); + COMPARE_NODE_FIELD(newowner); return true; } @@ -1618,7 +1611,7 @@ static bool _equalCreateTableSpaceStmt(const CreateTableSpaceStmt *a, const CreateTableSpaceStmt *b) { COMPARE_STRING_FIELD(tablespacename); - COMPARE_STRING_FIELD(owner); + COMPARE_NODE_FIELD(owner); COMPARE_STRING_FIELD(location); COMPARE_NODE_FIELD(options); @@ -1735,7 +1728,7 @@ _equalAlterForeignServerStmt(const AlterForeignServerStmt *a, const AlterForeign static bool _equalCreateUserMappingStmt(const CreateUserMappingStmt *a, const CreateUserMappingStmt *b) { - COMPARE_STRING_FIELD(username); + COMPARE_NODE_FIELD(user); COMPARE_STRING_FIELD(servername); COMPARE_NODE_FIELD(options); @@ -1745,7 +1738,7 @@ _equalCreateUserMappingStmt(const CreateUserMappingStmt *a, const CreateUserMapp static bool _equalAlterUserMappingStmt(const AlterUserMappingStmt *a, const AlterUserMappingStmt *b) { - COMPARE_STRING_FIELD(username); + COMPARE_NODE_FIELD(user); COMPARE_STRING_FIELD(servername); COMPARE_NODE_FIELD(options); @@ -1755,7 +1748,7 @@ _equalAlterUserMappingStmt(const AlterUserMappingStmt *a, const AlterUserMapping static bool _equalDropUserMappingStmt(const DropUserMappingStmt *a, const DropUserMappingStmt *b) { - COMPARE_STRING_FIELD(username); + COMPARE_NODE_FIELD(user); COMPARE_STRING_FIELD(servername); COMPARE_SCALAR_FIELD(missing_ok); @@ -1853,7 +1846,7 @@ _equalCreateRoleStmt(const CreateRoleStmt *a, const CreateRoleStmt *b) static bool _equalAlterRoleStmt(const AlterRoleStmt *a, const AlterRoleStmt *b) { - COMPARE_STRING_FIELD(role); + COMPARE_NODE_FIELD(role); COMPARE_NODE_FIELD(options); COMPARE_SCALAR_FIELD(action); @@ -1863,7 +1856,7 @@ _equalAlterRoleStmt(const AlterRoleStmt *a, const AlterRoleStmt *b) static bool _equalAlterRoleSetStmt(const AlterRoleSetStmt *a, const AlterRoleSetStmt *b) { - COMPARE_STRING_FIELD(role); + COMPARE_NODE_FIELD(role); COMPARE_STRING_FIELD(database); COMPARE_NODE_FIELD(setstmt); @@ -1912,7 +1905,7 @@ static bool _equalCreateSchemaStmt(const CreateSchemaStmt *a, const CreateSchemaStmt *b) { COMPARE_STRING_FIELD(schemaname); - COMPARE_STRING_FIELD(authid); + COMPARE_NODE_FIELD(authrole); COMPARE_NODE_FIELD(schemaElts); COMPARE_SCALAR_FIELD(if_not_exists); @@ -1983,7 +1976,7 @@ static bool _equalReassignOwnedStmt(const ReassignOwnedStmt *a, const ReassignOwnedStmt *b) { COMPARE_NODE_FIELD(roles); - COMPARE_STRING_FIELD(newrole); + COMPARE_NODE_FIELD(newrole); return true; } @@ -2455,6 +2448,16 @@ _equalXmlSerialize(const XmlSerialize *a, const XmlSerialize *b) return true; } +static bool +_equalRoleSpec(const RoleSpec *a, const RoleSpec *b) +{ + COMPARE_SCALAR_FIELD(roletype); + COMPARE_STRING_FIELD(rolename); + COMPARE_LOCATION_FIELD(location); + + return true; +} + /* * Stuff from pg_list.h */ @@ -3153,9 +3156,6 @@ equal(const void *a, const void *b) case T_CommonTableExpr: retval = _equalCommonTableExpr(a, b); break; - case T_PrivGrantee: - retval = _equalPrivGrantee(a, b); - break; case T_FuncWithArgs: retval = _equalFuncWithArgs(a, b); break; @@ -3165,6 +3165,9 @@ equal(const void *a, const void *b) case T_XmlSerialize: retval = _equalXmlSerialize(a, b); break; + case T_RoleSpec: + retval = _equalRoleSpec(a, b); + break; default: elog(ERROR, "unrecognized node type: %d", diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 581f7a1c1c..435c0451ca 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -143,6 +143,7 @@ static Node *makeBitStringConst(char *str, int location); static Node *makeNullAConst(int location); static Node *makeAConst(Value *v, int location); static Node *makeBoolAConst(bool state, int location); +static Node *makeRoleSpec(RoleSpecType type, int location); static void check_qualified_name(List *names, core_yyscan_t yyscanner); static List *check_func_name(List *names, core_yyscan_t yyscanner); static List *check_indirection(List *indirection, core_yyscan_t yyscanner); @@ -291,7 +292,6 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type opt_type %type foreign_server_version opt_foreign_server_version -%type auth_ident %type opt_in_database %type OptSchemaName @@ -474,12 +474,13 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type Iconst SignedIconst %type Sconst comment_text notify_payload -%type RoleId opt_granted_by opt_boolean_or_string +%type RoleId opt_boolean_or_string %type var_list %type ColId ColLabel var_name type_function_name param_name %type NonReservedWord NonReservedWord_or_Sconst %type createdb_opt_name %type var_value zone_value +%type auth_ident RoleSpec opt_granted_by %type unreserved_keyword type_func_name_keyword %type col_name_keyword reserved_keyword @@ -494,7 +495,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type constraints_set_list %type constraints_set_mode -%type OptTableSpace OptConsTableSpace OptTableSpaceOwner +%type OptTableSpace OptConsTableSpace +%type OptTableSpaceOwner %type opt_check_option %type opt_provider security_label @@ -1037,7 +1039,7 @@ CreateUserStmt: *****************************************************************************/ AlterRoleStmt: - ALTER ROLE RoleId opt_with AlterOptRoleList + ALTER ROLE RoleSpec opt_with AlterOptRoleList { AlterRoleStmt *n = makeNode(AlterRoleStmt); n->role = $3; @@ -1053,7 +1055,7 @@ opt_in_database: ; AlterRoleSetStmt: - ALTER ROLE RoleId opt_in_database SetResetClause + ALTER ROLE RoleSpec opt_in_database SetResetClause { AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt); n->role = $3; @@ -1079,7 +1081,7 @@ AlterRoleSetStmt: *****************************************************************************/ AlterUserStmt: - ALTER USER RoleId opt_with AlterOptRoleList + ALTER USER RoleSpec opt_with AlterOptRoleList { AlterRoleStmt *n = makeNode(AlterRoleStmt); n->role = $3; @@ -1091,7 +1093,7 @@ AlterUserStmt: AlterUserSetStmt: - ALTER USER RoleId SetResetClause + ALTER USER RoleSpec SetResetClause { AlterRoleSetStmt *n = makeNode(AlterRoleSetStmt); n->role = $3; @@ -1180,7 +1182,7 @@ CreateGroupStmt: *****************************************************************************/ AlterGroupStmt: - ALTER GROUP_P RoleId add_drop USER role_list + ALTER GROUP_P RoleSpec add_drop USER role_list { AlterRoleStmt *n = makeNode(AlterRoleStmt); n->role = $3; @@ -1228,15 +1230,12 @@ DropGroupStmt: *****************************************************************************/ CreateSchemaStmt: - CREATE SCHEMA OptSchemaName AUTHORIZATION RoleId OptSchemaEltList + CREATE SCHEMA OptSchemaName AUTHORIZATION RoleSpec OptSchemaEltList { CreateSchemaStmt *n = makeNode(CreateSchemaStmt); /* One can omit the schema name or the authorization id. */ - if ($3 != NULL) - n->schemaname = $3; - else - n->schemaname = $5; - n->authid = $5; + n->schemaname = $3; + n->authrole = $5; n->schemaElts = $6; n->if_not_exists = false; $$ = (Node *)n; @@ -1246,20 +1245,17 @@ CreateSchemaStmt: CreateSchemaStmt *n = makeNode(CreateSchemaStmt); /* ...but not both */ n->schemaname = $3; - n->authid = NULL; + n->authrole = NULL; n->schemaElts = $4; n->if_not_exists = false; $$ = (Node *)n; } - | CREATE SCHEMA IF_P NOT EXISTS OptSchemaName AUTHORIZATION RoleId OptSchemaEltList + | CREATE SCHEMA IF_P NOT EXISTS OptSchemaName AUTHORIZATION RoleSpec OptSchemaEltList { CreateSchemaStmt *n = makeNode(CreateSchemaStmt); - /* One can omit the schema name or the authorization id. */ - if ($6 != NULL) - n->schemaname = $6; - else - n->schemaname = $8; - n->authid = $8; + /* schema name can be omitted here, too */ + n->schemaname = $6; + n->authrole = $8; if ($9 != NIL) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), @@ -1272,9 +1268,9 @@ CreateSchemaStmt: | CREATE SCHEMA IF_P NOT EXISTS ColId OptSchemaEltList { CreateSchemaStmt *n = makeNode(CreateSchemaStmt); - /* ...but not both */ + /* ...but not here */ n->schemaname = $6; - n->authid = NULL; + n->authrole = NULL; if ($7 != NIL) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), @@ -2259,12 +2255,12 @@ alter_table_cmd: n->subtype = AT_DropOf; $$ = (Node *)n; } - /* ALTER TABLE OWNER TO RoleId */ - | OWNER TO RoleId + /* ALTER TABLE OWNER TO RoleSpec */ + | OWNER TO RoleSpec { AlterTableCmd *n = makeNode(AlterTableCmd); n->subtype = AT_ChangeOwner; - n->name = $3; + n->newowner = $3; $$ = (Node *)n; } /* ALTER TABLE SET TABLESPACE */ @@ -3756,7 +3752,7 @@ CreateTableSpaceStmt: CREATE TABLESPACE name OptTableSpaceOwner LOCATION Sconst } ; -OptTableSpaceOwner: OWNER name { $$ = $2; } +OptTableSpaceOwner: OWNER RoleSpec { $$ = $2; } | /*EMPTY */ { $$ = NULL; } ; @@ -4478,7 +4474,7 @@ import_qualification: CreateUserMappingStmt: CREATE USER MAPPING FOR auth_ident SERVER name create_generic_options { CreateUserMappingStmt *n = makeNode(CreateUserMappingStmt); - n->username = $5; + n->user = $5; n->servername = $7; n->options = $8; $$ = (Node *) n; @@ -4486,10 +4482,8 @@ CreateUserMappingStmt: CREATE USER MAPPING FOR auth_ident SERVER name create_gen ; /* User mapping authorization identifier */ -auth_ident: - CURRENT_USER { $$ = "current_user"; } - | USER { $$ = "current_user"; } - | RoleId { $$ = (strcmp($1, "public") == 0) ? NULL : $1; } +auth_ident: RoleSpec { $$ = $1; } + | USER { $$ = makeRoleSpec(ROLESPEC_CURRENT_USER, @1); } ; /***************************************************************************** @@ -4502,7 +4496,7 @@ auth_ident: DropUserMappingStmt: DROP USER MAPPING FOR auth_ident SERVER name { DropUserMappingStmt *n = makeNode(DropUserMappingStmt); - n->username = $5; + n->user = $5; n->servername = $7; n->missing_ok = false; $$ = (Node *) n; @@ -4510,7 +4504,7 @@ DropUserMappingStmt: DROP USER MAPPING FOR auth_ident SERVER name | DROP USER MAPPING IF_P EXISTS FOR auth_ident SERVER name { DropUserMappingStmt *n = makeNode(DropUserMappingStmt); - n->username = $7; + n->user = $7; n->servername = $9; n->missing_ok = true; $$ = (Node *) n; @@ -4527,7 +4521,7 @@ DropUserMappingStmt: DROP USER MAPPING FOR auth_ident SERVER name AlterUserMappingStmt: ALTER USER MAPPING FOR auth_ident SERVER name alter_generic_options { AlterUserMappingStmt *n = makeNode(AlterUserMappingStmt); - n->username = $5; + n->user = $5; n->servername = $7; n->options = $8; $$ = (Node *) n; @@ -4612,7 +4606,7 @@ RowSecurityOptionalWithCheck: RowSecurityDefaultToRole: TO role_list { $$ = $2; } - | /* EMPTY */ { $$ = list_make1(makeString("public")); } + | /* EMPTY */ { $$ = list_make1(makeRoleSpec(ROLESPEC_PUBLIC, -1)); } ; RowSecurityOptionalToRole: @@ -5432,7 +5426,7 @@ DropOwnedStmt: ; ReassignOwnedStmt: - REASSIGN OWNED BY role_list TO name + REASSIGN OWNED BY role_list TO RoleSpec { ReassignOwnedStmt *n = makeNode(ReassignOwnedStmt); n->roles = $4; @@ -6348,26 +6342,9 @@ grantee_list: | grantee_list ',' grantee { $$ = lappend($1, $3); } ; -grantee: RoleId - { - PrivGrantee *n = makeNode(PrivGrantee); - /* This hack lets us avoid reserving PUBLIC as a keyword*/ - if (strcmp($1, "public") == 0) - n->rolname = NULL; - else - n->rolname = $1; - $$ = (Node *)n; - } - | GROUP_P RoleId - { - PrivGrantee *n = makeNode(PrivGrantee); - /* Treat GROUP PUBLIC as a synonym for PUBLIC */ - if (strcmp($2, "public") == 0) - n->rolname = NULL; - else - n->rolname = $2; - $$ = (Node *)n; - } +grantee: + RoleSpec { $$ = $1; } + | GROUP_P RoleSpec { $$ = $2; } ; @@ -6438,7 +6415,7 @@ opt_grant_admin_option: WITH ADMIN OPTION { $$ = TRUE; } | /*EMPTY*/ { $$ = FALSE; } ; -opt_granted_by: GRANTED BY RoleId { $$ = $3; } +opt_granted_by: GRANTED BY RoleSpec { $$ = $3; } | /*EMPTY*/ { $$ = NULL; } ; @@ -8104,7 +8081,7 @@ AlterObjectSchemaStmt: * *****************************************************************************/ -AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId +AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_AGGREGATE; @@ -8113,7 +8090,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $7; $$ = (Node *)n; } - | ALTER COLLATION any_name OWNER TO RoleId + | ALTER COLLATION any_name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_COLLATION; @@ -8121,7 +8098,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $6; $$ = (Node *)n; } - | ALTER CONVERSION_P any_name OWNER TO RoleId + | ALTER CONVERSION_P any_name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_CONVERSION; @@ -8129,7 +8106,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $6; $$ = (Node *)n; } - | ALTER DATABASE database_name OWNER TO RoleId + | ALTER DATABASE database_name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_DATABASE; @@ -8137,7 +8114,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $6; $$ = (Node *)n; } - | ALTER DOMAIN_P any_name OWNER TO RoleId + | ALTER DOMAIN_P any_name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_DOMAIN; @@ -8145,7 +8122,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $6; $$ = (Node *)n; } - | ALTER FUNCTION function_with_argtypes OWNER TO RoleId + | ALTER FUNCTION function_with_argtypes OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_FUNCTION; @@ -8154,7 +8131,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $6; $$ = (Node *)n; } - | ALTER opt_procedural LANGUAGE name OWNER TO RoleId + | ALTER opt_procedural LANGUAGE name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_LANGUAGE; @@ -8162,7 +8139,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $7; $$ = (Node *)n; } - | ALTER LARGE_P OBJECT_P NumericOnly OWNER TO RoleId + | ALTER LARGE_P OBJECT_P NumericOnly OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_LARGEOBJECT; @@ -8170,7 +8147,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $7; $$ = (Node *)n; } - | ALTER OPERATOR any_operator oper_argtypes OWNER TO RoleId + | ALTER OPERATOR any_operator oper_argtypes OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_OPERATOR; @@ -8179,7 +8156,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $7; $$ = (Node *)n; } - | ALTER OPERATOR CLASS any_name USING access_method OWNER TO RoleId + | ALTER OPERATOR CLASS any_name USING access_method OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_OPCLASS; @@ -8188,7 +8165,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $9; $$ = (Node *)n; } - | ALTER OPERATOR FAMILY any_name USING access_method OWNER TO RoleId + | ALTER OPERATOR FAMILY any_name USING access_method OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_OPFAMILY; @@ -8197,7 +8174,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $9; $$ = (Node *)n; } - | ALTER SCHEMA name OWNER TO RoleId + | ALTER SCHEMA name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_SCHEMA; @@ -8205,7 +8182,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $6; $$ = (Node *)n; } - | ALTER TYPE_P any_name OWNER TO RoleId + | ALTER TYPE_P any_name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_TYPE; @@ -8213,7 +8190,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $6; $$ = (Node *)n; } - | ALTER TABLESPACE name OWNER TO RoleId + | ALTER TABLESPACE name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_TABLESPACE; @@ -8221,7 +8198,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $6; $$ = (Node *)n; } - | ALTER TEXT_P SEARCH DICTIONARY any_name OWNER TO RoleId + | ALTER TEXT_P SEARCH DICTIONARY any_name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_TSDICTIONARY; @@ -8229,7 +8206,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $8; $$ = (Node *)n; } - | ALTER TEXT_P SEARCH CONFIGURATION any_name OWNER TO RoleId + | ALTER TEXT_P SEARCH CONFIGURATION any_name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_TSCONFIGURATION; @@ -8237,7 +8214,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $8; $$ = (Node *)n; } - | ALTER FOREIGN DATA_P WRAPPER name OWNER TO RoleId + | ALTER FOREIGN DATA_P WRAPPER name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_FDW; @@ -8245,7 +8222,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $8; $$ = (Node *)n; } - | ALTER SERVER name OWNER TO RoleId + | ALTER SERVER name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_FOREIGN_SERVER; @@ -8253,7 +8230,7 @@ AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId n->newowner = $6; $$ = (Node *)n; } - | ALTER EVENT TRIGGER name OWNER TO RoleId + | ALTER EVENT TRIGGER name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); n->objectType = OBJECT_EVENT_TRIGGER; @@ -13113,19 +13090,86 @@ AexprConst: Iconst Iconst: ICONST { $$ = $1; }; Sconst: SCONST { $$ = $1; }; -RoleId: NonReservedWord { $$ = $1; }; - -role_list: RoleId - { $$ = list_make1(makeString($1)); } - | role_list ',' RoleId - { $$ = lappend($1, makeString($3)); } - ; SignedIconst: Iconst { $$ = $1; } | '+' Iconst { $$ = + $2; } | '-' Iconst { $$ = - $2; } ; +/* Role specifications */ +RoleId: RoleSpec + { + RoleSpec *spc = (RoleSpec *) $1; + switch (spc->roletype) + { + case ROLESPEC_CSTRING: + $$ = spc->rolename; + break; + case ROLESPEC_PUBLIC: + ereport(ERROR, + (errcode(ERRCODE_RESERVED_NAME), + errmsg("role name \"%s\" is reserved", + "public"), + parser_errposition(@1))); + case ROLESPEC_SESSION_USER: + ereport(ERROR, + (errcode(ERRCODE_RESERVED_NAME), + errmsg("%s cannot be used as a role name", + "SESSION_USER"), + parser_errposition(@1))); + case ROLESPEC_CURRENT_USER: + ereport(ERROR, + (errcode(ERRCODE_RESERVED_NAME), + errmsg("%s cannot be used as a role name", + "CURRENT_USER"), + parser_errposition(@1))); + } + } + ; + +RoleSpec: NonReservedWord + { + /* + * "public" and "none" are not keywords, but they must + * be treated specially here. + */ + RoleSpec *n; + if (strcmp($1, "public") == 0) + { + n = (RoleSpec *) makeRoleSpec(ROLESPEC_PUBLIC, @1); + n->roletype = ROLESPEC_PUBLIC; + } + else if (strcmp($1, "none") == 0) + { + ereport(ERROR, + (errcode(ERRCODE_RESERVED_NAME), + errmsg("role name \"%s\" is reserved", + "none"), + parser_errposition(@1))); + } + else + { + n = (RoleSpec *) makeRoleSpec(ROLESPEC_CSTRING, @1); + n->rolename = pstrdup($1); + } + $$ = (Node *) n; + } + | CURRENT_USER + { + $$ = makeRoleSpec(ROLESPEC_CURRENT_USER, @1); + } + | SESSION_USER + { + $$ = makeRoleSpec(ROLESPEC_SESSION_USER, @1); + } + ; + +role_list: RoleSpec + { $$ = list_make1($1); } + | role_list ',' RoleSpec + { $$ = lappend($1, $3); } + ; + /* * Name classification hierarchy. * @@ -13812,6 +13856,20 @@ makeBoolAConst(bool state, int location) return makeTypeCast((Node *)n, SystemTypeName("bool"), -1); } +/* makeRoleSpec + * Create a RoleSpec with the given type + */ +static Node * +makeRoleSpec(RoleSpecType type, int location) +{ + RoleSpec *spec = makeNode(RoleSpec); + + spec->roletype = type; + spec->location = location; + + return (Node *) spec; +} + /* check_qualified_name --- check the result of qualified_name production * * It's easiest to let the grammar production for qualified_name allow diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index c29f106529..1e6da9cc40 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -90,7 +90,7 @@ typedef struct { const char *stmtType; /* "CREATE SCHEMA" or "ALTER SCHEMA" */ char *schemaname; /* name of schema */ - char *authid; /* owner of schema */ + RoleSpec *authrole; /* owner of schema */ List *sequences; /* CREATE SEQUENCE items */ List *tables; /* CREATE TABLE items */ List *views; /* CREATE VIEW items */ @@ -2723,7 +2723,7 @@ transformCreateSchemaStmt(CreateSchemaStmt *stmt) cxt.stmtType = "CREATE SCHEMA"; cxt.schemaname = stmt->schemaname; - cxt.authid = stmt->authid; + cxt.authrole = (RoleSpec *) stmt->authrole; cxt.sequences = NIL; cxt.tables = NIL; cxt.views = NIL; diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c index 411d779be6..7701fc5ac0 100644 --- a/src/backend/utils/adt/acl.c +++ b/src/backend/utils/adt/acl.c @@ -5105,7 +5105,7 @@ select_best_grantor(Oid roleId, AclMode privileges, /* * get_role_oid - Given a role name, look up the role's OID. * - * If missing_ok is false, throw an error if tablespace name not found. If + * If missing_ok is false, throw an error if role name not found. If * true, just return InvalidOid. */ Oid @@ -5133,3 +5133,117 @@ get_role_oid_or_public(const char *rolname) return get_role_oid(rolname, false); } + +/* + * Given a RoleSpec node, return the OID it corresponds to. If missing_ok is + * true, return InvalidOid if the role does not exist. + * + * PUBLIC is always disallowed here. Routines wanting to handle the PUBLIC + * case must check the case separately. + */ +Oid +get_rolespec_oid(const Node *node, bool missing_ok) +{ + RoleSpec *role; + Oid oid; + + if (!IsA(node, RoleSpec)) + elog(ERROR, "invalid node type %d", node->type); + + role = (RoleSpec *) node; + switch (role->roletype) + { + case ROLESPEC_CSTRING: + Assert(role->rolename); + oid = get_role_oid(role->rolename, missing_ok); + break; + + case ROLESPEC_CURRENT_USER: + oid = GetUserId(); + break; + + case ROLESPEC_SESSION_USER: + oid = GetSessionUserId(); + break; + + case ROLESPEC_PUBLIC: + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("role \"%s\" does not exist", "public"))); + oid = InvalidOid; /* make compiler happy */ + break; + + default: + elog(ERROR, "unexpected role type %d", role->roletype); + } + + return oid; +} + +/* + * Given a RoleSpec node, return the pg_authid HeapTuple it corresponds to. + * Caller must ReleaseSysCache when done with the result tuple. + */ +HeapTuple +get_rolespec_tuple(const Node *node) +{ + RoleSpec *role; + HeapTuple tuple; + + role = (RoleSpec *) node; + if (!IsA(node, RoleSpec)) + elog(ERROR, "invalid node type %d", node->type); + + switch (role->roletype) + { + case ROLESPEC_CSTRING: + Assert(role->rolename); + tuple = SearchSysCache1(AUTHNAME, CStringGetDatum(role->rolename)); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("role \"%s\" does not exist", role->rolename))); + break; + + case ROLESPEC_CURRENT_USER: + tuple = SearchSysCache1(AUTHOID, GetUserId()); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for role %u", GetUserId()); + break; + + case ROLESPEC_SESSION_USER: + tuple = SearchSysCache1(AUTHOID, GetSessionUserId()); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for role %u", GetSessionUserId()); + break; + + case ROLESPEC_PUBLIC: + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("role \"%s\" does not exist", "public"))); + tuple = NULL; /* make compiler happy */ + + default: + elog(ERROR, "unexpected role type %d", role->roletype); + } + + return tuple; +} + +/* + * Given a RoleSpec, returns a palloc'ed copy of the corresponding role's name. + */ +char * +get_rolespec_name(const Node *node) +{ + HeapTuple tp; + Form_pg_authid authForm; + char *rolename; + + tp = get_rolespec_tuple(node); + authForm = (Form_pg_authid) GETSTRUCT(tp); + rolename = pstrdup(NameStr(authForm->rolname)); + ReleaseSysCache(tp); + + return rolename; +} diff --git a/src/include/commands/user.h b/src/include/commands/user.h index ccadb04b8c..d35cb0c90d 100644 --- a/src/include/commands/user.h +++ b/src/include/commands/user.h @@ -31,6 +31,6 @@ extern void GrantRole(GrantRoleStmt *stmt); extern ObjectAddress RenameRole(const char *oldname, const char *newname); extern void DropOwnedObjects(DropOwnedStmt *stmt); extern void ReassignOwnedObjects(ReassignOwnedStmt *stmt); -extern List *roleNamesToIds(List *memberNames); +extern List *roleSpecsToIds(List *memberNames); #endif /* USER_H */ diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 97ef0fcf7d..38469ef4d1 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -413,6 +413,7 @@ typedef enum NodeTag T_XmlSerialize, T_WithClause, T_CommonTableExpr, + T_RoleSpec, /* * TAGS FOR REPLICATION GRAMMAR PARSE NODES (replnodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index ac133023b4..497559df58 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -284,6 +284,25 @@ typedef struct CollateClause int location; /* token location, or -1 if unknown */ } CollateClause; +/* + * RoleSpec - a role name or one of a few special values. + */ +typedef enum RoleSpecType +{ + ROLESPEC_CSTRING, /* role name is stored as a C string */ + ROLESPEC_CURRENT_USER, /* role spec is CURRENT_USER */ + ROLESPEC_SESSION_USER, /* role spec is SESSION_USER */ + ROLESPEC_PUBLIC /* role name is "public" */ +} RoleSpecType; + +typedef struct RoleSpec +{ + NodeTag type; + RoleSpecType roletype; /* Type of this rolespec */ + char *rolename; /* filled only for ROLESPEC_CSTRING */ + int location; /* token location, or -1 if unknown */ +} RoleSpec; + /* * FuncCall - a function or aggregate invocation * @@ -1263,7 +1282,7 @@ typedef struct CreateSchemaStmt { NodeTag type; char *schemaname; /* the name of the schema to create */ - char *authid; /* the owner of the created schema */ + Node *authrole; /* the owner of the created schema */ List *schemaElts; /* schema components (list of parsenodes) */ bool if_not_exists; /* just do nothing if schema already exists? */ } CreateSchemaStmt; @@ -1362,7 +1381,8 @@ typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */ NodeTag type; AlterTableType subtype; /* Type of table alteration to apply */ char *name; /* column, constraint, or trigger to act on, - * or new owner or tablespace */ + * or tablespace */ + Node *newowner; /* RoleSpec */ Node *def; /* definition of new column, index, * constraint, or parent table */ DropBehavior behavior; /* RESTRICT or CASCADE for DROP cases */ @@ -1434,17 +1454,11 @@ typedef struct GrantStmt * or plain names (as Value strings) */ List *privileges; /* list of AccessPriv nodes */ /* privileges == NIL denotes ALL PRIVILEGES */ - List *grantees; /* list of PrivGrantee nodes */ + List *grantees; /* list of RoleSpec nodes */ bool grant_option; /* grant or revoke grant option */ DropBehavior behavior; /* drop behavior (for REVOKE) */ } GrantStmt; -typedef struct PrivGrantee -{ - NodeTag type; - char *rolname; /* if NULL then PUBLIC */ -} PrivGrantee; - /* * Note: FuncWithArgs carries only the types of the input parameters of the * function. So it is sufficient to identify an existing function, but it @@ -1487,7 +1501,7 @@ typedef struct GrantRoleStmt List *grantee_roles; /* list of member roles to add/delete */ bool is_grant; /* true = GRANT, false = REVOKE */ bool admin_opt; /* with admin option */ - char *grantor; /* set grantor to other than current role */ + Node *grantor; /* set grantor to other than current role */ DropBehavior behavior; /* drop behavior (for REVOKE) */ } GrantRoleStmt; @@ -1699,7 +1713,7 @@ typedef struct CreateTableSpaceStmt { NodeTag type; char *tablespacename; - char *owner; + Node *owner; char *location; List *options; } CreateTableSpaceStmt; @@ -1825,7 +1839,7 @@ typedef struct CreateForeignTableStmt typedef struct CreateUserMappingStmt { NodeTag type; - char *username; /* username or PUBLIC/CURRENT_USER */ + Node *user; /* user role */ char *servername; /* server name */ List *options; /* generic options to server */ } CreateUserMappingStmt; @@ -1833,7 +1847,7 @@ typedef struct CreateUserMappingStmt typedef struct AlterUserMappingStmt { NodeTag type; - char *username; /* username or PUBLIC/CURRENT_USER */ + Node *user; /* user role */ char *servername; /* server name */ List *options; /* generic options to server */ } AlterUserMappingStmt; @@ -1841,7 +1855,7 @@ typedef struct AlterUserMappingStmt typedef struct DropUserMappingStmt { NodeTag type; - char *username; /* username or PUBLIC/CURRENT_USER */ + Node *user; /* user role */ char *servername; /* server name */ bool missing_ok; /* ignore missing mappings */ } DropUserMappingStmt; @@ -1991,7 +2005,7 @@ typedef struct CreateRoleStmt typedef struct AlterRoleStmt { NodeTag type; - char *role; /* role name */ + Node *role; /* role */ List *options; /* List of DefElem nodes */ int action; /* +1 = add members, -1 = drop members */ } AlterRoleStmt; @@ -1999,7 +2013,7 @@ typedef struct AlterRoleStmt typedef struct AlterRoleSetStmt { NodeTag type; - char *role; /* role name */ + Node *role; /* role */ char *database; /* database name, or NULL */ VariableSetStmt *setstmt; /* SET or RESET subcommand */ } AlterRoleSetStmt; @@ -2375,7 +2389,7 @@ typedef struct AlterOwnerStmt RangeVar *relation; /* in case it's a table */ List *object; /* in case it's some other object */ List *objarg; /* argument types, if applicable */ - char *newowner; /* the new owner */ + Node *newowner; /* the new owner */ } AlterOwnerStmt; @@ -2831,7 +2845,7 @@ typedef struct ReassignOwnedStmt { NodeTag type; List *roles; - char *newrole; + Node *newrole; } ReassignOwnedStmt; /* diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h index ab0df6c7d8..d747579e7a 100644 --- a/src/include/utils/acl.h +++ b/src/include/utils/acl.h @@ -24,6 +24,7 @@ #ifndef ACL_H #define ACL_H +#include "access/htup.h" #include "nodes/parsenodes.h" #include "utils/array.h" #include "utils/snapshot.h" @@ -227,8 +228,11 @@ extern bool is_member_of_role(Oid member, Oid role); extern bool is_member_of_role_nosuper(Oid member, Oid role); extern bool is_admin_of_role(Oid member, Oid role); extern void check_is_member_of_role(Oid member, Oid role); -extern Oid get_role_oid(const char *rolname, bool missing_ok); -extern Oid get_role_oid_or_public(const char *rolname); +extern Oid get_role_oid(const char *rolename, bool missing_ok); +extern Oid get_role_oid_or_public(const char *rolename); +extern Oid get_rolespec_oid(const Node *node, bool missing_ok); +extern HeapTuple get_rolespec_tuple(const Node *node); +extern char *get_rolespec_name(const Node *node); extern void select_best_grantor(Oid roleId, AclMode privileges, const Acl *acl, Oid ownerId, diff --git a/src/test/regress/expected/rolenames.out b/src/test/regress/expected/rolenames.out new file mode 100644 index 0000000000..1879337ea0 --- /dev/null +++ b/src/test/regress/expected/rolenames.out @@ -0,0 +1,940 @@ +CREATE OR REPLACE FUNCTION chkrolattr() + RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool) + AS $$ +SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication + FROM pg_roles r + JOIN (VALUES(CURRENT_USER, 'current_user'), + (SESSION_USER, 'session_user'), + ('current_user', '-'), + ('session_user', '-'), + ('Public', '-'), + ('None', '-')) + AS v(uname, keyword) + ON (r.rolname = v.uname) + ORDER BY 1; +$$ LANGUAGE SQL; +CREATE OR REPLACE FUNCTION chksetconfig() + RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[]) + AS $$ +SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'), + COALESCE(v.keyword, '-'), s.setconfig + FROM pg_db_role_setting s + LEFT JOIN pg_roles r ON (r.oid = s.setrole) + LEFT JOIN pg_database d ON (d.oid = s.setdatabase) + LEFT JOIN (VALUES(CURRENT_USER, 'current_user'), + (SESSION_USER, 'session_user')) + AS v(uname, keyword) + ON (r.rolname = v.uname) + WHERE (r.rolname) IN ('Public', 'current_user', 'testrol1', 'testrol2') +ORDER BY 1, 2; +$$ LANGUAGE SQL; +CREATE OR REPLACE FUNCTION chkumapping() + RETURNS TABLE (umname name, umserver name, umoptions text[]) + AS $$ +SELECT r.rolname, s.srvname, m.umoptions + FROM pg_user_mapping m + LEFT JOIN pg_roles r ON (r.oid = m.umuser) + JOIN pg_foreign_server s ON (s.oid = m.umserver) + ORDER BY 2; +$$ LANGUAGE SQL; +CREATE ROLE "Public"; +CREATE ROLE "None"; +CREATE ROLE "current_user"; +CREATE ROLE "session_user"; +CREATE ROLE "user"; +CREATE ROLE current_user; -- error +ERROR: CURRENT_USER cannot be used as a role name +LINE 1: CREATE ROLE current_user; + ^ +CREATE ROLE current_role; -- error +ERROR: syntax error at or near "current_role" +LINE 1: CREATE ROLE current_role; + ^ +CREATE ROLE session_user; -- error +ERROR: SESSION_USER cannot be used as a role name +LINE 1: CREATE ROLE session_user; + ^ +CREATE ROLE user; -- error +ERROR: syntax error at or near "user" +LINE 1: CREATE ROLE user; + ^ +CREATE ROLE all; -- error +ERROR: syntax error at or near "all" +LINE 1: CREATE ROLE all; + ^ +CREATE ROLE public; -- error +ERROR: role name "public" is reserved +LINE 1: CREATE ROLE public; + ^ +CREATE ROLE "public"; -- error +ERROR: role name "public" is reserved +LINE 1: CREATE ROLE "public"; + ^ +CREATE ROLE none; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE ROLE none; + ^ +CREATE ROLE "none"; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE ROLE "none"; + ^ +CREATE ROLE testrol0 SUPERUSER LOGIN; +CREATE ROLE testrolx SUPERUSER LOGIN; +CREATE ROLE testrol2 SUPERUSER; +CREATE ROLE testrol1 SUPERUSER LOGIN IN ROLE testrol2; +\c - +SET SESSION AUTHORIZATION testrol1; +SET ROLE testrol2; +-- ALTER ROLE +BEGIN; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | f + session_user | - | f | f + testrol1 | session_user | t | f + testrol2 | current_user | f | f +(6 rows) + +ALTER ROLE CURRENT_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | f + session_user | - | f | f + testrol1 | session_user | t | f + testrol2 | current_user | f | t +(6 rows) + +ALTER ROLE "current_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | t + session_user | - | f | f + testrol1 | session_user | t | f + testrol2 | current_user | f | t +(6 rows) + +ALTER ROLE SESSION_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | t + session_user | - | f | f + testrol1 | session_user | t | t + testrol2 | current_user | f | t +(6 rows) + +ALTER ROLE "session_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | t + session_user | - | f | t + testrol1 | session_user | t | t + testrol2 | current_user | f | t +(6 rows) + +ALTER USER "Public" WITH REPLICATION; +ALTER USER "None" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_user | - | f | t + session_user | - | f | t + testrol1 | session_user | t | t + testrol2 | current_user | f | t +(6 rows) + +ALTER USER testrol1 WITH NOREPLICATION; +ALTER USER testrol2 WITH NOREPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_user | - | f | t + session_user | - | f | t + testrol1 | session_user | t | f + testrol2 | current_user | f | f +(6 rows) + +ROLLBACK; +ALTER ROLE USER WITH LOGIN; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER ROLE USER WITH LOGIN; + ^ +ALTER ROLE CURRENT_ROLE WITH LOGIN; --error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ALTER ROLE CURRENT_ROLE WITH LOGIN; + ^ +ALTER ROLE ALL WITH REPLICATION; -- error +ERROR: syntax error at or near "WITH" +LINE 1: ALTER ROLE ALL WITH REPLICATION; + ^ +ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error +ERROR: role "session_role" does not exist +ALTER ROLE PUBLIC WITH NOREPLICATION; -- error +ERROR: role "public" does not exist +ALTER ROLE "public" WITH NOREPLICATION; -- error +ERROR: role "public" does not exist +ALTER ROLE NONE WITH NOREPLICATION; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER ROLE NONE WITH NOREPLICATION; + ^ +ALTER ROLE "none" WITH NOREPLICATION; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER ROLE "none" WITH NOREPLICATION; + ^ +ALTER ROLE nonexistent WITH NOREPLICATION; -- error +ERROR: role "nonexistent" does not exist +-- ALTER USER +BEGIN; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | f + session_user | - | f | f + testrol1 | session_user | t | f + testrol2 | current_user | f | f +(6 rows) + +ALTER USER CURRENT_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | f + session_user | - | f | f + testrol1 | session_user | t | f + testrol2 | current_user | f | t +(6 rows) + +ALTER USER "current_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | t + session_user | - | f | f + testrol1 | session_user | t | f + testrol2 | current_user | f | t +(6 rows) + +ALTER USER SESSION_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | t + session_user | - | f | f + testrol1 | session_user | t | t + testrol2 | current_user | f | t +(6 rows) + +ALTER USER "session_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | f + Public | - | f | f + current_user | - | f | t + session_user | - | f | t + testrol1 | session_user | t | t + testrol2 | current_user | f | t +(6 rows) + +ALTER USER "Public" WITH REPLICATION; +ALTER USER "None" WITH REPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_user | - | f | t + session_user | - | f | t + testrol1 | session_user | t | t + testrol2 | current_user | f | t +(6 rows) + +ALTER USER testrol1 WITH NOREPLICATION; +ALTER USER testrol2 WITH NOREPLICATION; +SELECT * FROM chkrolattr(); + role | rolekeyword | canlogin | replication +--------------+--------------+----------+------------- + None | - | f | t + Public | - | f | t + current_user | - | f | t + session_user | - | f | t + testrol1 | session_user | t | f + testrol2 | current_user | f | f +(6 rows) + +ROLLBACK; +ALTER USER USER WITH LOGIN; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER USER USER WITH LOGIN; + ^ +ALTER USER CURRENT_ROLE WITH LOGIN; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ALTER USER CURRENT_ROLE WITH LOGIN; + ^ +ALTER USER ALL WITH REPLICATION; -- error +ERROR: syntax error at or near "ALL" +LINE 1: ALTER USER ALL WITH REPLICATION; + ^ +ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error +ERROR: role "session_role" does not exist +ALTER USER PUBLIC WITH NOREPLICATION; -- error +ERROR: role "public" does not exist +ALTER USER "public" WITH NOREPLICATION; -- error +ERROR: role "public" does not exist +ALTER USER NONE WITH NOREPLICATION; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER USER NONE WITH NOREPLICATION; + ^ +ALTER USER "none" WITH NOREPLICATION; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER USER "none" WITH NOREPLICATION; + ^ +ALTER USER nonexistent WITH NOREPLICATION; -- error +ERROR: role "nonexistent" does not exist +-- ALTER ROLE SET/RESET +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +----+------+------------+----------- +(0 rows) + +ALTER ROLE CURRENT_USER SET application_name to 'FOO'; +ALTER ROLE SESSION_USER SET application_name to 'BAR'; +ALTER ROLE "current_user" SET application_name to 'FOOFOO'; +ALTER ROLE "Public" SET application_name to 'BARBAR'; +ALTER ROLE ALL SET application_name to 'SLAP'; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +-----+--------------+--------------+--------------------------- + ALL | Public | - | {application_name=BARBAR} + ALL | current_user | - | {application_name=FOOFOO} + ALL | testrol1 | session_user | {application_name=BAR} + ALL | testrol2 | current_user | {application_name=FOO} +(4 rows) + +ALTER ROLE testrol1 SET application_name to 'SLAM'; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +-----+--------------+--------------+--------------------------- + ALL | Public | - | {application_name=BARBAR} + ALL | current_user | - | {application_name=FOOFOO} + ALL | testrol1 | session_user | {application_name=SLAM} + ALL | testrol2 | current_user | {application_name=FOO} +(4 rows) + +ALTER ROLE CURRENT_USER RESET application_name; +ALTER ROLE SESSION_USER RESET application_name; +ALTER ROLE "current_user" RESET application_name; +ALTER ROLE "Public" RESET application_name; +ALTER ROLE ALL RESET application_name; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +----+------+------------+----------- +(0 rows) + +ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; + ^ +ALTER ROLE USER SET application_name to 'BOOM'; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER ROLE USER SET application_name to 'BOOM'; + ^ +ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error +ERROR: role "public" does not exist +ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error +ERROR: role "nonexistent" does not exist +-- ALTER USER SET/RESET +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +----+------+------------+----------- +(0 rows) + +ALTER USER CURRENT_USER SET application_name to 'FOO'; +ALTER USER SESSION_USER SET application_name to 'BAR'; +ALTER USER "current_user" SET application_name to 'FOOFOO'; +ALTER USER "Public" SET application_name to 'BARBAR'; +ALTER USER ALL SET application_name to 'SLAP'; +ERROR: syntax error at or near "ALL" +LINE 1: ALTER USER ALL SET application_name to 'SLAP'; + ^ +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +-----+--------------+--------------+--------------------------- + ALL | Public | - | {application_name=BARBAR} + ALL | current_user | - | {application_name=FOOFOO} + ALL | testrol1 | session_user | {application_name=BAR} + ALL | testrol2 | current_user | {application_name=FOO} +(4 rows) + +ALTER USER testrol1 SET application_name to 'SLAM'; +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +-----+--------------+--------------+--------------------------- + ALL | Public | - | {application_name=BARBAR} + ALL | current_user | - | {application_name=FOOFOO} + ALL | testrol1 | session_user | {application_name=SLAM} + ALL | testrol2 | current_user | {application_name=FOO} +(4 rows) + +ALTER USER CURRENT_USER RESET application_name; +ALTER USER SESSION_USER RESET application_name; +ALTER USER "current_user" RESET application_name; +ALTER USER "Public" RESET application_name; +ALTER USER ALL RESET application_name; +ERROR: syntax error at or near "ALL" +LINE 1: ALTER USER ALL RESET application_name; + ^ +SELECT * FROM chksetconfig(); + db | role | rolkeyword | setconfig +----+------+------------+----------- +(0 rows) + +ALTER USER CURRENT_USER SET application_name to 'BAZ'; -- error +ALTER USER USER SET application_name to 'BOOM'; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER USER USER SET application_name to 'BOOM'; + ^ +ALTER USER PUBLIC SET application_name to 'BOMB'; -- error +ERROR: role "public" does not exist +ALTER USER NONE SET application_name to 'BOMB'; -- error +ERROR: role name "none" is reserved +LINE 1: ALTER USER NONE SET application_name to 'BOMB'; + ^ +ALTER USER nonexistent SET application_name to 'BOMB'; -- error +ERROR: role "nonexistent" does not exist +-- CREAETE SCHEMA +set client_min_messages to error; +CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER; +CREATE SCHEMA newschema2 AUTHORIZATION "current_user"; +CREATE SCHEMA newschema3 AUTHORIZATION SESSION_USER; +CREATE SCHEMA newschema4 AUTHORIZATION testrolx; +CREATE SCHEMA newschema5 AUTHORIZATION "Public"; +CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error +ERROR: syntax error at or near "USER" +LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION USER; + ^ +CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; + ^ +CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error +ERROR: role "public" does not exist +CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error +ERROR: role "public" does not exist +CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION NONE; + ^ +CREATE SCHEMA newschema6 AUTHORIZATION nonexistent; -- error +ERROR: role "nonexistent" does not exist +SELECT n.nspname, r.rolname FROM pg_namespace n + JOIN pg_roles r ON (r.oid = n.nspowner) + WHERE n.nspname LIKE 'newschema_' ORDER BY 1; + nspname | rolname +------------+-------------- + newschema1 | testrol2 + newschema2 | current_user + newschema3 | testrol1 + newschema4 | testrolx + newschema5 | Public +(5 rows) + +CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER; +CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user"; +CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION SESSION_USER; +CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION testrolx; +CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public"; +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error +ERROR: syntax error at or near "USER" +LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; + ^ +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ...ATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_RO... + ^ +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error +ERROR: role "public" does not exist +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error +ERROR: role "public" does not exist +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error +ERROR: role name "none" is reserved +LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; + ^ +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION nonexistent; -- error +ERROR: role "nonexistent" does not exist +SELECT n.nspname, r.rolname FROM pg_namespace n + JOIN pg_roles r ON (r.oid = n.nspowner) + WHERE n.nspname LIKE 'newschema_' ORDER BY 1; + nspname | rolname +------------+-------------- + newschema1 | testrol2 + newschema2 | current_user + newschema3 | testrol1 + newschema4 | testrolx + newschema5 | Public +(5 rows) + +-- ALTER TABLE OWNER TO +\c - +SET SESSION AUTHORIZATION testrol0; +set client_min_messages to error; +CREATE TABLE testtab1 (a int); +CREATE TABLE testtab2 (a int); +CREATE TABLE testtab3 (a int); +CREATE TABLE testtab4 (a int); +CREATE TABLE testtab5 (a int); +CREATE TABLE testtab6 (a int); +\c - +SET SESSION AUTHORIZATION testrol1; +SET ROLE testrol2; +ALTER TABLE testtab1 OWNER TO CURRENT_USER; +ALTER TABLE testtab2 OWNER TO "current_user"; +ALTER TABLE testtab3 OWNER TO SESSION_USER; +ALTER TABLE testtab4 OWNER TO testrolx; +ALTER TABLE testtab5 OWNER TO "Public"; +ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; + ^ +ALTER TABLE testtab6 OWNER TO USER; --error +ERROR: syntax error at or near "USER" +LINE 1: ALTER TABLE testtab6 OWNER TO USER; + ^ +ALTER TABLE testtab6 OWNER TO PUBLIC; -- error +ERROR: role "public" does not exist +ALTER TABLE testtab6 OWNER TO "public"; -- error +ERROR: role "public" does not exist +ALTER TABLE testtab6 OWNER TO nonexistent; -- error +ERROR: role "nonexistent" does not exist +SELECT c.relname, r.rolname + FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner) + WHERE relname LIKE 'testtab_' + ORDER BY 1; + relname | rolname +----------+-------------- + testtab1 | testrol2 + testtab2 | current_user + testtab3 | testrol1 + testtab4 | testrolx + testtab5 | Public + testtab6 | testrol0 +(6 rows) + +-- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are +-- changed their owner in the same way. +-- ALTER AGGREGATE +\c - +SET SESSION AUTHORIZATION testrol0; +CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8); +ERROR: function "testagg5" already exists with same argument types +CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8); +\c - +SET SESSION AUTHORIZATION testrol1; +SET ROLE testrol2; +ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER; +ALTER AGGREGATE testagg2(int2) OWNER TO "current_user"; +ALTER AGGREGATE testagg3(int2) OWNER TO SESSION_USER; +ALTER AGGREGATE testagg4(int2) OWNER TO testrolx; +ALTER AGGREGATE testagg5(int2) OWNER TO "Public"; +ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; + ^ +ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error +ERROR: syntax error at or near "USER" +LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO USER; + ^ +ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error +ERROR: role "public" does not exist +ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error +ERROR: role "public" does not exist +ALTER AGGREGATE testagg5(int2) OWNER TO nonexistent; -- error +ERROR: role "nonexistent" does not exist +SELECT p.proname, r.rolname + FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner) + WHERE proname LIKE 'testagg_' + ORDER BY 1; + proname | rolname +----------+-------------- + testagg1 | testrol2 + testagg2 | current_user + testagg3 | testrol1 + testagg4 | testrolx + testagg5 | Public + testagg6 | testrol0 + testagg7 | testrol0 + testagg8 | testrol0 + testagg9 | testrol0 +(9 rows) + +-- CREATE USER MAPPING +CREATE FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper; +CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); +CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); +CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR testrolx SERVER sv8 OPTIONS (user 'testrolx'); +CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 + OPTIONS (user 'CURRENT_ROLE'); -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 + ^ +CREATE USER MAPPING FOR nonexistent SERVER sv9 + OPTIONS (user 'nonexistent'); -- error; +ERROR: role "nonexistent" does not exist +SELECT * FROM chkumapping(); + umname | umserver | umoptions +--------------+----------+--------------------------- + testrol2 | sv1 | {user=CURRENT_USER} + current_user | sv2 | {"user=\"current_user\""} + testrol2 | sv3 | {user=USER} + user | sv4 | {"user=\"USER\""} + testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=testrolx} +(8 rows) + +-- ALTER USER MAPPING +ALTER USER MAPPING FOR CURRENT_USER SERVER sv1 + OPTIONS (SET user 'CURRENT_USER_alt'); +ALTER USER MAPPING FOR "current_user" SERVER sv2 + OPTIONS (SET user '"current_user"_alt'); +ALTER USER MAPPING FOR USER SERVER sv3 + OPTIONS (SET user 'USER_alt'); +ALTER USER MAPPING FOR "user" SERVER sv4 + OPTIONS (SET user '"user"_alt'); +ALTER USER MAPPING FOR SESSION_USER SERVER sv5 + OPTIONS (SET user 'SESSION_USER_alt'); +ALTER USER MAPPING FOR PUBLIC SERVER sv6 + OPTIONS (SET user 'public_alt'); +ALTER USER MAPPING FOR "Public" SERVER sv7 + OPTIONS (SET user '"Public"_alt'); +ALTER USER MAPPING FOR testrolx SERVER sv8 + OPTIONS (SET user 'testrolx_alt'); +ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 + OPTIONS (SET user 'CURRENT_ROLE_alt'); +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 + ^ +ALTER USER MAPPING FOR nonexistent SERVER sv9 + OPTIONS (SET user 'nonexistent_alt'); -- error +ERROR: role "nonexistent" does not exist +SELECT * FROM chkumapping(); + umname | umserver | umoptions +--------------+----------+------------------------------- + testrol2 | sv1 | {user=CURRENT_USER_alt} + current_user | sv2 | {"user=\"current_user\"_alt"} + testrol2 | sv3 | {user=USER_alt} + user | sv4 | {"user=\"user\"_alt"} + testrol1 | sv5 | {user=SESSION_USER_alt} + | sv6 | {user=public_alt} + Public | sv7 | {"user=\"Public\"_alt"} + testrolx | sv8 | {user=testrolx_alt} +(8 rows) + +-- DROP USER MAPPING +DROP USER MAPPING FOR CURRENT_USER SERVER sv1; +DROP USER MAPPING FOR "current_user" SERVER sv2; +DROP USER MAPPING FOR USER SERVER sv3; +DROP USER MAPPING FOR "user" SERVER sv4; +DROP USER MAPPING FOR SESSION_USER SERVER sv5; +DROP USER MAPPING FOR PUBLIC SERVER sv6; +DROP USER MAPPING FOR "Public" SERVER sv7; +DROP USER MAPPING FOR testrolx SERVER sv8; +DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; + ^ +DROP USER MAPPING FOR nonexistent SERVER sv; -- error +ERROR: role "nonexistent" does not exist +SELECT * FROM chkumapping(); + umname | umserver | umoptions +--------+----------+----------- +(0 rows) + +CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); +CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); +CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR testrolx SERVER sv8 OPTIONS (user 'testrolx'); +SELECT * FROM chkumapping(); + umname | umserver | umoptions +--------------+----------+--------------------------- + testrol2 | sv1 | {user=CURRENT_USER} + current_user | sv2 | {"user=\"current_user\""} + testrol2 | sv3 | {user=USER} + user | sv4 | {"user=\"USER\""} + testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=testrolx} +(8 rows) + +-- DROP USER MAPPING IF EXISTS +DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +--------------+----------+--------------------------- + current_user | sv2 | {"user=\"current_user\""} + testrol2 | sv3 | {user=USER} + user | sv4 | {"user=\"USER\""} + testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=testrolx} +(7 rows) + +DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +----------+----------+--------------------- + testrol2 | sv3 | {user=USER} + user | sv4 | {"user=\"USER\""} + testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=testrolx} +(6 rows) + +DROP USER MAPPING IF EXISTS FOR USER SERVER sv3; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +----------+----------+--------------------- + user | sv4 | {"user=\"USER\""} + testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=testrolx} +(5 rows) + +DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +----------+----------+--------------------- + testrol1 | sv5 | {user=SESSION_USER} + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=testrolx} +(4 rows) + +DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +----------+----------+--------------------- + | sv6 | {user=PUBLIC} + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=testrolx} +(3 rows) + +DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +----------+----------+--------------------- + Public | sv7 | {"user=\"Public\""} + testrolx | sv8 | {user=testrolx} +(2 rows) + +DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +----------+----------+----------------- + testrolx | sv8 | {user=testrolx} +(1 row) + +DROP USER MAPPING IF EXISTS FOR testrolx SERVER sv8; +SELECT * FROM chkumapping(); + umname | umserver | umoptions +--------+----------+----------- +(0 rows) + +DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; + ^ +DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error +NOTICE: role "nonexistent" does not exist, skipping +-- GRANT/REVOKE +UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_'; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+-------- + testagg1 | + testagg2 | + testagg3 | + testagg4 | + testagg5 | + testagg6 | + testagg7 | + testagg8 | + testagg9 | +(9 rows) + +REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC; +GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC; +GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER; +GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user"; +GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO SESSION_USER; +GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO testrolx; +GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2) + TO current_user, public, testrolx; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+--------------------------------------------------------------------------- + testagg1 | {testrol2=X/testrol2,=X/testrol2} + testagg2 | {current_user=X/current_user,testrol2=X/current_user} + testagg3 | {testrol1=X/testrol1,current_user=X/testrol1} + testagg4 | {testrolx=X/testrolx,testrol1=X/testrolx} + testagg5 | {Public=X/Public} + testagg6 | {testrol0=X/testrol0,testrolx=X/testrol0} + testagg7 | {testrol0=X/testrol0,=X/testrol0} + testagg8 | {testrol0=X/testrol0,testrol2=X/testrol0,=X/testrol0,testrolx=X/testrol0} + testagg9 | +(9 rows) + +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ...RANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_RO... + ^ +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error +ERROR: syntax error at or near "USER" +LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; + ^ +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error +ERROR: role name "none" is reserved +LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; + ^ +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error +ERROR: role name "none" is reserved +LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; + ^ +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+--------------------------------------------------------------------------- + testagg1 | {testrol2=X/testrol2,=X/testrol2} + testagg2 | {current_user=X/current_user,testrol2=X/current_user} + testagg3 | {testrol1=X/testrol1,current_user=X/testrol1} + testagg4 | {testrolx=X/testrolx,testrol1=X/testrolx} + testagg5 | {Public=X/Public} + testagg6 | {testrol0=X/testrol0,testrolx=X/testrol0} + testagg7 | {testrol0=X/testrol0,=X/testrol0} + testagg8 | {testrol0=X/testrol0,testrol2=X/testrol0,=X/testrol0,testrolx=X/testrol0} + testagg9 | +(9 rows) + +REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER; +REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM SESSION_USER; +REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM testrolx; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) + FROM current_user, public, testrolx; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+------------------------------- + testagg1 | {testrol2=X/testrol2} + testagg2 | {current_user=X/current_user} + testagg3 | {testrol1=X/testrol1} + testagg4 | {testrolx=X/testrolx} + testagg5 | {} + testagg6 | {testrol0=X/testrol0} + testagg7 | {testrol0=X/testrol0} + testagg8 | {testrol0=X/testrol0} + testagg9 | +(9 rows) + +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error +ERROR: syntax error at or near "CURRENT_ROLE" +LINE 1: ...KE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_RO... + ^ +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error +ERROR: syntax error at or near "USER" +LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; + ^ +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error +ERROR: role name "none" is reserved +LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; + ^ +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error +ERROR: role name "none" is reserved +LINE 1: ...EVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; + ^ +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + proname | proacl +----------+------------------------------- + testagg1 | {testrol2=X/testrol2} + testagg2 | {current_user=X/current_user} + testagg3 | {testrol1=X/testrol1} + testagg4 | {testrolx=X/testrolx} + testagg5 | {} + testagg6 | {testrol0=X/testrol0} + testagg7 | {testrol0=X/testrol0} + testagg8 | {testrol0=X/testrol0} + testagg9 | +(9 rows) + +-- clean up +\c +DROP OWNED BY testrol0, "Public", "current_user", testrol1, testrol2, testrolx CASCADE; +DROP ROLE testrol0, testrol1, testrol2, testrolx; +DROP ROLE "Public", "None", "current_user", "session_user", "user"; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index e0ae2f2b01..6d3b865351 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -59,7 +59,7 @@ test: create_index create_view # ---------- # Another group of parallel tests # ---------- -test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views +test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames # ---------- # sanity_check does a vacuum, affecting the sort order of SELECT * diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 7f762bd08f..8326894ed9 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -71,6 +71,7 @@ test: typed_table test: vacuum test: drop_if_exists test: updatable_views +test: rolenames test: sanity_check test: errors test: select diff --git a/src/test/regress/sql/rolenames.sql b/src/test/regress/sql/rolenames.sql new file mode 100644 index 0000000000..e8c6b335ec --- /dev/null +++ b/src/test/regress/sql/rolenames.sql @@ -0,0 +1,434 @@ +CREATE OR REPLACE FUNCTION chkrolattr() + RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool) + AS $$ +SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication + FROM pg_roles r + JOIN (VALUES(CURRENT_USER, 'current_user'), + (SESSION_USER, 'session_user'), + ('current_user', '-'), + ('session_user', '-'), + ('Public', '-'), + ('None', '-')) + AS v(uname, keyword) + ON (r.rolname = v.uname) + ORDER BY 1; +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION chksetconfig() + RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[]) + AS $$ +SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'), + COALESCE(v.keyword, '-'), s.setconfig + FROM pg_db_role_setting s + LEFT JOIN pg_roles r ON (r.oid = s.setrole) + LEFT JOIN pg_database d ON (d.oid = s.setdatabase) + LEFT JOIN (VALUES(CURRENT_USER, 'current_user'), + (SESSION_USER, 'session_user')) + AS v(uname, keyword) + ON (r.rolname = v.uname) + WHERE (r.rolname) IN ('Public', 'current_user', 'testrol1', 'testrol2') +ORDER BY 1, 2; +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION chkumapping() + RETURNS TABLE (umname name, umserver name, umoptions text[]) + AS $$ +SELECT r.rolname, s.srvname, m.umoptions + FROM pg_user_mapping m + LEFT JOIN pg_roles r ON (r.oid = m.umuser) + JOIN pg_foreign_server s ON (s.oid = m.umserver) + ORDER BY 2; +$$ LANGUAGE SQL; + +CREATE ROLE "Public"; +CREATE ROLE "None"; +CREATE ROLE "current_user"; +CREATE ROLE "session_user"; +CREATE ROLE "user"; + +CREATE ROLE current_user; -- error +CREATE ROLE current_role; -- error +CREATE ROLE session_user; -- error +CREATE ROLE user; -- error +CREATE ROLE all; -- error + +CREATE ROLE public; -- error +CREATE ROLE "public"; -- error +CREATE ROLE none; -- error +CREATE ROLE "none"; -- error + +CREATE ROLE testrol0 SUPERUSER LOGIN; +CREATE ROLE testrolx SUPERUSER LOGIN; +CREATE ROLE testrol2 SUPERUSER; +CREATE ROLE testrol1 SUPERUSER LOGIN IN ROLE testrol2; + +\c - +SET SESSION AUTHORIZATION testrol1; +SET ROLE testrol2; + +-- ALTER ROLE +BEGIN; +SELECT * FROM chkrolattr(); +ALTER ROLE CURRENT_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER ROLE "current_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER ROLE SESSION_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER ROLE "session_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER "Public" WITH REPLICATION; +ALTER USER "None" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER testrol1 WITH NOREPLICATION; +ALTER USER testrol2 WITH NOREPLICATION; +SELECT * FROM chkrolattr(); +ROLLBACK; + +ALTER ROLE USER WITH LOGIN; -- error +ALTER ROLE CURRENT_ROLE WITH LOGIN; --error +ALTER ROLE ALL WITH REPLICATION; -- error +ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error +ALTER ROLE PUBLIC WITH NOREPLICATION; -- error +ALTER ROLE "public" WITH NOREPLICATION; -- error +ALTER ROLE NONE WITH NOREPLICATION; -- error +ALTER ROLE "none" WITH NOREPLICATION; -- error +ALTER ROLE nonexistent WITH NOREPLICATION; -- error + +-- ALTER USER +BEGIN; +SELECT * FROM chkrolattr(); +ALTER USER CURRENT_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER "current_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER SESSION_USER WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER "session_user" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER "Public" WITH REPLICATION; +ALTER USER "None" WITH REPLICATION; +SELECT * FROM chkrolattr(); +ALTER USER testrol1 WITH NOREPLICATION; +ALTER USER testrol2 WITH NOREPLICATION; +SELECT * FROM chkrolattr(); +ROLLBACK; + +ALTER USER USER WITH LOGIN; -- error +ALTER USER CURRENT_ROLE WITH LOGIN; -- error +ALTER USER ALL WITH REPLICATION; -- error +ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error +ALTER USER PUBLIC WITH NOREPLICATION; -- error +ALTER USER "public" WITH NOREPLICATION; -- error +ALTER USER NONE WITH NOREPLICATION; -- error +ALTER USER "none" WITH NOREPLICATION; -- error +ALTER USER nonexistent WITH NOREPLICATION; -- error + +-- ALTER ROLE SET/RESET +SELECT * FROM chksetconfig(); +ALTER ROLE CURRENT_USER SET application_name to 'FOO'; +ALTER ROLE SESSION_USER SET application_name to 'BAR'; +ALTER ROLE "current_user" SET application_name to 'FOOFOO'; +ALTER ROLE "Public" SET application_name to 'BARBAR'; +ALTER ROLE ALL SET application_name to 'SLAP'; +SELECT * FROM chksetconfig(); +ALTER ROLE testrol1 SET application_name to 'SLAM'; +SELECT * FROM chksetconfig(); +ALTER ROLE CURRENT_USER RESET application_name; +ALTER ROLE SESSION_USER RESET application_name; +ALTER ROLE "current_user" RESET application_name; +ALTER ROLE "Public" RESET application_name; +ALTER ROLE ALL RESET application_name; +SELECT * FROM chksetconfig(); + + +ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; -- error +ALTER ROLE USER SET application_name to 'BOOM'; -- error +ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error +ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error + +-- ALTER USER SET/RESET +SELECT * FROM chksetconfig(); +ALTER USER CURRENT_USER SET application_name to 'FOO'; +ALTER USER SESSION_USER SET application_name to 'BAR'; +ALTER USER "current_user" SET application_name to 'FOOFOO'; +ALTER USER "Public" SET application_name to 'BARBAR'; +ALTER USER ALL SET application_name to 'SLAP'; +SELECT * FROM chksetconfig(); +ALTER USER testrol1 SET application_name to 'SLAM'; +SELECT * FROM chksetconfig(); +ALTER USER CURRENT_USER RESET application_name; +ALTER USER SESSION_USER RESET application_name; +ALTER USER "current_user" RESET application_name; +ALTER USER "Public" RESET application_name; +ALTER USER ALL RESET application_name; +SELECT * FROM chksetconfig(); + + +ALTER USER CURRENT_USER SET application_name to 'BAZ'; -- error +ALTER USER USER SET application_name to 'BOOM'; -- error +ALTER USER PUBLIC SET application_name to 'BOMB'; -- error +ALTER USER NONE SET application_name to 'BOMB'; -- error +ALTER USER nonexistent SET application_name to 'BOMB'; -- error + +-- CREAETE SCHEMA +set client_min_messages to error; +CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER; +CREATE SCHEMA newschema2 AUTHORIZATION "current_user"; +CREATE SCHEMA newschema3 AUTHORIZATION SESSION_USER; +CREATE SCHEMA newschema4 AUTHORIZATION testrolx; +CREATE SCHEMA newschema5 AUTHORIZATION "Public"; + +CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error +CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error +CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error +CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error +CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error +CREATE SCHEMA newschema6 AUTHORIZATION nonexistent; -- error + +SELECT n.nspname, r.rolname FROM pg_namespace n + JOIN pg_roles r ON (r.oid = n.nspowner) + WHERE n.nspname LIKE 'newschema_' ORDER BY 1; + +CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER; +CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user"; +CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION SESSION_USER; +CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION testrolx; +CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public"; + +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error +CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION nonexistent; -- error + +SELECT n.nspname, r.rolname FROM pg_namespace n + JOIN pg_roles r ON (r.oid = n.nspowner) + WHERE n.nspname LIKE 'newschema_' ORDER BY 1; + +-- ALTER TABLE OWNER TO +\c - +SET SESSION AUTHORIZATION testrol0; +set client_min_messages to error; +CREATE TABLE testtab1 (a int); +CREATE TABLE testtab2 (a int); +CREATE TABLE testtab3 (a int); +CREATE TABLE testtab4 (a int); +CREATE TABLE testtab5 (a int); +CREATE TABLE testtab6 (a int); + +\c - +SET SESSION AUTHORIZATION testrol1; +SET ROLE testrol2; + +ALTER TABLE testtab1 OWNER TO CURRENT_USER; +ALTER TABLE testtab2 OWNER TO "current_user"; +ALTER TABLE testtab3 OWNER TO SESSION_USER; +ALTER TABLE testtab4 OWNER TO testrolx; +ALTER TABLE testtab5 OWNER TO "Public"; + +ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error +ALTER TABLE testtab6 OWNER TO USER; --error +ALTER TABLE testtab6 OWNER TO PUBLIC; -- error +ALTER TABLE testtab6 OWNER TO "public"; -- error +ALTER TABLE testtab6 OWNER TO nonexistent; -- error + +SELECT c.relname, r.rolname + FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner) + WHERE relname LIKE 'testtab_' + ORDER BY 1; + +-- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are +-- changed their owner in the same way. + +-- ALTER AGGREGATE +\c - +SET SESSION AUTHORIZATION testrol0; +CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8); +CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8); + +\c - +SET SESSION AUTHORIZATION testrol1; +SET ROLE testrol2; + +ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER; +ALTER AGGREGATE testagg2(int2) OWNER TO "current_user"; +ALTER AGGREGATE testagg3(int2) OWNER TO SESSION_USER; +ALTER AGGREGATE testagg4(int2) OWNER TO testrolx; +ALTER AGGREGATE testagg5(int2) OWNER TO "Public"; + +ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error +ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error +ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error +ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error +ALTER AGGREGATE testagg5(int2) OWNER TO nonexistent; -- error + +SELECT p.proname, r.rolname + FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner) + WHERE proname LIKE 'testagg_' + ORDER BY 1; + +-- CREATE USER MAPPING +CREATE FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper; +CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper; + +CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); +CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); +CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR testrolx SERVER sv8 OPTIONS (user 'testrolx'); + +CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9 + OPTIONS (user 'CURRENT_ROLE'); -- error +CREATE USER MAPPING FOR nonexistent SERVER sv9 + OPTIONS (user 'nonexistent'); -- error; + +SELECT * FROM chkumapping(); + +-- ALTER USER MAPPING +ALTER USER MAPPING FOR CURRENT_USER SERVER sv1 + OPTIONS (SET user 'CURRENT_USER_alt'); +ALTER USER MAPPING FOR "current_user" SERVER sv2 + OPTIONS (SET user '"current_user"_alt'); +ALTER USER MAPPING FOR USER SERVER sv3 + OPTIONS (SET user 'USER_alt'); +ALTER USER MAPPING FOR "user" SERVER sv4 + OPTIONS (SET user '"user"_alt'); +ALTER USER MAPPING FOR SESSION_USER SERVER sv5 + OPTIONS (SET user 'SESSION_USER_alt'); +ALTER USER MAPPING FOR PUBLIC SERVER sv6 + OPTIONS (SET user 'public_alt'); +ALTER USER MAPPING FOR "Public" SERVER sv7 + OPTIONS (SET user '"Public"_alt'); +ALTER USER MAPPING FOR testrolx SERVER sv8 + OPTIONS (SET user 'testrolx_alt'); + +ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9 + OPTIONS (SET user 'CURRENT_ROLE_alt'); +ALTER USER MAPPING FOR nonexistent SERVER sv9 + OPTIONS (SET user 'nonexistent_alt'); -- error + +SELECT * FROM chkumapping(); + +-- DROP USER MAPPING +DROP USER MAPPING FOR CURRENT_USER SERVER sv1; +DROP USER MAPPING FOR "current_user" SERVER sv2; +DROP USER MAPPING FOR USER SERVER sv3; +DROP USER MAPPING FOR "user" SERVER sv4; +DROP USER MAPPING FOR SESSION_USER SERVER sv5; +DROP USER MAPPING FOR PUBLIC SERVER sv6; +DROP USER MAPPING FOR "Public" SERVER sv7; +DROP USER MAPPING FOR testrolx SERVER sv8; + +DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error +DROP USER MAPPING FOR nonexistent SERVER sv; -- error +SELECT * FROM chkumapping(); + +CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER'); +CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"'); +CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER'); +CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"'); +CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER'); +CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC'); +CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"'); +CREATE USER MAPPING FOR testrolx SERVER sv8 OPTIONS (user 'testrolx'); +SELECT * FROM chkumapping(); + +-- DROP USER MAPPING IF EXISTS +DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR USER SERVER sv3; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7; +SELECT * FROM chkumapping(); +DROP USER MAPPING IF EXISTS FOR testrolx SERVER sv8; +SELECT * FROM chkumapping(); + +DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error +DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error + +-- GRANT/REVOKE +UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_'; +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + +REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC; + +GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC; +GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER; +GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user"; +GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO SESSION_USER; +GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO testrolx; +GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public"; +GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2) + TO current_user, public, testrolx; + +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error +GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error + +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + +REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC; +REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER; +REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM SESSION_USER; +REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM testrolx; +REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public"; +REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) + FROM current_user, public, testrolx; + +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error +REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error + +SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_'; + +-- clean up +\c + +DROP OWNED BY testrol0, "Public", "current_user", testrol1, testrol2, testrolx CASCADE; +DROP ROLE testrol0, testrol1, testrol2, testrolx; +DROP ROLE "Public", "None", "current_user", "session_user", "user"; -- 2.40.0