PostgreSQL行级安全策略问题：插入与查询权限分离失效

Got it, let's sort out this Row Level Security (RLS) issue you're facing. The core problem here is that once you enable RLS on a table, PostgreSQL blocks all operations by default—you need to explicitly create policies for every action (insert, select, update, delete) you want to allow. It sounds like you only set up an insert policy, which is why your queries are getting restricted.

Here's the complete, working configuration for your requirements:

1. First, Clean Up the Table Structure (Optional but Recommended) #

Your existing table is functional, but using quoted identifiers like "api.items_id" can get messy. I'd recommend simplifying the field names for easier use later:

CREATE TABLE api.likes (
    id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
    item_id uuid NOT NULL, -- Maps to api.items.id (no need for the prefix in the field name)
    user_id uuid DEFAULT request.user_id() NOT NULL,
    data jsonb,
    PRIMARY KEY (id)
);
ALTER TABLE api.likes ENABLE ROW LEVEL SECURITY;

If you need to keep the original quoted field names ("api.items_id" and "api.users_id"), just adjust the policy code below to match those exact names.

2. Create the Required Policies #

You need two separate policies: one for inserting (restricted to the current user) and one for selecting (open to all users).

Insert Policy: Enforce User Ownership on New Likes #

This policy ensures that only likes tied to the current authenticated user can be inserted. It also prevents clients from manually setting a different user_id (since we use WITH CHECK to validate the value):

CREATE POLICY policy_likes_insert ON api.likes
    FOR INSERT
    WITH CHECK (user_id = request.user_id());

If using quoted fields:

CREATE POLICY policy_likes_insert ON api.likes
    FOR INSERT
    WITH CHECK ("api.users_id" = request.user_id());

Select Policy: Allow All Users to View All Likes #

This is the missing piece in your current setup. Without this, PostgreSQL blocks all SELECT operations on the table:

CREATE POLICY policy_likes_select ON api.likes
    FOR SELECT
    TO public
    USING (true);

The USING (true) condition means every row is accessible to any user (which aligns with your requirement of allowing arbitrary users to query all data).

3. Test the Policies #

To make sure everything works as expected:

• Test Insert: Try inserting a like with a user_id that doesn't match the current user—this should fail. Inserting a like with the default user_id (or matching your current user ID) should succeed.
• Test Select: Log in as any user and run SELECT * FROM api.likes;—you should see all rows in the table, no restrictions.

Why Your Original Setup Failed #

RLS doesn't assume any permissions by default. Even if you don't have a restrictive select policy, PostgreSQL will block all queries unless you explicitly create a policy that allows them. Your existing insert policy only controls writes, not reads—hence the query restriction.

内容的提问来源于stack exchange，提问作者user2392164