Database role is the person who have access to perform action on database.
We can create new database role in PostgreSQL by running below query:
For creating new role you must have CREATEROLE privilege or be a database superuser to use this command.
Parameters
name: This will be the name of new role.
SUPERUSER/NOSUPERUSER: This is clause is used to determine whether the new role will can override all access restrictions within the database. SUPERUSER clause will give all access to new role. If not specified, NOSUPERUSER is the default.
CREATEDB/NOCREATEDB: These are used to determine whether the new role can create Database or not. CREATEDB will give permission to create database to new role whereas NOCREATEDB will deny the ability to create database. If not specified NOCREATEDB is the default.
INHERIT/NOINHERIT: These clause define whether the new role can inherit privileges of roles it is a member of. INHERIT will give the all privileges that have been granted to all roles it is directly or indirectly a member of. If not specified NOINHERIT is the default.
LOGIN/NOLOGIN: These clauses determine whether a role is allowed to log in; that is, whether the role can be given as the initial session authorization name during client connection. A role having LOGIN attribute can be thought as user. If not specified NOLOGIN is default.
REPLICATION/NOREPLICATION: These clause define whether the role is a replication(replication means copying data from a PostgreSQL database server to another server) role. REPLICATION clause will give the permission to create replication. If not specified NOREPLICATION is default.
CONNECTION LIMIT: This clause specifies how many concurrent connections role can make. By default it is -1 means no limit and maximum limit is 115 connections.
PASSWORD: This will create a login password for the role. If you don't want to specify password you can omit. If no password is specified the password will be set to null and password authentication will always fail for this role. Password is always stored in encrypted form in system.
VALID UNTIL: This clause set the date and time after which password will be expired. If no value is specified for VALID UNTIL then password will never expire.
For example above query will create role with chetan name with login, inherit, createdb access and don't have superuser, replication and createrole access. The connection limit is -1 means no limit and password is chetan.
For example above query will create role with chetan name with all privilege. The password will expire after 30 Dec 2021 at 02:10. In same way we can create role with different parameters.
Top comments (0)