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.