Permission denied on schema partman when running create_parent()

[JerrySievert]

Hello,

I have a fairly clamped down database with roles for specific tasks and am having issues running partman.create_parent() on 4.6.0. (note that the BGW is not being built or installed).

our migrator role has the ability to create tables and indexes in the public schema, and pg_partman is installed in its own schema, using the docs with one additional grant:

      -- Create a schema for partman.
      CREATE SCHEMA IF NOT EXISTS partman;

      -- Create the extension in its own schema.
      CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;

      -- Set up the specific permissions for partman.
      CREATE ROLE partman WITH LOGIN;
      GRANT ALL ON SCHEMA partman TO partman;
      GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman;
      GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman;
      GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman;
      GRANT ALL ON SCHEMA public TO partman;
      GRANT TEMPORARY ON DATABASE test to partman;
      GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO migrator;
      GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO migrator;
      GRANT USAGE ON SCHEMA partman TO migrator;

a table is then created by the migrator role:

      CREATE TABLE test (
        id UUID NOT NULL DEFAULT public.gen_random_uuid(),
        status TEXT NOT NULL DEFAULT 'pending',
        created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
      ) PARTITION BY RANGE (created_at);

followed by the create_parent() also by the migrator role:

      SELECT partman.create_parent('public.test', 'created_at', 'native', 'monthly');

resulting in the error:

SELECT partman.create_parent('public.test', 'created_at', 'native', 'monthly');
ERROR:  permission denied for schema partman
CONTEXT: PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean,text) line 159 at EXECUTE
DETAIL:
HINT:
CONTEXT:  PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean,text) line 788 at RAISE

Any thoughts on where to look next? The line numbers don't seem to correspond with create_parent.sql,

Thanks much!

[JerrySievert]

changing to GRANT ALL PRIVILEGES ON SCHEMA partman to migrator gives the following error:

ERROR: permission denied for table part_config_sub
CONTEXT: SQL statement "WITH parent_table AS (
        SELECT h.inhparent AS parent_oid
        FROM pg_catalog.pg_inherits h
        JOIN pg_catalog.pg_class c ON h.inhrelid = c.oid
        JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
        WHERE c.relname = v_parent_tablename::name
        AND n.nspname = v_parent_schema::name
    ), sibling_children AS (
        SELECT i.inhrelid::regclass::text AS tablename
        FROM pg_inherits i
        JOIN parent_table p ON i.inhparent = p.parent_oid
    )
    -- This column list must be kept consistent between:
    --   create_parent, check_subpart_sameconfig, create_partition_id, create_partition_time, dump_partitioned_table_definition and table definition
    SELECT DISTINCT sub_partition_type
        , sub_control
        , sub_partition_interval
        , sub_constraint_cols
        , sub_premake
        , sub_inherit_fk
        , sub_retention
        , sub_retention_schema
        , sub_retention_keep_table
        , sub_retention_keep_index
        , sub_automatic_maintenance
        , sub_epoch
        , sub_optimize_trigger
        , sub_optimize_constraint
        , sub_infinite_time_partitions
        , sub_jobmon
        , sub_trigger_exception_handling
        , sub_upsert
        , sub_trigger_return_null
        , sub_template_table
        , sub_inherit_privileges
        , sub_constraint_valid
        , sub_subscription_refresh
    FROM partman.part_config_sub a
    JOIN sibling_children b on a.sub_parent = b.tablename LIMIT 1"
PL/pgSQL function partman.create_parent(text,text,text,text,text[],integer,text,text,boolean,text,text,text[],boolean,text,boolean,text) line 243 at FOR over SELECT rows
DETAIL:
HINT:

[keithf4]

You're missing GRANT ALL ON ALL TABLES IN SCHEMA partman TO migrator;

[keithf4]

Please review all the privileges given to the partman role and ensure they're all being given to migrator where needed.

[JerrySievert]

You're missing GRANT ALL ON ALL TABLES IN SCHEMA partman TO migrator;

as noted in my followup:

changing to GRANT ALL PRIVILEGES ON SCHEMA partman to migrator gives the following error:

and GRANT ALL ON ALL TABLES IN SCHEMA partman TO migrator; changes that to:

ERROR: permission denied for schema partman

so that doesn't appear the be the issue either.

migrator has full access to the partman schema, as well as create for tables, indexes, et al for schema public, partman has full access to partman as well as public.

obviously running this as the superuser works, but the idea is to use the partman security definer functions to limit access to defined roles.

[keithf4]

GRANT ALL PRIVILEGES ON SCHEMA

This only provides permissions on the schema (CREATE & USAGE) not on objects within the schema

You must grant privileges on the objects within a schema directly to those objects.

[keithf4]

I'm really not sure why you may still be getting schema privilege errors, tho. Looks like you've granted the proper privileges. Only thing I can suggest is to ensure you're running as the proper user when it runs. Also, double-check the output of \dn+ to ensure all the grants for the schema have been given as you intended.

[JerrySievert]

I'm really not sure why you may still be getting schema privilege errors, tho.

yeah, me either. I realize that the permissions are ratcheted down, but the security definer should be working past that, especially given the very generous permissions given to the partman role.

