PostgreSQL Basics: Creating a User with a Password

By /

Okay, here is the comprehensive article on PostgreSQL user creation, aiming for approximately 5000 words.

PostgreSQL Basics: A Deep Dive into Creating Users with Passwords

PostgreSQL, often simply called “Postgres,” stands as a titan in the world of open-source relational database management systems (RDBMS). Renowned for its robustness, extensibility, standards compliance, and vibrant community, it powers applications ranging from small personal projects to massive, mission-critical enterprise systems. A fundamental aspect of managing any database system, especially one as powerful as PostgreSQL, is controlling access. This is where user management comes into play.

Creating distinct users with appropriate permissions is not just good practice; it’s a cornerstone of database security, accountability, and operational integrity. It allows administrators to enforce the principle of least privilege, ensuring that users and applications only have access to the data and operations they absolutely need. This minimizes the potential damage from accidental errors, malicious attacks, or compromised application credentials.

This article provides an exhaustive guide to creating users (often referred to as roles in PostgreSQL terminology) with passwords in PostgreSQL. We will delve deep into the CREATE USER and CREATE ROLE commands, explore various user attributes and privileges, discuss password management best practices, demonstrate user creation through both SQL commands (using psql) and a popular graphical tool (pgAdmin), and cover related management tasks like altering, dropping, and listing users. Whether you’re a budding database administrator, a developer setting up an application database, or simply curious about PostgreSQL internals, this guide aims to equip you with a thorough understanding of this essential task.

Prerequisites

Before we embark on creating users, ensure you have the following:

  1. PostgreSQL Installed: A running PostgreSQL server instance is necessary. Installation methods vary depending on your operating system (Linux, macOS, Windows). Refer to the official PostgreSQL documentation or trusted guides for installation instructions specific to your platform.
  2. Superuser Access: To create new users (roles), you typically need to connect to the PostgreSQL server as a user with sufficient privileges. By default, the postgres user created during installation usually has superuser privileges (SUPERUSER attribute). You might also use another user account that has been granted the CREATEROLE privilege.
  3. Access Method: You need a way to interact with the PostgreSQL server. Common methods include:
    • psql: The standard command-line interface for PostgreSQL. It’s powerful, widely available, and will be used extensively in our examples.
    • Graphical Tools: Tools like pgAdmin, DBeaver, DataGrip, etc., provide a visual interface for database management, which many find more intuitive. We will demonstrate using pgAdmin.

Understanding Users and Roles in PostgreSQL

In PostgreSQL, the concepts of “user” and “group” are unified into a single entity: the role. When you talk about creating a user, you are technically creating a role. The distinction between a user and a group is primarily determined by the attributes assigned to that role.

  • Role: The fundamental entity representing an identity that can own database objects (like tables, schemas, functions) and have database privileges (like SELECT, INSERT, CONNECT).
  • User: Generally considered a role that has the LOGIN privilege. This means the role can be used to establish a connection to the database server.
  • Group: Often considered a role that does not have the LOGIN privilege. Its primary purpose is to group other roles (users or other groups) together to manage privileges collectively. A user can be granted membership in a group role, thereby inheriting the privileges granted to that group.

The CREATE USER Command:

The CREATE USER command is essentially an alias for the CREATE ROLE command. The key difference is that CREATE USER automatically grants the LOGIN privilege by default, whereas CREATE ROLE does not.

sql
CREATE USER username ... ;
-- is equivalent to:
CREATE ROLE username LOGIN ... ;

Throughout this article, we will primarily use the CREATE USER syntax when our intent is to create an entity that can log in, as it’s often more intuitive for those coming from other database systems. However, remember that under the hood, it’s all about roles and their attributes.

Why Create Dedicated Users? The Importance of Granular Access Control

