Re: Explain auth/access/priv system??

> philip(at)adhesivemedia(dot)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?

Well, *cough* it turns out that I somehow miss-set my password... and of
course realized this about 30 seconds after sending this. It works now.

> > - 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 ...)

Ah.. okay.. thanks!

> > - 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...

Got it. Thanks again. So really that last entry in pg_hba.conf (the
"local...all...trusted") is dangerous... can any user who can connect to a
database also drop tables?

> > - 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

Thanks Tom!