Обсуждение: database access
Hello, Howcome that all users i create have permission to view and edit all databases that i create. I want 1 user for each database. Anyone? Thanks, Daniel
Daniel, I have recently been revisiting my database security (or lack thereof you could say) and trying to find the right combination of pg_hba.conf settings to make it work. I have NOT TESTED this solution, but in theory I think it should work based on the reading I have done. If you want to restrict access to a database to a single user then this should do it. In pg_hba.conf create an entry like host userdb1 (IP) (MASK) password userdb1.pwd which would require password authentication for the database userdb1 and use an external password file called userdb1.pwd for authentication (Change IP and MASK to valid values for your situation of course). Now, in the userdb1.pwd file you would want to make a list of ALL VALID USERS that are allowed to connect to that database. From my reading the most basic format would be user1:+ which would then (in theory) ONLY allow user1 to validly connect to the database called userdb1 as desired. User2, user3, etc would not be authenticated to connect to userdb1. You could repeat the process for each database you have in order to restrict it per user. The development docs are really good for pg_hba.conf settings so I suggest taking a look at those. I don't think there has been a rewrite of how it works so all the settings should work with most recent versions of PostgreSQL. As I said I do not claim this WILL WORK, but it is the only way I can think of based on the valid settings in pg_hba.conf. I am also not sure if you would have to add your superusers into the userdb1.pwd file in order for them to connect to userdb1 (I assume you would). It may not be practical to maintain a separate password file for each DB, but it may be the only way to currently make that restriction. I am going to be testing some of my ideas soon so any other suggestions from anyone out there would be great. I also welcome any corrections to my logic as I have been mulling these ideas over for days now. I also would like some suggestions on the topic of running pg_dump/pg_dumpall when you use password/crypt authentication for databases. Thanks, Tim Frank >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 07/03/01, 8:49:12 AM, daniel <dk@wol.nl> wrote regarding database access: > Hello, > Howcome that all users i create have permission to view and edit all > databases that i create. I want 1 user for each database. Anyone? > Thanks, > Daniel
>> Howcome that all users i create have permission to view and edit all >> databases that i create. I want 1 user for each database. Anyone? The "sameuser" option in pg_hba.conf may offer a solution. This allows access only to a database named the same as the user. regards, tom lane
Tom, Your response puzzled me slightly until I combed the docs with a slightly finer toothed comb than before. The only references where I remembered seeing "sameuser" were in reference to the ident authentication type, but you are correct, it is also a possible value for a database. My question would be if I used "sameuser" as the database then would my database superusers be allowed to also connect to databases different from their usernames? My first guess would be that they wouldn't because their names wouldn't match all the DB names. The thinking for using individual password files to name valid users was that I would need a way to also allow my DB superusers to connect, and would therefore have to name them in the separate password file. While we are on this "permissions" topic, I have another related question. I am contemplating creating a "backup user" with a separate user/pass that is not a superuser. The reason being if I want password authentication for my localhost then I would have to provide a user/pass in order to do backup with a dump. I am not too keen on using a superuser account if it is going to be stored in a script, or environment variable, or stuck at the top of the dump when using echo, as other suggestions on the list have led me to believe. So, my question is, is there any way to automatically grant certain rights (read) on any new tables that are created in any database? If I could automatically grant read to my backup user on all new tables then in theory a backup could be run with this account without requiring superuser privledges. Sorry this turned out to be a long question, but I know these issues come up again and again without a solid answer from what I could find on the list archives. Maybe I'm just talking silly with these ideas, so please let me know if you wouldn't mind. Thanks in advance to everyone. Tim Frank >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 13/03/01, 12:09:51 AM, tgl@sss.pgh.pa.us (Tom Lane) wrote regarding Re: [GENERAL] Re: database access: > >> Howcome that all users i create have permission to view and edit all > >> databases that i create. I want 1 user for each database. Anyone? > The "sameuser" option in pg_hba.conf may offer a solution. This allows > access only to a database named the same as the user. > regards, tom lane > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Tim Frank <tfrank@registrar.uoguelph.ca> writes: > My question would be if I used "sameuser" as the database then would my > database superusers be allowed to also connect to databases different > from their usernames? You're looking at this the wrong way. Whether you are superuser or not is immaterial as far as connection privileges go --- that fact is determined *after* you have connected. The sort of thing you can set up with the current privilege model is like this: host sameuser hostip hostmask ident host all hostip hostmask passwd This essentially says that for connections originating from the hostip/hostmask part of cyberspace, "ident" authentication will be used if you are trying to connect to the database named after your username, otherwise "passwd" authentication will be used. Obviously you can use two other forms of authentication if you choose, but that's the basic model. The first line in pg_hba.conf that matches your target database and connection origination address determines the authentication method that is used. Generally you'd make the earlier lines describe a tighter match and a looser auth method than the later lines, but the machine doesn't care... regards, tom lane