Postgres says my password is invalid

Doug Easterbrook 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.


type 
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.

export PATH=/Library/PostgreSQL/14/bin:$PATH
export PGDATA=/Library/PostgreSQL/14/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
export PGLOCALEDIR=/Library/PostgreSQL/14/share/locale
export MANPATH=$MANPATH:/Library/PostgreSQL/14/share/man


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 start
pg_ctl stop -m fast
pg_ctl reload

pg_dump
pg_restore

psql

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)

or

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.






Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.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 mailing list