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

agora inbox for pgsql-admin@postgresql.org  
help / color / mirror / Atom feed

From: Pierre Forstmann <pierre.forstmann@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: what is the difference between ROLE clause and IN ROLE clause for CREATE USER statement ?
Date: Wed, 2 Sep 2026 07:15:49 +0200
Message-ID: <cc3ebec7-0690-4924-a644-9d0d708e1635@gmail.com> (raw)

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.

view thread (5+ messages)  latest in thread

Message-ID: <cc3ebec7-0690-4924-a644-9d0d708e1635@gmail.com>
Permalink:  ../cc3ebec7-0690-4924-a644-9d0d708e1635@gmail.com/
Also on:    postgresql.org/message-id/cc3ebec7-0690-4924-a644-9d0d708e1635@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: pierre.forstmann@gmail.com, pgsql-admin@lists.postgresql.org
  Subject: Re: what is the difference between ROLE clause and IN ROLE clause for CREATE USER statement ?
  In-Reply-To: <cc3ebec7-0690-4924-a644-9d0d708e1635@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox