Create schema of same name when user is created

[joshlk]

It common to have a schema that the same name as a user in a database. By default, postgres includes such a schema in its search_path automatically, see "section 5.8.3" in the docs.

It would be ace to have an option in ldap2pg that would create a schema of the same name when a user is newly created.

Thanks

[bersace]

Hi @joshlk actually, you can already do this by hacking the ACL mechanism :-) Just think grant as create and revoke as drop. This should look like:

acls:
  user_schema:
    type: datacl
    inspect: |
      SELECT NULL as "schema", rolname
      FROM pg_namespace n
      JOIN pg_authid ON rolname = nspname
    grant: |
      CREATE SCHEMA {role};
      ALTER USER {role} SET search_path {role},public;

sync_map:
- role: myuser
  grant:
    acl: user_schema
    database: mydb
    role: myuser

YMMV

[bersace]

I'm thinking of extending ldap2pg to help managing this case. Here is an idea:

- role:
    name: '{cn}'
    on_create: |
      CREATE SCHEMA {role};
    on_alter: |
      ALTER SCHEMA {old} RENAME TO {new};
    on_drop: |
      DROP SCHEMA {role} CASCADE;

What do you think of it ? Would you prefer global on_create, on_drop ? Thanks for the feedback.

[bersace]

Another solution would be the allows override of queries :

postgres:
  queries:
    role_create: |
      CREATE ROLE "{role}" WITH {options};
      COMMENT ON ROLE "{role}" IS '{comment}';
      CREATE SCHEMA "{role}";
      ALTER ROLE "{role}" SET search_path = "{role}",public;

[jeanpommier]

I'm thinking of extending ldap2pg to help managing this case. Here is an idea:

- role:
    name: '{cn}'
    on_create: |
      CREATE SCHEMA {role};
    on_alter: |
      ALTER SCHEMA {old} RENAME TO {new};
    on_drop: |
      DROP SCHEMA {role} CASCADE;

@bersace, is it still considered ? This looks quite interesting. Would definitely be useful for me !

[alehaa]

Would you prefer global on_create, on_drop ? Thanks for the feedback.

I would like to see both. Some queries might be useful on a global level, others just for specific roles.

[bersace]

Closing in favor of #565.