PostgreSQL first steps with psql: connect, explore databases and schemas, create a role and grant access

psql is the PostgreSQL command-line client. It is on every server and works the same against managed services. Ten minutes with it saves hours of clicking.

#Connect

# local, as the postgres OS user (self-managed Linux)
sudo -u postgres psql

# remote
psql "host=db01.internal port=5432 dbname=shop user=alice sslmode=require"

# managed services usually require TLS
psql "host=mydb.postgres.database.azure.com dbname=postgres user=alice sslmode=require"

Avoid typing passwords on the command line. Use a ~/.pgpass file (mode 600):

db01.internal:5432:shop:alice:S3cret!

#Backslash commands you will use daily

#Databases vs schemas

• A cluster (one PostgreSQL server) holds several databases. A connection is to one database; you cannot join across databases without extensions.
• A database holds schemas (namespaces). public exists by default.
• Tables live in schemas: sales.orders.

CREATE DATABASE shop;
\c shop
CREATE SCHEMA sales;
CREATE TABLE sales.orders (
  order_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer   text NOT NULL,
  amount     numeric(10,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

search_path decides which schema an unqualified name means:

SHOW search_path;
SET search_path = sales, public;

#Create an application role with least privilege

CREATE ROLE app_shop LOGIN PASSWORD 'change-me' CONNECTION LIMIT 50;

GRANT CONNECT ON DATABASE shop TO app_shop;
GRANT USAGE ON SCHEMA sales TO app_shop;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO app_shop;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA sales TO app_shop;

-- tables created later by the owner role get the same grants
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_shop;

ALTER DEFAULT PRIVILEGES applies to objects created later by the role that runs it (or the role named with FOR ROLE). Run it as the role that owns the schema's tables.

Tip

Do not let the application connect as the table owner or as postgres. A separate owner role for migrations and a separate app role for runtime limits what a bug or SQL injection can do.

A read-only role for reporting:

CREATE ROLE report_ro NOLOGIN;
GRANT CONNECT ON DATABASE shop TO report_ro;
GRANT USAGE ON SCHEMA sales TO report_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO report_ro;
GRANT report_ro TO bob;          -- bob inherits read-only access

PostgreSQL 14+ also has the predefined role pg_read_all_data for "read everything".

#Quick health checks

SELECT version();
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

#Common connection errors

#Run SQL from files and scripts

psql "host=db01 dbname=shop user=alice" -f migrate.sql -v ON_ERROR_STOP=1
psql "host=db01 dbname=shop user=alice" -At -c "select count(*) from sales.orders"

ON_ERROR_STOP=1 makes psql stop at the first error instead of running the rest of the script.

#Prevent it next time

• One owner role and one runtime role per application; no shared superuser.
• ~/.pgpass or a secrets manager instead of passwords in scripts.
• Keep pg_hba.conf rules specific (exact subnets, scram-sha-256, hostssl).