create-a-user-or-role.md

Create A User Or Role

Category: Postgres

For this TIL, a user called orbiks_dbsvc will be created using PSQL.

Do not create a user from the command line using plaintext passwords.

Create the user

CREATE USER orbiks_dbsvc PASSWORD 'b@dpa55w0rd';

The above command is identical to:

CREATE ROLE orbiks_dbsvc WITH LOGIN PASSWORD 'b@dpa55w0rd';

Create a user with an account valid until a specified date and a limit of 50 database connections:

CREATE USER orbiks_dbsvc
ENCRYPTED PASSWORD 'b@dpa55w0rd' 
VALID UNTIL '2023-12-31'
CONNECTION LIMIT 50;

Delete a user:

DROP USER orbiks_dbsvc;

Password hashing methods

Password should be hashed using the SCRAM-SHA-256 algorithm. This is a challenge-response scheme that prevents password sniffing on untrusted connections and supports storing passwords on the server in a cryptographically hashed form that is considered to be secure.

Ensure that postgresql.conf uses the following setting:

password_encryption = scram-sha-256

Following user creation, GRANT access permissions using the principle of least privilege.