Granting and Revoking Privileges
Learn how to grant and revoke privileges from users and groups.
Database user
In PostgreSQL, a database user is an account that’s used to connect to a PostgreSQL database. A user is identified by a name and can have a password, permissions, and other attributes that control how the user can access and interact with the database.
Creating a database user
Creating a database user in PostgreSQL is a simple and necessary task for managing access to our databases. We use the CREATE USER command followed by the username (of the user) we wish to create.
CREATE USER <username>;
Here, we replace <username> with the username (of the new user) we wish to create. The default password for the new user will be null, and it must be set before the user can access the database.
CREATE USER developer;\du
The code above will create a new user called developer. We can view all the existing users using the \du command.
We can also specify a password for a user (while creating a new user) by using the WITH PASSWORD option.
CREATE USER <username> WITH PASSWORD '<password>';
Here, <username> can be replaced with the username of the user we wish to create and <password> will be replaced with the desired password for that user. We specify a password by adding the WITH PASSWORD option followed by the desired password in single quotes.
CREATE USER developer WITH PASSWORD 'p@ssw0rd';SELECT rolname, rolpassword FROM pg_authid;
Line 1: The CREATE USER command creates a new user called newUser, with the password p@ssw0rd (without quotes).
Line 3: The SELECT statement is used to view all the users and their respective passwords (encrypted) in the PostgreSQL database. The pg_authid table contains the information about user and database ...