Before diving into the syntax, let’s solidify why spending time creating and managing individual users is crucial:

  1. Security (Principle of Least Privilege): This is paramount. The superuser account (like postgres) has unrestricted power over the entire database cluster. Using it for regular application access or for multiple developers is extremely risky. If application credentials are compromised, an attacker gains full control. By creating dedicated users with only the specific permissions needed for their tasks (e.g., an application user that can only SELECT, INSERT, UPDATE, DELETE on specific tables in a specific schema), you drastically limit the potential blast radius of a security breach or an accidental error.
  2. Accountability and Auditing: When different applications or human users connect with distinct usernames, database logs (if enabled) can track who performed which actions. This is invaluable for troubleshooting, auditing security events, and understanding system usage patterns. If everyone uses the same generic account, determining who modified or deleted critical data becomes nearly impossible.
  3. Separation of Concerns: Different components of a larger system might require different levels of access. For example:
    • A web application might need read/write access to user data tables.
    • A reporting tool might only need read-only access to certain views or tables.
    • A data migration script might need temporary elevated privileges, like creating tables.
    • Database administrators need broader control for maintenance tasks.
      Creating separate users for each distinct function ensures clean separation and prevents unintended interference.
  4. Resource Management: PostgreSQL allows setting connection limits per user (CONNECTION LIMIT attribute). This can prevent a single user or application from consuming all available database connections, ensuring fairer resource allocation.
  5. Simplified Privilege Management (with Roles/Groups): While we focus on user creation here, the role concept allows creating “group roles.” You can grant necessary privileges to a group role and then grant membership in that group role to multiple user roles. This simplifies managing permissions for many users with similar access needs – update the group’s permissions, and all members inherit the changes.

Neglecting proper user management often leads to insecure, hard-to-manage, and fragile database environments.

The CREATE USER Command: Syntax and Breakdown

The fundamental SQL command to create a user capable of logging in is CREATE USER. Its basic syntax for creating a user with a password is:

sql
CREATE USER username WITH PASSWORD 'password';

Let’s break this down:

  • CREATE USER: The SQL keyword initiating the command to create a new database role with the LOGIN privilege implicitly enabled.
  • username: This is the identifier you choose for the new user. It must adhere to PostgreSQL’s identifier rules (typically starting with a letter or underscore, followed by letters, digits, or underscores). It’s generally recommended to use lowercase names, as PostgreSQL folds unquoted identifiers to lowercase by default. If you need uppercase or special characters, you must enclose the username in double quotes (e.g., "AppUser"). Avoid using SQL keywords as usernames.
  • WITH: A keyword used to introduce various options or attributes for the user being created.
  • PASSWORD: Specifies that you are setting a password for this user.
  • 'password': The actual password string, enclosed in single quotes.

Important Note on Passwords: Placing the password directly in the SQL command as a literal string ('password') is convenient for simple scripts or interactive sessions but is generally insecure. The command might be logged in server logs, shell history (.bash_history, .psql_history), or version control systems if part of a script. We will discuss more secure ways to handle passwords later.

Choosing a Username

Selecting appropriate usernames is a small but significant part of database hygiene:

  • Clarity: Choose names that reflect the user’s purpose (e.g., webapp_user, reporting_readonly, api_service, john_doe).
  • Consistency: Establish a naming convention within your organization or project (e.g., appname_role, service_identifier).
  • Avoid Ambiguity: Don’t use names that are easily confused with system roles or SQL keywords.
  • Case Sensitivity: While PostgreSQL folds unquoted identifiers to lowercase, it’s best practice to stick to lowercase letters, numbers, and underscores for unquoted identifiers to avoid potential confusion or the need for quoting. If you must use mixed case or special characters, always enclose the identifier in double quotes ("MyUser"). Remember that quoted identifiers are case-sensitive.

Password Management: Security and Best Practices

Passwords are the most common authentication method. Managing them securely is critical.

1. Password Strength:

  • Complexity: Enforce strong password policies. Passwords should be long (e.g., 12+ characters) and include a mix of uppercase letters, lowercase letters, numbers, and symbols. Avoid dictionary words, common names, dates, or easily guessable patterns.
  • Uniqueness: Do not reuse passwords across different systems or users.
  • Tools: Consider using password managers to generate and store strong, unique passwords.

2. How PostgreSQL Stores Passwords:

PostgreSQL never stores passwords in plain text. It stores a hashed representation. When a user attempts to log in, PostgreSQL hashes the provided password using the same algorithm and compares the result with the stored hash.

  • Hashing Algorithms: PostgreSQL supports different hashing methods, configured via the password_encryption parameter in postgresql.conf:
    • scram-sha-256: (Recommended) The default in modern PostgreSQL versions. Salted Challenge Response Authentication Mechanism using SHA-256. It’s resistant to dictionary and rainbow table attacks and doesn’t transmit the password hash directly over the network during authentication.
    • md5: An older method. It uses salt but MD5 itself is considered cryptographically weak for password hashing compared to modern standards. It might be needed for compatibility with very old clients.
  • Viewing Stored Hashes: Superusers can view the stored password hashes (not the plain text passwords) by querying the pg_shadow system catalog or more commonly via the pg_authid catalog (which pg_shadow builds upon). The rolpassword column contains the hashed password string, often prefixed with the method (e.g., SCRAM-SHA-256$... or md5...). Non-superusers cannot see these hashes.

