PostgreSQL: list databases, create users and grant access in psql

Short answer: open psql as the superuser with sudo -u postgres psql, then type \l to list databases and \du to list users. Create a login user with CREATE USER app WITH PASSWORD '...'; and a database it owns with CREATE DATABASE app OWNER app;. \c app switches to that database, \dt lists its tables and \q quits.

sudo -u postgres psql

-- list databases, then users (roles)
\l
\du
CREATE USER app WITH PASSWORD 'a-long-random-password';
CREATE DATABASE app OWNER app;
-- connect to it and list its tables
\c app
\dt
\q

Everything here follows the PostgreSQL 18 documentation (18 is the newest stable major release as of October 2026, with 19 at release-candidate stage, per postgresql.org) and works the same on 14, 15, 16 and 17 unless a version is named. Installing the server itself, remote access and tuning are in installing PostgreSQL on Ubuntu and Debian; this page is about the databases and users once it runs.

Cheat sheet: psql commands and their SQL equivalents

Commands that start with a backslash are psql meta-commands: the client turns them into catalog queries, so they work only inside psql. The SQL column works from any client, driver or GUI tool.

How do I open psql as the postgres user?

A fresh install has one superuser role, postgres, and no password for it. On Debian, Ubuntu and Rocky it logs in through peer authentication on the local socket: PostgreSQL asks the kernel which Linux user is connecting and lets it in only as the role of the same name. So you become the Linux user postgres first:

sudo -u postgres psql             # interactive
sudo -u postgres psql -c '\l'      # one command, then exit

The prompt shows the database you are in and what you are: postgres=# with a hash for a superuser, app=> for an ordinary role. Running plain psql as root fails with FATAL: role "root" does not exist, and logging in as an application role from your own shell account fails with Peer authentication failed; the peer authentication section of the install guide explains both. For an application role, connect over TCP, where the Debian and Ubuntu defaults ask for a password: psql -h 127.0.0.1 -U app app. Rocky’s initial configuration uses ident there instead; the Rocky section shows the change.

How do I list databases in PostgreSQL?

Inside psql, \l (long form \list) prints every database in the cluster. From the shell, psql -l prints the same list without an interactive session:

                    List of databases
   Name    |  Owner   | Encoding |   Access privileges
-----------+----------+----------+-----------------------
 app       | app      | UTF8     |
 postgres  | postgres | UTF8     |
 template0 | postgres | UTF8     | =c/postgres          +
           |          |          | postgres=CTc/postgres
 template1 | postgres | UTF8     | =c/postgres          +
           |          |          | postgres=CTc/postgres
(4 rows)

Three of those exist on every server. postgres is an empty default database for tools and admins to connect to. template1 is copied whenever you create a new database, so anything you put there turns up in every later database. template0 is a pristine copy that nobody can connect to, used to create a database with a different encoding or locale. Everything else is yours.

Unlike MySQL’s SHOW DATABASES, the list is not filtered by privileges: every role that can log in sees every database name, even ones it cannot connect to. \l+ adds the size and tablespace of each. The SQL versions are handy in scripts and in tools that do not understand backslash commands:

-- names only, without the two templates
SELECT datname FROM pg_database WHERE NOT datistemplate ORDER BY datname;

-- with sizes, biggest first
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

sudo -u postgres psql -Atc "SELECT datname FROM pg_database WHERE NOT datistemplate"

-A drops the alignment, -t the header and footer, so the output is just the names. Typing SHOW DATABASES; out of MySQL habit fails with ERROR: unrecognized configuration parameter "databases", because SHOW in PostgreSQL reads server settings. The MySQL commands themselves are in listing users and databases in MySQL.

Switch database and list its tables

\c app
You are now connected to database "app" as user "postgres".
\dt
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | orders | table | app
 public | users  | table | app
(2 rows)

A PostgreSQL session is always connected to exactly one database; there is no USE statement, so \c opens a new connection. \dt shows tables in the schemas on your search path, normally just public. For other schemas use \dt sales.*, for all of them \dt *.* (that includes the system catalogs).

