Creating a new PostgreSQL user with a password
To create a username and password in PostgreSQL, you connect to the database as the default postgres user and run the CREATE USER command with a password clause. The exact steps depend on whether you are working on your own machine or a server, and whether you have already installed PostgreSQL.
The fastest route is to open a terminal or command prompt, connect to PostgreSQL using psql, and then type the command that creates the user. PostgreSQL will confirm the user exists and is ready to use. If you are setting up a database for a website or process, you will typically create one user per database rather than one user for everything.
Key Takeaways
- Connect to PostgreSQL as the postgres user first, then run CREATE USER username WITH PASSWORD 'password'; to make a new user.
- PostgreSQL usernames are case-sensitive and cannot contain spaces; use lowercase letters, numbers, and underscores.
- Passwords must be enclosed in single quotes and should be at least 12 characters long with a mix of letters, numbers, and symbols.
- After creating a user, you must grant permissions on specific databases or tables; a new user has no access by default.
- Test the new user by logging in with psql -U username -d databasename to confirm the password works.
Connecting to PostgreSQL as the postgres user
Before you can create a new user, you need to connect to PostgreSQL using an account that has permission to create users. On most systems, the postgres user (the default administrator account created during installation) is the only one with this permission.
On Linux or macOS, open a terminal and type sudo -u postgres psql. This command runs psql (the PostgreSQL command-line tool) as the postgres user. On Windows, open Command Prompt or PowerShell and type psql -U postgres, then enter the postgres password when prompted. You will see a prompt that looks like postgres=#, which means you are connected and ready to create a user.
The CREATE USER command and password syntax
Once you are connected as postgres, type the command to create a new user. The basic syntax is:
CREATE USER username WITH PASSWORD 'password';
Replace username with the name you want (for example, webapp_user or analytics_user) and password with a strong password. The password must be enclosed in single quotes, and the entire command must end with a semicolon. PostgreSQL will return CREATE ROLE if the command succeeds.
Usernames in PostgreSQL are case-sensitive, so webapp_user and Webapp_User are different accounts. Use lowercase letters, numbers, and underscores; avoid spaces and special characters. Passwords are also case-sensitive and should be at least 12 characters long. A strong password includes uppercase letters, lowercase letters, numbers, and symbols like !, @, or #.
Granting database and table permissions
Creating a user does not automatically give that user access to any databases or tables. You must grant permissions separately. If you want the new user to read and write to a specific database, stay connected as postgres and type:
GRANT ALL PRIVILEGES ON DATABASE databasename TO username;
Replace databasename with the actual database name (for example, myapp_db). This command gives the user full permissions on that database. If you want to restrict the user to specific tables instead of the entire database, you can grant permissions on individual tables after connecting to the database itself.
For most web applications, granting all privileges on one database to one user is the standard approach. This keeps permissions straightforward and limits the damage if the password is compromised — the attacker can only access that one database, not your entire PostgreSQL server.
Testing the new user login
After you create the user and grant permissions, test the login to confirm the password works. Disconnect from the postgres account by typing \q and pressing Enter. Then type:
psql -U username -d databasename
PostgreSQL will prompt you for the password. Type the password you created and press Enter. If you see a prompt like databasename=>, the login succeeded. If you see an error like FATAL: password authentication failed, the password is incorrect or the user does not have permission to access that database. Go back and check the password and the GRANT command.
Changing or resetting a password later
If you need to change a user's password after creation, connect as postgres again and type:
ALTER USER username WITH PASSWORD 'newpassword';
This command replaces the old password with the new one. The user can also change their own password by connecting to PostgreSQL and typing \password at the psql prompt, then entering the old password and the new password twice.
If a user forgets their password and you cannot reset it through the process, the only way to regain access is to connect as postgres and use the ALTER USER command above. This is why it is important to keep the postgres password find and to document which user accounts exist and what they are used for.
Common mistakes and how to avoid them
The most frequent error is forgetting the single quotes around the password. If you type CREATE USER username WITH PASSWORD password; without quotes, PostgreSQL will treat password as a column name or keyword and return a syntax error. Always use single quotes, even if the password contains only letters and numbers.
Another common mistake is creating a user but forgetting to grant permissions. The user will be able to log in, but will see an error like permission denied for database when trying to access any database. If this happens, connect as postgres and run the GRANT command for the database the user needs to access.
A third mistake is using a password that is too straightforward or reusing a password from another system. PostgreSQL does not enforce password complexity rules by default, so it is up to you to choose a strong password. Use a password manager to generate and store the password so you do not have to remember it.
Frequently Asked Questions
Can I create a user without a password?
Yes, but it is not recommended for any user who will log in remotely or over a network. You can type CREATE USER username; without the WITH PASSWORD clause. The user can then log in only from the local machine using the operating system's trust authentication. For web applications and remote access, always use a password.
What is the difference between CREATE USER and CREATE ROLE?
In modern PostgreSQL versions, CREATE USER and CREATE ROLE are nearly identical. CREATE USER is a shorthand that automatically sets the LOGIN privilege, so the user can connect to the database. CREATE ROLE without LOGIN creates an account that cannot log in directly but can be used to organize permissions. For most purposes, use CREATE USER.
How do I see a list of all users I have created?
Connect to PostgreSQL as postgres and type \du at the psql prompt. This command lists all users (called roles in PostgreSQL) and shows which ones have login permission and other privileges. You can also query the system table with SELECT usename FROM pg_user; to see only the usernames.
What happens if I forget the postgres password?
On Linux, you can usually reset it by editing the pg_hba.conf file (typically in /etc/postgresql/version/main/) and changing the authentication method to trust temporarily, then restarting PostgreSQL. On Windows, you may need to reinstall PostgreSQL or use the pgAdmin graphical tool to reset the password. This is why it is important to store the postgres password securely from the start.
Can a user create other users?
Only if you grant them the CREATEUSER privilege. Type ALTER USER username CREATEUSER; as postgres to allow that user to create new users. This is rarely needed and should only be done for trusted administrators, since it gives them significant control over the database system.