Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
John, It's the user created by the one-click installer. I believe it owns the postgres data directory and is used to start the server. Other than that, the intention is for this user to have no other file privileges. The default is postgres but it could be anything. doing the default install, the installer uses cacls to correctly set the access privileges to the database files for the created user. AS the database directory was zipped and copied from computer to computer; something different happened I know it is possible to transfer NTFS files keeping their ACL, but I have no information HOW the files were transferred. HOW containing information as in: - under which user account - using which method - preserving or not preserving ACLs Are there any group-policies or similar, or security-applications present, which can change the rights of this user postgres? (Or, can change the access-properties of files on the system?) I don't know. It is not my computer, it is my client's computer. We will investigate if anything like that is going on. He was only available until 4PM today and we just discovered what was happening shortly before that point. The people that do their security should be available Monday and we can ask them this type of question. please check out the cacls command line utitlity of windows. With this you should be able to print out all privileges of the PostgreSQL data directory to a text file, which can be transferred to you. You can then compare the privileges of the files on the non-working computer with the working computer. You can especially check the privileges for the toast-files (the ones named in the error message) Any idea of what to look for? when working with the first PostgreSQL versions on windows, I was surprised by a group policy randomly taking away the run as service privilege for the local user. Just want to point out that system-level changes which can affect PostgreSQL WITHOUT anything in PostgreSQL. That somebody was me, experimenting over the years. But I have not been messing around with this particular application. However, I'm not sure what the client did, as they copied the data files between the two computers at a time when I wasn't available. (They zipped, then unzipped after logging in as the proper user.) Okay, that experimenting is good thing to do :) on development systems. As a developer for multiple clients, I need easy access to my development copies of my clients' postgres data files. Therefore I have experimented with allowing my own userid to have access to the data directory and the subdirectories and files. I believe postgres doesn't care if you allow extra users, as long as postgres still has the proper access. Postgres does not even know about extra access privileges. Only the installer does something with access rights during database installation; after that everything changing the access permissions is from outside. (One possible scenario: the postgres service being started with its authorization set to local system - that would explain your files with owner system. And local system (or similar) is the default for SQL Server and Oracle ... the danger that one good-willing local administrator changed the logon-credentials?) Harald John Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger StraAYe 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - Using PostgreSQL is mostly about sleeping well at night. -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - Using PostgreSQL is mostly about sleeping well at night.
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
On Mon, Jun 14, 2010 at 05:17, John T. Dow j...@johntdow.com wrote: I was talking to a friend (Joe Newcomer) who said that Unix doesn't have mandatory file locks and he guessed that the empty, system, read only files I saw at my client's site were unix-like lock files. They are not. They are regular relation files. To test that, on my home development computer I typed this command in the base\16384 diretory: attrib +r 2611 That is, I made 2611 read only. Sure enough, pgadmin can't display the columns for any of the tables. I get permission denied for 2611. And sure enough, the Java application runs fine and indeed is able to export the table definition, complete with columns. Most likely because pgadmin tries to fetch all information about the table, including toast relations, whereas the java application only fetches the information it actually needs. So this is exactly the behavior observed at my client's site. Apparently the problem boils down to this question: how did some of the files get set to be system and read only? Yes. That would be very interesting to know. PostgreSQL never (intentionally) sets these flags, so they must've come from something else. If you remove those flags, do they eventually come back on? Is so, you probably want to install some level of monitoring tool (process monitor from sysinternals is recommended) to figure out when that gets set. Anybody ever seen this? Well, at the risk of sounding like a broken clock, yes - with antivirus or antispyware that sets the flag on things they find suspicious. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
On Mon, Jun 14, 2010 at 2:35 AM, Magnus Hagander mag...@hagander.net wrote: On Mon, Jun 14, 2010 at 05:17, John T. Dow j...@johntdow.com wrote: Apparently the problem boils down to this question: how did some of the files get set to be system and read only? Yes. That would be very interesting to know. PostgreSQL never (intentionally) sets these flags, so they must've come from something else. Being a non-privaledged account, does the postgres user even have the power to do that? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
On Mon, Jun 14, 2010 at 10:57, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Jun 14, 2010 at 2:35 AM, Magnus Hagander mag...@hagander.net wrote: On Mon, Jun 14, 2010 at 05:17, John T. Dow j...@johntdow.com wrote: Apparently the problem boils down to this question: how did some of the files get set to be system and read only? Yes. That would be very interesting to know. PostgreSQL never (intentionally) sets these flags, so they must've come from something else. Being a non-privaledged account, does the postgres user even have the power to do that? Yes, IIRC any user that has write permissions on a file can set the attributes, including readonly and system. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
Today we were able to look at the first computer's files. About 20 - 30 of them were marked system, hidden, read only. We cleared the attribute bits with the ATTRIB command and the database now appears to be normal. We were able to do a backup (that failed before) and were able to paste in 50K blocks of text with no problems. We verified that the data directory is owned by the proper postgres user (limited account) and that only that user has access to the directory or its files. (This is unlike the second computer, where we had permitted other users to have access.) So it appears to be a pristine postgres installation, except that somehow some of the files were changed. We have no clue how that happened. At this time the AV software is not installed. We will watch carefully to see if files get altered again in this way. Although the second computer has a newer OS and is more powerful, the client prefers to leave the database on the original computer because it has better physical security. Now we just wait to see what happens next, if anything. Thanks for your help. John On Mon, 14 Jun 2010 10:59:18 +0200, Magnus Hagander wrote: On Mon, Jun 14, 2010 at 10:57, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Jun 14, 2010 at 2:35 AM, Magnus Hagander mag...@hagander.net wrote: On Mon, Jun 14, 2010 at 05:17, John T. Dow j...@johntdow.com wrote: Apparently the problem boils down to this question: how did some of the files get set to be system and read only? Yes. That would be very interesting to know. PostgreSQL never (intentionally) sets these flags, so they must've come from something else. Being a non-privaledged account, does the postgres user even have the power to do that? Yes, IIRC any user that has write permissions on a file can set the attributes, including readonly and system. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
On Sun, Jun 13, 2010 at 5:11 AM, Craig Ringer cr...@postnewspapers.com.au wrote: On 13/06/10 02:34, Adrian Klaver wrote: Question: Is it possible that there's corruption in the database which is being incorrectly reported as Permission denied? It's certainly not impossible. It'd really help if Pg would print more details from Windows' error reporting - GetLastError() etc - in cases like this. In fact, some searching reveals complaints about just that as far back as mid-2008 related to the exact error you're encountering. It does if you enable debug logging. DEBUG5 is required from what I can tell (see src/port/win32error.c, function _dosmaperr(), which is called from pgwin32_open()). In a lot of cases it maps straight over, but in the cases where we have to map to an errno value and use that, there can be more than one. In the case of access denied, it can be: ERROR_ACCESS_DENIED ERROR_CURRENT_DIRECTORY ERROR_LOCK_VIOLATION ERROR_SHARING_VIOLATION (but this is taken care of already in pgwin32_open) ERROR_NETWORK_ACCESS_DENIED ERROR_CANNOT_MAKE ERROR_FAIL_I24 ERROR_DRIVE_LOCKED ERROR_SEEK_ON_DEVICE ERROR_NOT_LOCKED ERROR_LOCK_FAILED Most of these can't (shouldn't be possible at least) appear when we're opening a file for reading. But it'd be interesting to know what they were. So it'd be interesting to see the output of this at DEBUG5 (there should be a line saying mapped win32 error code n to n showing up - there will be *tons* of other logging output of course) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
I have information We had noticed two relations, their numbers being 16384/16642 and 16384/16792. Here is what pg_class has for them. relname;relnamespace;reltype;relowner;relam;relfilenode;reltablespace;relpages;reltuples;reltoastrelid;reltoastidxid;relhasindex;relisshared;relistemp;relkind;relnatts;relchecks;relhasoids;relhaspkey;relhasrules;relhastriggers;relhassubclass;relfrozenxid;relacl;reloptions pg_toast_16638;99;16643;16510;0;16642;0;0;0;0;16644;t;f;f;t;3;0;f;t;f;f;f;1581;; pg_toast_16788;99;16793;16510;0;16792;0;0;0;0;16794;t;f;f;t;3;0;f;t;f;f;f;2202;; We also looked at the permissions and whether the files actually exist. Findings: The files are both marked system file and have size 0 K. When logging on as an administrator and opening the files (eg with notepad, just to see if there is nothing at all) they appear to be empty. However, while we were working on the problem, pgadmin3 started reporting permission denied for 2611. At the same time, pgadmin was unable to see the columns of the tables. Attempting to do so is what caused the error for 2611. 2611 also appeared to be a system file with 0 bytes. Meantime, pgadmin was able to create a table and see the columns on the standard postgres database. Also, the Java application was able to see the columns and list them out as well. I have noticed that postgres is very unhappy if the proper postgres user doesn't have access to the files. But I have also noticed that other users seem to be able to have access without causing problems. I realize this compromises security, but in a development environment it is very convenient, eg when doing a system backup. Is it possible that some type of user might be causing files to be created as or changed to system files, marked read only, and apparently empty? I am not certain which users have access to the files at the client's site, but I know it's more than just the postgres user. All of these findings were on the second computer running XP. We ran out of time today before we investigated the original server to see if it also had system files marked read only with no apparent contents. John On Sun, 13 Jun 2010 11:51:45 +0200, Magnus Hagander wrote: On Sun, Jun 13, 2010 at 5:11 AM, Craig Ringer cr...@postnewspapers.com.au wrote: On 13/06/10 02:34, Adrian Klaver wrote: Question: Is it possible that there's corruption in the database which is being incorrectly reported as Permission denied? It's certainly not impossible. It'd really help if Pg would print more details from Windows' error reporting - GetLastError() etc - in cases like this. In fact, some searching reveals complaints about just that as far back as mid-2008 related to the exact error you're encountering. It does if you enable debug logging. DEBUG5 is required from what I can tell (see src/port/win32error.c, function _dosmaperr(), which is called from pgwin32_open()). In a lot of cases it maps straight over, but in the cases where we have to map to an errno value and use that, there can be more than one. In the case of access denied, it can be: ERROR_ACCESS_DENIED ERROR_CURRENT_DIRECTORY ERROR_LOCK_VIOLATION ERROR_SHARING_VIOLATION (but this is taken care of already in pgwin32_open) ERROR_NETWORK_ACCESS_DENIED ERROR_CANNOT_MAKE ERROR_FAIL_I24 ERROR_DRIVE_LOCKED ERROR_SEEK_ON_DEVICE ERROR_NOT_LOCKED ERROR_LOCK_FAILED Most of these can't (shouldn't be possible at least) appear when we're opening a file for reading. But it'd be interesting to know what they were. So it'd be interesting to see the output of this at DEBUG5 (there should be a line saying mapped win32 error code n to n showing up - there will be *tons* of other logging output of course) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
John, I have noticed that postgres is very unhappy if the proper postgres user doesn't have access to the files. But I have also noticed that other users seem to be able to have access without causing problems. can you please give more information about the (windows)-user postgres ? is it a local user on that machine? How was that user created? Are there any group-policies or similar, or security-applications present, which can change the rights of this user postgres? (Or, can change the access-properties of files on the system?) Your sentenceabout postgres being unhappy when not having access to the files makes me curious how you did learn that --- was somebody / something taking file access away from Postgres? Could that somebody / something still be active? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - Using PostgreSQL is mostly about sleeping well at night.
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
On Sunday 13 June 2010 1:41:01 pm John T. Dow wrote: I have information We had noticed two relations, their numbers being 16384/16642 and 16384/16792. Here is what pg_class has for them. relname;relnamespace;reltype;relowner;relam;relfilenode;reltab lespace;relpages;reltuples;reltoastrelid;reltoastidxid;relhasindex ;relisshared;relistemp;relkind;relnatts;relchecks;relhasoids;r elhaspkey;relhasrules;relhastriggers;relhassubclass;relfrozenxid;r elacl;reloptions pg_toast_16638;99;16643;16510;0;16642;0;0;0;0;16644;t;f;f;t;3;0;f;t;f;f ;f;1581;; pg_toast_16788;99;16793;16510;0;16792;0;0;0;0;16794;t;f;f;t;3;0;f;t;f;f ;f;2202;; We also looked at the permissions and whether the files actually exist. Findings: The files are both marked system file and have size 0 K. When logging on as an administrator and opening the files (eg with notepad, just to see if there is nothing at all) they appear to be empty. Whose permissions do they have? However, while we were working on the problem, pgadmin3 started reporting permission denied for 2611. At the same time, pgadmin was unable to see the columns of the tables. Attempting to do so is what caused the error for 2611. 2611 also appeared to be a system file with 0 bytes. What does Postgres think it is? Another TOAST table? Meantime, pgadmin was able to create a table and see the columns on the standard postgres database. Now I am confused. What are you calling the standard Postgres database? Also, the Java application was able to see the columns and list them out as well. Of which database? I have noticed that postgres is very unhappy if the proper postgres user doesn't have access to the files. But I have also noticed that other users seem to be able to have access without causing problems. I realize this compromises security, but in a development environment it is very convenient, eg when doing a system backup. Sort of the purpose of permissions :) Is it possible that some type of user might be causing files to be created as or changed to system files, marked read only, and apparently empty? It would seem so. The question is whether this a historical artifact from corruption in the past or is ongoing? I am not certain which users have access to the files at the client's site, but I know it's more than just the postgres user. All of these findings were on the second computer running XP. We ran out of time today before we investigated the original server to see if it also had system files marked read only with no apparent contents. John -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
can you please give more information about the (windows)-user postgres ? is it a local user on that machine? How was that user created? It's the user created by the one-click installer. I believe it owns the postgres data directory and is used to start the server. Other than that, the intention is for this user to have no other file privileges. The default is postgres but it could be anything. Are there any group-policies or similar, or security-applications present, which can change the rights of this user postgres? (Or, can change the access-properties of files on the system?) I don't know. It is not my computer, it is my client's computer. We will investigate if anything like that is going on. He was only available until 4PM today and we just discovered what was happening shortly before that point. The people that do their security should be available Monday and we can ask them this type of question. Any idea of what to look for? Your sentenceabout postgres being unhappy when not having access to the files makes me curious how you did learn that --- was somebody / something taking file access away from Postgres? Could that somebody / something still be active? That somebody was me, experimenting over the years. But I have not been messing around with this particular application. However, I'm not sure what the client did, as they copied the data files between the two computers at a time when I wasn't available. (They zipped, then unzipped after logging in as the proper user.) As a developer for multiple clients, I need easy access to my development copies of my clients' postgres data files. Therefore I have experimented with allowing my own userid to have access to the data directory and the subdirectories and files. I believe postgres doesn't care if you allow extra users, as long as postgres still has the proper access. John Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger StraAYe 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - Using PostgreSQL is mostly about sleeping well at night. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
I was talking to a friend (Joe Newcomer) who said that Unix doesn't have mandatory file locks and he guessed that the empty, system, read only files I saw at my client's site were unix-like lock files. To test that, on my home development computer I typed this command in the base\16384 diretory: attrib +r 2611 That is, I made 2611 read only. Sure enough, pgadmin can't display the columns for any of the tables. I get permission denied for 2611. And sure enough, the Java application runs fine and indeed is able to export the table definition, complete with columns. So this is exactly the behavior observed at my client's site. Apparently the problem boils down to this question: how did some of the files get set to be system and read only? Anybody ever seen this? Perhaps it's not even a postgres question. We will investigate further Monday when people are in the office. Any thoughts from anybody would be appreciated. Reminder: the problem with 2611 was observed on the second computer, which runs XP Pro 2002 SP3. The problems pasting 50K of text was first observed on the first computer, running 2000 Server if I remember right. It does not therefore seem to be related to AV software (the original suggestion) or the OS. John On Sun, 13 Jun 2010 14:10:27 -0700, Adrian Klaver wrote: On Sunday 13 June 2010 1:41:01 pm John T. Dow wrote: I have information We had noticed two relations, their numbers being 16384/16642 and 16384/16792. Here is what pg_class has for them. relname;relnamespace;reltype;relowner;relam;relfilenode;reltab lespace;relpages;reltuples;reltoastrelid;reltoastidxid;relhasindex ;relisshared;relistemp;relkind;relnatts;relchecks;relhasoids;r elhaspkey;relhasrules;relhastriggers;relhassubclass;relfrozenxid;r elacl;reloptions pg_toast_16638;99;16643;16510;0;16642;0;0;0;0;16644;t;f;f;t;3;0;f;t;f;f ;f;1581;; pg_toast_16788;99;16793;16510;0;16792;0;0;0;0;16794;t;f;f;t;3;0;f;t;f;f ;f;2202;; We also looked at the permissions and whether the files actually exist. Findings: The files are both marked system file and have size 0 K. When logging on as an administrator and opening the files (eg with notepad, just to see if there is nothing at all) they appear to be empty. Whose permissions do they have? However, while we were working on the problem, pgadmin3 started reporting permission denied for 2611. At the same time, pgadmin was unable to see the columns of the tables. Attempting to do so is what caused the error for 2611. 2611 also appeared to be a system file with 0 bytes. What does Postgres think it is? Another TOAST table? Meantime, pgadmin was able to create a table and see the columns on the standard postgres database. Now I am confused. What are you calling the standard Postgres database? Also, the Java application was able to see the columns and list them out as well. Of which database? I have noticed that postgres is very unhappy if the proper postgres user doesn't have access to the files. But I have also noticed that other users seem to be able to have access without causing problems. I realize this compromises security, but in a development environment it is very convenient, eg when doing a system backup. Sort of the purpose of permissions :) Is it possible that some type of user might be causing files to be created as or changed to system files, marked read only, and apparently empty? It would seem so. The question is whether this a historical artifact from corruption in the past or is ongoing? I am not certain which users have access to the files at the client's site, but I know it's more than just the postgres user. All of these findings were on the second computer running XP. We ran out of time today before we investigated the original server to see if it also had system files marked read only with no apparent contents. John -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
I posted this two days ago and nobody has commented. I'm reposting the message because I really need advice. Background info: My client got the permission denied error on his original server and we removed the AV software without solving the problem. Since the computer is running Windows 2000 Server, SP4, it was suggested that a newer OS might help. We moved the data and the problem persists even on the new computer. What follows is my status report about the new computer as well as the process of copying the data. Note that backing up the database on the original server also gets the permission denied error. Posting from 6/10 follows. Here's the current status. We installed postgres on an XP machine, 2002 SP3. (Same as my computer, which never has a problem.) We tried to do a backup of the database on the old computer, to copy the data to the new computer. The backup failed with the same problem mentioned in the subject line. So we zipped up the data directory and unzipped it on the XP computer. We then attempted to paste in the large block of text (200 lines of plain ascii, 49000 bytes) and got the same problem as before. Note that the load on the server and on postgres is very low, and that the problem can be recreated with 100% certainty when we paste certain text into certain fields. This computer is running Symantec Endpoint Protection, with the proactive threat feature turned off. Question: Is it possible that there's corruption in the database which is being incorrectly reported as Permission denied? Perhaps the original problem on the other computer created the corruption? Or the corruption came from another source and on both computers creates the incorrect message? We could of course recreate pretty much the same database. We're in development mode now: it was loaded with data from the legacy system extracted a few months ago and since then there has been additional data entered and changed as people have played with and tested the application. Is this a random event? A bug? Advice please on what to do next. John On Tue, 08 Jun 2010 08:37:02 -0400, John T. Dow wrote: On Tue, 08 Jun 2010 10:25:49 +0800, Craig Ringer wrote: On 8/06/2010 9:11 AM, John T. Dow wrote: OP here We removed AVG from the computer and rebooted. Same problem. OK, good to know. Thanks very much for testing that, and my apologies for recommending something that didn't work out. Of course, it would have been hard to progress without eliminating that possible factor. Could it be 2000 Server? SP4? I've seen reports of other problems that went away depending on the version of Windows. Well, certainly I'd expect that Pg on Windows 2000 server gets about zero regular testing. Why would you deploy a server OS that's already 10 years out of date, went EOL five years ago, and lost even the option of paid extended support this year? http://support.microsoft.com/lifecycle/?LN=en-aux=14y=11p1=7274 Good point. It's not my server, it's my client's server, and I don't know the history of it. They have mentioned another computer which runs XP I believe. It's dedicated to a single task and could double as the database server, although I don't think it has any RAID. I will suggest that we try installing Postgres on that computer and see if the problem goes away. If so, they might choose to make that their solution (perhaps adding another hard drive and a RAID controller). The application, daily backups, and WAL files could all live on the original server. If they go that route, we'd never know for certain what the original problem was. I'll post back after anything is done. JOhn It'd be interesting to investigate this issue ... but win2k server isn't exactly easy to come by. Anyone on the list got a win2k server (or license) around they can do some experimenting on? All I have here is NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop suspects. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
On Saturday 12 June 2010 11:07:32 am John T. Dow wrote: I posted this two days ago and nobody has commented. I'm reposting the message because I really need advice. Background info: My client got the permission denied error on his original server and we removed the AV software without solving the problem. Since the computer is running Windows 2000 Server, SP4, it was suggested that a newer OS might help. We moved the data and the problem persists even on the new computer. What follows is my status report about the new computer as well as the process of copying the data. Note that backing up the database on the original server also gets the permission denied error. Posting from 6/10 follows. Here's the current status. We installed postgres on an XP machine, 2002 SP3. (Same as my computer, which never has a problem.) We tried to do a backup of the database on the old computer, to copy the data to the new computer. The backup failed with the same problem mentioned in the subject line. So we zipped up the data directory and unzipped it on the XP computer. We then attempted to paste in the large block of text (200 lines of plain ascii, 49000 bytes) and got the same problem as before. Note that the load on the server and on postgres is very low, and that the problem can be recreated with 100% certainty when we paste certain text into certain fields. This computer is running Symantec Endpoint Protection, with the proactive threat feature turned off. Question: Is it possible that there's corruption in the database which is being incorrectly reported as Permission denied? Perhaps the original problem on the other computer created the corruption? Or the corruption came from another source and on both computers creates the incorrect message? We could of course recreate pretty much the same database. We're in development mode now: it was loaded with data from the legacy system extracted a few months ago and since then there has been additional data entered and changed as people have played with and tested the application. Is this a random event? A bug? Advice please on what to do next. John Some more questions. What is the relation that is having the permissions issue? Are the permissions on that file different from the others in the base directory tree? What is the 'certain text'? What are the 'certain fields' and do they have any functions running on them? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
On Saturday 12 June 2010 12:59:18 pm John T. Dow wrote: I am CC'ing the list so more eyes can follow this. Some more questions. What is the relation that is having the permissions issue? Are the permissions on that file different from the others in the base directory tree? What is the 'certain text'? What are the 'certain fields' and do they have any functions running on them? -- Adrian Klaver The actual message is: could not open relation base/16384/16642: Permission denied. I presume that the actual file names are not important as they change from time to time? They may or may not depending on what type of relation they are and the type of operation done to them. I should have been more specific. What is 16642? SELECT relname,relkind from pg_class where relfilenode=16642 should provide an answer. No, we have not diddled with the permissions. All files inherit their permissions from data. The question is really not whether you changed the permissions, but whether they have been changed? Hate to be anal about this, but have you actually looked at the permissions for that file or are you assuming inheritance of permissions? The text I've been using for test purposes is a plain ascii version of the US Constitution. It's about 50K, nothing special about it. Just a plain text file I had on hand. But large enough to invoke TOASTing the value. See here for more info: http://www.postgresql.org/docs/8.4/interactive/storage-toast.html The fields where we noticed it at a couple jTextArea fields in a Java application. They are defined as varchar. There is nothing special about these fields. No functions, etc. They are fields to hold plain English text. Data is normally entered by someone typing it in. (One example is a police officer typing in his report of an arrest he's made. After he types it in, it's printed and signed and becomes a legal document.) There is a similar field in each of three tables: two of the three produced this error when we pasted in the text, but the third one had no problem. We also pasted the text into some other fields and got the problem. However, except for these fields and this text, we have load many fields with data from a legacy system and have typed in information into many fields and have had no problems. That is to say, the problem is rare, although it has happened every time we've pasted that text into those two fields. Might be worth showing us the complete schema for those tables. Just to be clear, which describes the problem: A)100% reproducible by putting the above text into either of the two fields OR B) Rare but when it happens it involves the above text and these two fields. John -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
I will provide some answers tomorrow, but I don't have access now. It's a law enforcement agency in another state and the officer I work with will be in Sunday. The original computer's postgres server is stopped, and port forwarding wasn't changed yet so I don't have access to the new computer where the server is running. I have no access to the files themselves unless he's there and let's me in. Thanks. Keep tuned. John On Sat, 12 Jun 2010 15:59:06 -0700, Adrian Klaver wrote: On Saturday 12 June 2010 12:59:18 pm John T. Dow wrote: I am CC'ing the list so more eyes can follow this. Some more questions. What is the relation that is having the permissions issue? Are the permissions on that file different from the others in the base directory tree? What is the 'certain text'? What are the 'certain fields' and do they have any functions running on them? -- Adrian Klaver The actual message is: could not open relation base/16384/16642: Permission denied. I presume that the actual file names are not important as they change from time to time? They may or may not depending on what type of relation they are and the type of operation done to them. I should have been more specific. What is 16642? SELECT relname,relkind from pg_class where relfilenode=16642 should provide an answer. No, we have not diddled with the permissions. All files inherit their permissions from data. The question is really not whether you changed the permissions, but whether they have been changed? Hate to be anal about this, but have you actually looked at the permissions for that file or are you assuming inheritance of permissions? The text I've been using for test purposes is a plain ascii version of the US Constitution. It's about 50K, nothing special about it. Just a plain text file I had on hand. But large enough to invoke TOASTing the value. See here for more info: http://www.postgresql.org/docs/8.4/interactive/storage-toast.html The fields where we noticed it at a couple jTextArea fields in a Java application. They are defined as varchar. There is nothing special about these fields. No functions, etc. They are fields to hold plain English text. Data is normally entered by someone typing it in. (One example is a police officer typing in his report of an arrest he's made. After he types it in, it's printed and signed and becomes a legal document.) There is a similar field in each of three tables: two of the three produced this error when we pasted in the text, but the third one had no problem. We also pasted the text into some other fields and got the problem. However, except for these fields and this text, we have load many fields with data from a legacy system and have typed in information into many fields and have had no problems. That is to say, the problem is rare, although it has happened every time we've pasted that text into those two fields. Might be worth showing us the complete schema for those tables. Just to be clear, which describes the problem: A)100% reproducible by putting the above text into either of the two fields OR B) Rare but when it happens it involves the above text and these two fields. John -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
On 13/06/10 02:34, Adrian Klaver wrote: Question: Is it possible that there's corruption in the database which is being incorrectly reported as Permission denied? It's certainly not impossible. It'd really help if Pg would print more details from Windows' error reporting - GetLastError() etc - in cases like this. In fact, some searching reveals complaints about just that as far back as mid-2008 related to the exact error you're encountering. Anyway: When you moved the data dir over, did you reset all the permissions on it so that it is owned by the postgres user on the new machine? Applying those permissions recursively? Does the file that PostgreSQL is complaining about actually exist? Is it always the same 'xxx/xxx'? Is it an index or a relation? You can find out using the Pg catalogs: http://www.postgresql.org/docs/current/static/storage-file-layout.html http://www.postgresql.org/docs/current/static/catalog-pg-class.html ... from which you'll see that of: base/xxx/yyy 'base/xxx' is the prefix for your database, and within that 'yyy' is the oid of the table, so you can find out some details about it with the following SQL: \x select * from pg_class where oid = yyy; Does the table/index name reported by that query match one that is actually used in the problem query? What is it? Please post the full output of the above query. If it's an index, does REINDEXing your database help? If it's a relation, does CLUSTERing that relation succeed? Help? Is this a random event? A bug? Advice please on what to do next. It's really, really hard to know, especially with the involvement of elderly OSes and antivirus software. Could it be a Pg bug causing this? Of course. But it's really, really hard to know what, when, and how, especially with no access to the machines and data in question. Please keep a copy of this damaged cluster around, even if you decide to go ahead and rebuild the cluster. Now that its on a known-working platform and the issue has been shown not to be proximately* caused by antivirus software, it'd be preferable to find out what's actually going on here. That will be impossible without the damaged cluster. (* ie if the AV software was involved, it was to damage something that stays damaged after the AV is taken out of the picture) -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
Here's the current status. We installed postgres on an XP machine, 2002 SP3. (Same as my computer, which never has a problem.) We tried to do a backup of the database on the old computer, to copy the data to the new computer. The backup failed with the same problem mentioned in the subject line. So we zipped up the data directory and unzipped it on the XP computer. We then attempted to paste in the large block of text (200 lines of plain ascii, 49000 bytes) and got the same problem as before. Note that the load on the server and on postgres is very low, and that the problem can be recreated with 100% certainty when we paste certain text into certain fields. This computer is running Symantec Endpoint Protection, with the proactive threat feature turned off. Question: Is it possible that there's corruption in the database which is being incorrectly reported as Permission denied? Perhaps the original problem on the other computer created the corruption? Or the corruption came from another source and on both computers creates the incorrect message? We could of course recreate pretty much the same database. We're in development mode now: it was loaded with data from the legacy system extracted a few months ago and since then there has been additional data entered and changed as people have played with and tested the application. Is this a random event? A bug? Advice please on what to do next. John On Tue, 08 Jun 2010 08:37:02 -0400, John T. Dow wrote: On Tue, 08 Jun 2010 10:25:49 +0800, Craig Ringer wrote: On 8/06/2010 9:11 AM, John T. Dow wrote: OP here We removed AVG from the computer and rebooted. Same problem. OK, good to know. Thanks very much for testing that, and my apologies for recommending something that didn't work out. Of course, it would have been hard to progress without eliminating that possible factor. Could it be 2000 Server? SP4? I've seen reports of other problems that went away depending on the version of Windows. Well, certainly I'd expect that Pg on Windows 2000 server gets about zero regular testing. Why would you deploy a server OS that's already 10 years out of date, went EOL five years ago, and lost even the option of paid extended support this year? http://support.microsoft.com/lifecycle/?LN=en-aux=14y=11p1=7274 Good point. It's not my server, it's my client's server, and I don't know the history of it. They have mentioned another computer which runs XP I believe. It's dedicated to a single task and could double as the database server, although I don't think it has any RAID. I will suggest that we try installing Postgres on that computer and see if the problem goes away. If so, they might choose to make that their solution (perhaps adding another hard drive and a RAID controller). The application, daily backups, and WAL files could all live on the original server. If they go that route, we'd never know for certain what the original problem was. I'll post back after anything is done. JOhn It'd be interesting to investigate this issue ... but win2k server isn't exactly easy to come by. Anyone on the list got a win2k server (or license) around they can do some experimenting on? All I have here is NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop suspects. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
On Tue, 08 Jun 2010 10:25:49 +0800, Craig Ringer wrote: On 8/06/2010 9:11 AM, John T. Dow wrote: OP here We removed AVG from the computer and rebooted. Same problem. OK, good to know. Thanks very much for testing that, and my apologies for recommending something that didn't work out. Of course, it would have been hard to progress without eliminating that possible factor. Could it be 2000 Server? SP4? I've seen reports of other problems that went away depending on the version of Windows. Well, certainly I'd expect that Pg on Windows 2000 server gets about zero regular testing. Why would you deploy a server OS that's already 10 years out of date, went EOL five years ago, and lost even the option of paid extended support this year? http://support.microsoft.com/lifecycle/?LN=en-aux=14y=11p1=7274 Good point. It's not my server, it's my client's server, and I don't know the history of it. They have mentioned another computer which runs XP I believe. It's dedicated to a single task and could double as the database server, although I don't think it has any RAID. I will suggest that we try installing Postgres on that computer and see if the problem goes away. If so, they might choose to make that their solution (perhaps adding another hard drive and a RAID controller). The application, daily backups, and WAL files could all live on the original server. If they go that route, we'd never know for certain what the original problem was. I'll post back after anything is done. JOhn It'd be interesting to investigate this issue ... but win2k server isn't exactly easy to come by. Anyone on the list got a win2k server (or license) around they can do some experimenting on? All I have here is NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop suspects. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
Magnus Hagander, 07.06.2010 15:52: Some AV software probably behaves fine. Probably. In case anyone is interested: I have two development computers that run Postgres on Windows XP. One with Avira the other with Sophos. Neither has or had any problems installing or running Postgres Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
On Mon, Jun 7, 2010 at 15:58, Thomas Kellerer spam_ea...@gmx.net wrote: Magnus Hagander, 07.06.2010 15:52: Some AV software probably behaves fine. Probably. In case anyone is interested: I have two development computers that run Postgres on Windows XP. One with Avira the other with Sophos. Neither has or had any problems installing or running Postgres What kind of load do the systems have? Particularly, how many parallell connections? That seems to push things over the edge more often than high transaction single-user ones. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
Magnus Hagander, 07.06.2010 16:15: On Mon, Jun 7, 2010 at 15:58, Thomas Kellererspam_ea...@gmx.net wrote: Magnus Hagander, 07.06.2010 15:52: Some AV software probably behaves fine. Probably. In case anyone is interested: I have two development computers that run Postgres on Windows XP. One with Avira the other with Sophos. Neither has or had any problems installing or running Postgres What kind of load do the systems have? Particularly, how many parallell connections? That seems to push things over the edge more often than high transaction single-user ones. Ah, that might make the difference: I have no real load on those computers (as I said, developer machine) So it's more a single-user type of load Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
OP here We removed AVG from the computer and rebooted. Same problem. We are quite certain that AVG is no longer installed. It doesn't show up where it used to, and a search of the registry for AVG finds a couple leftovers but doesn't seem to indicate that it's still installed. The computer is running Windows 2000 Server, SP4. Build 5.00.2195. It's a Pentium (R) 4 CPU 2.80 GHz, AT/AT Compatible, 1 GB memory. That's not a particularly powerful computer, but so far we're only testing the application. Someone asked about load, someone also mentioned that mixing file serving and database serving is not good in general. Since we're only testing right now and in fact don't expect much load when in production, those probably aren't issues. Anyway, it now looks like the problem is not caused by AV software. Could it be 2000 Server? SP4? I've seen reports of other problems that went away depending on the version of Windows. Thanks. John On Mon, 07 Jun 2010 16:35:33 +0200, Thomas Kellerer wrote: Magnus Hagander, 07.06.2010 16:15: On Mon, Jun 7, 2010 at 15:58, Thomas Kellererspam_ea...@gmx.net wrote: Magnus Hagander, 07.06.2010 15:52: Some AV software probably behaves fine. Probably. In case anyone is interested: I have two development computers that run Postgres on Windows XP. One with Avira the other with Sophos. Neither has or had any problems installing or running Postgres What kind of load do the systems have? Particularly, how many parallell connections? That seems to push things over the edge more often than high transaction single-user ones. Ah, that might make the difference: I have no real load on those computers (as I said, developer machine) So it's more a single-user type of load Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied
On 8/06/2010 9:11 AM, John T. Dow wrote: OP here We removed AVG from the computer and rebooted. Same problem. OK, good to know. Thanks very much for testing that, and my apologies for recommending something that didn't work out. Of course, it would have been hard to progress without eliminating that possible factor. Could it be 2000 Server? SP4? I've seen reports of other problems that went away depending on the version of Windows. Well, certainly I'd expect that Pg on Windows 2000 server gets about zero regular testing. Why would you deploy a server OS that's already 10 years out of date, went EOL five years ago, and lost even the option of paid extended support this year? http://support.microsoft.com/lifecycle/?LN=en-aux=14y=11p1=7274 It'd be interesting to investigate this issue ... but win2k server isn't exactly easy to come by. Anyone on the list got a win2k server (or license) around they can do some experimenting on? All I have here is NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop suspects. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
