Re: Access restriction

GH <grasshacker@over-yonder.net> writes:
>> I think you want "sameuser" in pg_hba.conf.

> Would you mind elaborating on that a bit for me?
> i.e. Where should it go in pg_hba.conf?
> I don't think I saw this in the docs...

It's right in pg_hba.conf:

# Format:
#
#   host  DBNAME  IP_ADDRESS  ADDRESS_MASK  AUTHTYPE  [AUTH_ARGUMENT]
#
# DBNAME is the name of a PostgreSQL database, or "all" to indicate all
# databases, or "sameuser" to restrict a user's access to a database with
# the same name as the user.

One thing this doesn't make too clear is that the "restriction" really
means that this record *matches* if the username and the database name
are the same.  Else the postmaster moves on, looking for another
matching record.  A possibly useful example:

localhost    sameuser            ident
localhost    all                passwd passfile

This would mean that a user would get let into his own database on
the basis of IDENT checking, and would get let into other databases
on the basis of password checking using passwords in $PGDATA/passfile.
Since you'd control the contents of this last file, this would allow
you to grant access to all databases to only selected users.
You want some sort of escape hatch like that, at least for the DBA ---
else he couldn't get into any database but his own, either.  Superusers
aren't super as far as the postmaster is concerned... they have to pass
the authentication tests the same as mere mortals.

            regards, tom lane