Re: what is the difference between ROLE clause and IN ROLE clause for CREATE USER statement ?

I have asked chatgpt which says:

/CREATE USER ur  ROLE    pg_maintain;  -- pg_maintain is a member of ur
CREATE USER uri IN ROLE pg_maintain;  -- uri is a member of pg_maintain
/

/which creates following role hierarchy:/

/ur
↑
pg_maintain
↑
uri/

/
/

/and *only |uri| is below |pg_maintain| in the membership hierarchy, so
|uri| inherits |pg_maintain|'s maintenance privileges.*/

/That is exactly why |vacuumdb -U uri| succeeds while |vacuumdb -U ur|
skips the catalogs./

Even if know that roles and users are almost the same entities in PG, I
find this confusing.

Thanks.

Le 02/09/2026 à 07:52, Benjamin Leis a écrit :
> I think create in role makes the user a member of the specified role.
> While create role makes the roles members of this new user group.  So
> it's a question of which is the container and what is being contained.
>
> On Tue, Sep 1, 2026, 10:16 PM Pierre Forstmann
> <pierre(dot)forstmann(at)gmail(dot)com> wrote:
>
> Hello,
>
> I don't understand the difference even after reading CREATE
> USER/CREATE
> ROLE documentation.
>
> Here is a scenario that shows a difference with PG 18.6 (built
> with pgenv):
>
> pierre=# create user ur role pg_maintain;
> CREATE ROLE
> pierre=# create user uri  in role pg_maintain;
> CREATE ROLE
> pierre=# \du
>                               List of roles
>   Role name |                         Attributes
> -----------+------------------------------------------------------------
>   pierre    | Superuser
>   postgres  | Superuser, Create role, Create DB, Replication,
> Bypass RLS
>   ur        |
>   uri       |
>
> pierre=# \l
>                                                       List of
> databases
>     Name    |  Owner   | Encoding | Locale Provider |  Collate  |
> Ctype    | Locale | ICU Rules |   Access privileges
> -----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
>   pierre    | pierre   | UTF8     | libc            | en_US.UTF-8 |
> en_US.UTF-8 |        |           |
>   postgres  | postgres | UTF8     | libc            | en_US.UTF-8 |
> en_US.UTF-8 |        |           |
>   template0 | postgres | UTF8     | libc            | en_US.UTF-8 |
> en_US.UTF-8 |        |           | =c/postgres          +
>             |          |          |                 |  |        |
>        |           | postgres=CTc/postgres
>   template1 | postgres | UTF8     | libc            | en_US.UTF-8 |
> en_US.UTF-8 |        |           | =c/postgres          +
>             |          |          |                 |  |        |
>        |           | postgres=CTc/postgres
> (4 rows)
>
>
> pierre(at)alma:~$ vacuumdb -U ur
> vacuumdb: vacuuming database "pierre"
> WARNING:  permission denied to vacuum "pg_proc", skipping it
> WARNING:  permission denied to vacuum "pg_attribute", skipping it
> WARNING:  permission denied to vacuum "pg_description", skipping it
> WARNING:  permission denied to vacuum "pg_collation", skipping it
> WARNING:  permission denied to vacuum "pg_statistic", skipping it
> WARNING:  permission denied to vacuum "pg_type", skipping it
> WARNING:  permission denied to vacuum "pg_class", skipping it
> WARNING:  permission denied to vacuum "pg_rewrite", skipping it
> WARNING:  permission denied to vacuum "pg_operator", skipping it
> WARNING:  permission denied to vacuum "pg_depend", skipping it
> WARNING:  permission denied to vacuum "sql_features", skipping it
> WARNING:  permission denied to vacuum "pg_amop", skipping it
> WARNING:  permission denied to vacuum "pg_constraint", skipping it
> WARNING:  permission denied to vacuum "pg_amproc", skipping it
> WARNING:  permission denied to vacuum "pg_ts_config_map", skipping it
> WARNING:  permission denied to vacuum "pg_index", skipping it
> WARNING:  permission denied to vacuum "pg_init_privs", skipping it
> WARNING:  permission denied to vacuum "pg_opclass", skipping it
> WARNING:  permission denied to vacuum "pg_aggregate", skipping it
> WARNING:  permission denied to vacuum "pg_cast", skipping it
> WARNING:  permission denied to vacuum "pg_opfamily", skipping it
> WARNING:  permission denied to vacuum "pg_conversion", skipping it
> WARNING:  permission denied to vacuum "pg_namespace", skipping it
> WARNING:  permission denied to vacuum "sql_implementation_info",
> skipping it
> WARNING:  permission denied to vacuum "pg_am", skipping it
> WARNING:  permission denied to vacuum "pg_language", skipping it
> WARNING:  permission denied to vacuum "pg_authid", skipping it
> WARNING:  permission denied to vacuum "pg_database", skipping it
> WARNING:  permission denied to vacuum "pg_tablespace", skipping it
> WARNING:  permission denied to vacuum "pg_auth_members", skipping it
> WARNING:  permission denied to vacuum "pg_shdescription", skipping it
> WARNING:  permission denied to vacuum "pg_ts_config", skipping it
> WARNING:  permission denied to vacuum "pg_ts_dict", skipping it
> WARNING:  permission denied to vacuum "pg_ts_parser", skipping it
> WARNING:  permission denied to vacuum "pg_ts_template", skipping it
> WARNING:  permission denied to vacuum "pg_extension", skipping it
> WARNING:  permission denied to vacuum "pg_range", skipping it
> WARNING:  permission denied to vacuum "sql_parts", skipping it
> WARNING:  permission denied to vacuum "sql_sizing", skipping it
> WARNING:  permission denied to vacuum "pg_shdepend", skipping it
> WARNING:  permission denied to vacuum "pg_transform", skipping it
> WARNING:  permission denied to vacuum "pg_sequence", skipping it
> WARNING:  permission denied to vacuum "pg_subscription", skipping it
> WARNING:  permission denied to vacuum "pg_publication", skipping it
> WARNING:  permission denied to vacuum "pg_publication_namespace",
> skipping it
> WARNING:  permission denied to vacuum "pg_publication_rel",
> skipping it
> WARNING:  permission denied to vacuum "pg_subscription_rel",
> skipping it
> WARNING:  permission denied to vacuum "pg_foreign_data_wrapper",
> skipping it
> WARNING:  permission denied to vacuum "pg_foreign_server", skipping it
> WARNING:  permission denied to vacuum "pg_policy", skipping it
> WARNING:  permission denied to vacuum "pg_replication_origin",
> skipping it
> WARNING:  permission denied to vacuum "pg_default_acl", skipping it
> WARNING:  permission denied to vacuum "pg_inherits", skipping it
> WARNING:  permission denied to vacuum "pg_seclabel", skipping it
> WARNING:  permission denied to vacuum "pg_shseclabel", skipping it
> WARNING:  permission denied to vacuum "pg_user_mapping", skipping it
> WARNING:  permission denied to vacuum "pg_trigger", skipping it
> WARNING:  permission denied to vacuum "pg_event_trigger", skipping it
> WARNING:  permission denied to vacuum "pg_statistic_ext_data",
> skipping it
> WARNING:  permission denied to vacuum "pg_statistic_ext", skipping it
> WARNING:  permission denied to vacuum "pg_enum", skipping it
> WARNING:  permission denied to vacuum "pg_attrdef", skipping it
> WARNING:  permission denied to vacuum "pg_largeobject_metadata",
> skipping it
> WARNING:  permission denied to vacuum "pg_foreign_table", skipping it
> WARNING:  permission denied to vacuum "pg_parameter_acl", skipping it
> WARNING:  permission denied to vacuum "pg_db_role_setting",
> skipping it
> WARNING:  permission denied to vacuum "pg_partitioned_table",
> skipping it
> WARNING:  permission denied to vacuum "pg_largeobject", skipping it
> pierre(at)alma:~$ vacuumdb -U uri
> vacuumdb: vacuuming database "pierre"
> pierre(at)alma:~$
>
>
> Thanks.
>
>
>