**NOTE:** all of these methods may be obsoleted, when `ensure*` is
reworked, but it is expected that they will stay viable for running
database migrations.

**NOTE:** please make sure that any added migrations are idempotent (re-runnable).

#### as superuser {#module-services-postgres-initializing-extra-permissions-superuser}

**Advantage:** compatible with postgres < 15, because it's run
as the database superuser `postgres`.

##### in database `postStart` {#module-services-postgres-initializing-extra-permissions-superuser-post-start}

**Disadvantage:** need to take care of ordering yourself. In this
example, `mkAfter` ensures that permissions are assigned after any
databases from `ensureDatabases` and `extraUser1` from `ensureUsers`
are already created.

```nix
  {
    systemd.services.postgresql.postStart = lib.mkAfter ''
      $PSQL service1 -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "extraUser1"'
      $PSQL service1 -c 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "extraUser1"'
      # ....
    '';
  }
```

##### in intermediate oneshot service {#module-services-postgres-initializing-extra-permissions-superuser-oneshot}

```nix
  {
    systemd.services."migrate-service1-db1" = {
      serviceConfig.Type = "oneshot";
      requiredBy = "service1.service";
      before = "service1.service";
      after = "postgresql.service";
      serviceConfig.User = "postgres";
      environment.PSQL = "psql --port=${toString services.postgresql.settings.port}";
      path = [ postgresql ];
      script = ''
        $PSQL service1 -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "extraUser1"'
        $PSQL service1 -c 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "extraUser1"'
        # ....
      '';
    };
  }
```

#### as service user {#module-services-postgres-initializing-extra-permissions-service-user}

**Advantage:** re-uses systemd's dependency ordering;

**Disadvantage:** relies on service user having grant permission. To be combined with `ensureDBOwnership`.

##### in service `preStart` {#module-services-postgres-initializing-extra-permissions-service-user-pre-start}

```nix
  {
    environment.PSQL = "psql --port=${toString services.postgresql.settings.port}";
    path = [ postgresql ];
    systemd.services."service1".preStart = ''
      $PSQL -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "extraUser1"'
      $PSQL -c 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "extraUser1"'
      # ....
    '';
  }
```

##### in intermediate oneshot service {#module-services-postgres-initializing-extra-permissions-service-user-oneshot}

```nix
  {
    systemd.services."migrate-service1-db1" = {
      serviceConfig.Type = "oneshot";
      requiredBy = "service1.service";
      before = "service1.service";
      after = "postgresql.service";
      serviceConfig.User = "service1";
      environment.PSQL = "psql --port=${toString services.postgresql.settings.port}";
      path = [ postgresql ];
      script = ''
        $PSQL -c 'GRANT SELECT ON ALL TABLES IN SCHEMA public TO "extraUser1"'
        $PSQL -c 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO "extraUser1"'
        # ....
      '';
    };
  }
```

## Authentication {#module-services-postgres-authentication}

Local connections are made through unix sockets by default and support [peer authentication](https://www.postgresql.org/docs/current/auth-peer.html).
This allows system users to login with database roles of the same name.
For example, the `postgres` system user is allowed to login with the database role `postgres`.

System users and database roles might not always match.
In this case, to allow access for a service, you can create a [user name map](https://www.postgresql.org/docs/current/auth-username-maps.html) between system roles and an existing database role.

### User Mapping {#module-services-postgres-authentication-user-mapping}

Assume that your app creates a role `admin` and you want the `root` user to be able to login with it.
You can then use [](#opt-services.postgresql.identMap) to define the map and [](#opt-services.postgresql.authentication) to enable it: