Thread: duplicate templates, wrong users
Well, a while ago I set up postgresql and have been using it for a small
database ever since. I'm now looking at local authentication and
permissions and a number of questions have come up. When I run select *
from pg_database, I get this:
datname | datdba | encoding | datistemplate | datallowconn |
datlastsysoid | datpath
------------------+--------+----------+---------------+--------------+------
---------+---------
db1 | 72 | 0 | f | t |
18539 |
template1 | 72 | 0 | t | t |
18539 |
template0 | 72 | 0 | t | f |
18539 |
db2 | 73 | 0 | f | t |
18539 |
db3 | 75 | 0 | f | t |
18539 |
(I've replaced the names of the databases with db1, db2, and db3).
here's pg_user:
usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
passwd | valuntil
------------+----------+-------------+----------+----------+-----------+----
------+----------
pgsql | 72 | t | t | t | t |
******** |
os_user | 71 | t | f | t | t |
******** |
user3 | 75 | f | f | f | f |
******** |
user1 | 72 | f | f | f | f |
******** |
user2 | 73 | f | f | f | f |
******** |
where user1 should correspond to db1 etc.
When I list my databases, it looks like this:
List of databases
Database | Owner
------------------+------------
db2 | user2
db1 | user1
db1 | pgsql
template0 | pgsql
template0 | user1
template1 | user1
template1 | pgsql
db3 | user3
What's with the duplicate databases? Why does user1 own TEMPLATES? Clearly
I have screwed something up. What's the proper way to fix it? As I see it,
I should be able to create a user, create a database with that user...
change that user to not be able to create databases, and then lock down the
databases so other users cannot get in. This is possible, yes? I cannot
find good documentation on this anywhere. Any help would be GREATLY
appreciated.
-Yanek.
On Wed, 5 Dec 2001, Yanek Korff wrote: > here's pg_user: > usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd | > passwd | valuntil > ------------+----------+-------------+----------+----------+-----------+---- > ------+---------- > pgsql | 72 | t | t | t | t | > ******** | > os_user | 71 | t | f | t | t | > ******** | > user3 | 75 | f | f | f | f | > ******** | > user1 | 72 | f | f | f | f | > ******** | > user2 | 73 | f | f | f | f | > ******** | > > where user1 should correspond to db1 etc. > > When I list my databases, it looks like this: > List of databases > Database | Owner > ------------------+------------ > db2 | user2 > db1 | user1 > db1 | pgsql > template0 | pgsql > template0 | user1 > template1 | user1 > template1 | pgsql > db3 | user3 > > > What's with the duplicate databases? Why does user1 own TEMPLATES? Clearly > I have screwed something up. What's the proper way to fix it? As I see it, > I should be able to create a user, create a database with that user... > change that user to not be able to create databases, and then lock down the > databases so other users cannot get in. This is possible, yes? I cannot > find good documentation on this anywhere. Any help would be GREATLY > appreciated. You have two users with the same usesysid and it happens to be pgsql and user1 that conflict.
> Here's your problem: user1 has the same userid as pgsql. this > used to happen in 7.0, I think. Is that what you're running? I'm sure it's what I used to be running. I am running this now: postgresql-7.1.2_2 according to FreeBSD ports. > As to fixing it - have you created a lot of db objects (tables, views, > functions, etc.) as user1 that need to be kept, or is this a test db? There is data in a variety of tables belonging to user1 in the db1 database that CANNOT be lost. I have tried using pg_dump and pg_dumpall with little to no success: yanek:~> pg_dump db1 > dump getDatabase(): SELECT failed. Explanation from backend: 'ERROR: More than one tuple returned by a subselect used as an expression.'. > I _think_ you can get away with connecting to the db as a superuser, > and updating the pg_shadow table so that user1 has a unique > id. This means that everything user1 used to own will be owned by > pgsql. Now you can drop and recreate the db that user1 is supposed to > own. Anyone can give confirmation on this procedure? I really cannot afford to lose this data, though I have done an offline tar -zcvf and will again before I do any dropping. So... just update the 'usesysid' in pg_shadow (what about pg_user?), drop the database (which is now only owned by pgsql?), and re-create that DB as user1? Thanks for the info so far. -Yanek.
On Thu, Dec 06, 2001 at 01:32:56PM -0500, Yanek Korff wrote:
> > Here's your problem: user1 has the same userid as pgsql. this
> > used to happen in 7.0, I think. Is that what you're running?
> I'm sure it's what I used to be running. I am running this now:
> postgresql-7.1.2_2
> according to FreeBSD ports.
>
> > As to fixing it - have you created a lot of db objects (tables, views,
> > functions, etc.) as user1 that need to be kept, or is this a test db?
> There is data in a variety of tables belonging to user1 in the db1 database
> that CANNOT be lost. I have tried using pg_dump and pg_dumpall with little
> to no success:
> yanek:~> pg_dump db1 > dump
> getDatabase(): SELECT failed. Explanation from backend: 'ERROR: More than
> one tuple returned by a subselect used as an expression.'.
>
> > I _think_ you can get away with connecting to the db as a superuser,
> > and updating the pg_shadow table so that user1 has a unique
> > id. This means that everything user1 used to own will be owned by
> > pgsql. Now you can drop and recreate the db that user1 is supposed to
> > own.
> Anyone can give confirmation on this procedure? I really cannot afford to
> lose this data, though I have done an offline tar -zcvf and will again
> before I do any dropping. So... just update the 'usesysid' in pg_shadow
> (what about pg_user?), drop the database (which is now only owned by
> pgsql?), and re-create that DB as user1?
No, it'll remove the duplicate id: there's really only one of each database,
it just looks like two when you use psql -l because psql does a join to the
pg_user table to get the owner name. Here:
reedstrm@wallace:~$ psql -E -l
********* QUERY *********
SELECT pg_database.datname as "Database",
pg_user.usename as "Owner",
pg_encoding_to_char(pg_database.encoding) as "Encoding"FROM pg_database, pg_user
WHERE pg_database.datdba = pg_user.usesysid
UNION
SELECT pg_database.datname as "Database",
NULL as "Owner",
pg_encoding_to_char(pg_database.encoding) as "Encoding"FROM pg_database
WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user)
ORDER BY "Database"
*************************
List of databases
Database | Owner | Encoding
------------+----------+-----------
bioinfo | reedstrm | SQL_ASCII
ifs_test | reedstrm | SQL_ASCII
nsf | reedstrm | SQL_ASCII
reedstrm | reedstrm | SQL_ASCII
repository | reedstrm | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
test | reedstrm | SQL_ASCII
(8 rows)
So, the first part there will return two rows for each database owned by
'72' (in your case), one for each user with id = 72. You can verify this
by doing a select * from pg_database; You'll see one of each database.
By breaking that duplication, you should then be able to pg_dump the
database. You'll then have to go through the schema by hand, and find
everything that's supposed to be owned by user1 (which will dump as
being owned by pgsql) and fix it. There's no automatic way to do this,
since your system beleives that 'user1' and 'pgsql' are both '72' and
that's how the items are actually tracked: by usesysid.
I just tested this by artificially creating a user with duplicate usesysid
(I had to UPDATE pg_shadow to do it : the CREATE USER command is guarded
against this) and got exactly the symptoms you report:
reedstrm@wallace:~$ pg_dump ifs_test >ifs_test.sql
reedstrm@wallace:~$ psql template1 -c "update pg_shadow set usesysid=32 where usename='user1';"
UPDATE 1
reedstrm@wallace:~$ pg_dump ifs_test > ifs_test.sql
getDatabase(): SELECT failed. Explanation from backend: 'ERROR: More than one tuple returned by a subselect used as
anexpression.
'.
reedstrm@wallace:~$ psql template1 -c "update pg_shadow set usesysid=33 where usename='user1';"
UPDATE 1
reedstrm@wallace:~$ pg_dump ifs_test > ifs_test.sql
reedstrm@wallace:~$
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
> I just tested this by artificially creating a user with duplicate usesysid
> (I had to UPDATE pg_shadow to do it : the CREATE USER command is guarded
> against this) and got exactly the symptoms you report:
FWIW, this sort of problem should be impossible in 7.2 and beyond: we
finally got around to attaching a unique index to pg_shadow.usesysid.
regards, tom lane