What is the difference between a user and a role in PostgreSQL?

In PostgreSQL, a user is simply a role that has been granted the LOGIN privilege. The CREATE USER command is an alias for CREATE ROLE ... WITH LOGIN . This unified role system is what makes PostgreSQL's permission management so powerful and flexible.

How can I see a list of all users in my PostgreSQL database?

You can list all users (roles with login privileges) by connecting to your database with the psql command-line tool and running the \du command. Alternatively, you can query the system catalog directly with SELECT usename FROM pg_catalog.pg_user; .

What should I do if `DROP USER` fails because the user owns objects?

If DROP USER fails, you must first reassign ownership of all objects owned by that user to another role. You can do this by connecting to each relevant database and running REASSIGN OWNED BY user_to_delete TO new_owner; . After reassigning ownership, you can safely drop the user.

How do I grant a user permission to only specific tables?

To grant specific table permissions, use the GRANT command. For example, GRANT SELECT, INSERT ON my_table TO my_user; . For better long-term management, it is recommended to grant these permissions to a group role, and then grant membership of that role to your user.

A Developer's Guide to PostgreSQL User Management: CREATE, ALTER, DROP

As developers, particularly when building robust back-ends with frameworks like Laravel, we know that database security is non-negotiable. A critical layer of that security is proper user and permission management. In PostgreSQL, this is handled through a powerful and flexible role-based system. Let's dive into how to manage PostgreSQL users effectively, ensuring your architecture is both secure and maintainable.

The Foundation: Users are Roles

Before we write any SQL, it's essential to understand a core PostgreSQL concept: a "user" is fundamentally a "role" that has the LOGIN privilege. The CREATE USER command is just convenient shorthand for CREATE ROLE ... WITH LOGIN. Grasping this will help you design a much cleaner permission structure.

Creating a New User (`CREATE USER`)

When setting up a new application or service, you'll need a dedicated database user. Always follow the principle of least privilege—grant only what is absolutely necessary. Avoid using the default postgres superuser for your application connections.

To create a new user, the CREATE USER command is your starting point. Always specify WITH ENCRYPTED PASSWORD to ensure the password is not stored in plain text.

-- Creates a basic user for a web application
CREATE USER my_app_user WITH ENCRYPTED PASSWORD 'a-very-strong-and-secret-password';

For more administrative roles, you can grant additional capabilities directly at creation time. However, be cautious with these privileges.

-- Creates a user who can create databases but not other roles
CREATE USER db_manager WITH ENCRYPTED PASSWORD 'another-secure-password' CREATEDB;

Modifying an Existing User (`ALTER USER`)

Needs change. Passwords need rotating, and privileges need updating. The ALTER USER command is the tool for these modifications. It's a versatile command for ongoing database administration.

-- To update a user's password (a common security practice)
ALTER USER my_app_user WITH ENCRYPTED PASSWORD 'a-new-stronger-password';
 
-- To grant a privilege after creation
ALTER USER my_app_user CREATEDB;
 
-- To revoke a privilege
ALTER USER my_app_user NOCREATEDB;
 
-- To rename a user, for example, if a role's purpose changes
ALTER USER my_app_user RENAME TO legacy_app_user;

Deleting a User (`DROP USER`)

When a user is no longer needed, it's crucial to remove it to minimize the security footprint. The DROP USER command handles this, but with an important safeguard.

DROP USER legacy_app_user;

You cannot drop a user that owns any database objects (like tables, schemas, or functions). This is a common hurdle. If you try, PostgreSQL will return an error. The correct procedure is to first reassign the ownership of those objects.

-- Connect to the database where the user owns objects
\c my_database;
 
-- Reassign all objects owned by the user to another role, like the superuser
REASSIGN OWNED BY legacy_app_user TO postgres;
 
-- Now, the user can be dropped safely
DROP USER legacy_app_user;

Best Practice: Decouple Permissions with Group Roles

For a truly clean and scalable architecture—something we all strive for—avoid granting permissions directly to users. Instead, create a "group role" that holds the permissions, and then grant membership in that group to your users.

This decouples the who (the user) from the what (the permissions). When you need to add a new application user, you just grant them membership to the appropriate group instead of re-applying dozens of GRANT statements.

-- 1. Create a group role (no login privilege needed)
CREATE ROLE web_app_permissions;
 
-- 2. Grant the necessary permissions to this group role
GRANT CONNECT ON DATABASE my_database TO web_app_permissions;
GRANT USAGE ON SCHEMA public TO web_app_permissions;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO web_app_permissions;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO web_app_permissions;
 
-- 3. Create your application user
CREATE USER my_app_user WITH ENCRYPTED PASSWORD 'a-very-strong-and-secret-password';
 
-- 4. Grant membership in the group role to the user
GRANT web_app_permissions TO my_app_user;

By adopting this role-based strategy, your PostgreSQL user management becomes an extension of your application's clean architecture, making it more secure, maintainable, and easier to reason about as your project grows.