Initial RLS test passing in pg and postgres.js

pg.test.ts

test.serial('transaction with RLS', async (t) => {
	const { db } = t.context;

	const users = pgTable('users_transactions', {
		id: serial('id').primaryKey(),
		name: text('name').notNull(),
		lastName: text('lastname'),
	});

	await db.execute(sql`drop table if exists ${users}`);

	await db.execute(
		sql`create table ${users} (id serial not null primary key, name text not null, lastname text)`,
	);
	await db.execute(sql`CREATE ROLE admin_all`);

	await db.insert(users).values([{ name: 'user', lastName: 'lastName' }, {
		name: 'user2',
		lastName: 'lastName2',
	}]);

	await db.execute(sql`ALTER TABLE ${users} ENABLE ROW LEVEL SECURITY`);
	await db.execute(
		sql`CREATE POLICY all_view ON ${users} for select USING (true)`,
	);

	await db.execute(sql`GRANT USAGE ON SCHEMA public TO admin_all`);
	await db.execute(sql`GRANT all ON ${users} TO admin_all`);

	await db.transaction(async (tx) => {
		await tx.update(users).set({ lastName: 'another' }).where(eq(users.id, 1));
	}, {
		rlsConfig: {
			role: pgRole('admin_all'),
		},
	});

	const badResult = await db.select().from(users).where(eq(users.id, 1));

	t.deepEqual(badResult, [{ id: 1, name: 'user', lastName: 'lastName' }]);

	await db.execute(sql`CREATE POLICY administrator_all ON ${users} TO admin_all USING (true) WITH CHECK (true)`);

	await db.transaction(async (tx) => {
		await tx.update(users).set({ lastName: 'another' }).where(eq(users.id, 1));
	}, {
		rlsConfig: {
			role: pgRole('admin_all'),
		},
	});

	const result = await db.select().from(users).where(eq(users.id, 1));

	t.deepEqual(result, [{ id: 1, name: 'user', lastName: 'another' }]);

	await db.execute(sql`drop owned by admin_all`);
	await db.execute(sql`drop policy if exists administrator_all on ${users}`);
	await db.execute(sql`drop policy if exists all_view on ${users}`);
	await db.execute(sql`drop role if exists admin_all`);
	await db.execute(sql`drop table if exists ${users}`);
});