ERROR: row is too big: size XXX, maximum size 8160 - related to pg_policies table

Question

When trying to CREATE POLICY there is the following error:

ERROR: row is too big: size XXX, maximum size 8160

Is there a size limitation on the pg_policies table?

Can that be enlarged or maybe it was bad practice to write so many conditions with joins?

For now, I solved it by changing the query to fewer characters but how can that be avoided?

Example for an almost too long policy.

        CREATE POLICY check_out_insert ON check_out FOR INSERT
            WITH CHECK ( 
                (user_id = current_setting('app.current_user_id')::bigint AND
                file_version_id = ANY(ARRAY(
                    SELECT file_version.id fv_id FROM file_version 
                        INNER JOIN "file" ON (file_version.file_id = "file".id)
                        INNER JOIN "file_user" ON ("file".id = "file_user".file_id AND file_user.deleted_at IS NULL)
                        WHERE file_user.user_id = current_setting('app.current_user_id')::bigint)
                )
                OR
                true = ANY(
                    ARRAY(SELECT is_owner 
                    FROM "group"
                    WHERE id = current_setting('app.current_user_group_id')::bigint
                    LIMIT 1
                    )
                )
                OR
                file_version_id = ANY( 
                    ARRAY(SELECT DISTINCT file_version.id fv_id FROM file_version 
                        INNER JOIN "file" ON (file_version.file_id = "file".id)
                        INNER JOIN "sub_folder" ON (file.sub_folder_id = "sub_folder".id)
                        INNER JOIN "folder" ON (sub_folder.folder_id = "folder".id)
                        INNER JOIN "folder_group" ON (folder_group.folder_id = "folder".id AND folder_group.group_id = current_setting('app.current_user_group_id')::bigint)
                        INNER JOIN "project_user" ON (folder.project_id = "project_user".project_id AND "project_user".user_id = current_setting('app.current_user_id')::bigint)
                        WHERE folder.type = 'nda'
                    )
                )
            )
        );`);

score 3 · Accepted Answer · answered Dec 02 '20 at 16:02

You are probably running a PostgreSQL version older than v12, because pg_policy has a TOAST table since v12, so that oversized attributes can be stored out of line.

Before that, you were limited to policies where the pg_policy row fits into a single table block. Your policy text is less than that, but PostgreSQL stored the parsed query tree, which can be larger than the text.

Upgrade to a later PostgreSQL release or use several PERMISSIVE policies, which will be connected with OR.

ERROR: row is too big: size XXX, maximum size 8160 - related to pg_policies table

1 Answers1