A database user is required to establish a connection to the database. At a minimum, you need to specify the ‘username’ for user creation.
Below statement creates a database user with specified ‘username’. You can also create user as ‘CREATE ROLE username LOGIN;’ However, if you use ‘CREATE USER’ clause, login is assumed by default.
CREATE USER username;
To allow the user to connect with a password, you can create a password for user during user creation as shown below:
CREATE USER username PASSWORD ‘password‘;
CREATEDB is an advanced privilege and it allows user to create additional databases.
CREATE USER username PASSWORD ‘password‘ CREATEDB;
Use the SUPERUSER option to grant super user privileges to the user.
CREATE USER username PASSWORD ‘password‘ SUPERUSER;
By specifying ‘IN GROUP’ option, all the privileges assigned to the ‘rolename’ will be assigned to the user.
CREATE USER username PASSWORD ‘password‘ IN GROUP rolename;
‘Valid until’ option allows you to set an expiration date for the password. It can also be used to invalidate the user without dropping the user from the database.
CREATE USER username PASSWORD ‘password‘ VALID UNTIL ‘2022-12-31‘;
CONNECTION LIMIT option allows you to specify a limit on the maximum number of connections that the user can establish concurrently.
CREATE USER username PASSWORD ‘password‘ CONNECTION LIMIT 20;