3. Setting Passwords Securely:

As mentioned earlier, embedding plain-text passwords in SQL scripts is risky. Here are better approaches:

  • psql Password Prompt: When creating or altering a user in an interactive psql session, you can use PASSWORD NULL initially and then use the \password meta-command, which prompts securely for the password without echoing it or storing it in history:

    sql
    -- Connect as superuser in psql
    CREATE USER myappuser WITH PASSWORD NULL; -- Create user without setting password initially
    \password myappuser
    -- psql will prompt securely for the password twice

  • ALTER USER with Prompt: Similar to the above, you can create the user and then immediately alter them using \password.

  • Environment Variables (Use with Caution): For automated scripts, you might pass passwords via environment variables (e.g., PGPASSWORD). This is better than hardcoding but still has risks, as environment variables can sometimes be inspected by other processes on the system. Ensure the script runs in a controlled environment.

    “`bash

    Example in bash (use cautiously)

    export PGPASSWORD=’mysecretpassword’
    psql -U superuser -d dbname -c “CREATE USER myappuser WITH PASSWORD ‘$PGPASSWORD’;”
    unset PGPASSWORD # Unset immediately after use
    “`

  • Secrets Management Tools: For production environments and CI/CD pipelines, use dedicated secrets management tools (like HashiCorp Vault, AWS Secrets Manager, Azure Key Vault) to store and retrieve database credentials securely. Your application or script would fetch the password from the vault at runtime.

  • Encrypted Password: You can provide an already encrypted password string if you know the required format for md5 or scram-sha-256. This is less common for manual creation but might be used in provisioning tools.

    “`sql
    — Example with pre-computed MD5 hash (less common now)
    — CREATE USER user_md5 WITH PASSWORD ‘md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’;

    — Example with pre-computed SCRAM hash (complex to generate manually)
    — CREATE USER user_scram WITH PASSWORD ‘SCRAM-SHA-256$………….’;
    ``
    Using    PASSWORD ‘cleartext’or the\passwordprompt is generally preferred as PostgreSQL handles the hashing correctly based on thepassword_encryption` setting.

User Attributes (Privileges) Beyond LOGIN and PASSWORD

The CREATE USER (or CREATE ROLE) command allows specifying various attributes that define the user’s capabilities beyond just logging in. These are crucial for implementing the principle of least privilege.

Syntax with Attributes:

“`sql
CREATE USER username
[ [ WITH ] option [ … ] ]

where option can be:

  SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]  -- Obsolete spelling of IN ROLE
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]      -- Obsolete spelling of ROLE
| SYSID uid                   -- Ignored, for compatibility

“`

