User Creation

Database users and OS user are completely different. The username should be unique and should not begin with “Pg_”. A super user is created on installation itself. A Postgres gets all the privileges from GRANT option. Only a superuser with Create role privilege can create a USER. Database users are global across the cluster.

SYNTAX: 
    CREATING A USER:   “createuser name”
TO FIND LIST OF USERS : 
 
    “SELECT usename FROM pg_user;”
    
    postgres=# select usename from pg_user;
    usename  
    ----------
    postgres
    (1 row)
    
    postgres=# \du
                                    List of roles
    Role name |                         Attributes                         | Member of 
    -----------+------------------------------------------------------------+-----------
    postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
CREATING A USER FROM DATABASE LEVEL : 
    postgres=# create user tesdb;
    CREATE ROLE
    postgres=# \du
                                    List of roles
    Role name |                         Attributes                         | Member of 
    -----------+------------------------------------------------------------+-----------
    postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    tesdb     |                                                            | {}
    
    -bash-4.2$ psql -U tesdb
    psql (13.13)
    Type "help" for help.
    
    postgres=> \conninfo
    You are connected to database "postgres" as user "tesdb" via socket in "/var/run/postgresql" at port "5432".


(Setting default schema)