Re: Explain auth/access/priv system??

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

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!