2.3.6 Creating a New Role Associated with a Profile

Table of Contents Previous Next


2 SQL Tutorial : 2.3 Profile Management : 2.3.6 Creating a New Role Associated with a Profile

A database superuser can use clauses of the CREATE USER|ROLE command to assign a named profile to a role when creating the role, or to specify profile management details for a role. The command syntax related to profile management functionality is:
CREATE USER|ROLE name [[WITH] option […]]
where option can be the following compatible clauses:
PROFILE profile_name
| ACCOUNT {LOCK|UNLOCK}
| PASSWORD EXPIRE [AT 'timestamp']
or option can be the following non-compatible clauses:
| LOCK TIME 'timestamp'
For information about the administrative clauses of the CREATE USER or CREATE ROLE command that are supported by Advanced Server, please see the PostgreSQL core documentation available at:
CREATE ROLE|USER… PROFILE adds a new role with an associated profile to an Advanced Server database cluster.
Roles created with the CREATE USER command are (by default) login roles. Roles created with the CREATE ROLE command are (by default) not login roles. To create a login account with the CREATE ROLE command, you must include the LOGIN keyword.
Only a database superuser can use the CREATE USER|ROLE clauses that enforce profile management; these clauses enforce the following behaviors:
Include the PROFILE clause and a profile_name to associate a pre-defined profile with a role, or to change which pre-defined profile is associated with a user.
Include the ACCOUNT clause and the LOCK or UNLOCK keyword to specify that the user account should be placed in a locked or unlocked state.
Include the LOCK TIME 'timestamp' clause and a date/time value to lock the role at the specified time, and unlock the role at the time indicated by the PASSWORD_LOCK_TIME parameter of the profile assigned to this role. If LOCK TIME is used with the ACCOUNT LOCK clause, the role can only be unlocked by a database superuser with the ACCOUNT UNLOCK clause.
Include the PASSWORD EXPIRE clause with the optional AT 'timestamp' keywords to specify a date/time when the password associated with the role will expire. If you omit the AT 'timestamp' keywords, the password will expire immediately.
The following example uses CREATE USER to create a login role named john who is associated with the acctg_profile profile:
john can log in to the server, using the password 1safepwd.
The following example uses CREATE ROLE to create a login role named john who is associated with the acctg_profile profile:
john can log in to the server, using the password 1safepwd.

2 SQL Tutorial : 2.3 Profile Management : 2.3.6 Creating a New Role Associated with a Profile

Table of Contents Previous Next