Roles, GRANT/REVOKE and SQL injection

Part of the free LogicWiz SQL & Databases course, Module 15: Views, functions and security. The queries and practice run in the app on real Postgres.

An intern's dashboard logs in as the database owner. One day someone types x' OR '1'='1 into its search box and the page lists every customer of the app. Two mistakes made that possible, and this lesson fixes both.

You'll learn to

Create roles and give them only the privileges they need with GRANT and REVOKE
Test privileges with SET ROLE and has_table_privilege
See how SQL injection works and stop it with parameters and prepared statements

Learn

Roles and least privilege

In Postgres, users and groups are both roles. A role that can log in is a user; a role that others are members of is a group.

Privileges say what a role may do: SELECT, INSERT, UPDATE, DELETE on a table, USAGE on a schema, EXECUTE on a function.

The golden rule is least privilege: give each app or person only what they need. The support dashboard reads orders; it should not be able to DROP TABLE or read card details.

Create a role, grant, and try it

CREATE ROLE support;
GRANT USAGE ON SCHEMA public TO support;
GRANT SELECT ON customers, orders TO support;

SET ROLE support;
SELECT current_user, COUNT(*) AS orders FROM orders;
RESET ROLE;

SELECT has_table_privilege('support', 'menu_items', 'SELECT') AS can_read_menu;

SET ROLE makes this session act as support until RESET ROLE. has_table_privilege answers yes/no without switching. Every role can already use the public schema in a fresh database; for a schema you create, a role also needs USAGE on it. Granting it here is harmless and a good habit.

What a role is not allowed to do

CREATE ROLE support;
GRANT USAGE ON SCHEMA public TO support;
GRANT SELECT ON customers, orders TO support;

SET ROLE support;
UPDATE menu_items SET price = 1 WHERE item_id = 1;

permission denied for table menu_items. Even if the support app has a bug or is hacked, it cannot change prices.

GRANT, REVOKE and group roles

GRANT SELECT, UPDATE ON menu_items TO kitchen; gives privileges.
REVOKE UPDATE ON menu_items FROM kitchen; takes them back.
Column level: GRANT UPDATE (status) ON orders TO support; lets support change the status and nothing else.
Group roles: CREATE ROLE analysts; GRANT analysts TO meher; and Meher gets whatever analysts have.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO intern; covers the tables that exist now.

To hide rows or columns, grant a view instead of the table: the view reads the table with its owner's rights.

SQL injection: when data becomes code

An app that builds SQL by gluing strings is in danger:

sql = "SELECT * FROM customers WHERE name = '" + userInput + "'"

If the user types Riya Patel, all is well. If they type x' OR '1'='1, the query becomes

SELECT * FROM customers WHERE name = 'x' OR '1'='1'

The quote in the input closed the string, and the rest became SQL. '1'='1' is always true, so every customer comes back. Worse inputs can add ; DROP TABLE orders. The next card shows it happening in a real function.

A vulnerable search, attacked

CREATE FUNCTION find_customer(p_name TEXT)
RETURNS SETOF customers
LANGUAGE plpgsql
AS $
BEGIN
  -- DANGER: the input is glued into the SQL text
  RETURN QUERY EXECUTE
    'SELECT * FROM customers WHERE name = ''' || p_name || '''';
END;
$;

SELECT customer_id, name FROM find_customer('Riya Patel');

SELECT customer_id, name FROM find_customer('x'' OR ''1''=''1');

The second call passes the text x' OR '1'='1 (each '' is one quote inside a SQL string). It returns all 12 customers.

The fix: send data as a parameter

PREPARE find_by_name(TEXT) AS
SELECT customer_id, name
FROM customers
WHERE name = $1;

EXECUTE find_by_name('Riya Patel');

EXECUTE find_by_name('x'' OR ''1''=''1');

With a prepared statement, the query is parsed first and $1 is filled in later as a pure value. The attack text is just a strange name that nobody has: 0 rows. Inside PL/pgSQL the same fix is EXECUTE '... WHERE name = $1' USING p_name;.

Escaping quotes by hand (replace(input, '''', '''''')) is not the fix. People miss cases. Use parameters every time: $1 in Postgres, ? in JDBC and MySQL drivers, placeholders in your ORM. Use format('%I', name) only for identifiers such as a table name, which cannot be parameters.

Interviewers love: what is SQL injection and how do you prevent it? Answer in three layers: (1) parameterised queries / prepared statements, so input is never parsed as SQL; (2) least privilege, so even a hole can't drop tables; (3) validate input (an id must be a number). Exams ask GRANT/REVOKE syntax and the WITH GRANT OPTION clause, which lets the receiver grant the privilege on.

In one line

Grant each role only what it needs (GRANT/REVOKE, test with SET ROLE), and never glue user input into SQL: pass it as a parameter.

Interview corner

What is SQL injection and how do you prevent it?

It happens when user input is concatenated into a SQL string, so a quote in the input ends the string and the rest runs as SQL (' OR '1'='1). Prevent it with parameterised queries or prepared statements, where the query is parsed before the values arrive. Add least privilege so a hole can do little damage, and validate input types.

What is the difference between a user and a role in Postgres?

Postgres has only roles. A role with the LOGIN attribute acts as a user; a role that other roles are granted into acts as a group. CREATE USER meher is just CREATE ROLE meher LOGIN.

How would you let an analyst see totals but not personal data?

Create a view that exposes only the safe columns or aggregates, GRANT SELECT on the view, and do not grant the base table. The view reads the table with its owner's privileges. Column-level grants (GRANT SELECT (city) ON customers) and row-level security policies are other options.

What does WITH GRANT OPTION do?

GRANT SELECT ON orders TO lead WITH GRANT OPTION; lets lead grant SELECT on orders to others. Revoking it later with REVOKE ... CASCADE also removes what lead passed on.