Creating PostgreSQL Users & Databases

When you install PostgreSQL (a popular open-source relational database), it ships with a single all-powerful account and an empty slate. Before any real app can connect, you need to create a database to hold its tables and a user that account is allowed to log in as. The trick most tutorials skip is doing this with least privilege — giving each app only the access it actually needs, so a leaked password cannot wipe your whole server. This page shows you exactly how to create users, databases, and grant the right privileges on Ubuntu.

Roles vs users — what’s the difference?

In PostgreSQL there is technically only one concept: a role. A role is a named identity that can own objects and hold permissions. A user is just a role that has the LOGIN attribute (meaning it is allowed to connect and authenticate). Older versions had separate CREATE USER and CREATE GROUP commands; today they are both aliases for CREATE ROLE with different defaults.

Term	What it really is	Can log in?	Typical use
Role	The base identity object	Depends on LOGIN flag	Generic term for both below
User	A role with LOGIN	Yes	An app or a person connecting
Group role	A role without LOGIN	No	A bundle of privileges you grant to other roles

CREATE USER is exactly the same as CREATE ROLE ... LOGIN. CREATE ROLE on its own creates a role that cannot log in. If your new user gets “role is not permitted to log in”, you created a role without LOGIN.

When to use a group role: when several users need the same permissions (e.g. three app servers all needing read access to a reporting database). You grant the privileges once to a group role, then add each user to that group. When NOT to: a single app with a single login does not need a group.

Connecting as the superuser first

PostgreSQL installs a Linux system user called postgres and a matching database superuser of the same name. By default you switch to that account to administer the server. There is no password — you authenticate by already being the postgres Linux user (this is called peer authentication).

sudo -i -u postgres
psql

Output:

psql (16.3 (Ubuntu 16.3-1.pgdg24.04+1))
Type "help" for help.

postgres=#

The postgres=# prompt means you are connected as the superuser. The # (instead of >) confirms you have superuser rights.

Creating a user (role) with a password

Inside psql, create a login role and give it a password. Always quote the password with single quotes.

CREATE ROLE shop_app WITH LOGIN PASSWORD 'S3cure-Pass-Change-Me';

Output:

CREATE ROLE

A few useful attributes you can add:

Attribute	Meaning	When to use
LOGIN	Role may connect	Always, for app/human accounts
NOLOGIN	Role may not connect	Group roles only
CREATEDB	May create new databases	Rarely — keep off for app users
CREATEROLE	May create other roles	Admin accounts only
SUPERUSER	Bypasses all permission checks	Never for app users
CONNECTION LIMIT 20	Max simultaneous connections	Cap a noisy app
VALID UNTIL '2026-12-31'	Password expiry date	Temporary/contractor access

Never give an application account SUPERUSER or CREATEROLE. If that one password leaks, the attacker owns your entire database cluster. Least privilege means the app user can touch its own database and nothing else.

Creating a database with an owner

Now create a database and make your new role its owner. The owner can create, alter, and drop any object inside that database — which is exactly what an app needs for its own schema.

CREATE DATABASE shop OWNER shop_app;

Output:

CREATE DATABASE

If you created the database first as postgres and want to hand it over later, you can re-assign ownership:

ALTER DATABASE shop OWNER TO shop_app;

Granting privileges (least privilege in practice)

If the app user owns the database, it already has full control inside it, so no extra GRANT is needed. The GRANT command matters when you want a second user with limited access — for example a read-only reporting user.

Connect to the specific database first (privileges on tables are per-database):

\c shop

Create a read-only role and grant it just enough to query existing and future tables:

CREATE ROLE shop_readonly WITH LOGIN PASSWORD 'ReadOnly-Pass-Change-Me';

GRANT CONNECT ON DATABASE shop TO shop_readonly;
GRANT USAGE ON SCHEMA public TO shop_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO shop_readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO shop_readonly;

Output:

CREATE ROLE
GRANT
GRANT
GRANT
ALTER DEFAULT PRIVILEGES

The last ALTER DEFAULT PRIVILEGES line is the part tutorials forget: a plain GRANT SELECT ON ALL TABLES only covers tables that exist right now. Default privileges make sure tables created later by the owner are also readable by the reporting user.

Common privilege keywords:

Privilege	Lets the role
CONNECT	Open a connection to the database
USAGE	Access objects in a schema
SELECT	Read rows
INSERT, UPDATE, DELETE	Modify rows
ALL PRIVILEGES	Everything (use sparingly)

To revoke later: REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM shop_readonly;

Doing it from the shell with createuser / createdb

PostgreSQL ships wrapper commands so you can skip psql entirely. Run them as the postgres system user. The -P flag prompts for a password; -O sets the owner.

sudo -u postgres createuser --pwprompt --no-createdb --no-createrole shop_app
sudo -u postgres createdb --owner=shop_app shop

Output:

Enter password for new role:
Enter it again:

These are equivalent to the SQL above and are handy inside provisioning scripts.

Verifying your setup

List roles and databases to confirm everything exists. Inside psql, \du lists roles and \l lists databases.

\du

Output:

                                   List of roles
  Role name    |             Attributes
---------------+------------------------------------------------
 postgres      | Superuser, Create role, Create DB, Replication
 shop_app      |
 shop_readonly |

Test the new login from a normal shell (not as postgres):

psql "postgresql://shop_app:S3cure-Pass-Change-Me@localhost:5432/shop" -c "SELECT current_user, current_database();"

Output:

 current_user | current_database
--------------+------------------
 shop_app     | shop
(1 row)

Best practices

• Give each application its own user and its own database — never share one login across services.
• Make the app user the owner of its database and grant it nothing beyond that; no CREATEDB, CREATEROLE, or SUPERUSER.
• Use a separate read-only role (with ALTER DEFAULT PRIVILEGES) for analytics, dashboards, and BI tools.
• Use strong, unique passwords and store them in a secrets manager or environment variable — never commit them to Git.
• Prefer ALTER ROLE ... VALID UNTIL for temporary/contractor access so credentials expire automatically.
• Connect to the correct database with \c dbname before running GRANT on tables — table privileges are per-database, not cluster-wide.

Related Topics

• Installing PostgreSQL on Ubuntu
• What is PostgreSQL?
• Allowing remote access to PostgreSQL
• PostgreSQL backup & restore
• Connecting to a database from your app