ROLES USAGE IN POSTGRESQL

  • In postgresql roles are also called as users.
  • By default after installation of postgresql ‘postgres’ is the existing role/user, it is a super user.
  • In creation of role superuser plays an important role .
  • If we create role as super user it will get all permissions means the user can create another user,creation of new data base,login authentication.
Syntax for role creation 

CREATE ROLE ROLENAME;

       OR

CREATE USER USERNAME;

Syntax for role creation with password

CREATE ROLE ROLENAME WITH PASSWORD 'HEREROLEPASSWORD' ; The password which you have created is encoded into the table
Syntax for creation of superuser 

CREATE ROLE ROLENAME SUPERUSER
To know the present roles in the postgresql then we go for

testdb=# SELECT * FROM pg_roles ;

 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid 
----------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-----
 postgres | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |  10
(1 row) 
we can see the default role postgres with privillages
To know the existing roles permissions go for

testdb=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {} Altering the existing user

testdb=# ALTER ROLE ROLENAME WITH NOLOGIN;
ALTER ROLE After WITH in the above query we can give required privilage to alter the role.

Contact Us

Fields with (*) are compulsory.

Anvesh Cherukuri