Auto user provisioning depends on default rights on PostgreSQL and they can be revoked

[albundy83]

Hello,
when using nextcloud and PostgreSQL, nextcloud database creation issue this command:

REVOKE ALL PRIVILEGES ON DATABASE <database_name> FROM PUBLIC;

You can find it here.

And actually, it removes those 2 privileges that are implicitely used by Teleport database access controls and auto user provisioning:

GRANT CONNECT, TEMPORARY ON DATABASE <database_name> TO PUBLIC;

You can find the details here from official PostgreSQL doc.

Here the interesting part:

For other types of objects, the default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases; EXECUTE privilege for functions and procedures; and USAGE privilege for languages and data types (including domains).

This implies 2 things if the REVOKE ALL PRIVILEGES ON DATABASE has been issued:

• Update doc when you want to create "teleport-admin" user and add the following GRANT:

GRANT CONNECT ON DATABASE <database_name> TO "teleport-admin" WITH GRANT OPTION;
GRANT TEMPORARY ON DATABASE <database_name> TO "teleport-admin";  # This one may not need WITH GRANT OPTION

• And for moment (I mean using teleport v17.2.7), we must restore the PUBLIC GRANTS to allow auto user provisioning to work:

GRANT CONNECT, TEMPORARY ON DATABASE <database_name> TO PUBLIC;

If not, we have the "normal" error:

FATAL: permission denied for database "<database_name>"
  Detail: User does not have CONNECT privilege.

So maybe, the following GRANT should be added somewhere here or here.

GRANT CONNECT, TEMPORARY ON DATABASE <database_name> TO <provisioned-user>;