| From: | Pierre Forstmann <pierre(dot)forstmann(at)gmail(dot)com> |
|---|---|
| To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
| Subject: | what is the difference between ROLE clause and IN ROLE clause for CREATE USER statement ? |
| Date: | 2026-09-02 05:15:49 |
| Message-ID: | cc3ebec7-0690-4924-a644-9d0d708e1635@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Benjamin Leis | 2026-09-02 05:52:27 | Re: what is the difference between ROLE clause and IN ROLE clause for CREATE USER statement ? |
| Previous Message | Ron Johnson | 2026-09-01 16:14:19 | Feature Request: pg_stat_activity.client_hostname and Unix sockets |