The previous article explained the basic commands in the PostgreSQL Database. This article will explain how to set up a user in PostgreSQL.
Problem
How to manage a user in PostgreSQL?
Solution
By default, PostgreSQL runs MD5 encryption to save PostgreSQL user passwords in the pg_authid table. However, since PostgreSQL version 10 and later, PostgreSQL has added scram-sha-256 encryption, which is more secure compared to MD5 encryption. To use this encryption, you have to go to the /etc/postgresql/18/main/postgresql.conf file if you use PostgreSQL 18 and look for the word scram-sha-256 in the file, and open the fence located to the left of the word so it looks like the one below:
password_encryption = scram-sha-256
Then restart PostgresQL using the command:
sudo systemctl restart postgresql
A. List all users
To see all users in PostgreSQL, type the following command:
\du
By default, PostgreSQL uses the postgres user to access all databases in PostgreSQL.
B. Create a new user
To create a new user in PostgreSQL, use the format below on the PostgreSQL server:
create user username with encrypted password 'your-password';
For example, if you want to create a mydb_user user with the password qwerty, then use the command below:
create user mydb_user with encrypted password 'qwerty';
C. Change user password
To change the password in PostgreSQL, use the format below:
ALTER USER username WITH PASSWORD 'your_password';
For example, if you want to change the password for the mydb_user to q1w2e3, then use the command below:
ALTER USER mydb_user WITH PASSWORD 'q1w2e3';
D. Create a user privilege
To give the user privileges for a database in PostgreSQL using the format below:
GRANT permission_type ON db_name TO username;
For example, in the previous article, you created a mydb database, so use the command below to grant all permission types:
grant all privileges on database mydb to mydb_user;
If you want a user to only be able to view the mydb database for the sysadmin user, use the command below:
CREATE user john with encrypted password '123456';
GRANT CONNECT ON DATABASE mydb TO john;
\c mydb
GRANT USAGE ON SCHEMA public TO john;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO john;
To see the grants you have made in PostgreSQL, use the command:
\l+
E. Remove a user privilege
If you want to remove a privilege from a user, you can use the format below:
REVOKE permission_type ON table_name FROM user_name;
For example, if you want to remove a privilege for mydb_user, use the command below:
REVOKE all privileges on database mydb FROM mydb_user;
Or, use the below command if you want to revoke from user john:
REVOKE CONNECT on database mydb FROM john;
F. Delete a user
Use the format below to delete a user in PostgreSQL:
DROP USER username;
If you want to delete mydb_user, use the command below:
DROP user mydb_user;
You can see from the image above, there is an error when you want to delete a user. So, you have to remove some objects that depend on it. Use the command below:
REVOKE all privileges on database mydb FROM mydb_user;
and then delete the user using the below command:
DROP user mydb_user;
Note
If you want a user to only be able to access the PostgreSQL database for a certain time, for example, until January 31, 2030, use the command below:
ALTER USER mydb_user VALID UNTIL 'Jan 31 2030';
References
medium.com
datacamp.com
digitalocean.com
sentry.io
