duplicate templates, wrong users

From: "Yanek Korff" <yanek(at)cigital(dot)com>
To: pgsql-admin(at)postgresql(dot)org(dot)pgsql-general(at)postgresql(dot)org
Subject: duplicate templates, wrong users
Date: 2001-12-05 20:23:42
Message-ID: 9ulvof$6db$1@news.tht.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

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.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Florin Andrei 2001-12-05 22:58:57 optimizing for fast INSERTs
Previous Message Kirk Strauser 2001-12-05 18:11:58 Re: I wrote a program to migrate Interbase -> PostgreSQL

Browse pgsql-general by date

  From Date Subject
Next Message August Zajonc 2001-12-05 20:27:41 Perf number on a project
Previous Message Laszlo Hornyak 2001-12-05 19:39:28 Re: [GENERAL] java stored procedures