Seeding auth.users with Gitlab CI/CD job

Hi all.

I am adding a DB testing job to my CI/CD pipelin in GitLab.
Following this guide -> https://supabase.com/docs/guides/database/testing I was able to correctly add a dozen of DB tests that run correctly locally.

Now, starting from this guide -> https://supabase.com/docs/guides/deployment/ci/testing I am trying to adapt the pipeline for a GitLab CI/CD job.

This is the code from my YAML file:

db_test:
  stage: quality
  image: node:18
  before_script:
    - npm i supabase
  cache:
    paths:
      - node_modules/
  variables:
    DOCKER_HOST: tcp://docker:2375
  script:
    - npx supabase db start
    - npx supabase test db
  services:
    - docker:dind
  rules:
    - if: '$CI_MERGE_REQUEST_TITLE =~ /^Draft:.*/'
      when: never
    - if: '$CI_PIPELINE_SOURCE == "merge_request_event"'
    - if: '$CI_COMMIT_BRANCH == "develop" && $CI_PIPELINE_SOURCE == "push"'
    - if: '$CI_COMMIT_BRANCH == "main" && $CI_PIPELINE_SOURCE == "push"'

db_test:
  stage: quality
  image: node:18
  before_script:
    - npm i supabase
  cache:
    paths:
      - node_modules/
  variables:
    DOCKER_HOST: tcp://docker:2375
  script:
    - npx supabase db start
    - npx supabase test db
  services:
    - docker:dind
  rules:
    - if: '$CI_MERGE_REQUEST_TITLE =~ /^Draft:.*/'
      when: never
    - if: '$CI_PIPELINE_SOURCE == "merge_request_event"'
    - if: '$CI_COMMIT_BRANCH == "develop" && $CI_PIPELINE_SOURCE == "push"'
    - if: '$CI_COMMIT_BRANCH == "main" && $CI_PIPELINE_SOURCE == "push"'

The job runs correctly, but I am having an issue.

During the

supabase db start

supabase db start

phase, the CLI correctly applies all migrations and runs my

seed.sql

seed.sql

file, that manipulates the auth.users table

alter table "auth"."users" add column IF NOT EXISTS is_anonymous boolean not null default false;

... 

INSERT INTO "auth"."users" (
  ....
) VALUES (
  ...
);

alter table "auth"."users" add column IF NOT EXISTS is_anonymous boolean not null default false;

... 

INSERT INTO "auth"."users" (
  ....
) VALUES (
  ...
);

The job exits with error

ERROR: must be owner of table users (SQLSTATE 42501)

ERROR: must be owner of table users (SQLSTATE 42501)

.

Anybody knows what' the best approach in this case?
I tried to set an high level role (e.g.

SET ROLE supabase_auth_admin;

SET ROLE supabase_auth_admin;

) in the seed file, but this operation is denied due to missing permissions (and also I don't think is a best practice

).

Any help wuold be much appreciated.
Thanks in advance,

Francesco

Testing Your Database | Supabase Docs

Test your database schema, tables, functions, and policies.

Automated testing using GitHub Actions | Supabase Docs

Run your tests when you or your team make changes.

Seeding auth.users with Gitlab CI/CD job

db_test:
  stage: quality
  image: node:18
  before_script:
    - npm i supabase
  cache:
    paths:
      - node_modules/
  variables:
    DOCKER_HOST: tcp://docker:2375
  script:
    - npx supabase db start
    - npx supabase test db
  services:
    - docker:dind
  rules:
    - if: '$CI_MERGE_REQUEST_TITLE =~ /^Draft:.*/'
      when: never
    - if: '$CI_PIPELINE_SOURCE == "merge_request_event"'
    - if: '$CI_COMMIT_BRANCH == "develop" && $CI_PIPELINE_SOURCE == "push"'
    - if: '$CI_COMMIT_BRANCH == "main" && $CI_PIPELINE_SOURCE == "push"'

db_test:
  stage: quality
  image: node:18
  before_script:
    - npm i supabase
  cache:
    paths:
      - node_modules/
  variables:
    DOCKER_HOST: tcp://docker:2375
  script:
    - npx supabase db start
    - npx supabase test db
  services:
    - docker:dind
  rules:
    - if: '$CI_MERGE_REQUEST_TITLE =~ /^Draft:.*/'
      when: never
    - if: '$CI_PIPELINE_SOURCE == "merge_request_event"'
    - if: '$CI_COMMIT_BRANCH == "develop" && $CI_PIPELINE_SOURCE == "push"'
    - if: '$CI_COMMIT_BRANCH == "main" && $CI_PIPELINE_SOURCE == "push"'

The job runs correctly, but I am having an issue.

During the

supabase db start

supabase db start

phase, the CLI correctly applies all migrations and runs my

seed.sql

seed.sql

file, that manipulates the auth.users table

alter table "auth"."users" add column IF NOT EXISTS is_anonymous boolean not null default false;

... 

INSERT INTO "auth"."users" (
  ....
) VALUES (
  ...
);

alter table "auth"."users" add column IF NOT EXISTS is_anonymous boolean not null default false;

... 

INSERT INTO "auth"."users" (
  ....
) VALUES (
  ...
);

The job exits with error

ERROR: must be owner of table users (SQLSTATE 42501)

ERROR: must be owner of table users (SQLSTATE 42501)

.

Anybody knows what' the best approach in this case?
I tried to set an high level role (e.g.

SET ROLE supabase_auth_admin;

SET ROLE supabase_auth_admin;

) in the seed file, but this operation is denied due to missing permissions (and also I don't think is a best practice

).

Any help wuold be much appreciated.
Thanks in advance,

Francesco

Testing Your Database | Supabase Docs

Test your database schema, tables, functions, and policies.

Automated testing using GitHub Actions | Supabase Docs

Run your tests when you or your team make changes.

Seeding auth.users with Gitlab CI/CD job

Seeding auth.users with Gitlab CI/CD job

Similar Threads

Similar Threads

Similar Threads