Creating Your First Database User and Schema in PostgreSQL

The first time you use PostgreSQL for a project, it is tempting to connect as postgres and create tables. It works. It also means every program you write has the keys to the whole server, and the permission errors that would show up in production never show up on your machine. The better habit costs three statements: a role for the project, a database that role owns and, if you like, a schema inside it. This article walks through them, explains roles and schemas in the PostgreSQL sense, and covers the permission change in version 15 that surprises people who learned the older behavior. It is one of the setup steps in setting up PostgreSQL for development without fighting it and comes after installing PostgreSQL on macOS, Linux and Windows.

Roles, not users

PostgreSQL has one concept for accounts and groups: the role. A role may have the LOGIN attribute (so it can connect), may own objects, may have privileges and may be a member of other roles. CREATE USER is an alias for CREATE ROLE ... LOGIN, and CREATE GROUP is an alias for CREATE ROLE without login. You will see all three in tutorials; they create the same thing with different defaults.

A superuser role bypasses all permission checks. The role named postgres is a superuser created at installation. Use superuser roles for administration only.

Connect as the superuser

You need an account that can create roles. Depending on the platform, that is sudo -u postgres psql (Debian, Red Hat family), psql postgres (Homebrew, where your own account is the superuser) or the account and password you chose in the Windows installer or the POSTGRES_USER of a Docker container.

sudo -u postgres psql

At the postgres=# prompt you can create roles. The # signals a superuser; an ordinary role shows >.

Create the role

CREATE ROLE shop LOGIN PASSWORD 'dev-only-password';

LOGIN allows connections. The password is stored as a hash (SCRAM-SHA-256 by default in current versions). Writing the password in a statement puts it in your psql history and possibly in the server log if statement logging is on. To keep it out, create the role without a password and set it with the \password meta-command, which prompts and sends the hash:

CREATE ROLE shop LOGIN;
\password shop

Useful attributes at creation: CREATEDB lets the role create databases (handy for a test runner), CREATEROLE lets it create other roles (avoid for application roles), NOSUPERUSER is the default, CONNECTION LIMIT 20 caps simultaneous sessions, and VALID UNTIL '2027-01-01' makes a password expire. Do not give application roles SUPERUSER.

List roles with \du:

                                   List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 shop      |

Create the database

CREATE DATABASE shop OWNER shop;

The owner has full control of the database's contents and can drop it. One database per project is the norm, and it isolates data and settings. Clients connect to a specific database, and a connection cannot query across databases without extra tooling.

You can add ENCODING 'UTF8' and a locale; the defaults from template1 are usually right. Check with \l:

                                 List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |   Ctype
-----------+----------+----------+-----------------+-------------+-------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8
 shop      | shop     | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8

Connect as the new role

Leave the superuser session (\q) and connect as the project role.

psql -h localhost -U shop -d shop

Note the -h localhost. Without it, psql uses the local socket, where Debian and Red Hat family systems apply peer authentication by default and will reject the role shop unless your operating system user is also shop (fixing peer authentication failed for user postgres explains why). Over TCP, the password applies.

Check who you are and where:

SELECT current_user, current_database(), current_schema();

Schemas

A schema is a namespace inside a database: a container for tables, views, functions and types. The same table name can exist in two schemas. Every database starts with a schema called public. Tables are referred to as schema.table, and unqualified names are resolved through the search path.

SHOW search_path;
-- "$user", public

The default search path first looks for a schema named after the current role (shop), then public. If a schema shop exists, unqualified CREATE TABLE orders creates the table there, because it is the first schema in the path that exists.

Create a schema owned by the project role:

CREATE SCHEMA app AUTHORIZATION shop;
ALTER ROLE shop SET search_path = app, public;

Reconnect for the setting to apply (role-level settings apply to new sessions). Now CREATE TABLE orders (...) in a shop session goes into app. Using a named schema keeps your objects apart from extensions' objects in public and gives you something to grant permissions on. For a single-application database, public is fine, with the caveat below.

The change in PostgreSQL 15

Before version 15, every role could create tables in the public schema of any database it could connect to. That was convenient and a security weakness: a shared database invited objects from anyone. From version 15 the privilege is removed: CREATE on public is no longer granted to PUBLIC (everyone), and the database owner owns public.

The consequence for beginners: if you create a role, connect to a database you do not own, and run CREATE TABLE t (...), you see:

ERROR:  permission denied for schema public

Three ways to resolve it. Make the role the owner of the database (so it owns public, as in CREATE DATABASE shop OWNER shop). Create the role its own schema and use that. Or, as a superuser or the owner, grant the privilege deliberately:

GRANT USAGE, CREATE ON SCHEMA public TO shop;

Prefer the first two. If you upgraded a database from an older version, the old permissive grant may still be in place; check with \dn+.

Grant access to other roles

Roles can read the project's data without owning it. A read-only role for reporting:

CREATE ROLE reporting LOGIN;
GRANT CONNECT ON DATABASE shop TO reporting;
GRANT USAGE ON SCHEMA app TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO reporting;
ALTER DEFAULT PRIVILEGES FOR ROLE shop IN SCHEMA app GRANT SELECT ON TABLES TO reporting;

The first three give the role entry; GRANT SELECT ON ALL TABLES covers tables that exist now; ALTER DEFAULT PRIVILEGES covers tables the owner creates later, which is the step people forget. Check privileges with \dp (table privileges) and \ddp (default privileges).

Production-shaped separation

In production, one role often owns the schema and runs migrations, and a different role with narrower rights serves the application: it can read and write rows but cannot alter tables. Rehearse that in development:

CREATE ROLE shop_migrator LOGIN;
CREATE ROLE shop_app LOGIN;
ALTER SCHEMA app OWNER TO shop_migrator;
GRANT USAGE ON SCHEMA app TO shop_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO shop_app;
ALTER DEFAULT PRIVILEGES FOR ROLE shop_migrator IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO shop_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO shop_app;

Running the application as the narrow role locally catches the "permission denied for table" error before a deployment does.

Dropping and cleaning up

To remove a development setup: drop the database first, then the role. A role cannot be dropped while it owns objects or holds privileges; DROP OWNED BY shop; removes its objects and revokes its grants in the current database, then DROP ROLE shop;.

DROP DATABASE IF EXISTS shop;
DROP ROLE IF EXISTS shop;

DROP DATABASE fails if someone is connected; terminate sessions with SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'shop'; or, in recent versions, DROP DATABASE shop WITH (FORCE);.

Common mistakes

Creating the database as the superuser without an owner. The superuser then owns it, and your role cannot create tables. Add OWNER, or ALTER DATABASE shop OWNER TO shop;.

Case and quoting. CREATE ROLE Shop creates shop, since unquoted identifiers fold to lowercase; CREATE ROLE "Shop" creates a different, case-sensitive role that you must quote forever. Use lowercase names.

Reserved words. A role or table called user or order needs quoting. Avoid them.

Passwords in scripts. Fine for disposable local roles; never reuse them anywhere that matters.

When one role is not enough

One role is enough for a personal project or a prototype. It stops being enough when more than one application or person touches the database, when you separate migrations from runtime or when you give analysts read-only access. Then use several roles and group roles (roles with NOLOGIN that hold privileges, granted to login roles with GRANT group TO user). For the full model, the manual's chapters on database roles, privileges and schemas are the reference, and connecting to PostgreSQL from the command line with psql shows how to use the new role.