Postgres says my password is invalid
doug at artsman.com
Sun Jan 30 22:59:44 UTC 2022
good. you are running.
and not to make too fine about of it, here are some useful practical postgres tips
1) REINSTALLING is OK most ties
you can reinstall postgres on top of postgres any time you want, thats how we update within the same main verison. eg you can simply install postgres 14.3 on top of 14.0 or 14.1 or 14.2 or 14.3 installation
in a nod to ANDY’s suggestion of postgres taking up a different port, it will do that if you have an earlier version of postgres and you install a later one WITHOUT UNINSTALLING FIRST.
CAVEAT PAY ATTENTION TO WHAT THE INSTALLER says, it will tell you the version, database directory and port it will install on.
don’t install version 14 when postgres 13 is on the machine without un-installing postgres 13 first.
2) POSTGRES USER is special.
you can make it visible using the osx DSCL command. I do that in our installer and yes, we can log in using that account if we need to. but, in practice, we never do.
Why do I make one? — so that I can set home directories for the terminal command su - postgres
and why is it special. The postgres foundation designed the database engine to be used under a user which DOES NOT HAVE SPECIAL PERMISSIONS. meaning, you don’t need to be a super user to run the database engine.
thats super important because it means you can’t have postgres as an attack vector since it lacks anything special to do its job.
however, it won’t run unless only it (the psotgres user) is the only person to access the database, making it even more secure.
3) you can get into postgres user in terminal very easily.
su - postgres
then you are acting as postgres in terminal.
if you know about the .bash_profile (there is a period in front of it) and where to put it. /Library/Postgres/14
then you can set up the following in it.
so, after using
su - postgres
typnig the psotgres password
then you can use all the postgres commands in the command line such as
pg_ctl stop -m fast
without specifying the data directory.
you don’t have to be the postgres user if you use those same commands and ADD the data directory to the command line.
eg, the above can be done outside the postgres user by any user if you add the -D flag and the data directory path to the command line. so it works both ways, either as postgres user or as any user.
/Library/Postgres/14/bin/pg_ctl start -D /Library/Postgres/14/data
4). COPY from a file
need permissions to the file from postgres if you do it as the postgres user (using su - postgres)
using pgamdin in the way xavier mentions it.
food for thought. postgres is a comprehensive database …. you can do a lot without knowing a lot. but advanced. stuff needs a little terminal knowledge, security knowledge etc.
Arts Management Systems Ltd.
mailto:doug at artsman.com
Phone (403) 650-1978
> On January 30, 2022, at 12:42 PM, Das Goravani <goravanis at gmail.com> wrote:
> I want to thank Doug, Andy, Xavier, Alex.. and any others that chipped in, for helping me on this long thread.
> I did ultimately get Postgres back up and running. I am able to log in from the server on localhost and a remote Mac using my public IP address as the destination IP.. so hba is good.. all is good.
> The trick that worked was to delete the postgres system user and reboot before installing again.
> What happened I think is this: My first install, which went well at first, stopped working. In an effort to get it working I thought perhaps I should log into the server using that system Postgres user… and I tried to use it, but it refused my password.
> Confused, I logged in as my master user, and changed the password of that postgres user. I then logged into it, but it did not go well.
> Thereafter postgres stopped working. Then this long thread ensued.
> In the end I now know you don’t log into that user. I’ve been told. I also noted now that the postgres installer says it is creating a "Locked UNIX user" when it creates that account. So that user is off limits now, I get this.
> I think by allowing it to re-create that user, that that fixed things. I think changing it’s password was a no- no.
> I am up and running now.
> Previous to this thread was one where I was trying to get the COPY command to work.
> It is now working, but it comes up with too many errors from my import file. I can’t use it. It is way too careful.
> So I am going to be importing the file with Omnis. I have another thread on here now about that.
> Thank you to you four for helping me here. I will save the exchange because there is a lot of useful terminal commands laced throughout it and other knowledge.
> Thank you very much,
> Das Goravani
> Manage your list subscriptions at http://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com
More information about the omnisdev-en