agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedwhat is the difference between ROLE clause and IN ROLE clause for CREATE USER statement ?
5+ messages / 4 participants
[nested] [flat]
* what is the difference between ROLE clause and IN ROLE clause for CREATE USER statement ?
@ 2026-09-02 05:15 Pierre Forstmann <pierre.forstmann@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Pierre Forstmann @ 2026-09-02 05:15 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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@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@alma:~$ vacuumdb -U uri
vacuumdb: vacuuming database "pierre"
pierre@alma:~$
Thanks.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: what is the difference between ROLE clause and IN ROLE clause for CREATE USER statement ?
@ 2026-09-02 05:52 Benjamin Leis <benleis1@gmail.com>
parent: Pierre Forstmann <pierre.forstmann@gmail.com>
1 sibling, 2 replies; 5+ messages in thread
From: Benjamin Leis @ 2026-09-02 05:52 UTC (permalink / raw)
To: Pierre Forstmann <pierre.forstmann@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.forstmann@gmail.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@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@alma:~$ vacuumdb -U uri
> vacuumdb: vacuuming database "pierre"
> pierre@alma:~$
>
>
> Thanks.
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: what is the difference between ROLE clause and IN ROLE clause for CREATE USER statement ?
@ 2026-09-02 05:55 Kristjan Mustkivi <sonicmonkey@gmail.com>
parent: Benjamin Leis <benleis1@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Kristjan Mustkivi @ 2026-09-02 05:55 UTC (permalink / raw)
To: Benjamin Leis <benleis1@gmail.com>; +Cc: Pierre Forstmann <pierre.forstmann@gmail.com>; pgsql-admin@lists.postgresql.org
Hello,
The documentation seems also to be sufficiently clear:
"The IN ROLE clause causes the new role to be automatically added as a
member of the specified existing roles."
"The ROLE clause causes one or more specified existing roles to be
automatically added as members.."
With best regards,
K
On Wed, Sep 2, 2026 at 8:52 AM Benjamin Leis <benleis1@gmail.com> wrote:
>
> 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.forstmann@gmail.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@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@alma:~$ vacuumdb -U uri
>> vacuumdb: vacuuming database "pierre"
>> pierre@alma:~$
>>
>>
>> Thanks.
>>
>>
>>
--
Kristjan Mustkivi
Email: kristjan.mustkivi@gmail.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: what is the difference between ROLE clause and IN ROLE clause for CREATE USER statement ?
@ 2026-09-02 07:34 Alija Rahmanovic <rmc.aly17@gmail.com>
parent: Pierre Forstmann <pierre.forstmann@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Alija Rahmanovic @ 2026-09-02 07:34 UTC (permalink / raw)
To: Pierre Forstmann <pierre.forstmann@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Show
V sre., 2. sep. 2026 07:16 je oseba Pierre Forstmann <
pierre.forstmann@gmail.com> napisala:
> 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@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@alma:~$ vacuumdb -U uri
> vacuumdb: vacuuming database "pierre"
> pierre@alma:~$
>
>
> Thanks.
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: what is the difference between ROLE clause and IN ROLE clause for CREATE USER statement ?
@ 2026-09-02 18:31 Pierre Forstmann <pierre.forstmann@gmail.com>
parent: Benjamin Leis <benleis1@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Pierre Forstmann @ 2026-09-02 18:31 UTC (permalink / raw)
To: Benjamin Leis <benleis1@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org; sonicmonkey@gmail.com
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.forstmann@gmail.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@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@alma:~$ vacuumdb -U uri
> vacuumdb: vacuuming database "pierre"
> pierre@alma:~$
>
>
> Thanks.
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-09-02 18:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-02 05:15 what is the difference between ROLE clause and IN ROLE clause for CREATE USER statement ? Pierre Forstmann <pierre.forstmann@gmail.com>
2026-09-02 05:52 ` Benjamin Leis <benleis1@gmail.com>
2026-09-02 05:55 ` Kristjan Mustkivi <sonicmonkey@gmail.com>
2026-09-02 18:31 ` Pierre Forstmann <pierre.forstmann@gmail.com>
2026-09-02 07:34 ` Alija Rahmanovic <rmc.aly17@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox