Thread: Permission denied in file_fdw (Windows)
Hi I'm getting an error when reading from a file_fdw table in a Windows environment. Any hints? (see below). And http://www.postgresql.org/docs/9.1/static/file-fdw.html is not really verbose :-> At least following format options should be mentioned: 'xml', 'text', 'csv', 'binary'. Yours, Stefan That's what I'm doing: CREATE EXTENSION file_fdw; CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw; CREATE FOREIGN TABLE foreignfiletable(id text, title text) SERVER file_server OPTIONS(format 'csv', header 'true', filename 'C:/Users/someuser/Downloads/file.csv', delimiter '|', null ''); SELECT * FROM foreignfiletable; -- ERROR: file »C:/Users/someuser/Downloads/file.csv« could not be opened for reading: Permission denied.
On 04/12/2012 23:39, Stefan Keller wrote: > Hi > > I'm getting an error when reading from a file_fdw table in a Windows > environment. > Any hints? (see below). > > And http://www.postgresql.org/docs/9.1/static/file-fdw.html is not > really verbose :-> > At least following format options should be mentioned: 'xml', 'text', > 'csv', 'binary'. > > Yours, Stefan > > > That's what I'm doing: > > CREATE EXTENSION file_fdw; > > CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw; > > CREATE FOREIGN TABLE foreignfiletable(id text, title text) > SERVER file_server > OPTIONS(format 'csv', header 'true', filename > 'C:/Users/someuser/Downloads/file.csv', delimiter '|', null ''); > > SELECT * FROM foreignfiletable; > -- ERROR: file »C:/Users/someuser/Downloads/file.csv« could not be > opened for reading: Permission denied. Have you checked that the user account under which the server is running has the necessary permissions to read that file? - That's what the error message would seem to indicate. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Hi, On 5.12.2012 00:39, Stefan Keller wrote: > Hi > > I'm getting an error when reading from a file_fdw table in a Windows > environment. > Any hints? (see below). Well, the file clearly isn't accessible by the postgres user (or whatever user you're using in Windows). The file is opened from a PostgreSQL backend process, not using your regular user. > And http://www.postgresql.org/docs/9.1/static/file-fdw.html is not > really verbose :-> > At least following format options should be mentioned: 'xml', 'text', > 'csv', 'binary'. It clearly states that "format" accepts the same values as COPY and even provides a link to COPY documentation. Placing this kind of details onto both pages would be just maintenance burden. > Yours, Stefan > > > That's what I'm doing: > > CREATE EXTENSION file_fdw; > > CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw; > > CREATE FOREIGN TABLE foreignfiletable(id text, title text) > SERVER file_server > OPTIONS(format 'csv', header 'true', filename > 'C:/Users/someuser/Downloads/file.csv', delimiter '|', null ''); > > SELECT * FROM foreignfiletable; > -- ERROR: file »C:/Users/someuser/Downloads/file.csv« could not be > opened for reading: Permission denied. What else could PostgreSQL tell you? It tried to open the file, did a regular open() or something like that and it failed with EACCES error code. That's all the info there is. Tomas
Hi Thomas Works like a charm no with different file path. 2012/12/5 Tomas Vondra <tv@fuzzy.cz>: > The file is opened from a > PostgreSQL backend process, not using your regular user. Thanks for the hint. Did'nt think of that - and I agree with this implementation decision. You are referring to RTFM below: May I dare to say that this is not mentioned in the - otherwise good - docs? > It clearly states that "format" accepts the same values as COPY and even > provides a link to COPY documentation. Placing this kind of details onto > both pages would be just maintenance burden. Ack. But let me object that from a readers view the sentence from the docs "Data files must be in a format that can be read by COPY FROM; see COPY for details." refers to the format of the file - not to the syntax of the parameters (strictly speaking :->). Yours, S. 2012/12/5 Tomas Vondra <tv@fuzzy.cz>: > Hi, > > On 5.12.2012 00:39, Stefan Keller wrote: >> Hi >> >> I'm getting an error when reading from a file_fdw table in a Windows >> environment. >> Any hints? (see below). > > Well, the file clearly isn't accessible by the postgres user (or > whatever user you're using in Windows). The file is opened from a > PostgreSQL backend process, not using your regular user. > >> And http://www.postgresql.org/docs/9.1/static/file-fdw.html is not >> really verbose :-> >> At least following format options should be mentioned: 'xml', 'text', >> 'csv', 'binary'. > > It clearly states that "format" accepts the same values as COPY and even > provides a link to COPY documentation. Placing this kind of details onto > both pages would be just maintenance burden. > >> Yours, Stefan >> >> >> That's what I'm doing: >> >> CREATE EXTENSION file_fdw; >> >> CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw; >> >> CREATE FOREIGN TABLE foreignfiletable(id text, title text) >> SERVER file_server >> OPTIONS(format 'csv', header 'true', filename >> 'C:/Users/someuser/Downloads/file.csv', delimiter '|', null ''); >> >> SELECT * FROM foreignfiletable; >> -- ERROR: file »C:/Users/someuser/Downloads/file.csv« could not be >> opened for reading: Permission denied. > > What else could PostgreSQL tell you? It tried to open the file, did a > regular open() or something like that and it failed with EACCES error > code. That's all the info there is. > > Tomas > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general