Re: pg_hba.conf "sameuser"

Tim Frank writes:

> <snip from 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.
> </end snip>
>
> Now, that snippet of instructions doesn't indicate that there are any
> restrictions for which AUTHTYPE "sameuser" can be used with. For my
> testing I set this line for a host (with the correct IP in place of xxx
> of course),
>
> host sameuser xxx.xxx.xxx.xxx 255.255.255.255 password
>
> which I assumed from the description would restrict access to the
> database named the same as the user being authenticated. This does not
> seem to work as expected,
>
> $ psql -h mydbhost -p 5433 myuser
> Password:
> Welcome to psql, the PostgreSQL interactive terminal.
>
> This connects me to the database called "myuser" correctly as the user
> "myuser".
>
> $ psql -h mydbhost -p 5433 -U otheruser myuser
> Password:
> Welcome to psql, the PostgreSQL interactive terminal.
>
> This, however, also connects me to the database called "myuser" but as
> the user "otheruser" which doesn't seem to make sense.

The snippet you quoted is slightly misworded. The "sameuser" key word
when used in place of a database name determines whether the record
matches. So a record of the form

host sameuser xxx.xxx.xxx.xxx 255.255.255.255 password

says to use password authentication if the host IP matches *and* the
requested database name is equal to the requested user name. If these
conditions aren't satisfied then the record doesn't match and is not
considered. Most likely, in your case there are subsequent records that
also match the host IP that have different authentication set. E.g., if
you add a record

host all xxx.xxx.xxx.xxx 255.255.255.255 trust

after the above record, then all connection requests where the username is
equal to the database name will require a password, whereas all other
connections will be allowed unconditionally. (A rather silly setup, of
course.)

> The only actual references I have seen in examples for "sameuser" use it
> in conjunction with an AUTHTYPE of ident. Such as,
>
> host sameuser (IP) (MASK) ident (which doesn't seem to work as
> ident always fails?)

An argument after "ident" is required.

>
> or
>
> host myuser (IP) (MASK) ident sameuser (which doesn't seem to
> restrict a user to their own DB either)

This is different. This means that the connection is allowed if the Unix
user name and the database user name match.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/