What a stolen login can do

Start from the breach

Security reviews usually ask “how could someone get in?”. For a database, a more useful first question is: if they did, what could they do? The application’s password ends up in logs, in a leaked config file, in a laptop backup. A single query somewhere builds SQL from user input. Either way, the attacker is now that login, with exactly its privileges.

So the first design decision is what each login is allowed to do.

One role per kind of work

The distinction that matters most is between owning a table and being granted access to it. Only the owner (or a superuser) can drop or alter a table, and grants cannot take that away. An application that logs in as the owner can do anything to its schema that a migration can.

CREATE ROLE shop_owner NOLOGIN;
ALTER TABLE customers OWNER TO shop_owner;   -- and every other table

CREATE ROLE app LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON customers, orders, order_items, products TO app;

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

Without that last statement, every new table is invisible to the application until someone remembers to grant it, and the usual “fix” under pressure is to make the application the owner.

What a role gets without asking

A brand-new role with no grants at all is not powerless. Every role is a member of PUBLIC, which by default gets CONNECT and TEMPORARY on databases and EXECUTE on functions. We checked a fresh role on PostgreSQL 18.3: it could not read any table or create anything in public, but it could create temporary tables and execute 3,347 functions. Since PostgreSQL 15, CREATE on the public schema is no longer granted to everyone; on databases upgraded from older versions it may still be.

That matters for anything you write with SECURITY DEFINER, which runs with its owner’s privileges no matter who calls it. You will hijack one in the lab.

Least privilege limits damage; parameters prevent it

SELECT id, name FROM customers WHERE email = '<input>'

The OWASP SQL injection guidance puts it in this order: parameterised queries first, least privilege as a second layer. Every mainstream driver supports parameters; in psql you can try them with \bind:

SELECT id, name FROM customers WHERE email = $1 \bind 'x'' OR true --' \g

SQL built inside the database

Injection is not only an application bug. A PL/pgSQL function that builds a statement as a string has exactly the same problem:

CREATE FUNCTION count_by_city(p_city text) RETURNS bigint LANGUAGE plpgsql AS $$
DECLARE n bigint;
BEGIN
  EXECUTE 'SELECT count(*) FROM customers WHERE city = ''' || p_city || '''' INTO n;
  RETURN n;
END $$;

It only ever returns a number, so it looks harmless. We passed it a crafted city:

SELECT count_by_city($$x' AND 1 = CAST((SELECT string_agg(email, ',') FROM customers) AS int) AND 'a' = 'a$$);

ERROR:  invalid input syntax for type integer: "asha@example.com,ben@example.com,chen@example.com,
        divya@example.com,elena@example.com,farhan@example.com,grace@example.com,hugo@example.com"

Every email address in the table, delivered through the error message. Pass values with USING instead, and quote any identifiers with format('%I', ...):

EXECUTE 'SELECT count(*) FROM customers WHERE city = $1' INTO n USING p_city;

Getting in at all

Two settings decide who can even try a password:

• Password method. Use scram-sha-256. MD5 password authentication is deprecated in PostgreSQL 18 and will be removed in a future version.
• Network. In pg_hba.conf, accept connections from other hosts only on hostssl lines, so passwords and data never cross the network unencrypted, and only from the networks your application runs in.

Check yourself

1. The application logs in as app with SELECT on customers. An injection is found. What can the attacker still do, and what can they not do?

   Show answer

   They can read, and with the other grants change, any row the application could, including every customer’s data. They cannot drop or alter tables, because only the owner can do that.

   I could explain this without looking

2. Why does adding ALTER DEFAULT PRIVILEGES matter for keeping the application off the owner role?

   Show answer

   Without it, each new table has no grants for the application, so it fails until someone grants access by hand. Under pressure, the easy fix is making the application the owner, and the separation is lost.

   I could explain this without looking

3. count_by_city only returns a number. How did it leak every email address?

   Show answer

   The injected SQL cast all the emails to an integer, which failed, and PostgreSQL’s error message included the value it could not convert. The caller saw the error.

   I could explain this without looking