Hi. I’ve recently switched from Ubuntu to openSUSE. After installing postgresql following instructions given on openSUSE wiki, I tried to run sudo -u postgres psql but it required a password.
Based on postgresql documentation there’s no default password for postgres. Anyway, in /var/lib/pgsql/data/pg_hba.conf I changed the authentication method for local all local users to trust, restarted postgresql service and tried again but still a password was required.
I even tried setting a password for postgres via psql but nothing changed.
With openSUSE,
There are a large number of highly authoritative community documentation that are called SDB, although many are not maintained, you can look at the “history” of the article and “discussion” comments to evaluate how useful they may be. SDB articles that have been reviewed recently can be considered as important and reliable as official openSUSE documentation.
In the case of PostgreSQL, there is an SDB updated as recently as Dec 2019 which looks like it answers your question and a lot more…
https://en.opensuse.org/SDB:PostgreSQL
If you continue to have problems, you have to post and be very specific exactly what commands you run and the result you see. Ideally, you should copy and paste your commands and results within
blocks which are created by the hash button in the web tool for forum postings.
TSUThanks for your answer and instructions. I had read the SDB for installing postgresql (as I had provided the link to the SDB page as well). I also checked discussions and history of the postgresql SDB but I couldn’t find solution to my problem. I’m re-stating it more precisely:
Attempting to login to psql without user switching, I was asked to provide a password (which based on postgresql docs, there’s no default password for postgres user):
fateme@localhost: ~> sudo -u postgres psql
[sudo] password for postgres:
Providing no password will not solve the issue as it’ll ask again for a password so I changed authentication method in ***/var/lib/pgsql/data/pg_hba.conf ***for all local users to trust and restarted postgresql service:
fateme@localhost: ~> sudo vim /var/lib/pgsql/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
fateme@localhost: ~> sudo service postgresql restart
Running the command again, nothing had changed and still a password was required so I set a password for postgres:
fateme@localhost: ~> sudo su postgres
postgres@localhost: ~> psql
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q
I switched back user to myself and restarted the postgresql service, ran the command again and still a password was required. As I provided the password I’d set via psql, I still couldn’t login getting wrong password error:
fateme@localhost:~> sudo -u postgres psql
[sudo] password for postgres:
Sorry, try again.
[sudo] password for postgres:
Sorry, try again.
[sudo] password for postgres:
sudo: 3 incorrect password attempts
There are actually two users named postgres: one system user listed in /etc/passwd and one db user in pg_user. The first one runs the server, the second ist the database administrator. If that does not immediately make sense, think of a user that has no direct access to the host system but can only connect via psql. You can switch to the first one with “sudo su - postgres” and, independent of that, connect to the second one via “psql -U postgres”.
When you do sudo, you are switching to the system user. That one should not have a password set so that “sudo su - postgres” should switch you over to the system user. If that asks for postgres password, either give whatever you set it to or reset it prior via “sudo passwd postgres”. Btw, I prefer setting up sudo so that I have to give my password instead of target password. I mean, if I knew the target password I could directly switch via “su - postgres”. Never got the point of targetpw in sudoers.
Anway, now being system user postgres, you could simply do “psql” which will put you into the db as the db user postgres. Then do you SQL.
What you did, was this
sudo -u postgres psql
# since your sudoers is apparently set to targetpw, this will ask for the pw of the system user postgres
postgres=# \password postgres
# this sets the password of the db user postgres, cf. \du
sudo -u postgres psql
# will again ask for pw of the system user postgres, try as your user
psql -U postgres
# this should ask for the pw you set in the db with the previous \password call while being in the db
I would recommend starting from scratch again. And if you want to play with postgres, I recommend docker containers instead of messing with system files.
An observation…
Unlike other distros, “sudo” is not usually the recommended security context to do things in openSUSE…
You’ll likely have better success with either “su” if you only need elevated permissions to do something or “su -” aka “su --login” if you need the full root envirnoment (ie environment paths, relocate default cursor location).
You’ll also find that when you elevate in a console, it’s a timesaver…
All your commands will execute in that security context until you change to another (including current logged in) User.
This is particularly important and possibly might be your issue (I don’t know for sure without testing), when you execute a command that might perform something complex, running as root (by elevating using su) ensures that the whole command executes in that complex. I’ve seen sudo sometimes not execute everything in the correct elevated context.
As such and FWIW,
I’ve never had the problems you describe when I log into the administrator account of any database application when I “su.”
TSU
Yes you’re right about sudo and it’s different operation in comparison to other distros like ubuntu and debian which the exact command worked as postgresql docs has stated. Honestly being new to openSUSE, it has given me some hard times with postgresql config and later installing psycopg2. Anyway thanks for your help. 
Which is why we’re here to help you with all aspects of your assignments assignment writing services. Our professional writers can help you with any or all of the following