User / Roles

How to Start psql terminal

sudo -i -u postgres
psql

List all the database

\l

List all the user

\du

Create a new user

create user prabin with password 'abcde@12345';

 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 prabin    |                                                            | {}

Make superuser

Alter user prabin with superuser;

 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 prabin    | Superuser                                                  | {}

Drop user

postgres=# create user user1 with password 'abcde@12345';
CREATE ROLE
postgres=# Drop user user1;
DROP ROLE

Show current user

select current_user;

 current_user
--------------
 postgres
(1 row)

Change user and database

postgres@linux:~$ psql -U prabin -h 127.0.0.1 -d mydb

Reset the password

Alter user prabin with password 'abcde@12345';