Обсуждение: current_user changes immediately after login
Hi list, I have a strange problem: When I connect to one of my databases, the current_user immediatly changes without any interaction from my side. This is what I do: chschroe@db1:~> psql -h db2 testdb Welcome to psql 8.2.5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit testdb=> select session_user; session_user -------------- chschroe (1 row) testdb=> select current_user; current_user -------------- itstaff (1 row) How can this happen? The consequence of this is that I cannot access the tables because the role "itstaff" has no privileges. "reset role" does not have any effect. After "set role none" the current_user is equal to the session_user and the tables are accessible again. This problem does only occur with this database. Do you have any explanation? Regards, Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT and Consulting Fax: +49 551 489500-91 Hans-Böckler-Straße 2 http://www.deriva.de D-37079 Göttingen Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
=?ISO-8859-1?Q?Christian_Schr=F6der?= <cs@deriva.de> writes: > I have a strange problem: When I connect to one of my databases, the > current_user immediatly changes without any interaction from my side. That's bizarre. Do you have anything in ~/.psqlrc? I'm also wondering (though too lazy to experiment) whether this effect could be produced via "ALTER DATABASE foo SET role = bar" ... checking pg_database.datconfig would be enough to eliminate that idea ... regards, tom lane
Tom Lane wrote: > =?ISO-8859-1?Q?Christian_Schr=F6der?= <cs@deriva.de> writes: > >> I have a strange problem: When I connect to one of my databases, the >> current_user immediatly changes without any interaction from my side. >> > > That's bizarre. Do you have anything in ~/.psqlrc? I'm also wondering > (though too lazy to experiment) whether this effect could be produced > via "ALTER DATABASE foo SET role = bar" ... checking > pg_database.datconfig would be enough to eliminate that idea ... > I had found the problem just before I read your mail and you are perfectly right with your guess: These are the corresponding entries from the pg_database system catalog (without the datacl column): datname | datconfig ---------+---------------- testdb | {role=itstaff} So it seems that one of the users (one with the appropriate permissions for this database) has issued this "alter database" command (probably without really understanding what he did *g*). After "alter database testdb reset role" everything is ok now. Thanks for your help! Regards, Christian -- Deriva GmbH Tel.: +49 551 489500-42 Financial IT and Consulting Fax: +49 551 489500-91 Hans-Böckler-Straße 2 http://www.deriva.de D-37079 Göttingen Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer