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.