Create User in Postgres: The Messy Truth About Roles and Permissions

Setting up a database shouldn't feel like a chore, but here we are. You've probably realized by now that the command to create user in postgres is actually a bit of a lie. Well, it's not a lie exactly, but it’s a nickname. In PostgreSQL, there is no fundamental difference between a "user" and a "group." They are both just "roles." If you’ve spent any time in the terminal lately, you know that missing a single semicolon or forgetting a LOGIN attribute can basically lock you out of your own workflow.

Honestly, the documentation for PostgreSQL is fantastic, but it's dense. It’s written by geniuses for geniuses. Most of us just want to get a web app running or give a junior dev read-only access without accidentally handing them the keys to the kingdom. We’re going to walk through how this actually works in the real world, past the "Hello World" examples.

Why the CREATE USER Command is Secretly Just CREATE ROLE

If you type CREATE USER alex; into psql, Postgres looks at that and thinks, "Okay, I'll run CREATE ROLE alex WITH LOGIN; for you." That’s the big secret. A "User" is just a role that has permission to log in. If you use CREATE ROLE instead, you’ll find yourself scratching your head wondering why your connection is being rejected. It’s because roles created that way don’t get the LOGIN attribute by default.

It’s a subtle distinction that trips up everyone. Every single person.

💡 You might also like: The Truth About the Duct Tape Airplane: Engineering Marvel or Just a Myth?

You've got to think about the environment you’re building. Are you in a production setting on AWS RDS? Or are you just spinning up a Docker container for a weekend project? The stakes are different, but the syntax remains largely the same. Let’s look at the basic syntax that actually works:

CREATE USER my_app_user WITH PASSWORD 'something_actually_secure';

Don't use 'password123'. Seriously. Postgres stores these in the pg_authid system catalog, and while it hashes them, you don't want to be the person who caused a leak because of a weak string.

Dealing with the create user in postgres Workflow

Permissions are where the real headache starts. You create the user, you think you’re done, and then—boom—Permission denied for table users. Creating the user is only step one of a three-step dance. You have the user, but they don't own anything, and they can't see anything.

The most common mistake is forgetting to grant usage on the schema. Even if you give a user SELECT rights on all tables, they can't even "walk" into the schema to see those tables unless you explicitly let them.

Granting the actual power

Usually, you'll want to do something like this:

1. First, create the role.
2. Then, grant it connection rights to the specific database.
3. Finally, give it permissions on the schema (usually public).

GRANT CONNECT ON DATABASE my_prod_db TO my_app_user;
GRANT USAGE ON SCHEMA public TO my_app_user;

If you're working in a team, stop creating individual users for every single task. It’s a nightmare to manage. Instead, create a "group" role. For example, create a role called read_only_access. Give that role the permissions. Then, when a new dev joins, you just create user in postgres for that specific person and "grant" them the group role.

GRANT read_only_access TO sarah;

🔗 Read more: Netflix Household Error on Laptop: Why It Happens and How to Fix It

It makes offboarding so much easier. When Sarah leaves for a better-paying job at a startup, you just revoke her role or drop her user. You don't have to go hunting through every table to see what specific permissions she had.

The Password Problem and MD5 vs SCRAM

Postgres 10 and 14 changed the game on how passwords are handled. Historically, everything was MD5. Nowadays, if you aren't using scram-sha-256, you’re basically living in the stone age of security. When you create user in postgres, the server checks the password_encryption setting.

If you’re trying to connect a legacy app and it keeps failing, check your pg_hba.conf file. This is the "bouncer" of the Postgres world. It decides who gets through the door based on their IP address and the method of authentication. You can create users all day long, but if pg_hba.conf is set to reject or is misconfigured for your subnet, nobody is getting in.

Dropping Users is Harder Than Creating Them

You’d think DROP USER alex; would be easy. It never is. Postgres is protective. It won't let you delete a user if they own even a single, tiny sequence or a random table in a corner of the database.

You’ll get an error: role "alex" cannot be dropped because some objects depend on it.

You have two choices here. You can either reassign everything they own to a different user, which is the responsible thing to do:
REASSIGN OWNED BY alex TO tech_lead;

Or, if you’re feeling aggressive and just want it gone:
DROP OWNED BY alex;
DROP USER alex;

👉 See also: 50 in onn roku tv Explained: What Most People Get Wrong About This Budget Screen

Be careful with DROP OWNED. It doesn't just drop the permissions; it can drop the actual objects if that user was the owner. I’ve seen people wipe out entire reporting tables because they were trying to delete a former intern’s account. It’s not a fun day.

Superusers and the "Least Privilege" Principle

It is incredibly tempting to just give every user SUPERUSER status so you don't have to deal with permission errors. Don't do it. A superuser bypasses all permission checks. They can read any data, delete any logs, and potentially execute shell commands on the underlying server if the configuration allows it.

Instead, look into INHERIT. By default, when you create user in postgres and add them to a group, they inherit the group's permissions. This is usually what you want. Occasionally, you’ll find a setup where NOINHERIT is used, which means the user has to explicitly "become" the other role using SET ROLE. It’s more secure but adds a lot of friction to your SQL queries.

Real-World Example: Setting Up a Read-Only Replica User

Let's say you're setting up a tool like Metabase or Tableau. You don't want these tools to ever be able to delete data.

CREATE USER analytics_service WITH PASSWORD 'very_secure_password_123';
ALTER USER analytics_service SET statement_timeout = '60s'; -- Keep those slow queries in check
GRANT CONNECT ON DATABASE production TO analytics_service;
GRANT USAGE ON SCHEMA public TO analytics_service;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_service;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics_service;

That last line is the kicker. ALTER DEFAULT PRIVILEGES. Without it, if you create a new table tomorrow, your analytics_service user won't be able to see it. You’d have to run the grant command again. This one line saves you from getting pings at 2 AM because a dashboard is broken.

Common Pitfalls to Keep an Eye On

Case sensitivity is a big one. In Postgres, if you create a user like this: CREATE USER "Alex"; (with double quotes), you have to use double quotes and that exact casing every time you refer to them. If you just do CREATE USER Alex;, Postgres converts it to lowercase alex. Just stay lowercase. It saves so much typing and frustration.

Another thing: public isn't always public. In newer versions of Postgres (specifically version 15 and later), the default permissions on the public schema have been tightened. You can no longer just create tables there as any user. You have to explicitly grant CREATE rights on the schema.

Actionable Next Steps for Managing Roles

If you’re currently staring at a terminal, here is how you should handle your next user setup:

• Check your current roles: Run \du in psql to see who already exists and what attributes they have (like Superuser or Create DB).
• Audit your password encryption: Run SHOW password_encryption; to ensure you are using scram-sha-256.
• Create a template role: Instead of granting permissions to people, create a developer_role or app_role and grant permissions to that.
• Use a password manager: Never keep database credentials in your .bash_history or in plain text files.
• Test the connection: Immediately after you create user in postgres, try to log in as that user from a different terminal window to verify it actually works. Use psql -U username -d databasename -h localhost.

Managing users is less about the command itself and more about the architecture of permissions you build around it. Start small, stay restrictive, and only grant power when it's absolutely necessary.