Firstly, it is important to understand that for most Unix distributions, the default Postgres user neither requires nor uses a password for authentication. Instead, depending how Postgres was originally installed and what version you are using, the default authentication method will either be ident or peer.
PostgreSQL configuration without password on Mac for Rails - postgresqlconfigurationonmacforrails.md. Brew install postgresql 但是好像还有另一种方法，就是使用官网提供的Postgres.app， 号称是：The easiest way to get started with PostgreSQL on the Mac。 看到这条口号，是不是有点心动，按照官网的提示来：. Postgres.app is a simple, native macOS app that runs in the menubar without the need of an installer. Open the app, and you have a PostgreSQL server ready and awaiting new connections. Close the app, and the server shuts down. PostgreSQL can also be installed on macOS using Homebrew. Please see the Homebrew documentation.
ident authentication uses the operating system’s identification server running at TCP port 113 to verify the user’s credentials.
peer authentication on the other hand, is used for local connections and verifies that the logged in username of the operating system matches the username for the Postgres database.
Login and Connect as Default User
For most systems, the default Postgres user is postgres and a password is not required for authentication. Thus, to add a password, we must first login and connect as the postgres user.
If you successfully connected and are viewing the psql prompt, jump down to the Changing the Password section.
If you received an error stating that the database “postgres” doesn’t exist, try connecting to the template1 database instead and if successful, continue to Changing the Password.
- We can change the password of the user either by using the ALTER command or metacommand password in PostgreSQL. Recommended Articles. This is a guide to Postgres Change Password. Here we discuss an introduction to Postgres Change Password, syntax, examples with code and output. You can also go through our other related articles to learn more –.
- Note that using the ALTER ROLE statement will transfer the password to the server in cleartext. In addition, the cleartext password may be logged in the psql’s command history or the server log. In this tutorial, you have learned how to change the password of a PostgreSQL user using the ALTER ROLE statement.
Mac Install Postgresql
Authentication Error
If you receive an authentication error when attempting to connect to the psql client, you may need to alter the Postgres authentication config file (pg_hfa.conf).
Open the config file, typically located at /etc/postgresql/#.#/main/pg_hba.conf, where #.# is the Postgres version you are using:
The auth config file is a list of authentication rules. Scroll down the file until you locate the first line displaying the postgres user in the third column (if such a line exists). Uncomment the line if necessary (remove the semicolon), or otherwise if the line is missing entirely, add the following line to the top of the file and save your changes:
This authentication rule simply tells Postgres that for local connections established to all databases for the user postgres, authenticate using the peer protocol.
Note: Some older versions of Postgres prefer the default authentication method of ident, but most modern installations will utilize peer as specified above instead. You may need to test both if your results differ.
Now with your configuration file updated, repeat the steps in the Login and Connect as Default User section to try to connect to as the default postgres user. Once successful, proceed with changing the password.
Changing the Password
With a connection now established to Postgres at the psql prompt, issue the ALTER USER command to change the password for the postgres user:
If successful, Postgres will output a confirmation of ALTER ROLE as seen above.
Finally, exit the psql client by using the q command.
You’re all done. The default postgres user now has a password associated with the account for use in your other applications.
Configure PostgreSQL to allow remote connection
By default PostgreSQL is configured to be bound to 'localhost'.
As we can see above port 5432 is bound to 127.0.0.1. It means anyattempt to connect to the postgresql server from outside the machine will be refused.We can try hitting the port 5432 by using telnet.
Configuring postgresql.conf
Stop Postgres Mac
In order to fix this issue we need to find postgresql.conf. Indifferent systems it is located at different place. I usually search forit.
Open postgresql.conf file and replace line
with
Now restart postgresql server.
Here we can see that 'Local Address' for port 5432 has changed to 0.0.0.0.
Configuring pg_hba.conf
Let's try to connect to remote postgresql server using 'psql'.
In order to fix it, open pg_hba.conf and add following entry at thevery end.
Mac Postgresql Client
The second entry is for IPv6 network.
Do not get confused by 'md5' option mentioned above. All it means isthat a password needs to be provided. If you want client to allowcollection without providing any password then change 'md5' to 'trust'and that will allow connection unconditionally.
Restart postgresql server.
You should be able to see list of databases.
Now we are able to connect to postgresql server remotely.
Please note that in the real world you should be using extra layer ofsecurity by using 'iptables'.