SQL Security and Performance: Essential Commands, Examples, Outputs

A SQL query should return the correct data, respect access rules, and use database resources efficiently. This guide explains practical commands for protecting data and improving query performance, with examples and expected outputs.

Database: PostgreSQL. Permission commands, prepared-statement syntax, row-level security, and maintenance behavior differ across database engines. Examples assume a fresh practice database and an authorized administrator for role creation and role switching. Run sections in order, using the table owner except where SET ROLE is shown. Roles are created without login access.

Output note: These are expected outputs, not measurements from a live PostgreSQL server. Query plans, timings, error detail formatting, and command messages vary by environment. The four-row dataset demonstrates correctness; it cannot establish a performance improvement.

Sample data

CREATE SCHEMA sql_blog_lab;

CREATE TABLE sql_blog_lab.employees (
    emp_id INTEGER PRIMARY KEY,
    emp_name VARCHAR(40) NOT NULL,
    dept_id INTEGER NOT NULL,
    salary INTEGER NOT NULL CHECK (salary >= 0),
    email VARCHAR(100) NOT NULL UNIQUE,
    hired_on DATE NOT NULL
);

INSERT INTO sql_blog_lab.employees VALUES
(1, 'Asha', 10, 90000, 'asha@example.com', '2025-01-10'),
(2, 'Ravi', 10, 70000, 'ravi@example.com', '2025-03-15'),
(3, 'Neha', 20, 70000, 'neha@example.com', '2024-07-20'),
(4, 'Imran', 20, 50000, 'imran@example.com', '2025-06-01');

SELECT emp_id, emp_name, dept_id, salary
FROM sql_blog_lab.employees
ORDER BY emp_id;

Expected output:

1. CREATE ROLE: separate application permissions

What and why: A role groups privileges. Create reporting roles without administrative powers and grant only required access.

CREATE ROLE sql_blog_reader
    NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;

SELECT rolname, rolcanlogin, rolsuper
FROM pg_roles
WHERE rolname = 'sql_blog_reader';

Expected output:

Interview question: Why should an application avoid connecting as a superuser?

Answer: A compromised connection would inherit broad authority. Use a dedicated runtime role with narrowly scoped privileges. NOLOGIN roles can group permissions; separate login roles may receive membership.

2. GRANT: allow only selected columns

What and why: Column privileges can expose employee identifiers and names while keeping salary and email unavailable through this grant.

GRANT USAGE ON SCHEMA sql_blog_lab TO sql_blog_reader;
GRANT SELECT (emp_id, emp_name)
ON sql_blog_lab.employees TO sql_blog_reader;

SET ROLE sql_blog_reader;
SELECT emp_id, emp_name
FROM sql_blog_lab.employees
ORDER BY emp_id;
RESET ROLE;

Expected output:

Interview question: Does schema USAGE automatically allow reading every table?

Answer: No. Schema access and table or column privileges are separate. Also check inherited privileges: a broader table-level grant can allow columns beyond this narrow grant.

3. REVOKE: remove a specific permission

What and why: Remove privileges that are no longer needed, then verify effective access.

REVOKE SELECT (emp_id, emp_name)
ON sql_blog_lab.employees FROM sql_blog_reader;

SELECT has_column_privilege(
    'sql_blog_reader',
    'sql_blog_lab.employees',
    'emp_name',
    'SELECT'
) AS can_read_name;

Expected output in this fresh lab:

Interview question: Does REVOKE always eliminate a user's access?

Answer: No. Another grant, role membership, ownership, or privileges granted to PUBLIC may still provide access. Review effective privileges rather than one grant in isolation.

4. PREPARE and EXECUTE: keep values separate from SQL

What and why: Parameters represent input values without concatenating them into SQL structure.

PREPARE employee_by_email(TEXT) AS
SELECT emp_id, emp_name
FROM sql_blog_lab.employees
WHERE email = $1;

EXECUTE employee_by_email('ravi@example.com');
DEALLOCATE employee_by_email;

Expected query output:

Interview question: How do prepared statements help prevent SQL injection?

Answer: Bound input is handled as a value rather than SQL syntax. Application code should use the database driver's parameter API. Parameters usually cannot substitute table or column names; select dynamic identifiers from an allowlist and quote them with trusted APIs. Preparing SQL that already contains concatenated untrusted text does not fix the problem.

5. CREATE VIEW: provide a limited data interface

What and why: A view can publish a reusable projection that excludes sensitive fields.

CREATE VIEW sql_blog_lab.employee_directory AS
SELECT emp_id, emp_name, dept_id
FROM sql_blog_lab.employees;

GRANT SELECT ON sql_blog_lab.employee_directory TO sql_blog_reader;

SET ROLE sql_blog_reader;
SELECT emp_id, emp_name, dept_id
FROM sql_blog_lab.employee_directory
WHERE emp_id = 2;
RESET ROLE;

Expected output:

Interview question: Does a view automatically secure its underlying table?

Answer: No. Restrict direct table access and review ownership, functions, and view options. This simple default PostgreSQL view checks underlying-table privileges using its owner. security_invoker changes privilege checking; security_barrier addresses certain predicate-evaluation concerns. An ordinary view stores a query definition, not cached result rows.

6. Row-level security: restrict visible records

What and why: A row-security policy restricts which records an authorized role may read. This example allows a dedicated department role to see only department 10.

CREATE ROLE sql_blog_dept10 NOLOGIN NOBYPASSRLS;
GRANT USAGE ON SCHEMA sql_blog_lab TO sql_blog_dept10;
GRANT SELECT ON sql_blog_lab.employees TO sql_blog_dept10;

ALTER TABLE sql_blog_lab.employees ENABLE ROW LEVEL SECURITY;

CREATE POLICY dept10_select ON sql_blog_lab.employees
FOR SELECT TO sql_blog_dept10
USING (dept_id = 10);

SET ROLE sql_blog_dept10;
SELECT emp_id, emp_name, dept_id
FROM sql_blog_lab.employees
ORDER BY emp_id;
RESET ROLE;

Expected output:

Interview question: Does row-level security apply equally to every role?

Answer: No. Superusers and BYPASSRLS roles bypass it; table owners normally do too. FORCE ROW LEVEL SECURITY can subject the owner to policies. Policies complement table privileges; they do not replace them. Test with the actual runtime role. This department-specific policy does not restrict the owner-backed directory view created above, which intentionally exposes all directory names.

7. CHECK constraints: reject invalid data

What and why: Database constraints enforce data rules even when an application misses validation.

BEGIN;
UPDATE sql_blog_lab.employees
SET salary = -100
WHERE emp_id = 4;
ROLLBACK;

Expected result: The update fails with a check-constraint violation. A typical PostgreSQL message begins:

ERROR: new row for relation "employees" violates check constraint "employees_salary_check"

ROLLBACK clears the failed transaction. Imran's salary remains 50000.

Interview question: Are constraints a replacement for authorization?

Answer: No. Constraints enforce integrity; permissions determine who can perform operations.

8. EXPLAIN: inspect the plan before optimizing

What and why: EXPLAIN shows how PostgreSQL plans to access and combine data.

EXPLAIN (COSTS OFF)
SELECT emp_id, emp_name
FROM sql_blog_lab.employees
WHERE salary >= 70000;

Illustrative plan shape, not a captured or guaranteed plan:

Seq Scan on employees
  Filter: (salary >= 70000)

Interview question: Does a sequential scan always mean a slow query?

Answer: No. Scanning a small table or a large portion of a table may be cheaper than using an index. Costs are planner estimates, not elapsed milliseconds.

9. EXPLAIN ANALYZE and BUFFERS: measure execution

What and why: ANALYZE executes the statement and reports runtime information. BUFFERS adds buffer activity.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT emp_id, emp_name
FROM sql_blog_lab.employees
WHERE salary >= 70000;

