[Bug] [postgres] profile role: silently ignored for on-run-* hooks (SET ROLE reverted by clear_transaction)

### Is this a new bug?

- [x] I believe this is a new bug
- [x] I have searched the existing issues, and I could not find an existing issue for this bug

### Which packages are affected?

- [ ] dbt-adapters
- [ ] dbt-tests-adapter
- [ ] dbt-athena
- [ ] dbt-athena-community
- [ ] dbt-bigquery
- [x] dbt-postgres
- [ ] dbt-redshift
- [ ] dbt-snowflake
- [ ] dbt-spark

### Current Behavior

The `role:` profile option is documented as "the Postgres role that dbt assumes when opening new connections to the database," with no hook-specific exception. In practice, `role:` is silently ignored for `on-run-start` and `on-run-end` hooks: statements inside those hooks run as the login role, not the configured role.

On connection open, dbt-postgres issues `SET ROLE ` inside psycopg2's implicit transaction. Immediately before each hook phase, dbt-core calls `adapter.clear_transaction()`, which starts with `ROLLBACK`. Per PostgreSQL semantics, that rollback reverts the SET ROLE. Hook SQL then executes as the login role.

The failure is silent — no error is raised. Observable symptoms include:

Objects created by hooks are owned by the login role, not the configured role.
`GRANT` / `ALTER DEFAULT PRIVILEGES` statements in hooks record the login role as the grantor, breaking intended ownership chains.
Any session-level `SET` or `set_config(name, value, false)` that dbt-postgres itself issues on connection open (e.g. SET search_path, `SET TIME ZONE`) is subject to the same rollback.
This is a contract violation, not a hook-usage concern: the rolled-back `SET ROLE` is the one dbt-postgres itself emits on `open()`, regardless of what the user writes in hooks.

### Expected Behavior

Statements in `on-run-start` and `on-run-end` hooks should execute under the role configured via `role:`, consistent with model execution. Objects created by hooks should be owned by that role.

### Steps To Reproduce

1. Create two roles and a DB:
```sql
CREATE ROLE repro_role;
CREATE ROLE repro_login WITH LOGIN PASSWORD 'repro_pass';
GRANT repro_role TO repro_login;
CREATE DATABASE repro_bug OWNER repro_login;
```
2. profiles.yml:
```yaml
repro:
target: dev
outputs:
dev:
type: postgres
host: localhost
port: 5432
user: repro_login
password: repro_pass
dbname: repro_bug
schema: public
threads: 1
role: repro_role
```
3. dbt_project.yml:
```yaml
name: repro_role_bug
version: '1.0.0'
config-version: 2
profile: repro
on-run-start:
- "CREATE TABLE IF NOT EXISTS public.hook_created_table (id int)"
```
4. Run dbt run
5. Check ownership:
```sql
SELECT tablename, tableowner FROM pg_tables WHERE schemaname='public';
```
Actual: `tableowner = repro_login`. Expected: `repro_role`.

### Relevant log output

```shell
# PostgreSQL server log (log_statement=all) for the master connection handling on-run-start:
connection authorized: user=repro_login database=repro_bug
statement: BEGIN -- psycopg2 implicit TX
statement: set role repro_role -- PostgresConnectionManager.open()
statement: ROLLBACK -- clear_transaction()._rollback() ★ reverts SET ROLE
statement: BEGIN -- clear_transaction().begin()
statement: COMMIT -- clear_transaction().commit()
statement: CREATE TABLE IF NOT EXISTS public.hook_created_table (id int)
-- runs as repro_login, not repro_role

Inside the hook, `session_user`, `current_user`, and `current_role` are all `repro_login`.
```

### Environment

```markdown
- OS: macOS 14 (Darwin 25.3.0); also reproduces on Linux
- Python: 3.13.11
- dbt-core: 1.11.8
- dbt-adapters: 1.22.10
- dbt-postgres: 1.10.0
- PostgreSQL: 15.4
```

### Additional Context

#### Root cause

Interaction of three code paths plus PostgreSQL semantics:

