keepcalculator

06 Numbers and Powers

How to Create a User in PostgreSQL: Complete Guide

Card No. 06·57318 · Updated · Standard formula, shown below

This guide walks you through creating a user (role) in PostgreSQL, from the basic CREATE USER command to setting passwords, privileges, and login permissions. You’ll learn the exact SQL statements and psql commands needed, plus how to verify your new user works. Before you begin, you need PostgreSQL installed and running, and you need access to a superuser account (usually postgres).

What You’ll Need

  • A running PostgreSQL server (local or remote)
  • Access to a superuser account (e.g., postgres) or an account with CREATEROLE privilege
  • The psql command-line tool (comes with PostgreSQL) or a GUI like pgAdmin
  • Basic familiarity with terminal/command prompt

Step-by-Step

  1. Connect to PostgreSQL as a superuser. Open your terminal and run:
    psql -U postgres
    You’ll be prompted for the postgres password. If you’re on Windows and haven’t set one, you might need to use psql -U postgres -h localhost or adjust your pg_hba.conf. On Linux/macOS, you may need to switch to the postgres system user first: sudo -u postgres psql.
  2. Check current roles (optional). To see existing users, run:
    \du
    This shows all roles and their attributes.
  3. Create a basic user. The simplest command is:
    CREATE USER myuser;
    This creates a role named myuser with no password, no login privilege by default (actually, CREATE USER implies LOGIN, unlike CREATE ROLE), and no other privileges.
  4. Create a user with a password. To set a password at creation time:
    CREATE USER myuser WITH PASSWORD 'secure_password';
    Replace secure_password with a strong password. Note: if you have password_encryption set to scram-sha-256 (default in PostgreSQL 14+), the password is stored hashed.
  5. Set specific attributes. You can add options like:
    CREATE USER myuser WITH PASSWORD 'secure_password' CREATEDB CREATEROLE;
    Common attributes include: SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, LOGIN, and CONNECTION LIMIT. For example, to limit connections to 5:
    CREATE USER myuser WITH PASSWORD 'secure_password' CONNECTION LIMIT 5;
  6. Alter an existing user. If you want to change a password or add attributes later, use ALTER USER:
    ALTER USER myuser WITH PASSWORD 'new_password';
    Or add CREATEDB:
    ALTER USER myuser CREATEDB;
  7. Grant privileges. Creating a user doesn’t give it access to databases. To grant access to a specific database:
    GRANT CONNECT ON DATABASE mydb TO myuser;
    Then, within that database, grant schema usage and table privileges. Connect to the database first:
    \c mydb
    Then:
    GRANT USAGE ON SCHEMA public TO myuser;
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser;
    You can also grant all privileges on a specific table: GRANT ALL ON mytable TO myuser;
  8. Verify the user works. Exit the superuser session (\q), then connect as the new user:
    psql -U myuser -d mydb -h localhost
    Enter the password when prompted. If you can connect and run queries, success!
  9. Delete a user (if needed). To remove a user, first drop any objects they own or reassign them, then:
    DROP USER myuser;
    If the user owns a database, you’ll need to drop or reassign it first.

Common Mistakes

  • Forgetting the password. Creating a user without a password means they can’t log in via TCP/IP (unless using trust auth). Always set a password for remote access.
  • Using CREATE ROLE instead of CREATE USER. CREATE ROLE does not add LOGIN by default, so the user can’t log in. Use CREATE USER or add LOGIN.
  • Not granting database access. A user can’t connect to a database unless they have CONNECT privilege on it. Also, they need schema usage and table privileges.
  • Setting SUPERUSER unnecessarily. Superuser bypasses all permission checks. Only grant it to trusted admin accounts.
  • Using weak passwords. PostgreSQL doesn’t enforce password strength by default. Use a long, random password.
  • Not quoting identifiers. If your username contains uppercase letters or spaces, you must quote it: CREATE USER "MyUser"; but then you must always quote it when connecting.

Tips

  • Use \du to list roles and \du+ to see more details like password expiry.
  • Set a password expiry for security: CREATE USER myuser WITH PASSWORD 'password' VALID UNTIL '2025-12-31';
  • Create a group role for easier privilege management: CREATE ROLE app_users; then GRANT app_users TO myuser; and grant privileges to the group.
  • Use \password in psql to change your own password without typing it in plain text.
  • Backup roles with pg_dumpall --roles-only to keep role definitions.
  • Test with a harmless query like SELECT current_user; to confirm the session.

Troubleshooting

  • “Role does not exist” error. You may have created the user in a different database cluster or the name is misspelled. Check with \du.
  • “Password authentication failed” error. The password is wrong, or the password_encryption setting changed. Reset with ALTER USER.
  • “Permission denied for database” error. You haven’t granted CONNECT on the database. Run the GRANT command as superuser.
  • “Permission denied for schema” error. Grant USAGE on the schema, and then grant table privileges.
  • “No pg_hba.conf entry for host” error. The server’s pg_hba.conf doesn’t allow connections from your IP or authentication method. Edit pg_hba.conf to add a line like host all myuser 0.0.0.0/0 scram-sha-256 and reload with SELECT pg_reload_conf();.
  • “Cannot drop role because some objects depend on it” error. Reassign or drop the objects first. Use REASSIGN OWNED BY myuser TO postgres; then DROP OWNED BY myuser;.

Frequently Asked Questions

  1. What is the difference between CREATE USER and CREATE ROLE? CREATE USER is the same as CREATE ROLE but with LOGIN privilege added by default. So CREATE USER can log in, CREATE ROLE cannot unless you specify LOGIN.
  2. How do I list all users in PostgreSQL? Run \du in psql, or query the pg_roles system catalog: SELECT rolname FROM pg_roles;
  3. How do I change a user’s password? Use ALTER USER username WITH PASSWORD 'new_password'; as a superuser, or the user can run \password in psql.
  4. How do I grant all privileges on a database to a user? You can’t grant all on a database directly. Grant CONNECT on the database, then grant USAGE on the schema, and then grant privileges on tables. Or use GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;.
  5. How do I make a user a superuser? Run ALTER USER username SUPERUSER; as a superuser. Be very careful—superusers bypass all permissions.
  6. Can I create a user without a password? Yes, but they can only log in if the authentication method allows it (e.g., trust or peer). For remote access, a password is required.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →