Issue with pg_partman Table Ownership and Permissions
Hello everyone,
I’m trying to set up the pg_partman extension following the guide as outlined below:
CREATE SCHEMA IF NOT EXISTS partman;
CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;
Then, I create a user for partman:
CREATE ROLE partman_user WITH LOGIN PASSWORD 'partman_user_my_long_password';
The following statement works without issues:
GRANT ALL ON SCHEMA partman TO partman_user;
However, when I attempt to execute:
GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman_user;
It fails.
After some investigation, I ran this query:
SELECT
c.relname AS table_name,
r.rolname AS table_owner
FROM
pg_class c
JOIN
pg_namespace n ON n.oid = c.relnamespace
JOIN
pg_roles r ON r.oid = c.relowner
WHERE
n.nspname = 'partman'
AND c.relkind = 'r'; -- 'r' stands for regular tables
This revealed that the table_owner of the tables in the partman schema is cloud_admin, not my user.
I suspect this ownership mismatch is causing the problem, but I don’t understand why the tables are being created under a role other than mine. I also couldn’t find any documentation or references explaining this behavior or the role cloud_admin.
Has anyone else encountered a similar issue? Any insights or guidance would be greatly appreciated!
Thank you!
6 Replies
modern-teal•8mo ago
Same error here. Any clue?
genetic-orange•8mo ago
have you tried this without creating a separate schema?
genetic-orange•8mo ago
ok, i have an update. this is a problem with how Neon handles roles in Postgres. We can fix this issue for you if you reach out to support. And we're working on an overall fix for this which you can follow in this issue: https://github.com/neondatabase/neon/issues/9894
GitHub
Epic: Postgres roles related issues · Issue #9894 · neondatabase/ne...
Motivation We have a number of issues that are related to the way we manage Postgres roles. Collect them all in one Epic to have a better overview of the problems and solve them consistently. See P...
genetic-orange•8mo ago
And I'm sorry but I don't have a workaround for you right now
NOTE: If you do this in the public schema it works. It only doesn't work in a different schema at the moment.
dependent-tanOP•8mo ago
Thanks @bryan for the reply! How long do you estimate it will take to release a fix for this specific issue?
genetic-orange•8mo ago
this change looks fairly complicated in our stack. it affects multiple extensions so we need to fix it. but i think we're looking some time this year, starting around summer time?