There is no default username and password without you creating one. The simplest possible setup is to follow these steps to set up your own user as a superuser.
At a terminal prompt, create a postgres user with your own username
sudo -u postgres createuser --superuser $USER
Start the postgresql command prompt as your username but running as root since you didn't set a password yet;
sudo -u postgres psql
At the postgresql prompt, set your password;
\password $USER
After that, you should be able to log on just fine.
The setup is more thoroughly documented here.
EDIT:
If you get stuck not being able to authenticate automatically as the postgres user, you may want to compare your /etc/postgresql/9.1/main/pg_hba.conf (ie authentication config file) with the following lines from mine that works; you can get the uncommented ones using
grep -v ^# pg_hba.conf
The "local" lines should be the essential ones in this case since you can't authenticate even from the same machine;
local all postgres peer
local all all peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
Answer from Joachim Isaksson on Stack OverflowThere is no default username and password without you creating one. The simplest possible setup is to follow these steps to set up your own user as a superuser.
At a terminal prompt, create a postgres user with your own username
sudo -u postgres createuser --superuser $USER
Start the postgresql command prompt as your username but running as root since you didn't set a password yet;
sudo -u postgres psql
At the postgresql prompt, set your password;
\password $USER
After that, you should be able to log on just fine.
The setup is more thoroughly documented here.
EDIT:
If you get stuck not being able to authenticate automatically as the postgres user, you may want to compare your /etc/postgresql/9.1/main/pg_hba.conf (ie authentication config file) with the following lines from mine that works; you can get the uncommented ones using
grep -v ^# pg_hba.conf
The "local" lines should be the essential ones in this case since you can't authenticate even from the same machine;
local all postgres peer
local all all peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
During the installation process you've probably missed steps:
Now we need to reset the password for the ‘postgres’ admin account for the server, so we can use this for all of the system administration tasks. Type the following at the command-line (substitute in the password you want to use for your administrator account):
sudo su postgres -c psql template1
template1=# ALTER USER postgres WITH PASSWORD 'password';
template1=# \q
That alters the password for within the database, now we need to do the same for the unix user ‘postgres’:
sudo passwd -d postgres
sudo su postgres -c passwd
Now enter the same password that you used previously.
http://hocuspokus.net/2008/05/install-postgresql-on-ubuntu-804/
database - How can I log in and authenticate to PostgreSQL after a fresh install? - Stack Overflow
ubuntu - Logging into PostgreSQL on Linux - Stack Overflow
Access postgresql server on Linux command line, but not GUI (beekeeper)
linux - Postgres Initial Setup and User Login - Database Administrators Stack Exchange
There are two methods you can use. Both require creating a user and a database.
By default psql connects to the database with the same name as the user. So there is a convention to make that the "user's database". And there isn't any reason to break that convention if your user only needs one database. We'll be using mydatabase as the example database name.
Using createuser and createdb, we can be explicit about the database name,
$ sudo -u postgres createuser -s $USER $ createdb mydatabase $ psql -d mydatabaseYou should probably be omitting that entirely and letting all the commands default to the user's name instead.
$ sudo -u postgres createuser -s $USER $ createdb $ psqlUsing the SQL administration commands, and connecting with a password over TCP
$ sudo -u postgres psql postgresAnd, then in the psql shell
CREATE ROLE myuser LOGIN PASSWORD 'mypass'; CREATE DATABASE mydatabase WITH OWNER = myuser;Then you can login,
$ psql -h localhost -d mydatabase -U myuser -p <port>If you don't know the port, you can always get it by running the following, as the
postgresuser,SHOW port;Or,
$ grep "port =" /etc/postgresql/*/main/postgresql.conf
Sidenote: the postgres user
I suggest not modifying the postgres user.
- It's normally locked from the OS. No one is supposed to "log in" to the operating system as
postgres. You're supposed to have root to get to authenticate aspostgres. - It's normally not password protected and delegates to the host operating system. This is a good thing. This normally means in order to log in as
postgreswhich is the PostgreSQL equivalent of SQL Server'sSA, you have to have write-access to the underlying data files. And, that means that you could normally wreck havoc anyway. - By keeping this disabled, you remove the risk of a brute-force attack through a named super-user. Concealing and obscuring the name of the superuser has advantages.
By default, you would need to use the postgres user:
sudo -u postgres psql postgres
I don't expect anyone to be familiar with beekeeper, however, I'm hoping that maybe there are some common threads to this issue, via other GUIs as well.
I use this command to get into the postgres server as user postgres ( I think ) on command line:
sudo -i -u postgres psql
I'm prompted for my user password (one I use to get into computer), however, I'm not prompted for any other password, like one specifically for the database.
when I use the same info for my GUI (beekeeper studio), I get this:
connect ECONNREFUSED 127.0.0.1:5432
all i need is to access it myself on the localhost. Oddly I''m able to access the postgres database as user postgres on the same GUI, on my Apple running OS 11. I do require a password for that database, that I set myself, but it was so long ago,that I don't remember how or why I created a password for the default postgres db.
It might help to explain that I may be an unconventional poster here, since I'm not a dba nor do I have dba knowledge. I used to be just another Microsoft SQL admin user, at my last couple jobs, running basic queries and whatnot. The type of employee who had to submit a ticket to a dba, to update a table for any reason. I found that I preferred SQL for organizational tasks, that most consumers use spreadsheet applications for, so I found postgresql and the GUI Beekeeper, to basically recreate my Microsoft SQL admin experience, on my Apple OS ( and now Linux ) at home. I expect I need to provide more information, please let me know, thank you
When trying to connect via pgadminIII i am prompted for a password when using the same user name.
pgAdminIII is probably connecting over TCP (as opposed to unix domain sockets) and so is subject to the rules of a 'host*' line from pg_hba.conf, rather than a 'local' line. If that line says a password is needed, then it will ask for a password--even if no password is actually assigned to the user. There should be a message in the server log describing the situation in more detail. The details are not sent to the (failed) client, to avoid leaking sensitive information.
Additionally, I would like to make my own user name an admin and use my linux password
It sounds like you might want PAM authentication (or maybe GSSAPI or LDAP). But to use those, you have to change your linux log-on system so that it also uses one of them, as I don't think any of them are configured to do that by default. You can also use peer authentication, which doesn't require your password explicitly, it just uses the fact that you are logged on as the local Linux user (which presumably did require the password) to authenticate to PostgreSQL. Or you could take the easy way out, and just set your PostgreSQL password to be the same thing as your linux password, and then manually keep them in sync.
However when atempting to connect form command line it acts like there should also be a database named the same as my user.
Yes, that is what happens if you don't specify a database name--it assumes you want to connect to a database with the same name as the user. If you want to connect to a different database, then just specify it.
Users, roles, and permissions can get kind of confusing in PostgreSQL. To control who can try to connect to the database, Postgres uses the pg_hba.conf file. In Ubuntu, it's buried in /etc/postgres/<#>/main.
From inside the data base there are roles which control permissions each user has inside the database.
An easy, but probably insecure way to connect is to set up trust from localhost connections in pg_hba.conf.
Recently changed from windows to ubuntu and when i installed pgadmin4 I am having this issue where pgadmin does not recognize postgresql or something like that:
On windows i just installed and on servers I saw POSTGRESQL with the elephant but here it does not show.
The other answers were not completely satisfying to me. Here's what worked for postgresql-9.1 on Xubuntu 12.04.1 LTS.
Connect to the default database with user postgres:
sudo -u postgres psql template1
Set the password for user postgres, then exit psql (Ctrl-D):
ALTER USER postgres with encrypted password 'xxxxxxx';
Edit the
pg_hba.conffile:sudo vim /etc/postgresql/9.1/main/pg_hba.conf
and change "peer" to "md5" on the line concerning postgres:
local all postgres peer md5
To know what version of postgresql you are running, look for the version folder under
/etc/postgresql. Also, you can use Nano or other editor instead of VIM.Restart the database :
sudo /etc/init.d/postgresql restart
(Here you can check if it worked with
psql -U postgres).Create a user having the same name as you (to find it, you can type
whoami):sudo createuser -U postgres -d -e -E -l -P -r -s
<my_name>The options tell postgresql to create a user that can login, create databases, create new roles, is a superuser, and will have an encrypted password. The really important ones are -P -E, so that you're asked to type the password that will be encrypted, and -d so that you can do a
createdb.Beware of passwords: it will first ask you twice the new password (for the new user), repeated, and then once the postgres password (the one specified on step 2).
Again, edit the
pg_hba.conffile (see step 3 above), and change "peer" to "md5" on the line concerning "all" other users:local all all peer md5
Restart (like in step 4), and check that you can login without -U postgres:
psql template1
Note that if you do a mere
psql, it will fail since it will try to connect you to a default database having the same name as you (i.e.whoami). template1 is the admin database that is here from the start.Now
createdb <dbname>should work.
Under Linux PostgresQL is usually configured to allow the root user to login as the postgres superuser postgres from the shell (console or ssh).
$ psql -U postgres
Then you would just create a new database as usual:
CREATE ROLE myuser LOGIN password 'secret';
CREATE DATABASE mydatabase ENCODING 'UTF8' OWNER myuser;
This should work without touching pg_hba.conf. If you want to be able to do this using some GUI tool over the network - then you would need to mess with pg_hba.conf.