SQL Error [XX000]: ERROR: DuckDB execution is not allowed because you have not been granted the duckdb.postgres_role

[lgonzalezsa]

What happens?

Using docker compose example from the repo, created a new PostgreSQL database and schema.

But when I tested I got this message:
SQL Error [XX000]: ERROR: DuckDB execution is not allowed because you have not been granted the duckdb.postgres_role

To Reproduce

Then, in my new database I ran:

new_db=# CREATE EXTENSION pg_duckdb;
CREATE EXTENSION
new_db=# CREATE EXTENSION pg_duckdb WITH SCHEMA my_new_schema;
ERROR:  extension "pg_duckdb" already exists
new_db=# exit

OS:

Linux

pg_duckdb Version (if built from source use commit hash):

0.2.0

Postgres Version (if built from source use commit hash):

16.4

Hardware:

No response

Full Name:

Luis Rolando Gonzalez

Affiliation:

HPE

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

[JelteF]

By default duckdb execution can only be done as superuser. You can change that by configuring a duckdb.postgres_role. Docs for this:

1. https://github.com/duckdb/pg_duckdb/blob/main/docs/settings.md#duckdbpostgres_role
2. https://github.com/duckdb/pg_duckdb/blob/main/docs/motherduck.md#non-supersuer-configuration

I guess you were not using a superuser for your tests.