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!
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!