How do I list users and roles?

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

PostgreSQL has no separate concept of a user: there are only roles. A role with the LOGIN attribute behaves like a user; one without it is a group that other roles can be members of. An empty Attributes cell means an ordinary login role with no special powers. In SQL:

SELECT rolname, rolsuper, rolcreatedb, rolcanlogin
FROM pg_roles
WHERE rolname !~ '^pg_'
ORDER BY rolname;

The filter hides the built-in pg_* roles such as pg_read_all_data, which pg_roles lists too. Roles belong to the whole cluster, not to one database, so the list is the same whichever database you are connected to.

How do I create a user in PostgreSQL?

CREATE USER is CREATE ROLE with LOGIN implied, so these two lines do the same thing:

CREATE USER app WITH PASSWORD 'a-long-random-password';
CREATE ROLE app WITH LOGIN PASSWORD 'a-long-random-password';

The PostgreSQL manual warns that a password written into the command travels to the server in clear text and can end up in your psql history or the server log. The cleaner way is to create the role without one and set it with \password, which prompts twice and sends only a hash:

CREATE ROLE app LOGIN;
\password app
Enter new password for user "app":
Enter it again:

From the shell, the createuser wrapper does the same and also hashes the password before sending it:

sudo -u postgres createuser --pwprompt app          # login role, asks for a password
sudo -u postgres createuser --pwprompt --createdb dev  # may also create databases

Attributes you can add to CREATE ROLE or change later with ALTER ROLE:

• CREATEDB: may create databases. Useful for a developer account or a test runner that creates throwaway databases.
• CREATEROLE: may create and manage other roles. Close to admin rights in practice.
• SUPERUSER: bypasses every permission check, can read any file the server can and run programs as the postgres Linux user. Never give it to an application.
• CONNECTION LIMIT 20 and VALID UNTIL '2027-01-01': caps on sessions and on how long the password works.

How do I create a database for that user?

CREATE DATABASE app OWNER app;

sudo -u postgres createdb --owner=app app

Make the application role the owner. The owner may create schemas in its database and install extensions marked as trusted, and, on PostgreSQL 15 and later, owns the public schema in it, which saves most of the permission trouble below. For an existing database, ALTER DATABASE app OWNER TO app; changes it. The new database is a copy of template1 with the cluster’s default encoding, which is usually UTF8 on a Debian or Ubuntu install; to force a different encoding or locale, copy template0 instead with TEMPLATE template0 ENCODING 'UTF8'.

How do GRANT and privileges work?

Privileges sit on three levels, and a grant on one level says nothing about the next. That is the source of most “permission denied” questions:

So GRANT ALL PRIVILEGES ON DATABASE app TO alice; lets alice connect and create schemas, but not read a single table. To give a second role read and write access to the tables the application role already owns, connect to that database first, because schema and table grants apply only to the database you are in:

\c app
GRANT USAGE ON SCHEMA public TO alice;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO alice;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO alice;

-- tables the app creates later get the same grants automatically
ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO alice;

ON ALL TABLES covers only the tables that exist now; ALTER DEFAULT PRIVILEGES covers future ones created by the named role. Forgetting the second is why a reporting user suddenly cannot read a table added in last week’s migration.

A read-only user

CREATE ROLE reader LOGIN;
\password reader
REVOKE CONNECT ON DATABASE app FROM PUBLIC;   -- optional: stop everyone else connecting
GRANT CONNECT ON DATABASE app TO app, reader;
\c app
GRANT USAGE ON SCHEMA public TO reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reader;
ALTER DEFAULT PRIVILEGES FOR ROLE app IN SCHEMA public GRANT SELECT ON TABLES TO reader;

For a backup or monitoring account that needs to read everything, the built-in role pg_read_all_data (PostgreSQL 14 and later) is simpler: GRANT pg_read_all_data TO reader;. It applies to every database in the cluster, so use it only where that is what you want.

How do I change a user’s password?

-- prompts twice, sends only a hash
\password app