I'll give it a few more attempts tomorrow, even trying within the public schema, but I'm hesitant to move non-application functions into the same schema as production data if I can help it.

[keithf4]

That list of grants in the top level README should be everything that's need to allow a non-superuser to run pg_partman in a specific schema. However, if you find something missing from my list, let me know. I'd realized I needed to add the temporary table permissions relatively recently.

[JerrySievert]

in my case, I needed GRANT USAGE ON SCHEMA partman TO migrator;, but that's a special case due to the migrator role not having access to the partman schema.

I'll start adding elog(NOTICE, ...) er, RAISE NOTICE to the function tomorrow, and see if I can better trace it down.

another thought I had is to downgrade the pg instance to 12 or 13 to see if the problem goes away. I don't expect it will, but failed to note that I'm running this on 14.2 (which is the final target across self-hosted, rds, aurora, and likely a k8s buildout like big animal).

[JerrySievert]

      GRANT ALL ON ALL TABLES IN SCHEMA partman TO migrator;
      GRANT ALL PRIVILEGES ON SCHEMA partman TO migrator;

it looks like it works with both specified, but not with only one (either) of them specified. I didn't have time to track down which call in create_parent() is getting the permission error: I don't know why the security definer isn't working in our favor here.

this is a little more open, permissions-wise than I like, so I'll end up doing an audit at some point and hopefully figure out where it's having issues, but for now at least it's working with that change.

it might be worth a quick doc update :)

[keithf4]

Right. Just to clarify.

GRANT ALL ON ALL TABLES IN SCHEMA partman TO migrator; This would give you only permissions on the objects within a schema. If you don't have at least USAGE privileges on the schema, you cannot access the objects within the schema even if you technically have SELECT, UPDATE, etc on them.

GRANT ALL PRIVILEGES ON SCHEMA partman TO migrator; This only gives you permissions to access objects within the schema. More fine grained, USAGE gives you access to see and potentially use (depending on previous command) objects in the schema. CREATE gives you access to create objects in that schema. You still need some form of the previous command to actually do anything on the objects within the schema.

So, yes, both commands are required. It is why I have them both in the list. :)

[JerrySievert]

So, yes, both commands are required. It is why I have them both in the list. :)

must be a different list than I'm looking at - here's the one from the README:

CREATE ROLE partman WITH LOGIN;
GRANT ALL ON SCHEMA partman TO partman;
GRANT ALL ON ALL TABLES IN SCHEMA partman TO partman;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO partman;
GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO partman;  -- PG11+ only
GRANT ALL ON SCHEMA my_partition_schema TO partman;
GRANT TEMPORARY ON DATABASE mydb to partman; -- allow creation of temp tables to move data out of default 

is there another section of the documentation where those permissions are specified that I'm just missing? I was looking at the docs that specify the permissions needed for installing partman not as a super-user. maybe there needs to be an ancillary comment about how to run as a non-superuser when it too is installed as a non-superuser?

[keithf4]

Yes, those two commands are in there. The paragraph before that states that superuser is still required for installing pg_partman, but the following privilege commands are needed to allow a non-superuser to run it. There is currently no option for actually installing as a non-supueruser.

[keithf4]

Just noticed you have the word PRIVILEGES in there where I don't. That is an optional clause

GRANT ALL PRIVILEGES ON SCHEMA partman TO migrator;

is the same as

GRANT ALL ON SCHEMA partman TO migrator;

[JerrySievert]

the commands are there for partman, but not for the non-privileged user calling it - those were my changes/additions.

both roles are created, and the calling role (migrator) needs to have specific permissions as well as partman.

and yeah, I know it's an optional clause, but it's going into a template on our end, so I was being pedantic on our side (and copy/pasted) :)

[keithf4]

partman is just an example non-privileged user. pg_partman makes no users for itself.

[keithf4]

You technically don't even need the partman user if you give the necessary privileges to your migrator user.

[keithf4]

Will be closing this issue in the near future unless you have any other questions.

[JerrySievert]

thanks for your help, going ahead and closing!

[baznikin]

There is currently no option for actually installing as a non-supueruser.

Can I ask why? Actually I can create schema, install extension without superuser rights (I do it under database owner role). But for some unknown reason partman tables owned by postgres user. I simply can't apply my app migrations straightforward without giving permissions on those tables or changing ownership (it works as well). Why not to create them with owner same as database itself?

[keithf4]

There is currently no option for actually installing as a non-supueruser.

Can I ask why? Actually I can create schema, install extension without superuser rights (I do it under database owner role). But for some unknown reason partman tables owned by postgres user. I simply can't apply my app migrations straightforward without giving permissions on those tables or changing ownership (it works as well). Why not to create them with owner same as database itself?

Several functions still required SECURITY DEFINER to not break trigger-based partitioning. With that going away in 5.x, I can look into allowing the extension to be installed as a non-superuser. Will make an issue for it, but it may not make it into the initial 5.x release since I have a lot of other stuff to do and I want to thoroughly test that first.

[keithf4]

@baznikin New issue https://github.com/pgpartman/pg_partman/issues/512 created if you want to follow that