Cannot use an app account with db-migrator

[rleffd]

Hello,

A few weeks ago, I have reported that there was an error with the pg_partman extension when using an account that is not super admin.

A new commit fix this issue: #4104

I have build a new image with the two files modified, and now I have a new error:

ERROR: function partman.create_parent(unknown, unknown, unknown, p_start_partition => text) does not exist (SQLSTATE 42883)

RDS Postgres 16.4

Is there a way to run db-migrator without a super admin user on the postgres db ?

[tegioz]

Hi @rleffd 👋

It looks like the pg_partman extension hasn't been created yet. You still need to do that with a user that has privileges to do so, like you were trying to do in #4103 (comment). Once the extension is ready, you should be able to proceed with the migrations as we won't try to create it again if it already exists.

[rleffd]

the pg_partman extension is already created when the db-migrator starts. I have a script that create it in my pipeline:

CREATE USER :username WITH PASSWORD :'secret';
CREATE DATABASE :dbname ENCODING=UTF8 OWNER :username;
GRANT ALL PRIVILEGES ON DATABASE :dbname TO :username;
\c :dbname
CREATE EXTENSION IF NOT EXISTS pg_partman;

psql -h $PG_HOST -p $PG_PORT -U $PG_USERNAME -v "sslmode=require" \
        -v username=$PG_APP_USERNAME -v dbname=$PG_APP_USERNAME -v secret=$PG_APP_PASSWORD \
        -f ./scripts/initdb.sql

• PG_USERNAME is the master user
• PG_PASSWORD is the master password
• PG_APP_USERNAME is the app username

• rds:prepare execute de script below.

[tegioz]

The migration file referenced below creates the pg_partman extension in the partman schema, and the calls to the functions in the migration assume they'll be there (i.e. partman.create_parent(...)). But it looks like you are not creating the extension in that schema.

https://github.com/artifacthub/hub/blob/master/database/migrations/schema/034_partman.sql

Hope this helps 🙂

[rleffd]

Hello. i'will test that today. I can't use a super user to run artifacthub. The password change every week and it is not a good pratice at all.

I'll be back soon to give a feedback.

[tegioz]

You shouldn't need it. If you modify your script to create the extension in the partman schema it should work fine.

[rleffd]

Ok just run a new test with a fresh install and I run into a problem:

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
        a.sub_control
        , a.sub_partition_interval
        , a.sub_partition_type
        , a.sub_premake
        , a.sub_automatic_maintenance
        , a.sub_template_table
        , a.sub_retention
        , a.sub_retention_schema
        , a.sub_retention_keep_index
        , a.sub_retention_keep_table
        , a.sub_epoch
        , a.sub_constraint_cols
        , a.sub_optimize_constraint
        , a.sub_infinite_time_partitions
        , a.sub_jobmon
        , a.sub_inherit_privileges
        , a.sub_constraint_valid
        , a.sub_date_trunc_interval
        , a.sub_ignore_default_data
        , a.sub_default_table
        , a.sub_retention_keep_publication
    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,boolean,text,text[],text,boolean,text) line 217 at FOR over SELECT rows
SQL statement "SELECT partman.create_parent(
                'public.package_views',
                'day',
                '1 month',
                p_start_partition := current_date::text
            )"
PL/pgSQL function inline_code_block line 16 at PERFORM
DETAIL: 
HINT:  (SQLSTATE P0001)

The steps I've done:

• New image db-migrator with these two files:
  024_tsm_system_rows_extension.sql

create extension if not exists tsm_system_rows;
...

034_partman.sql

create schema if not exists partman;
create extension if not exists pg_partman schema partman;
...

• My script looks like this now:

create user :username with password :'secret';
create database :dbname encoding=utf8 owner :username;
grant all privileges on database :dbname to :username;
\c :dbname
create schema if not exists partman;
alter schema partman owner to :username;
create extension if not exists pg_partman schema partman;

psql -h xxx -p 5432 -U postgres -v "sslmode=require"         -v username=artifacthub -v dbname=artifacthub -v secret=xxxx         -f ./initdb.sql

I miss something ?

[tegioz]

I think you need to grant some extra permissions. In the setup section of the pgpartman readme file you'll find the privileges required when not using a superuser. The db user used for Artifact Hub will probably need those.

[rleffd]

exact ! it works with:

grant all privileges on all tables in schema partman to :username;