Let’s explore the most important attributes:

  • LOGIN / NOLOGIN:

    • LOGIN: Allows the role to connect to the database server. CREATE USER implies LOGIN by default.
    • NOLOGIN: Prevents the role from connecting. Useful for creating “group roles” intended solely for managing permissions. CREATE ROLE implies NOLOGIN by default.
    • Example: CREATE ROLE reporting_group NOLOGIN; (Creates a group role)
    • Example: CREATE USER api_service LOGIN PASSWORD '...'; (Explicitly stating LOGIN, though redundant with CREATE USER)

  • SUPERUSER / NOSUPERUSER:

    • SUPERUSER: Grants the role unrestricted privileges, bypassing all permission checks. This is extremely dangerous and should be granted very sparingly, typically only to the main administrative accounts. Superusers can read/write any data, drop databases, manage users, load C functions, etc. Compromise of a superuser account means total compromise of the PostgreSQL cluster.
    • NOSUPERUSER: (Default for CREATE USER and CREATE ROLE) Denies superuser privileges. This should be the setting for almost all users, especially application users.
    • Example: CREATE USER admin_user SUPERUSER PASSWORD '...'; (Use with extreme caution!)
    • Example: CREATE USER web_app NOSUPERUSER PASSWORD '...'; (Explicitly stating the default, good for clarity)

  • CREATEDB / NOCREATEDB:

    • CREATEDB: Allows the role to create new databases.
    • NOCREATEDB: (Default) Prevents the role from creating new databases.
    • Use Case: Useful for administrative users or specific roles responsible for setting up new database environments, but generally not needed for application users.
    • Example: CREATE USER db_creator CREATEDB PASSWORD '...';

  • CREATEROLE / NOCREATEROLE:

    • CREATEROLE: Allows the role to create, alter, and drop other roles. It also allows granting or revoking membership in roles. However, a role with CREATEROLE cannot create superusers or roles with replication privileges unless they are themselves a superuser. They also cannot drop superuser roles.
    • NOCREATEROLE: (Default) Prevents the role from managing other roles.
    • Use Case: Useful for delegating user management tasks without granting full superuser status.
    • Example: CREATE USER user_manager CREATEROLE PASSWORD '...';

  • REPLICATION / NOREPLICATION:

    • REPLICATION: Grants privileges required for streaming replication. The role must also have the LOGIN privilege. This role is used by standby servers or logical replication subscribers to connect to the primary/publisher server.
    • NOREPLICATION: (Default) Denies replication privileges.
    • Use Case: Essential for setting up physical or logical replication streams. Should only be granted to dedicated replication user accounts.
    • Example: CREATE USER replication_user REPLICATION LOGIN PASSWORD '...';

  • BYPASSRLS / NOBYPASSRLS:

    • BYPASSRLS: Allows the role to bypass Row-Level Security (RLS) policies. RLS is a feature where access to rows within a table can be restricted based on policies associated with the table and the current user.
    • NOBYPASSRLS: (Default) Enforces RLS policies for the role.
    • Use Case: BYPASSRLS is powerful and should be granted cautiously, typically only to administrative or backup roles that need to see all data, regardless of RLS policies. Application users should almost always have NOBYPASSRLS. Superusers and roles that own the table being queried inherently bypass RLS unless explicitly configured otherwise.
    • Example: CREATE USER backup_agent BYPASSRLS LOGIN PASSWORD '...';

  • CONNECTION LIMIT connlimit:

    • Specifies the maximum number of concurrent connections the user can make to the database server. The default is -1, meaning no limit.
    • Use Case: Prevents a single user or application instance from exhausting all available server connections (defined by max_connections in postgresql.conf). Useful for resource management and preventing denial-of-service scenarios (whether accidental or intentional).
    • Example: CREATE USER rate_limited_user CONNECTION LIMIT 10 PASSWORD '...';

  • VALID UNTIL 'timestamp':

    • Sets an expiration date and time for the password. After this timestamp, the password becomes invalid, and the user cannot log in using password authentication until a new password is set. If omitted, the password never expires.
    • The timestamp is specified as a string literal, e.g., '2024-12-31 23:59:59+00'. Time zones are respected.
    • Use Case: Enforcing password rotation policies for security compliance. Creating temporary accounts.
    • Example: CREATE USER temp_contractor PASSWORD '...' VALID UNTIL '2024-09-30';

  • IN ROLE role_name [, ...]:

    • Adds the newly created user as a member of one or more existing roles (typically group roles). This means the new user immediately inherits the privileges granted to those roles. The creator must have permissions to grant membership in the specified roles.
    • Example: CREATE ROLE developers NOLOGIN; GRANT SELECT ON TABLE projects TO developers; CREATE USER alice IN ROLE developers PASSWORD '...'; (Alice inherits SELECT privilege on projects table).

  • ROLE role_name [, ...]:

    • Grants membership in the new role to one or more existing roles. Essentially makes the specified existing roles members of the role being created.
    • Example: CREATE ROLE project_lead LOGIN PASSWORD '...'; CREATE USER bob PASSWORD '...'; CREATE ROLE project_members NOLOGIN ROLE project_lead, bob; (Both project_lead and bob become members of the project_members group role).

  • ADMIN role_name [, ...]:

    • Similar to IN ROLE, but also grants the ADMIN OPTION for the specified roles to the new user. This means the new user can grant or revoke membership in those roles to others.
    • Example: CREATE ROLE team_a NOLOGIN; CREATE USER team_a_admin ADMIN team_a PASSWORD '...'; (The team_a_admin user can now add/remove other users from the team_a role).

  • INHERIT / NOINHERIT:

    • INHERIT: (Default) Specifies that the role inherits privileges granted to roles it is a member of.
    • NOINHERIT: Prevents the role from automatically inheriting privileges from its member roles. The user would need to explicitly use SET ROLE role_name; within their session to assume the privileges of a specific role they are a member of.
    • Use Case: NOINHERIT can be used for finer-grained control, forcing users to explicitly elevate their privileges for specific tasks, but INHERIT is more common and often simpler to manage.
    • Example: CREATE USER special_ops NOINHERIT PASSWORD '...';

