Security, Roles, and Safe Application Queries

Apply least privilege, use parameterized SQL, understand row-level security, and protect sensitive data.

Roles, Grants, and Least Privilege

PostgreSQL privileges govern actions such as connecting to a database, using a schema, selecting from a table, and inserting or updating rows. A role may inherit permissions from other roles. Separate runtime application access from migration or administrative access, and avoid using a superuser role for ordinary application queries.

Remember that schema privileges and table privileges are different. A role may need USAGE on a schema and specific privileges on its tables. Default privileges affect objects created in the future by a particular role; they do not retroactively grant access to all existing objects.

Grant narrowly scoped access

GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE ON TABLE customers TO app_user;

💡 The example grants only selected table operations. Production permissions should match the application's responsibilities and may require sequence privileges or default privileges depending on how IDs are generated and objects are created.

Key Takeaways

Avoid superuser privileges for application runtime roles.
Schema USAGE and table privileges are separate.
Default privileges affect future objects, not all existing objects.

Knowledge Check

Which principle should govern production application permissions?

SQL Injection and Parameterized Queries

SQL injection occurs when untrusted input is interpreted as part of SQL syntax. Use parameterized queries through your database driver or ORM so values are transmitted separately from the SQL statement. Do not build queries by concatenating user-supplied strings. Parameters generally represent values, not arbitrary identifiers such as table names or sort directions; dynamic identifiers must be selected from an allowlist and safely quoted using appropriate library support.

An ORM does not automatically make every query safe if raw SQL is assembled unsafely. Review raw query paths and ensure values are bound as parameters.

Parameterized query with node-postgres

const result = await pool.query(
  "SELECT id, name FROM customers WHERE email = $1",
  [email]
);

💡 The $1 placeholder is bound to the email value separately from the SQL text. This example uses node-postgres; other drivers have their own parameter placeholder conventions.

Key Takeaways

Bind values as parameters instead of concatenating them.
Dynamic identifiers need allowlisting and safe identifier handling.
Raw SQL in an ORM still requires safe construction.

Knowledge Check

What is the primary defense against SQL injection for query values?

Row-Level Security (RLS)

Row-level security allows policies to control which rows a role may read or modify. Enable RLS on a table and create policies that define conditions for commands and roles. RLS can provide a valuable defense-in-depth layer for multi-tenant applications, but the application must establish tenant context safely and understand policy behavior.

Table owners typically bypass row-level security unless configured otherwise, and roles with BYPASSRLS can bypass it. Test policies using the same role and connection settings as the application. RLS does not replace authentication, authorization design, or secure handling of tenant identifiers.

Enable a tenant policy

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_documents
ON documents
USING (tenant_id = current_setting('app.tenant_id', true)::bigint)
WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::bigint);

💡 This is an illustrative policy pattern. The application must set the tenant context safely for each transaction or connection, handle missing settings, and ensure untrusted clients cannot choose another tenant's context. Validate casts and policy behavior in a test environment.

Key Takeaways

RLS policies filter rows according to role and policy conditions.
Table owners and BYPASSRLS roles need special consideration.
Test policies using the actual application role and tenant context.

Knowledge Check

What does row-level security primarily control?

Roles, Grants, and Least Privilege

SQL Injection and Parameterized Queries

Row-Level Security (RLS)

STAY UPDATED WITH BYTES!