Postgresql - create user command notes

Create user command is an alias to create role

Just that create user command gets the user login permisson by default vs the create role doesnt get the login permisson by default.

Like tablespace user or role are a cluster level entity, a user can be a owner of multiple databases in postgres. Also a user can access/create objects from multiple database/schemas.

Command:
create user <username> [[with] option [....]];

Ex:
create user pgtst_usr createdb password 'pgtst_usr';

output:
postgres=# create user pgtst_usr createdb password 'pgtst_usr';
CREATE ROLE
postgres=#

List of permisson the user pgtst_usr has?

Ex:

\du+

Output:

postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 pgtst_usr | Create DB                                                  | {}        |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
postgres=#
postgres=# select * from pg_user where usename='pgtst_usr';
  usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
-----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 pgtst_usr |    16388 | t           | f        | f       | f            | ******** |          |
(1 row)

Notice the login attribute isnt displayed above.
This is system wide permission.
To look @ table or object level permission, we have to check information_schema.table_privileges on each of the PostgreSQL database.

Ex:
select * from information_schema.table_privileges where grantee='pgtst_usr';

Output:
postgres=# select * from information_schema.table_privileges where grantee='pgtst_usr';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)
postgres=#

We can user upper/lower function to avoid ambiguity.

select * from information_schema.table_privileges where lower(grantee)='pgtst_usr';

Right now we dont have any object. So it is expected result.