1. **dbt-postgres** opens connections with `psycopg2` (default `autocommit=False`) and issues `SET ROLE` inside the implicit transaction — [`dbt-postgres/.../connections.py`](https://github.com/dbt-labs/dbt-adapters/blob/main/dbt-postgres/src/dbt/adapters/postgres/connections.py) (`PostgresConnectionManager.open`):

```python
if credentials.role:
handle.cursor().execute("set role {}".format(credentials.role))
```

2. **dbt-core** calls `adapter.clear_transaction()` immediately before each `on-run-*` hook phase — [`dbt-core/core/dbt/task/run.py#L1024-L1025`](https://github.com/dbt-labs/dbt-core/blob/main/core/dbt/task/run.py#L1024-L1025):

```python
# on-run-* hooks should run outside a transaction. This happens because
# psycopg2 automatically begins a transaction when a connection is created.
adapter.clear_transaction()
```

3. **dbt-adapters (base)** `clear_transaction()` issues `ROLLBACK`, then an empty `BEGIN`/`COMMIT` — [`dbt-adapters/.../base/connections.py#L114-L121`](https://github.com/dbt-labs/dbt-adapters/blob/main/dbt-adapters/src/dbt/adapters/base/connections.py#L114-L121):

```python
def clear_transaction(self) -> None:
conn = self.get_thread_connection()
if conn is not None:
if conn.transaction_open:
self._rollback(conn)
self.begin()
self.commit()
```

4. **PostgreSQL** [`SET` docs](https://www.postgresql.org/docs/current/sql-set.html):
> If `SET` (or equivalently `SET SESSION`) is issued within a transaction that is later aborted, the effects of the `SET` command disappear when the transaction is rolled back.

The comment in `run.py` acknowledges psycopg2's implicit `BEGIN` and uses the rollback to satisfy the "hooks run outside a transaction" invariant — but the same rollback erases the `SET ROLE` executed during `open()`.

#### Why this is a bug, not a hook-usage concern

dbt's docs list UDF creation, `grant` statements, `analyze`/`vacuum`, and materialized view refresh among standard `on-run-start` use cases ([Hooks & Operations](https://docs.getdbt.com/docs/build/hooks-operations)). The `role:` profile option ([Postgres profile](https://docs.getdbt.com/docs/core/connect-data-platform/postgres-setup)) has no hook-specific exception. The bug is independent of what users write in hooks — the rolled-back `SET ROLE` is the one dbt-postgres itself emits.

#### Known workarounds (neither documented in the postgres profile reference)

**1. Per-role-per-database GUC at the login role**

```sql
ALTER ROLE IN DATABASE SET role = ;
```

PostgreSQL applies this at login and it is not subject to ROLLBACK. Most complete, but requires superuser access at DB bootstrap.

**2. Explicit `SET ROLE` at the top of `on-run-start` and `on-run-end`**

```yaml
on-run-start:
- "SET ROLE "
- ""
on-run-end:
- "SET ROLE "
- ""
```

Empirically verified: once the `SET ROLE` is executed inside the hook sequence, the role is active for the remainder of `on-run-start`, the model phase (including pre/post-hooks), and `on-run-end`. Caveats: any statement placed **before** the `SET ROLE` still runs as the login role; must be repeated in `on-run-end` (since `clear_transaction()` is called before end-hooks too).

#### Suggested fix directions (not prescriptive)

**A** — Override `clear_transaction()` in `PostgresConnectionManager` to re-apply `SET ROLE` and commit it when `credentials.role` is set:

```python
def clear_transaction(self) -> None:
super().clear_transaction()
conn = self.get_thread_connection()
if conn is None or not getattr(conn.credentials, "role", None):
return
with conn.handle.cursor() as cursor:
cursor.execute("set role {}".format(conn.credentials.role))
conn.handle.commit()
```

Also apply in `rollback_if_open()` (or extract a shared helper) since that's another rollback path.

**B** — Commit the initial `SET ROLE` in `open()` so it persists across subsequent ROLLBACKs:

```python
if credentials.role:
handle.cursor().execute("set role {}".format(credentials.role))
handle.commit() # persist SET ROLE so the clear_transaction ROLLBACK can't undo it
```

**C** — Document the behavior and the `ALTER ROLE ... IN DATABASE ... SET role` workaround in the postgres profile reference.