When I use postgres_scan an exception occurs: permission denied for table

[k0rsakov]

What happens?

I am using Quickdb 0.9.2.

In my SQL script, I call the postgres_scan command.

I specify the correct data for the connection: host, port, user, and so on. (I checked on other tables).

When I want to make a typical request:

SELECT
    user_id
FROM 
    postgres_scan('host=**** port=**** dbname=**** user=**** password=****', 'public', 'some_table')
LIMIT 10

I'm getting an error:

SQL Error: Invalid Error: Failed to prepare COPY "
	COPY (SELECT "user_id" FROM "public"."some_table" WHERE id BETWEEN '(0,0)'::tid AND '(1000,0)'::tid) TO STDOUT (FORMAT binary);
	": ERROR:  permission denied for table some_table.

But the table allows you to get data from it while working with it through PostgreSQL itself.

Remark: In the table, there is a viewing limit for some columns. Perhaps the problem is related to this moment?

To Reproduce

1. You need to install DuckDB 0.9.2
2. Run the postgres_scan command on a regular SELECT
3. Get an error
4. If there is no error, then check the constraints on the table (columns) and run SELECT again

OS:

MacOS 14.2.1 (23C71)

DuckDB Version:

0.9.2

DuckDB Client:

DBeaver

Full Name:

Ivan Korsakov

Affiliation:

By yourself

Have you tried this on the latest main branch?

I have tested with a main build

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• [X] Yes, I have

[duckdblabs-bot]

Thanks for opening this issue! Based on our automated check, it seems that your post contains some code but it does not use code blocks to format it.

Please double-check your post and revise it if necessary. To employ syntax highlighting, it's recommended to use code blocks with triple backticks, e.g.:

```sql
SELECT ...
```

If this is a false positive, feel free to disregard this comment.

[Mytherin]

Thanks for the report! I'm guessing you might not be able to query the tid column? Could you try running with SET threads=1?

[k0rsakov]

@Mytherin Thanks for your reply. I did as you said, but it didn't help. The error is exactly the same as before.

[Mytherin]

In https://github.com/duckdb/postgres_scanner/pull/195 I've added a new setting pg_use_ctid_scan that controls this behavior so you can more easily disable the parallel scans.