philip@adhesivemedia.com (Philip Hallstrom) writes:
> I'm new to postgres and have some questions regarding the
> authentication and access systems. I've got postgres installed and can
> connect from remote machines, but have some questions:
> - I cannot connect as the postgres user 'postgres' from remote machines?
> Why?
That's weird --- you can connect as other users but not as postgres?
The only way I know to do that is to set up a specific 'reject' entry
in pg_hba.conf, which doesn't seem like something you'd have done by
accident. What do you have in pg_hba.conf, anyway?
> - How is pg_shadow managed? Is it built from the pg_user table? If so,
> how do I clean it up (doing a "strings pg_shadow" shows users that no
> longer exist -- is that a problem?)
No, actually pg_shadow is the master and pg_user is just a view of it.
Don't worry about what 'strings' tells you --- that will find deleted
tuples and all sorts of junk. As long as you use CREATE USER and DROP
USER (or the shellscripts that invoke them) to manage users you should
be fine. (Actually, in 7.0 it should work to use plain INSERT and
DELETE commands on pg_shadow ... but I don't really recommend it ...)
> - In the docs under "Database/Table Privileges" it says "TBD". Can
> someone fill me in a bit. For example, as 'postgres' I did "CREATE
> DATABSE foo". Then I created the user "foo". I would have thought that
> I would have had to grant some sort of access to user "foo" to database
> "foo", but as user "foo" I was able to create tables in database "foo".
The database-level protection is pretty lame at the moment: any user who
can connect to a database can create tables in it. pg_hba.conf can be
used to deny particular users any access to particular databases, but
that's about the extent of your flexibility. This is being worked on...
> - What do I need to do in order to allow multiple users the abililty to
> create tables in a single database?
Nada, see above.
regards, tom lane