postgres rls row‐level sequrity - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/sql-createpolicy.html
• https://github.com/steve-chavez/socnet/blob/master/security/friendships.sql
• https://www.postgresql.org/docs/current/rules-privileges.html
• https://www.postgresql.org/docs/current/ddl-rowsecurity.html

Enable on table

[ALTER TABLE ... ENABLE ROW LEVEL SECURITY](https://www.postgresql.org/docs/current/sql-altertable.html)

all normal access to the table for selecting rows or modifying rows must be allowed by a row security policy. (However, the table's owner is typically not subject to row security policies.) If no policy exists for the table, a default-deny policy is used, meaning that no rows are visible or can be modified. Operations that apply to the whole table, such as TRUNCATE and REFERENCES, are not subject to row security.

CREATE TABLE accounts (manager text, company text, contact_email text);

ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_managers ON accounts TO managers
    USING (manager = current_user);

This pair of policies would allow all users to view all rows in the users table, but only modify their own

CREATE POLICY user_sel_policy ON users
    FOR SELECT
    USING (true);
CREATE POLICY user_mod_policy ON users
    USING (user_name = current_user);