Creating a Role in Postgres Properly

Nhat Bui — Sun, 25 Sep 2022 13:10:51 +0000

Here's how to create a role that can log in, but don't give it a password:

CREATE ROLE nhatbui LOGIN;

And here is creating a role with a password that is valid until the end of 2022. After one second has ticked in 2023, the password is no longer valid:

CREATE USER nhatbui WITH PASSWORD 'st@bl3nhAt' VALID UNTIL '2023-01-01';

The syntax is fine. But what could possibly go wrong? Oh dear, your password will appear as plain text somewhere in the log file; it can be stolen in no easier way. To do it properly, we first create a role that can log in but don't give it a password:

CREATE ROLE nhatbui LOGIN;

Once the role nhatbui is created, we can then give it a password with this command (psql):

\password nhatbui

The terminal now will ask you to type in the new password. Just like other passwords, it will be hidden in the terminal. So just type without wondering. Here's what mine displays:

postgres=# \password nhatbui
Enter new password for user "nhatbui": 
Enter it again:

As far as I am aware, Postgres will hash the password before storing it, unless the supplied string is already hashed. So you don't have to hash it manually.

DEV Community: Nhat Bui

Creating a Role in Postgres Properly