Thread: ODBC connection stopped working.
I have a small (but growing) postgresql database that employees in the office connect to with Access97. I built the client application in Access and distributed it, and it has been running perfectly for the past two weeks or so. The ODBC connection is set up to connect as the superuser (postgres) with no password (and I have not added a password) (yeah, insecure, but I'm working on a bigger database to replace it that will have good security, and I'm on an internal network anyway). Anyhow, some time this morning, the datbase started rejecting connections from the clients, saying "ERROR: (tablename): Permission denied." If I re-link the tables, sometimes it will go back to working until I close the datbase and open it again. Here is a snippet from my log: 010821.10:55:15.935 [898] ERROR: Relation 'msysconf' does not exist 010821.10:55:15.966 [898] ERROR: client: Permission denied. 010821.10:58:01.806 [899] ERROR: Relation 'msysconf' does not exist 010821.10:58:01.812 [899] ERROR: client: Permission denied. 010821.10:58:28.961 [899] ERROR: workorder: Permission denied. 010821.11:03:08.287 [936] ERROR: Relation 'msysconf' does not exist 010821.11:03:20.907 [937] ERROR: Relation 'msysconf' does not exist 010821.11:03:20.938 [937] ERROR: employee: Permission denied. 010821.11:03:36.038 [939] ERROR: Relation 'msysconf' does not exist If I re-link the tables, sometimes only some of the tables will start working again. The ones that still do not work no longer produce the 'msysconf' lines in the log, but they do still give the 'Permission denied" ones. Does anyone know what the 'msysconf' errors are, or why the database would be denying me connections as the superuser? Please help! Neal Lindsay
Logic would say, if you haven't changed anything then there's know reason why the behavior should change. I don't know why PG would be rejecting connections as the superuser, other than if you (ie, Access) is not actually connecting as the superuser. It looks like you're running PG 7.1.x.. try enabling connection logging, just to make sure. As for MSysConf, that is something Access looks for to control things like background population and password caching. Notice that Access only looks for it once per connection. Have a look in Access help, I think the table definition and record meanings are in there. Also, you should read this FAQ: http://www.scw.org/pgaccess -Cedar On Tue, 21 Aug 2001, Neal Lindsay wrote: > I have a small (but growing) postgresql database that employees in the > office connect to with Access97. I built the client application in Access > and distributed it, and it has been running perfectly for the past two > weeks or so. The ODBC connection is set up to connect as the superuser > (postgres) with no password (and I have not added a password) (yeah, > insecure, but I'm working on a bigger database to replace it that will have > good security, and I'm on an internal network anyway). Anyhow, some time > this morning, the datbase started rejecting connections from the clients, > saying "ERROR: (tablename): Permission denied." If I re-link the tables, > sometimes it will go back to working until I close the datbase and open it > again. Here is a snippet from my log: > > 010821.10:55:15.935 [898] ERROR: Relation 'msysconf' does not exist > 010821.10:55:15.966 [898] ERROR: client: Permission denied. > 010821.10:58:01.806 [899] ERROR: Relation 'msysconf' does not exist > 010821.10:58:01.812 [899] ERROR: client: Permission denied. > 010821.10:58:28.961 [899] ERROR: workorder: Permission denied. > 010821.11:03:08.287 [936] ERROR: Relation 'msysconf' does not exist > 010821.11:03:20.907 [937] ERROR: Relation 'msysconf' does not exist > 010821.11:03:20.938 [937] ERROR: employee: Permission denied. > 010821.11:03:36.038 [939] ERROR: Relation 'msysconf' does not exist > > If I re-link the tables, sometimes only some of the tables will start > working again. The ones that still do not work no longer produce the > 'msysconf' lines in the log, but they do still give the 'Permission denied" > ones. > > Does anyone know what the 'msysconf' errors are, or why the database would > be denying me connections as the superuser? Please help! > > Neal Lindsay > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
I read up about msysconf in the Access help files - you're right, that's normal behavior. I also read up on log settings in the PostgreSQL on-line manual (very helpful). I have tried making the connection while upping the debug level in between tries and I came up with this on levels 3 and 5 (but not 4 ?!?): 010821.21:21:08.905 [2795] ERROR: job: Permission denied. 010821.21:21:08.905 [2795] NOTICE: LockReleaseAll: xid table corrupted Does anyone know what the "LockReleaseAll: xid table corrupted" line means? At 12:38 AM 8/22/01 +0300, you wrote: >Logic would say, if you haven't changed anything then there's know reason >why the behavior should change. I don't know why PG would be rejecting >connections as the superuser, other than if you (ie, Access) is not >actually connecting as the superuser. It looks like you're running PG >7.1.x.. try enabling connection logging, just to make sure. As for >MSysConf, that is something Access looks for to control things like >background population and password caching. Notice that Access only looks >for it once per connection. Have a look in Access help, I think the table >definition and record meanings are in there. Also, you should read this >FAQ: > http://www.scw.org/pgaccess > >-Cedar
Neal Lindsay <neal.lindsay@peaofohio.com> writes:
> 010821.21:21:08.905 [2795] NOTICE: LockReleaseAll: xid table corrupted
> Does anyone know what the "LockReleaseAll: xid table corrupted" line means?
Bad things :-(. If you can give a reproducible example that causes
that, I'd be glad to look into it.
regards, tom lane
At 11:10 PM 8/21/01 -0400, Tom Lane wrote: >Neal Lindsay <neal.lindsay@peaofohio.com> writes: > > 010821.21:21:08.905 [2795] NOTICE: LockReleaseAll: xid table corrupted > > > Does anyone know what the "LockReleaseAll: xid table corrupted" line > means? > >Bad things :-(. If you can give a reproducible example that causes >that, I'd be glad to look into it. > > regards, tom lane That bad, eh? :-( I am trying to copy the data to a parallel installation right now. The only thing that I can think of that I might have done wrong is deleting and redefining functions used in views. I didn't even think about it at the time, but views probably refer to the oid of the function and not the name (am I right?). Of course, I could be totally off-track. What is this xid table, anyway? How do I find it? I'll try to get you a reproducible method to get this error, but I'm kinda' poking around in the dark here. -Neal Lindsay
Neal Lindsay <neal.lindsay@peaofohio.com> writes:
> That bad, eh? :-( I am trying to copy the data to a parallel installation
> right now. The only thing that I can think of that I might have done wrong
> is deleting and redefining functions used in views. I didn't even think
> about it at the time, but views probably refer to the oid of the function
> and not the name (am I right?).
Right, which is one source of the infamous "cache lookup for function
NNN failed". But that's not what you're seeing here. I've not heard
of any way to produce lock-table corruption before, so I'm interested
in finding out what's happening.
regards, tom lane