Thread: Database permissions
I had this working once before, but restarted the database and things went to hell. This user authentication for postgresql I will have to say is the most complex I have seen. Need a block diagram of how it works to understand it. Have .pgpass in postgres home directory, data directory is /home/postgres/data Per the md5 instructions. pg_shadow has postgres md5 password, and my password. Host computer is suse 9.2 at 192.168.121.252 I can acces phproject database from windows machine with no problem. I can access database with pgadmin3 from windows machine, no problem. Can no longer access via ODBC on windows machine Can no longer access from phppgadmin on windows machine Can no longer access from webmin on host machine. All I get is FATAL: IDENT authentication failed for user afore or for user postgres pg_hba.conf is below #local all all trust # IPv4-style local connections: host MPC all 192.168.121.0 255.255.255.0 trust # IPv6-style local connections: #host all all ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff trust #local all all ident sameuser local all all ident md5 host template1 all 192.168.121.0 255.255.255.0 trust host phprojekt all 192.168.121.252 255.255.255.0 trust host phpPgAdmin all 192.168.121.252 255.255.255.0 trust Any help would be welcome. Art
More confused than ever. The pg_hba.conf file shown below was what I had originaly to get phppgadmin to work. Changed the local all all ident md5 to local all all trust and it started to working again with phppgadmin, but now, access via ODBC (with MSAccess)does not work. All I get is #DELETED for every oolumn and every row. could someone explain that to me? What should the pg_hba.conf file look like? Art Art Fore wrote: > I had this working once before, but restarted the database and things > went to hell. This user authentication for postgresql I will have to say > is the most complex I have seen. Need a block diagram of how it works to > understand it. > > Have .pgpass in postgres home directory, data directory is > /home/postgres/data Per the md5 instructions. > > pg_shadow has postgres md5 password, and my password. > > Host computer is suse 9.2 at 192.168.121.252 > > I can acces phproject database from windows machine with no problem. > I can access database with pgadmin3 from windows machine, no problem. > > Can no longer access via ODBC on windows machine > > Can no longer access from phppgadmin on windows machine > > Can no longer access from webmin on host machine. All I get is > FATAL: IDENT authentication failed for user afore > > or for user postgres > > pg_hba.conf is below > > #local all all trust > # IPv4-style local connections: > host MPC all 192.168.121.0 255.255.255.0 trust > # IPv6-style local connections: > #host all all ::1 > ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff trust > #local all all ident sameuser > local all all ident md5 > host template1 all 192.168.121.0 255.255.255.0 trust > host phprojekt all 192.168.121.252 255.255.255.0 trust > host phpPgAdmin all 192.168.121.252 255.255.255.0 trust > > Any help would be welcome. > > Art > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
local is for all local connections, phppgadmin i guess is local, if run on the same computer could be local, depending on your configuration. is access and odbc on a different computer? in that case you could add host all all ipnumber mask trust to allow access to all databeses as any user for connections coming from ipnumber. in this case ipnumber being the computer with access. if you use "local" and "ident with sameuser" and run php (like needed for phppgadmin) then your web user must be allowed to access that database, meaning you should have a postgresql user with the same name and with permission to access that database. this is the obvious, so have probably tried it already, but i can't think of other answer. javier On Tue, 08 Feb 2005 20:51:29 -0800, Art Fore <art.fore@comcast.net> wrote: > More confused than ever. The pg_hba.conf file shown below was what I had > originaly to get phppgadmin to work. Changed the > > local all all ident md5 > > to > > local all all trust > > and it started to working again with phppgadmin, but now, access via > ODBC (with MSAccess)does not work. All I get is #DELETED for every > oolumn and every row. > > could someone explain that to me? > > What should the pg_hba.conf file look like? > > Art > > > Art Fore wrote: > > I had this working once before, but restarted the database and things > > went to hell. This user authentication for postgresql I will have to say > > is the most complex I have seen. Need a block diagram of how it works to > > understand it. > > > > Have .pgpass in postgres home directory, data directory is > > /home/postgres/data Per the md5 instructions. > > > > pg_shadow has postgres md5 password, and my password. > > > > Host computer is suse 9.2 at 192.168.121.252 > > > > I can acces phproject database from windows machine with no problem. > > I can access database with pgadmin3 from windows machine, no problem. > > > > Can no longer access via ODBC on windows machine > > > > Can no longer access from phppgadmin on windows machine > > > > Can no longer access from webmin on host machine. All I get is > > FATAL: IDENT authentication failed for user afore > > > > or for user postgres > > > > pg_hba.conf is below > > > > #local all all trust > > # IPv4-style local connections: > > host MPC all 192.168.121.0 255.255.255.0 trust > > # IPv6-style local connections: > > #host all all ::1 > > ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff trust > > #local all all ident sameuser > > local all all ident md5 > > host template1 all 192.168.121.0 255.255.255.0 trust > > host phprojekt all 192.168.121.252 255.255.255.0 trust > > host phpPgAdmin all 192.168.121.252 255.255.255.0 trust > > > > Any help would be welcome. > > > > Art > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Art, On Feb 8, 2005, at 11:51 PM, Art Fore wrote: > More confused than ever. The pg_hba.conf file shown below was what I > had originaly to get phppgadmin to work. Changed the > > local all all ident md5 > > to > > local all all trust > > and it started to working again with phppgadmin, but now, access via > ODBC (with MSAccess)does not work. All I get is #DELETED for every > oolumn and every row. > > could someone explain that to me? > I assume phpPgAdmin now works because the web server is on the same machine as the database. Accepting the connection has nothing to do with the machine the browser is running on. Setting this to trust and using phpPgAdmin is not good from a security standpoint. You should look at the FAQ and INSTALL files in the pgpPgAdmin installation for further details on setting this up. With ODBC, do you see authentication errors in your PostgreSQL log? This might provide the clue you need to fix the problem. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL