Thread: pg_hba.conf "sameuser"
Everyone,
I'm still trying to get a handle on all of the possible authentication
methods and what combinations will work best for what I need, and what
combinations will work in general. I am having difficulty getting the
"sameuser" parameter to do anything under DBNAME.
<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 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?)
or
host myuser (IP) (MASK) ident sameuser (which doesn't seem to
restrict a user to their own DB either)
What I am trying to clear up is if "sameuser" is actually a valid DBNAME
or if it is only a valid an AUTH_ARGUMENT. Also, is "sameuser" only ever
valid when used in conjunction with an AUTHTYPE of ident. All of my
testing was done on a snapshot of 7.1 taken sometime in early March.
Maybe I am not properly understanding the meaning of "to restrict a
user's access to a database with the same name as the user." as it is
stated in the docs, but I just can't seem to get that feature to work for
me. This is just bugging me for the sake of bugging me.
Thanks to anyone who can help me clear my head, it has been one of those
weeks. If you could email me directly as well as posting to the list I
would appreciate it as well.
Tim Frank
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@gmx.net http://yi.org/peter-e/
Peter,
Thank you for pointing out the VERY, VERY obvious to me that there was
most likely a line later on in my config that was still letting that
machine through. There was indeed a line there for the entire subnet for
password authentication (DUH!) that I forgot about while testing the
"sameuser" parameter.
Of course once I commented out that line things behaved appropriately
for "sameuser" as both a DBNAME and as the parameter for ident. I knew I
was missing something stupid, but to hell if I could find it on my own.
Lesson learned: When trying to add new authentication entries be sure
current authentcation lines don't conflict with or override the new ones.
Thanks a bunch, I have a tiny bit of my sanity back for the week now :)
Now that sameuser was working fine I tried to pull of something like this
host sameuser xxx.xxx.xxx.xxx 255.255.255.255 password other.pwd
to provide a list of "additional" users who could access a databse named
the same as a user (so I could allow superuser accounts to also connect
to those DB's in one step without having to explicitly list each DB with
its own password file) but it only let me connect as a user in the
other.pwd regardless of whether or not I was actually connecting as the
same username as the DB. Here I just assume that the optional file is
given a higher priority than the "sameuser" option. I just thought I
would give it a crack and see what happened.
Most of this is just academic exercies on my part to figure out what
can/can't be done and how. I may not end up using "sameuser" but I want
to have a good understanding of it before I discount it altogether.
Thanks again for your time.
Tim Frank
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
> 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@gmx.net http://yi.org/peter-e/
Tim Frank <tfrank@registrar.uoguelph.ca> writes:
> I'm still trying to get a handle on all of the possible authentication
> methods and what combinations will work best for what I need, and what
> combinations will work in general. I am having difficulty getting the
> "sameuser" parameter to do anything under DBNAME.
Keep in mind that DBNAME and the host IP info are a pattern to be
matched. The postmaster looks for the first line in pg_hba.conf that
matches the connection request, and then applies the AUTHTYPE method
specified in that line. If DBNAME is "sameuser" then the line matches
requests where the username and dbname are the same --- but if they're
not the same, the postmaster will keep right on looking for a matching
line. So your example didn't prove anything except that you had another
pg_hba line that would allow connections where the dbname and username
are different.
In general you have to look at the whole set of potentially applicable
pg_hba.conf lines and the order that they appear in to understand the
behavior. Showing one line won't let anyone help you.
regards, tom lane
Tom,
Agreed, I will have to be more careful in watching my other settings in
the pg_hba.conf file when testing new parameters as one affects another.
Peter, as well as you, were kind enough to point out the obvious that I
probably had another line in my pg_hba.conf that was still letting people
through, which there was. So I apologise for not being more careful in
checking that before hand.
Sometimes you just need someone else to point out an obvious fact to you
when you get tunnel vision from looking at the same file for too long.
Thanks for your help, problem is solved and working as it should.
Tim Frank
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 14/03/01, 1:26:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote regarding Re:
[GENERAL] pg_hba.conf "sameuser" :
> Tim Frank <tfrank@registrar.uoguelph.ca> writes:
> > I'm still trying to get a handle on all of the possible
authentication
> > methods and what combinations will work best for what I need, and what
> > combinations will work in general. I am having difficulty getting the
> > "sameuser" parameter to do anything under DBNAME.
> Keep in mind that DBNAME and the host IP info are a pattern to be
> matched. The postmaster looks for the first line in pg_hba.conf that
> matches the connection request, and then applies the AUTHTYPE method
> specified in that line. If DBNAME is "sameuser" then the line matches
> requests where the username and dbname are the same --- but if they're
> not the same, the postmaster will keep right on looking for a matching
> line. So your example didn't prove anything except that you had another
> pg_hba line that would allow connections where the dbname and username
> are different.
> In general you have to look at the whole set of potentially applicable
> pg_hba.conf lines and the order that they appear in to understand the
> behavior. Showing one line won't let anyone help you.
> regards, tom lane