Row-level security policies in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a row-level security policy in PostgreSQL?

A row-level security policy controls which rows a user can see or modify in a table, based on rules defined by the database administrator.

beginner

How do you enable row-level security on a PostgreSQL table?

Use the command ALTER TABLE table_name ENABLE ROW LEVEL SECURITY; to turn on row-level security for that table.

intermediate

What is the purpose of a policy in row-level security?

A policy defines the conditions under which rows are visible or modifiable by users. It acts like a filter for rows based on user roles or other criteria.

intermediate

How do you create a row-level security policy in PostgreSQL?

Use CREATE POLICY policy_name ON table_name FOR {SELECT | INSERT | UPDATE | DELETE} TO role_name USING (condition); to define who can access which rows and how.

advanced

Can a user bypass row-level security policies in PostgreSQL?

Yes, users with the BYPASSRLS privilege can bypass row-level security policies and see all rows regardless of policies.

Which command enables row-level security on a PostgreSQL table?

A ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

B CREATE POLICY ON table_name;

C GRANT SELECT ON table_name;

D ALTER TABLE table_name SET SECURITY POLICY;

The correct command to enable row-level security is ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;.

What does a row-level security policy control?

A Which columns a user can see

B Which rows a user can see or modify

C Which tables a user can access

D The database connection settings

Row-level security policies control access to individual rows, not columns or tables.

Which privilege allows a user to bypass row-level security policies?

A SUPERUSER

B ADMIN

C BYPASSRLS

D SECURITY_ADMIN

The BYPASSRLS privilege allows users to bypass row-level security policies.

In the command CREATE POLICY, what does the USING clause specify?

A The condition to filter rows

B The columns to include

C The user roles allowed

D The table name

The USING clause specifies the condition that filters which rows are accessible.

Can row-level security policies be applied to INSERT operations?

A No, only SELECT operations are controlled

B Policies only apply to SELECT and UPDATE

C Only UPDATE and DELETE are controlled

D Yes, policies can control INSERT, SELECT, UPDATE, and DELETE

Row-level security policies can be defined for INSERT, SELECT, UPDATE, and DELETE operations.

Explain what row-level security policies are and why they are useful in PostgreSQL.

Think about how you can limit what data different users see in the same table.

Describe the steps to enable and create a row-level security policy on a table in PostgreSQL.

Start with enabling, then define who can do what on which rows.