Обсуждение: Explain auth/access/priv system??

Hi -
    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?
- 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?)
- 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".
Why?
- What do I need to do in order to allow multiple users the abililty to
  create tables in a single database?
Thanks!
-philip

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

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

Philip Hallstrom <philip@adhesivemedia.com> writes:
> 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?

No, because there is table-level protection.  I think only the table
owner (creator) or the superuser can drop a table.  Other access rights
for a table are controlled by GRANT/REVOKE --- see the doc pages for
those commands for more info.  IIRC, the default is no access...

"local...all...trusted" is not a good idea on a machine where you don't
trust the other users, but that's because someone else can pretend to
be any authorized user (even the superuser!).  I'd at least suggest
"ident" authentication in that situation, maybe "password" if the need
to enter passwords isn't too much of a PITA.  You could also use
Kerberos if you have that installed.

            regards, tom lane