Expected output type: A plan containing estimated and actual row counts, loops, buffer information, and execution time. This SELECT produces three matching rows internally; the command returns the plan rather than the ordinary three-row result table. Exact timings and access methods vary.

Interview question: Is EXPLAIN ANALYZE UPDATE ... read-only?

Answer: No. It executes the update. Use an isolated test environment; even rolling back does not undo every possible side effect, such as sequence consumption or external actions triggered by code.

10. CREATE INDEX: improve suitable lookups

What and why: An index can reduce scanning for selective predicates, joins, or ordering requirements. The UNIQUE email constraint already created an index, so another plain email index would be redundant here.

CREATE INDEX idx_blog_salary
ON sql_blog_lab.employees (salary);

SELECT emp_id, emp_name
FROM sql_blog_lab.employees
WHERE salary = 90000
ORDER BY emp_id;

Expected command status: CREATE INDEX.

Expected query output:

Interview question: Why not index every column?

Answer: Indexes consume space and add work to inserts, updates, and deletes. Choose them from real query patterns. The optimizer may still prefer a sequential scan on this tiny dataset.

11. Composite indexes: match filtering and ordering

What and why: A multicolumn index can support a common combination of equality conditions and ordering.

CREATE INDEX idx_blog_dept_salary
ON sql_blog_lab.employees (dept_id, salary DESC, emp_id);

SELECT emp_id, emp_name, salary
FROM sql_blog_lab.employees
WHERE dept_id = 10
ORDER BY salary DESC, emp_id
LIMIT 2;

Expected output:

Interview question: Does (dept_id, salary) behave the same as (salary, dept_id)?

Answer: No. Column order affects efficient access paths. Leading equality conditions are particularly useful for B-tree indexes; later columns can support ordering or ranges. Avoid claiming later columns can never be used: planner features, including skip scans in suitable cases, make that too absolute.

12. Partial and covering indexes: index a focused workload

What and why: A partial index stores only rows satisfying its predicate. INCLUDE stores extra payload columns that may support index-only scans.

CREATE INDEX idx_blog_high_earners
ON sql_blog_lab.employees (salary DESC, emp_id)
INCLUDE (emp_name)
WHERE salary >= 70000;

SELECT emp_id, emp_name, salary
FROM sql_blog_lab.employees
WHERE salary >= 70000
ORDER BY salary DESC, emp_id;

Expected output:

Interview question: Does INCLUDE guarantee an index-only scan?

Answer: No. Plan choice and visibility-map state matter; heap visits may still be needed. A partial index is usable only when the planner can establish that the query satisfies its predicate. Included columns are payload, not additional search keys.

13. Index-friendly predicates: compare the stored column directly

What and why: Applying a function to an indexed column can prevent an ordinary index from providing an efficient search condition. A range often expresses the same requirement more effectively.

CREATE INDEX idx_blog_hired_on
ON sql_blog_lab.employees (hired_on);

SELECT emp_name, hired_on
FROM sql_blog_lab.employees
WHERE hired_on >= DATE '2025-01-01'
  AND hired_on < DATE '2026-01-01'
ORDER BY hired_on, emp_id;

Expected output:

Interview question: Why prefer this range over EXTRACT(YEAR FROM hired_on) = 2025?

Answer: The range directly constrains the indexed date column. An expression index is another option when queries genuinely require a matching expression. Neither approach guarantees the optimizer will choose an index.

14. Keyset pagination: continue after the last key

What and why: Deep OFFSET pages may require scanning and discarding many preceding rows. Keyset pagination continues after a stable cursor.

-- Previous page ended at emp_id = 2.
SELECT emp_id, emp_name
FROM sql_blog_lab.employees
WHERE emp_id > 2
ORDER BY emp_id
LIMIT 2;

Expected output:

Interview question: Why must the order be deterministic?

Answer: A stable unique ordering gives an unambiguous continuation point. For non-unique sort columns, include a unique tiebreaker in both the ordering and cursor predicate. Keyset pagination is less suited to jumping directly to an arbitrary page number.

15. ANALYZE and VACUUM: maintain planner statistics and storage

