SHOW POLICIES

SHOW POLICIES FOR {table_name}

CREATE TABLE orders (
    user_id TEXT PRIMARY KEY,
    order_details TEXT,
    is_archived BOOLEAN DEFAULT FALSE NOT NULL
);

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Add role
CREATE ROLE customer_service;

-- Example policies
CREATE POLICY user_orders_policy ON orders
    FOR ALL
    TO PUBLIC
    USING (user_id = current_user)
    WITH CHECK (user_id = current_user);

CREATE POLICY archived_orders_policy ON orders
    AS RESTRICTIVE
    FOR SELECT
    TO customer_service
    USING (user_id = current_user AND is_archived = FALSE);

SHOW POLICIES FOR orders;

           name          |  cmd   |    type     |       roles        |                      using_expr                      |     with_check_expr
-------------------------+--------+-------------+--------------------+------------------------------------------------------+---------------------------
  user_orders_policy     | ALL    | permissive  | {public}           | user_id = current_user()                             | user_id = current_user()
  archived_orders_policy | SELECT | restrictive | {customer_service} | (user_id = current_user()) AND (is_archived = false) |
(2 rows)

SELECT current_user();

  current_user
----------------
  root
(1 row)

SELECT * FROM pg_policies;

  schemaname | tablename |     policyname     | permissive |      roles      | cmd |               qual                |        with_check
-------------+-----------+--------------------+------------+-----------------+-----+-----------------------------------+---------------------------
  public     | orders    | user_orders_policy | permissive | {public}        | ALL | user_id = current_user()          | user_id = current_user()
  public     | employees | self_access        | permissive | {public}        | ALL | username = current_user()         | NULL
  public     | employees | manager_access     | permissive | {public}        | ALL | manager_username = current_user() | NULL
  public     | invoices  | tenant_isolation   | permissive | {public}        | ALL | NULL                              | NULL
  public     | employees | hr_access          | permissive | {hr_department} | ALL | NULL                              | NULL
(5 rows)