PostgreSQL User Password

Introduction to PostgreSQL User Password

PostgreSQL provides user management facility, in which we can create user, can create password for the user, change password of specified user. Also we can update the role of user, delete password of the user. User password is very important in PostgreSQL because it maintains the security of the database management system. The user management plays an important role in the database administration of systems. Under user management we can change the role of the user as well as change the password of the user to maintain a safe environment.

How does Sort Function work in PostgreSQL?

• We must install PostgreSQL in our system.
• We require basic knowledge about PostgreSQL.
• We must require users to perform different operations.
• We must need basic knowledge about user management that means how it is used.
• We can perform different operations on users with the help of psql and pgAdmin.

How to Create a Password for Users in PostgreSQL?

First we see how to create users in PostgreSQL.

Following statement is used to create a password for the user.

Syntax:

create user user  name;

Explanation:

• In above syntax where create user or role statement is used to create user or role in PostgreSQL and user name means specified user name or role name. Here we just create users without any privilege. Second syntax also used to create user through the psql terminal.

Example:

Code:

create user sam;

In the above example we use a create statement to create a user and sam is a new user that we need to create.

Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

Now we have the same user, so we create a password for the sam user using the following syntax.

Syntax:

alter user    user name   password ‘XXXXXXXX’;

Explanation:

• In the above syntax we use an alter user or role statement to create a password for the user, where user name is specified user name which we need to assign password by using password keyword.

Example:

Code:

alter user sam password ‘123456’;

Explanation:

• In the above example we use an alter user statement, where sam is a specified user name and we assign a password to sam user by using the above statement.
• Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

How to Change a Password for the User in PostgreSQL?

If we change the password time to time then it is convenient to protect data and a safe environment can be maintained to users. In this type, suppose we need to change the user password. At that time we used the following syntax and it was an easy method to change passwords.

Syntax:

alter  user  user  name  with password  ‘xxxxxxx’;

Explanation:

• In above syntax we use alter user or role statement, where name is the specified user name and with and password is a keyword to change user password, where user name means specified user name and new password for that user.

Example:

Suppose we need to change the password of sam user at that time we use the following statement.

Code:

alter  user sam with  password 'sam123';

Explanation:

• In the above statement we used alter user statement, where sam is specified user name and with, password is a keywords used to change password of sam user.
• Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

Suppose after some days we want to change the password of the user or update password of user at that time we provide a valid date to the user password. Now see how we can provide validity to the password of the specified user.

Syntax:

alter   user   user   name with  password  ‘xxxxxxx’ valid until  timestamp;

Explanation:

• In above syntax we use alter user or role statement where user name means specified user name with, password and valid until is a keyword to assign password and timestamp to the user.
• Timestamp means assigning a valid date of password to a specific user.

Example:

Code:

alter   user   sam with password 'sam123' valid until   '1 August 2020';

Explanation:

• In above example we use alter user statement, where sam is specified user name and we assign password to sam user with valid date of password. Here the assigned password is sma123 and valid until 1 August 2020.
• Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

How to Delete a Password for the User in PostgreSQL?

In this type, suppose we need to delete the password of the user. But when we delete the password it cannot protect data as well as the safe environment of the user at that time we use the following syntax.

Syntax:

alter  user   user name   with  password  null;

Explanation:

• In the above syntax we use an alter user or role statement.
• Where user name means specified user name, with and password is the keywords it is used to assign password to specific user in above syntax we use null keyword for assign null password.

Example:

Code:

alter user  sam  with  password  null;

Explanation:

• In the above statement where we use the alter user statement, sam is specified user name, where with and password is the keywords and null indicates password is null for sam user.
• Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

Conclusion

From the above article we saw basic syntax of create user, create password, change password and delete password. We also saw how we can implement them in PostgreSQL with different examples of each type. In this article we saw how we can maintain security of data and how we can maintain a safe environment. From this article we saw how we can handle User Passwords in PostgreSQL on the server.

This is a guide to PostgreSQL User Password. Here we discuss the introduction, how sort function works? how to create, change and delete password for users in PostgreSQL? You may also have a look at the following articles to learn more –

1. PostgreSQL IS NULL
2. PostgreSQL DDL
3. PostgreSQL ORDER BY Random
4. PostgreSQL Clone Database