What and why: ANALYZE updates statistics used for planning. VACUUM reclaims reusable space from dead row versions and supports visibility maintenance.

ANALYZE sql_blog_lab.employees;
VACUUM (ANALYZE) sql_blog_lab.employees;

Expected command statuses:

ANALYZE
VACUUM

Interview question: Does ordinary VACUUM always shrink a table file?

Answer: No. Its main effect is to make space reusable inside the table; it may truncate empty end pages in some cases. VACUUM FULL rewrites the table, needs extra disk space, and takes a strong lock. Autovacuum normally handles routine work. Run VACUUM outside an explicit transaction block; avoid unnecessary duplicate maintenance runs in a real workload.

16. SET LOCAL: bound query runtime and lock waiting

What and why: Transaction-local timeouts keep individual operations from waiting or executing longer than an intended budget.

BEGIN;
SET LOCAL statement_timeout = '2s';
SET LOCAL lock_timeout = '500ms';

SELECT current_setting('statement_timeout') AS statement_limit,
       current_setting('lock_timeout') AS lock_wait_limit;
ROLLBACK;

Expected output:

Interview question: What is the difference between these timeouts?

Answer: statement_timeout limits overall statement execution time; lock_timeout limits each lock-acquisition wait. These are operational safeguards, not query optimization or protection against all resource abuse. SET LOCAL settings end with the transaction.

17. Transactions and short updates: combine safety with concurrency

What and why: Group dependent changes atomically, target records precisely, and keep transactions short so locks are released promptly.

BEGIN;
UPDATE sql_blog_lab.employees
SET salary = salary + 1000
WHERE emp_id = 4
RETURNING emp_id, emp_name, salary;
ROLLBACK;

Expected output before rollback:

After rollback, the stored salary remains 50000. Use COMMIT when the intended changes should persist.

Interview question: Can long-running transactions hurt performance?

Answer: Yes. They can retain locks and old row versions, delay cleanup, and increase contention. Avoid waiting for user interaction or unrelated network requests inside an open transaction.

Common interview mistakes

References

• PostgreSQL GRANT
• PostgreSQL prepared statements
• PostgreSQL row-security policies
• PostgreSQL EXPLAIN
• PostgreSQL multicolumn indexes
• PostgreSQL VACUUM

Colorful SQL security and performance architecture

flowchart TD
    APP["Application query and input"]:::blue
    APP --> PARAM["Bind values with parameters"]:::blue
    PARAM --> AUTH["Role and privilege checks"]:::purple
    AUTH --> ACCESS["GRANT / REVOKE / controlled views"]:::purple
    ACCESS --> ROWS["RLS policies: permitted rows"]:::purple
    ROWS --> PLAN["Optimizer and EXPLAIN"]:::yellow
    STATS["ANALYZE: planner statistics"]:::orange --> PLAN
    IDX["Composite / partial / covering indexes"]:::green --> PLAN
    PLAN --> RUN["Execute: filters, joins, ordering"]:::green
    RUN --> RESULT["Authorized query results"]:::blue
    RUN -.-> TX["Short transactions and timeouts"]:::pink
    TX -.-> MAINT["VACUUM and concurrency maintenance"]:::orange
    classDef blue fill:#dbeafe,stroke:#2563eb,color:#172554,stroke-width:2px;
    classDef purple fill:#f3e8ff,stroke:#9333ea,color:#581c87,stroke-width:2px;
    classDef yellow fill:#fef9c3,stroke:#ca8a04,color:#713f12,stroke-width:2px;
    classDef green fill:#dcfce7,stroke:#16a34a,color:#14532d,stroke-width:2px;
    classDef orange fill:#ffedd5,stroke:#ea580c,color:#7c2d12,stroke-width:2px;
    classDef pink fill:#fce7f3,stroke:#db2777,color:#831843,stroke-width:2px;

This is a simplified learning diagram, not a strict account of PostgreSQL's internal execution order. Privilege checks, query rewriting, RLS, and optimization interact. Mermaid rendering requires publisher support.