Combining Attributes:

You can combine multiple attributes in a single CREATE USER statement:

sql
CREATE USER app_user
WITH
NOSUPERUSER -- Essential security practice
NOCREATEDB
NOCREATEROLE
NOREPLICATION
NOBYPASSRLS
LOGIN -- Implicit with CREATE USER, explicit here for clarity
CONNECTION LIMIT 20
PASSWORD 'complex_password_here'
VALID UNTIL '2025-01-01';

This creates a typical application user: non-privileged, limited connections, with a password that expires.

Practical Examples: Creating Users

Let’s walk through creating users using both psql and pgAdmin.

Example 1: Using psql (Command-Line Interface)

Assume you are logged into your operating system terminal and can run psql.

Step 1: Connect to PostgreSQL as a Superuser

You typically connect to the default postgres database as the postgres user (or another user with CREATEROLE privileges).

“`bash

Replace ‘your_db_host’, ‘your_db_port’, ‘your_superuser’ if different

You might be prompted for the superuser password

psql -h your_db_host -p your_db_port -U your_superuser -d postgres
“`

If connecting locally and peer authentication is set up for the postgres user, you might use:

bash
sudo -u postgres psql -d postgres

You should now see the psql prompt (e.g., postgres=#).

Step 2: Create a Basic User with a Password

Let’s create a user named web_reporter intended for read-only access (permissions will be granted separately). We’ll set the password directly first (less secure, for demonstration) and then show the secure prompt method.

  • Method A: Password in Command (Less Secure)

    sql
    CREATE USER web_reporter WITH PASSWORD 'Str0ngP@ssw0rd1';

    Output:
    CREATE ROLE
    (Remember, CREATE USER results in CREATE ROLE)

  • Method B: Secure Password Prompt (Recommended)

    sql
    CREATE USER dashboard_user WITH PASSWORD NULL; -- Create user first
    Output:
    CREATE ROLE
    sql
    \password dashboard_user
    Output:
    Enter new password:
    Enter it again:
    Enter the desired password when prompted. It won’t be echoed to the screen or saved in psql history.

Step 3: Create a User with Specific Attributes

Let’s create an application user api_backend with a connection limit and membership in a hypothetical api_users group role (assuming api_users exists).

“`sql
— First, ensure the group role exists (if not already created)
CREATE ROLE api_users NOLOGIN;

— Now, create the user and add them to the group
CREATE USER api_backend
WITH
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
CONNECTION LIMIT 50
IN ROLE api_users
PASSWORD NULL; — Use NULL and \password for security

— Set password securely
\password api_backend
“`

Step 4: Verify User Creation

You can list existing roles (users) using the psql meta-command \du or by querying the pg_roles catalog.

sql
\du

Output (will vary based on your setup, but should include the new users):

List of roles
Role name | Attributes | Member of
-----------------+------------------------------------------------------------+-----------
api_backend | Connection limit 50 | {api_users}
api_users | Cannot login | {}
dashboard_user | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
web_reporter | | {}
... other roles ...

Alternatively, query pg_roles:

sql
SELECT rolname, rolcanlogin, rolsuper, rolcreaterole, rolcreatedb, rolconnlimit, rolvaliduntil
FROM pg_roles
WHERE rolname IN ('web_reporter', 'dashboard_user', 'api_backend');

Output:

rolname | rolcanlogin | rolsuper | rolcreaterole | rolcreatedb | rolconnlimit | rolvaliduntil
---------------+-------------+----------+---------------+-------------+--------------+---------------
web_reporter | t | f | f | f | -1 |
dashboard_user| t | f | f | f | -1 |
api_backend | t | f | f | f | 50 |
(3 rows)
(Note: t means true, f means false. rolvaliduntil would show a timestamp if set.)

Step 5: Exit psql

sql
\q

Example 2: Using pgAdmin (Graphical Interface)

pgAdmin is a popular open-source administration and development platform for PostgreSQL. The exact steps might vary slightly between pgAdmin versions, but the general process remains similar.

Step 1: Connect to Your PostgreSQL Server

  • Launch pgAdmin.
  • If you haven’t already, add your PostgreSQL server connection details (hostname, port, maintenance database (usually postgres), username (superuser like postgres), and password).
  • Connect to the server. You should see it listed in the Browser panel on the left.

Step 2: Navigate to Login/Group Roles

  • Expand the server node in the Browser panel.
  • Find the “Login/Group Roles” section (it might be directly under the server node).

Step 3: Create a New Login/Group Role

  • Right-click on “Login/Group Roles”.
  • Select “Create” -> “Login/Group Role…”.

Step 4: Define the User in the Dialog Box

A dialog box (“Create – Login/Group Role”) will appear with several tabs.

  • General Tab:

    • Name: Enter the desired username (e.g., gui_user).

  • Definition Tab:

    • Password: Enter the desired password for the user.
    • Confirm password: Re-enter the password.
    • Account expires: (Optional) Set an expiration date using the calendar picker if you want the password/account to expire (VALID UNTIL).
    • Connection limit: (Optional) Enter a number if you want to limit concurrent connections (CONNECTION LIMIT). Use -1 for no limit (default).

  • Privileges Tab: This is where you set the core attributes.

    • Can login?: Set to “Yes” for a user role (LOGIN). Set to “No” for a group role (NOLOGIN). This is the key differentiator mapped by CREATE USER vs CREATE ROLE. For creating a user, ensure this is “Yes”.
    • Superuser?: Set to “No” (default and recommended) unless absolutely necessary (NOSUPERUSER).
    • Create databases?: Set to “No” (default) unless needed (NOCREATEDB).
    • Create roles?: Set to “No” (default) unless needed (NOCREATEROLE).
    • Inherit privileges…?: Usually “Yes” (default, INHERIT).
    • Can initiate replication…?: Usually “No” (default, NOREPLICATION), unless it’s a replication user.
    • Bypass row level security?: Usually “No” (default, NOBYPASSRLS).

  • Membership Tab:

    • Member of: Use the dropdown/search to select existing group roles you want this new user to be a member of (IN ROLE).
    • Members: (Less common for user creation) Use this if you want other existing roles to become members of this new role (ROLE).

  • SQL Tab: (Optional but very useful!)

    • This tab shows the actual SQL command (CREATE ROLE ...) that pgAdmin will execute based on the options you selected in the other tabs. It’s a great way to learn the SQL syntax corresponding to the GUI actions.

Step 5: Save the User

  • Click the “Save” button.

pgAdmin will execute the corresponding CREATE ROLE command on the server. The new user (gui_user in this example) should now appear under “Login/Group Roles” in the Browser panel. You can right-click the user and select “Properties” to review or modify its settings later.

Connecting as the New User

Once a user is created with a password and the LOGIN privilege, you need to ensure they can actually connect. This involves two parts:

  1. User Existence and Password: Done via CREATE USER ... WITH PASSWORD ....
  2. Connection Permissions (pg_hba.conf): PostgreSQL uses a configuration file named pg_hba.conf (Host-Based Authentication) to control which users are allowed to connect from which client IP addresses, and how they must authenticate.

Even if a user exists with a valid password, if pg_hba.conf doesn’t have a matching rule allowing their connection attempt (based on connection type, database, username, client address, and authentication method), the connection will fail – often before the password check even happens.

Common pg_hba.conf Entry:

A typical entry allowing password authentication (scram-sha-256 or md5) for any user connecting to any database from any IP address might look like this (use with caution, restricting IPs is safer):

“`

TYPE DATABASE USER ADDRESS METHOD

host all all 0.0.0.0/0 scram-sha-256 # For IPv4
host all all ::/0 scram-sha-256 # For IPv6
“`

Or, more restrictively, allowing a specific user from a specific subnet:

“`

TYPE DATABASE USER ADDRESS METHOD

host mydatabase webapp_user 192.168.1.0/24 scram-sha-256
“`

  • scram-sha-256: Preferred modern password authentication method.
  • md5: Older password authentication method.
  • password: Sends the password in clear text (AVOID unless over SSL/TLS).
  • peer / ident: Used for local connections based on operating system username.
  • trust: Allows connection without a password (DANGEROUS, use only in trusted environments or for specific local cases).

Important: After modifying pg_hba.conf, you must reload the PostgreSQL server configuration for the changes to take effect. You can do this using:

sql
-- From psql connected as superuser:
SELECT pg_reload_conf();

or via system service commands (e.g., sudo systemctl reload postgresql).

Testing the Connection:

Try connecting using the newly created user and their password:

“`bash

Using psql

psql -h your_db_host -p your_db_port -U new_username -d target_database

You will be prompted for the password for ‘new_username’

“`

If the connection succeeds, the user exists, the password is correct, and pg_hba.conf allows the connection. If it fails, check the PostgreSQL server logs for detailed error messages (e.g., “password authentication failed for user…”, “no pg_hba.conf entry for host…”).

Granting Permissions: The Necessary Next Step

Creating a user allows them to connect (if LOGIN is granted and pg_hba.conf permits), but by default, a new user has very few privileges within any specific database besides the ability to connect and see some system catalog information. They typically cannot read data from tables, insert data, execute functions, or even connect to specific databases other than template ones unless explicitly allowed.

After creating a user, the next critical step is granting the necessary permissions using the GRANT command.

Common GRANT Examples:

  • Grant CONNECT privilege on a database:

    sql
    GRANT CONNECT ON DATABASE my_app_db TO web_reporter;

  • Grant USAGE privilege on a schema: (Allows accessing objects within the schema)

    sql
    GRANT USAGE ON SCHEMA public TO web_reporter;
    GRANT USAGE ON SCHEMA api_schema TO api_backend;

  • Grant specific privileges on a table:

    “`sql
    — Read-only access
    GRANT SELECT ON TABLE products TO web_reporter;
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO web_reporter; — Grant on all existing tables

    — Read/Write access
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO api_backend;
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA api_schema TO api_backend;
    “`

  • Grant execution privilege on a function:

    sql
    GRANT EXECUTE ON FUNCTION calculate_totals(int) TO api_backend;

  • Granting membership in a group role (alternative to IN ROLE at creation):

    sql
    GRANT api_users TO some_other_user;

Privilege management with GRANT and REVOKE is a vast topic in itself. The key takeaway here is that CREATE USER is only the first step; GRANT is required to make the user useful. Remember the principle of least privilege: grant only what is necessary.

Managing Existing Users

Database administration involves more than just creation.

1. Listing Users/Roles:

As shown before, use \du in psql or query pg_roles.

sql
\du
SELECT * FROM pg_roles;

2. Altering Users/Roles (ALTER USER / ALTER ROLE)

You can modify most attributes of a user after creation using ALTER USER (or ALTER ROLE).

  • Changing Password:

    “`sql
    — Method 1: Password in command (less secure)
    ALTER USER web_reporter WITH PASSWORD ‘NewSecur3P@ssword!’;

    — Method 2: Secure prompt (recommended in psql)
    \password web_reporter
    “`

  • Changing Attributes:

    “`sql
    — Add CREATEDB privilege
    ALTER USER db_creator WITH CREATEDB;

    — Remove CREATEDB privilege
    ALTER USER db_creator WITH NOCREATEDB;

    — Change connection limit
    ALTER USER api_backend WITH CONNECTION LIMIT 100;

    — Set password expiration
    ALTER USER temp_contractor VALID UNTIL ‘2024-10-31’;

    — Remove password expiration
    ALTER USER temp_contractor VALID UNTIL ‘infinity’;

    — Rename a user (requires superuser privilege)
    ALTER USER old_username RENAME TO new_username;
    “`

  • Adding/Removing Role Membership:

    “`sql
    — Add user ‘bob’ to group ‘developers’
    GRANT developers TO bob;

    — Remove user ‘alice’ from group ‘developers’
    REVOKE developers FROM alice;
    “`

3. Dropping Users/Roles (DROP USER / DROP ROLE)

To remove a user completely:

sql
DROP USER username;
-- or equivalently
DROP ROLE username;

Important Considerations when Dropping Users:

  • Ownership: You cannot drop a role if it still owns any database objects (tables, schemas, functions, databases, etc.) or if it has privileges granted on objects owned by others.
  • Dependency: You must first either drop the objects owned by the role, reassign their ownership to another role (REASSIGN OWNED BY old_role TO new_role;), or revoke the privileges granted to the role.

  • REASSIGN OWNED: This command is very useful for transferring ownership before dropping a user. It must be run in each database where the user might own objects.

    sql
    -- Connect to each database where 'user_to_drop' might own objects
    -- Run as superuser
    REASSIGN OWNED BY user_to_drop TO some_other_user_or_group;
    -- Then, you can drop the user
    DROP USER user_to_drop;

  • DROP OWNED: This command drops all objects owned by the specified role within the current database. Use with extreme caution as it permanently deletes data!

    sql
    -- Connect to database, run as superuser
    DROP OWNED BY user_to_drop; -- DANGEROUS! Verifies object ownership first.
    DROP USER user_to_drop;

Security Best Practices Summary

  • Principle of Least Privilege: Always grant the minimum privileges necessary. Avoid SUPERUSER for application or regular users. Use NOSUPERUSER, NOCREATEDB, NOCREATEROLE, NOBYPASSRLS by default.
  • Strong, Unique Passwords: Enforce complexity and uniqueness. Use secure methods (\password, secrets managers) to set passwords.
  • Use scram-sha-256: Ensure password_encryption is set to scram-sha-256. Configure pg_hba.conf to require it.
  • Restrict Connections (pg_hba.conf): Don’t blindly allow connections from 0.0.0.0/0 or ::/0 if you can restrict access to specific application server IPs or subnets.
  • Dedicated Users: Create separate users for different applications, services, and human administrators.
  • Role-Based Access Control (RBAC): Utilize group roles (NOLOGIN roles) to manage permissions for sets of users efficiently. Grant privileges to the group, and add/remove users from the group.
  • Regular Audits: Periodically review user accounts, their privileges, and pg_hba.conf settings. Remove unused accounts.
  • Password Rotation: Consider using VALID UNTIL or external policies to enforce password changes, especially for sensitive accounts.
  • Secure Application Credentials: Do not hardcode database passwords in application source code. Use configuration files with restricted permissions, environment variables carefully, or preferably, secrets management systems.
  • Use SSL/TLS: Encrypt database connections, especially over untrusted networks, to protect credentials and data in transit. Configure ssl = on in postgresql.conf and require SSL in pg_hba.conf (hostssl type).

Troubleshooting Common Issues

  • FATAL: password authentication failed for user "username":
    • Incorrect password provided.
    • User might not exist (typo in username).
    • pg_hba.conf might require a different method (e.g., peer) when scram-sha-256 or md5 was expected for the connection type/address. Check server logs for more detail.
  • FATAL: no pg_hba.conf entry for host "x.x.x.x", user "username", database "dbname", SSL off/on:
    • The combination of client IP address, username, and target database doesn’t match any host or hostssl/hostnossl line in pg_hba.conf.
    • Add or modify a line in pg_hba.conf to allow this specific connection.
    • Remember to SELECT pg_reload_conf(); after editing pg_hba.conf.
  • ERROR: role "username" already exists:
    • Attempting to create a user with a name that is already in use. Choose a different name or drop the existing role first if it’s obsolete.
  • ERROR: permission denied to create role:
    • The user attempting to run CREATE USER does not have the CREATEROLE privilege or SUPERUSER status. Connect as an appropriate administrative user.
  • ERROR: must be superuser to create superusers / ...create roles with replication privileges / ...bypassrls:
    • Trying to grant powerful privileges like SUPERUSER, REPLICATION, or BYPASSRLS without being a superuser yourself.
  • ERROR: role "username" cannot be dropped because some objects depend on it:
    • The user owns objects (tables, databases, etc.). Use REASSIGN OWNED or DROP OWNED (carefully!) before dropping the user. Check details in the error message for hints about dependent objects.
  • Connection successful, but permission denied for table/schema/...:
    • The user was created and authenticated successfully, but lacks the necessary GRANT permissions on the specific database objects they are trying to access. Grant CONNECT on the database, USAGE on the schema, and SELECT/INSERT/etc. on tables/views/functions as needed.

Conclusion

Creating users with passwords is a fundamental administrative task in PostgreSQL, essential for securing data, ensuring accountability, and managing database access effectively. While the basic CREATE USER username WITH PASSWORD 'password'; command is simple, a robust setup involves understanding the nuances of roles vs. users, leveraging various user attributes (NOSUPERUSER, CONNECTION LIMIT, etc.) to enforce the principle of least privilege, managing passwords securely, configuring host-based authentication (pg_hba.conf), and diligently granting specific object permissions using GRANT.

We’ve explored the syntax and options of CREATE USER, demonstrated creation via psql and pgAdmin, discussed critical security considerations, and covered related management tasks like altering and dropping users. By applying these concepts, database administrators and developers can build more secure, manageable, and reliable systems powered by PostgreSQL. Remember that user management is not a one-time setup but an ongoing process of review, adjustment, and adherence to best practices as your application and security needs evolve. Investing time in proper user setup from the beginning pays significant dividends in the long run.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top