Cannot use UPath on S3 with pandas: PermissionError/Access Denied

[ba1dr]

import pandas as pd
from upath import UPath

AWS_KEY = "AKIAxxxxxxx"
AWS_SECRET = "xxxxxxxxxxxxxxx"

bucket = 'upathtest'
fkey = f"folder1/folder2/test1.xlsx"
s3base = UPath(f"s3://{bucket}", key=AWS_KEY, secret=AWS_SECRET)
s3path = s3base / fkey

print(list(s3base.iterdir()))      # THIS WORKS!
with s3path.open('w') as ff:
    ff.write("test1,test2")        # THIS WORKS EITHER!

df = pd.DataFrame()
df.to_excel(s3path)           # !! This fails

Traceback

Traceback (most recent call last):
  File "/mypath/venv/lib/python3.11/site-packages/s3fs/core.py", line 113, in _error_wrapper
    return await func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/aiobotocore/client.py", line 411, in _make_api_call
    raise error_class(parsed_response, operation_name)
botocore.exceptions.ClientError: An error occurred (403) when calling the HeadObject operation: Forbidden

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/mypath/try_fss.py", line 57, in <module>
    main()
  File "/mypath/try_fss.py", line 53, in main
    test03()
  File "/mypath/try_fss.py", line 47, in test03
    pd.read_csv(s3path)
  File "/mypath/venv/lib/python3.11/site-packages/pandas/io/parsers/readers.py", line 1026, in read_csv
    return _read(filepath_or_buffer, kwds)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/pandas/io/parsers/readers.py", line 620, in _read
    parser = TextFileReader(filepath_or_buffer, **kwds)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/pandas/io/parsers/readers.py", line 1620, in __init__
    self._engine = self._make_engine(f, self.engine)
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/pandas/io/parsers/readers.py", line 1880, in _make_engine
    self.handles = get_handle(
                   ^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/pandas/io/common.py", line 728, in get_handle
    ioargs = _get_filepath_or_buffer(
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/pandas/io/common.py", line 443, in _get_filepath_or_buffer
    ).open()
      ^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/fsspec/core.py", line 147, in open
    return self.__enter__()
           ^^^^^^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/fsspec/core.py", line 105, in __enter__
    f = self.fs.open(self.path, mode=mode)
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/fsspec/spec.py", line 1303, in open
    f = self._open(
        ^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/s3fs/core.py", line 689, in _open
    return S3File(
           ^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/s3fs/core.py", line 2183, in __init__
    super().__init__(
  File "/mypath/venv/lib/python3.11/site-packages/fsspec/spec.py", line 1742, in __init__
    self.size = self.details["size"]
                ^^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/fsspec/spec.py", line 1755, in details
    self._details = self.fs.info(self.path)
                    ^^^^^^^^^^^^^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/fsspec/asyn.py", line 118, in wrapper
    return sync(self.loop, func, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/fsspec/asyn.py", line 103, in sync
    raise return_result
  File "/mypath/venv/lib/python3.11/site-packages/fsspec/asyn.py", line 56, in _runner
    result[0] = await coro
                ^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/s3fs/core.py", line 1375, in _info
    out = await self._call_s3(
          ^^^^^^^^^^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/s3fs/core.py", line 366, in _call_s3
    return await _error_wrapper(
           ^^^^^^^^^^^^^^^^^^^^^
  File "/mypath/venv/lib/python3.11/site-packages/s3fs/core.py", line 145, in _error_wrapper
    raise err
PermissionError: Forbidden

I tried to use client_kwargs - this does not work either.

aioboto_client_kwargs = {
    'aws_access_key_id': AWS_KEY,
    'aws_secret_access_key': AWS_SECRET,
}
s3base = UPath(f"s3://{bucket}", client_kwargs=aioboto_client_kwargs)
...
# same error

AWS user has AmazonS3FullAccess policy attached.

[ap--]

Thank you for opening the issue.

The implementation in pandas.io.common of _get_filepath_or_buffer basically converts the provided UPath instance into a string and drops the storage_options.
This causes pandas to then try to interpret the returned s3 uri without the storage options.

The reason for this happening is that UPath incorrectly pretends to be local path, which is going to be fixed when we move the correct base class: PathBase which is not going to provide a __fspath__ dunder anymore for non-local paths.

In the future we could also try to add support for arbitrary PathBase subclasses in pandas. But at least for universal_pathlib the mentioned changes in UPath should happen first.

All that being said, you can either provide the buffer as you've done in the with context directly to .to_excel() or provide the storage_options explicitly as shown here:

import pandas as pd
from upath import UPath

pth = UPath(f"s3://some-bucket/some-file", key=..., secret=...)

df = pd.DataFrame()
df.to_excel(pth, storage_options=pth.storage_options)   

Let me know if that helps,
Andreas

[ba1dr]

Thank you for the answer. However, this does not help much, as the idea was in simply replacing the Path objects to UPath, without changing it everywhere. I am refactoring a big piece of code and was hoping this could help to transparently work with any path objects.

[ap--]

Given the current implementation in pandas, and the current implementation in universal_pathlib, what you can do to achieve what you're asking for is to not provide credentials explicitly, but set the credentials via any of the supported methods for s3fs described here: https://s3fs.readthedocs.io/en/latest/#credentials

I also recommend to subscribe to #193 to be notified once work starts to move UPath to its correct base class available in future versions of stdlib pathlib (and backported in pathlib-abc)