-- same result, but the password travels in clear text
ALTER USER app WITH PASSWORD 'new-long-password';

-- lift an expiry date
ALTER ROLE app VALID UNTIL 'infinity';

Since PostgreSQL 14, new passwords are stored as SCRAM-SHA-256 hashes by default. Roles whose password was set years ago may still carry an MD5 hash, which the current manual marks as deprecated and slated for removal. Check, as a superuser:

SELECT rolname, left(rolpassword, 14) AS hash_type
FROM pg_authid WHERE rolcanlogin AND rolpassword IS NOT NULL;

A value starting with SCRAM-SHA-256$ is current; one starting with md5 needs the password set again (with password_encryption at its default of scram-sha-256). An MD5-hashed role cannot log in through a scram-sha-256 line in pg_hba.conf, which surfaces as password authentication failed even with the right password.

How do I delete a user or a database?

DROP DATABASE app;                 -- the database and everything in it
DROP DATABASE app WITH (FORCE);    -- PostgreSQL 13+: ends open sessions first
DROP ROLE alice;                   -- DROP USER is the same command

-- shell equivalents
sudo -u postgres dropdb app
sudo -u postgres dropuser alice

Neither has an undo; take a pg_dump first (the backup section of the install guide has the commands). You cannot drop the database you are connected to, so \c postgres first. A role that still owns objects or holds privileges refuses to go:

DROP ROLE alice;
ERROR:  role "alice" cannot be dropped because some objects depend on it

-- run in EVERY database where alice owns or was granted something:
\c app
REASSIGN OWNED BY alice TO app;    -- hand her tables to another role
DROP OWNED BY alice;               -- remove her remaining grants
-- then, once:
DROP ROLE alice;

pg_hba.conf: who may log in, from where, and how

Roles and passwords decide what a user may do once connected. Whether the connection is accepted at all is decided by pg_hba.conf (on Debian and Ubuntu in /etc/postgresql/<version>/main/). Each line names a connection type, database, user, client address and method; the first line that matches decides, and there is no fall-through to the next one if authentication fails. The relevant part of the Debian and Ubuntu default:

# TYPE  DATABASE  USER      ADDRESS        METHOD
local   all       postgres                 peer
local   all       all                      peer
host    all       all       127.0.0.1/32   scram-sha-256
host    all       all       ::1/128        scram-sha-256

After an edit, reload instead of restarting, then ask the server whether it parsed the file cleanly:

sudo systemctl reload postgresql
sudo -u postgres psql -c "SELECT pg_reload_conf();"     # same thing from SQL

sudo -u postgres psql -c "SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;"

Opening the server to another machine also needs listen_addresses and a firewall rule; that is covered step by step in the remote access section of the install guide. If only you need to reach the database from your laptop, an SSH tunnel avoids opening port 5432 at all.

Connecting from an application

Most frameworks and drivers take a single connection URI, often from an environment variable such as DATABASE_URL:

postgresql://app:PASSWORD@127.0.0.1:5432/app          # TCP, password (scram-sha-256)
postgresql:///app?host=/var/run/postgresql              # socket, peer: app runs as Linux user "app"
postgresql://app:PASSWORD@db.example.com:5432/app?sslmode=require   # remote, encrypted

Characters such as @, :, / or # in the password must be percent-encoded inside a URI (@ becomes %40), or the driver splits the string in the wrong place. For psql, cron jobs and scripts, keep passwords out of the command line with a ~/.pgpass file, one line per connection in the form host:port:database:user:password. libpq ignores the file unless it is private, so run chmod 600 ~/.pgpass.

Common errors and what they mean

On a VPS: keep the users few and the port closed

A tidy layout for one server: the postgres role only for administration through sudo -u postgres psql, one owner role per application with its own database, a read-only role where a dashboard or a backup job needs one, and port 5432 reachable from localhost only. Scanners try PostgreSQL logins on every public address they find, so a server that needs no remote clients should never see one. A Linux VPS from us comes with root access and stock Ubuntu, Debian and Rocky images, so the commands on this page apply as shown.

Frequently asked