0

I've installed postgresql 9.3, but can't get any users authorized. I also can't use initdb or pg_ctl. I can't find pg_hba.conf either.

2
  • Can you provide to us examples of commands you've used? Any documentation you're following? – earthmeLon Jul 7 '15 at 22:11
  • I agree with @earthmeLon, please say what you've tried so far, etc. But also, read my answer, as I try and answer some of your questions/concerns below. – Thomas Ward Jul 7 '15 at 22:23
0

Where the pg_hba.conf file resides:

pg_hba.conf is in /etc/postgresql/X.Y/main/ (where X.Y is your version, 9.3 for you, 9.4 for me).


"Missing" programs:

pg_ctl may not be installed by default. initdb may also not be installed by default. Both are installable by postgres-xc.


Adding users so they can be authorized:

You'll have to add a user/role in via the psql command line client. You'll then have to add the user to pg_hba.conf.

I suggest using pgadmin3, a graphical interface/program, to work with user roles and adding them. It allows you to specifically select the individual permissions you want to give a user globally (which should usually be no special permissions, just to create the user, and give permissions for a given DB separately).


Configuring pg_hba.conf

This can be a little tricky. Once you've got a user login role, you need to add them to pg_hba.conf. The format of the file is fully explained in detail in the PosgreSQL documentation. A short summary is here.

Unless there is a matching line here, the default access rule is "deny access".

This is the format of a pg_hba.conf line:

TYPE DATABASE USER ADDRESS METHOD

type is the type of connection. Local connections from the same computer get the local type. Connections from a non-local IP (i.e. over TCP) get the host type. hostnossl can be used instead, and will identify any network connection to it (i.e. non-local) that is not over SSL. hostssl can be used to specify any network connection that is over SSL, however it is not default to have SSL set up.

database is self-explanatory - what database do you want to set these access permissions/restrictions to. A special database type is all which indicates all databases.

user is the username / role that you've added to Postgres. It has to exist within postgres, and it may need to exist as a local system user (however that's explained in where I explain METHOD).

ADDRESS is an IPv4 or IPv6 address or range. It is only to be used when host, hostnossl, or hostssl are specified under type. It can be a CIDR range or an individual IP address.

METHOD is how to handle authentication.

  • For a local user with the local type, it should be peer authentication, or md5 authentication if you want the user to be able to login from a different local computer user account with a password.
  • For a host, hostssl, or hostnossl connection type, you probably want to use md5 here, however you may have other types of authentication (see the documentation link above).
  • Under no circumstances unless you are insane or really know what you are doing should you use the trust method. This has zero authentication of the person and is a security hole from Hell, even for local type connections.

I advise you to read through the linked documentation on the pg_hba.conf file, it's a better explanation of things.

10
  • I can't get psql to do anything, so I'm looking for another way to add a user. 'pgadmin3' looks neat but I can't get the psql console plugin working, maybe because I haven't linked it to pgadmin3. – Brian Cady Jul 8 '15 at 2:01
  • Thomas W. thanks for your response. I did edit the pg_hba.conf file to set local authentication to md5, but no behavior change - I stll can't log in. Postgres-xc installation wanted to remove every postgre instance installed in ordered to work - I'm not that brave. – Brian Cady Jul 8 '15 at 2:09
  • @BrianCady yeah that's why it's not installed by default. Did you create the login role for the user inside postgres yet? You actually have to create the user with password inside of postgres, either via pgadmin3 or psql directly. Then reload the postgresql config (sudo service postgresql reload) or restart the postgres instance (sudo service postgresql restart). Don't forget that last step - you have to refresh the config to get the pg_hba.conf changes. – Thomas Ward Jul 8 '15 at 2:45
  • I've tried to create the user with pgadmin3 (failed - no psql console) and with psql (failed -ident authentication error). I tried as both postgre, the non-root user I set up a system account for, and as briancady101, the admin account. I finally got pg_hba.conf edited, changing peer to md5 for user postgre. – Brian Cady Jul 8 '15 at 16:23
  • Does it matter that I have both postgresql 8.4 and 9.3 installed? – Brian Cady Jul 8 '15 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.