Обсуждение: Connecting to postgresql with pdadmin III
Thanks Dave Page, Kevin Grittner & Walter Hurry for the replies since I had so many problems last time I am really doing this step-by-step. Before I want to add any users I simply want to get (and understand) my connections right. My step-by-step plan looks like this... 1. do installations (on my laptop) - done 2. using terminal, connect as postgres (no password) to pgsl - done 3. create database test - done 4. quit psql - done 5. start pgadmin III and attempt to ADD SERVER - failed (password error) 6. etc It may be that I am missunderstanding something here and the help document says the following... "Use this dialog to add a new server connection to the root of the pgAdmin tree. If you're experiencing connection problems, check the connection problems page. The description is a text to identify the server in the pgAdmin tree. The host is the IP address of the machine to contact, or the fully qualified domain name. On Unix based systems the address field may be left blank to use the default PostgreSQL Unix Domain Socket on the local machine, or be set to an alternate path containing a PostgreSQL socket. If a path is entered, it must begin with a "/". The port number and SSL connection options may also be specified." This explains the Host field but not the Name field. Perhaps those that wrote this help document thought that this was obvious but IS IT ??? Up till now I thought I was trying to connect to a Database on my local machine but perhaps pgadmin III requires me to connect to the server first (WOULD THAT BE POSTMASTER ON LOCALHOST ???) and then the database. In any case the help-documentation does not deal with these problems so guys like me have to ask in forums. I have changed my pg_hba.conf to look like this (# IPv4 local connections: now switched to trust did not solve the password problem) # Database administrative login by UNIX sockets local all postgres ident sameuser # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident sameuser # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 md5. So my pgadmin questions at this stage are... 1. Do I have to Add a server before adding a database (and what would be the name of the Server) 2. Is a password for user postgres mandatory (will pgadmin III fail if no password ???) Sorry to bother everyone but it doesn't work and there is no documentation. But perhaps instead of getting frustrated perhaps it would be better if I should get involved in the pgadmin III documentation project (which I would be happy to do, since at the moment there appears to be no .pdf documentation). That would save all the irritation of people on the mailing list reading posts like this from someone who just can't get started. I am also reading PostgreSQL 9 Administration Cookbook - Simon Riggs & Hannu Krosing (but no solutions there). Thanks for your help. regards Dragonfish ----------------------------------------- Meddle not in the affairs of Dragons For you are crunchy and good with Mustard David M. Gullever david.gullever@web.de -----------------------------------------
On Thu, Jan 19, 2012 at 12:57 PM, David M. Gullever <david.gullever@web.de> wrote: > > So my pgadmin questions at this stage are... > 1. Do I have to Add a server before adding a database (and what would be the > name of the Server) Yes, and whatever you like - it's just a name for you to identify the server (i.e. "Development Server" or "HR System"). > 2. Is a password for user postgres mandatory (will pgadmin III fail if no > password ???) No. Please see my previous email in which I explained why you cannot connect and gave a couple of suggestions to make it work as you want. > Sorry to bother everyone but it doesn't work and there is no documentation. I have to disagree with both of those points. We get something like 40,000 downloads per week of pgAdmin, and if it didn't work I'm fairly certain we'd have more than one complaint. There also is documentation, though I certainly agree it could be much better. We've made changes for the next major release of pgAdmin that will help with that by replacing the doc build system to one which allows the docs to be written in a simple text format, and is capable of generating the docs in formats like PDF as well as the current HTML/CHM formats that we use. > But perhaps instead of getting frustrated perhaps it would be better if I > should get involved in the pgadmin III documentation project (which I would > be happy to do, since at the moment there appears to be no .pdf > documentation). That would save all the irritation of people on the mailing > list reading posts like this from someone who just can't get started. That's never really been an issue, but improvements to the docs are always welcome regardless. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 19/01/2012 12:57, David M. Gullever wrote:
> This explains the Host field but not the Name field. Perhaps those
> that wrote this help document thought that this was obvious but IS
> IT ??? Up till now I thought I was trying to connect to a Database on
> my local machine but perhaps pgadmin III requires me to connect to
> the server first (WOULD THAT
Just to clarify a small point here - there's no such thing as connecting
to a PostgreSQL database without first connecting to a server. Clients
don't have any access to the data on disk; the server always does the
serving. :-)
> BE POSTMASTER ON LOCALHOST ???) and then the database. In any case
> the help-documentation does not deal with these problems so guys
> like me have to ask in forums.
>
> I have changed my pg_hba.conf to look like this (# IPv4 local
> connections: now switched to trust did not solve the password
> problem)
>
> # Database administrative login by UNIX sockets local all postgres
> ident sameuser
>
> # TYPE DATABASE USER CIDR-ADDRESS METHOD #
> "local" is for Unix domain socket connections only local all all
> ident sameuser
>
> # IPv4 local connections: host all all 127.0.0.1/32 trust
>
> # IPv6 local connections: host all all ::1/128
> md5.
>
> So my pgadmin questions at this stage are... 1. Do I have to Add a
> server before adding a database (and what would be the name of the
> Server)
Yes, you do. The "name" field is up to you - it's just a label in
PgAdmin used to identify the server in the list.
> 2. Is a password for user postgres mandatory (will pgadmin III fail
> if no password ???)
This depends on (i) how authentication is set up in Postgres, and (ii)
how you are connecting. According to the above pg_hba.conf, if you
connect via a Unix socket ("local" rules), Postgres will use ident for
authentication; whereas if you connect over TCP/IP ("host" rules) it
will just let you straight in without any authentication ("trust").
Remember too that the order of rules in pg_hba.conf is important - the
first matching rule will take effect.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
Thanks Dave for the reply, Sorry you must think I'm a 24 Carat Moron but since there is no documentation, this is actually becoming interesting - especially since thousands of users before me have started-up with pdadmin apparently without problems. But my thoughts at the moment are as follows... 1. If at the outset I'm simply creating a server (just a name) for my own use in which to put databases at a later date, then this server has initially no relationship with any databases anywhere. That comes later !!! 2. So why the password problems ??? Again the question - is a password mandatory for a "newly created" server. No the password must relate to the user (in my case "postgres") and not the server. 3. But I am then asked for a HOST. So this "server" can only relate to a specific machine and a specific user but NOT a specific postmaster (various versions i.e. various postmasters can run on a single machine at any time so indicating the HOST is NOT unique ???). AND NONE OF THIS IS IN THE DOCUMENTATION !!! 30 years ago my girlfriend was working at DEC (digital equipment corp.) in Munich, writing documentation and as an ICT Instructor I worked with her on various projects. At the time the motto was to write documentation for "den duemmsten anzunehmenden Benutzer" (translated - the thickest user one could reasonably assume). DECs documentation was universally acclaimed as being the "industry best" and they saved themselves a fortune on Telephone Helpdesk services - there was no email at the time. Of course you can never defend yourself from users that think that RTFM doesn't apply to them - but then I don't see myself as that kind of user. On the contrary - being only of average intelligence I rely on good documentation. I wonder how much the flow of mailinglist correspondence could be reduced (thereby reducing the irritation between mailinglist members) if one were to apply DECs approach to documentation to the open source world. I have offered my services but no-one has taken me up on the offer. In any case if all of this were in the documentation. Then I wouldn't be getting on everyone's nerves - incl. my own. To come back to your hint... Because you specified "localhost", pgAdmin is using a tcp/ip connection. psql probably defaulted to a Unix socket, which is typically setup in Postgres to use ident authentication by default. If you replace 'localhost' with the directory containing the Unix socket (typically '/tmp', but it may be different on Debian), then it too should authenticate using ident. Alternatively, modify pg_hba.conf to allow "trust" authentication from localhost, but that's a potential security risk. I am crrently reading through postgres-8.0-A4.pdf 19. Client - 281 19.1. The pg_hba.conf - 281 19.2. Authentication methods - 285 19.2.1. Trust authentication- 285 19.2.2. Password authentication - 286 19.2.3. Kerberos authentication - 286 19.2.4. Ident-basedauthentication - 287 19.2.4.1. Ident Authentication over TCP/IP - 287 19.2.4.2. Ident Authenticationover Local Sockets - 288 19.2.4.3. Ident Maps - 288 The following paragraph nailed down the problem. On your assumption that the default was UNIX sockets, I changed the first line from # Database administrative login by UNIX sockets local all postgres ident sameuser to local all postgres trust And the little bugger did what it was supposed to do. Since the pg_hba.conf records are examined sequentially for each connection attempt, the order of the records is significant. Typically, earlier records will have tight connection match parameters and weaker authentication methods, while later records will have looser match parameters and stronger authentication methods. For example, one might wish to use trust Authentication for local TCP/IP connections but require a password for remote TCP/IP connections. In this case a record specifying trust authentication for connections from 127.0.0.1 would appear before a record specifying password authentication for a wider range of allowed client IP addresses. /tmp offers no idication as to where the default connection method is defined and strangely the pg_ident.conf file is empty Thanks again Dave Regards from sunny Uganda Dragonfish
On 19/01/2012 18:58, David M. Gullever wrote: > Thanks Dave for the reply, > > Sorry you must think I'm a 24 Carat Moron but since there is no > documentation, this is actually becoming interesting - especially since > thousands of users before me have started-up with pdadmin apparently without > problems. But my thoughts at the moment are as follows... > 1. If at the outset I'm simply creating a server (just a name) for my own > use in which to put databases at a later date, then this server has > initially no relationship with any databases anywhere. That comes later !!! Hi David, A "server" is a specific instance of PostgreSQL running on a computer. If you have more than one instance of PostgreSQL running on a single computer, you need to create a single "server" in PgAdmin for each one. > 2. So why the password problems ??? Again the question - is a password > mandatory for a "newly created" server. No the password must relate to the > user (in my case "postgres") and not the server. Whether or not the password is mandatory depends on whether or not the specific server (the PostgreSQL instance, not the node in the PgAdmin tree view) requires it in order to authenticate the connecting user. You can certainly create a "server" in PgAdmin without a password; but whether this will actually connect successfully depends on pg_hba.conf on the server (PostgreSQL instance). > 3. But I am then asked for a HOST. So this "server" can only relate to a > specific machine and a specific user but NOT a specific postmaster (various > versions i.e. various postmasters can run on a single machine at any time so > indicating the HOST is NOT unique ???). AND NONE OF THIS IS IN THE > DOCUMENTATION !!! The "server" in the PgAdmin tree view relates to a a specific postmaster, listening on a specific port and/or Unix socket, on a specific machine. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie