1

Installing PostgreSQL on GCE requires root password to run sudo -u postgresql. This prompts for a password, which I was never given.
How do I get this pass, or any way to run postgresql commands from the shell in a different way?

Tal
  • 7,827
  • 6
  • 38
  • 61

3 Answers3

0

If you want to run commands against your postgresql server you should not need to use sudo, just use this syntax to enter the Postgresql Interactive Shell:

psql -U username database_name

OR 

psql -U username hostname database_name

Replacing username with your postgresql usename, hostname (if not running on the same server) with the servers host name and database_name with the name of your database. For example:

psql -U postgressql customers
IanGSY
  • 3,664
  • 1
  • 22
  • 40
  • thanks but this doesn't work for the `createdb` command, or the `createuser` command, which is what I'm trying to run. I run `psql -U postgres createdb mydb` and get `Peer authentication failed for user "postgres"` – Tal Mar 31 '14 at 11:49
  • createdb -U postgres, assuming your username is postgres. you need valid role with the ability to create DB to use that. do you have a user? – Doon Mar 31 '14 at 11:51
  • a postgres user is added as part of the db installation. Here's a line from the installation log: `Adding user postgres to group ssl-cert`. I must admit I didn't setup anything else after issuing the apt-get, but I am sure the linux user `postgres` exists and that the default db user `postgres` is also auto created. Now the question is - if one doesn't have the ability to run `su - postgres` how can one issue the createuser command? – Tal Mar 31 '14 at 12:15
  • postresql roles and System users are 2 different things, and do not have to be related at all, by default if you do not specify the postgresql user(role) on the command line with the -U(or --username) option it will try to connect to the server using the same username that ran the command. Have you verified that the server is running, and what postgresql users where created (not the system user). – Doon Apr 01 '14 at 03:42
0

Normally sudo requires your user account password. So assuming that the account you are running the command from is listed in the sudoers file, the password it is prompting you for should be your own. Have you tried that, as opposed to the root or the postgresql password, which you don't appear to have (or might not even be set).

Doon
  • 19,719
  • 3
  • 40
  • 44
  • Correct, My account is listed in the `sudoers` file, but I don't have the login password to neither my login account nor to the root account. Google logs you into the GCE box using root certificates instead of username/password. So I can't use – Tal Apr 01 '14 at 09:00
  • 1
    is your account the instance creator? since GCE says instance creator should have passwordless sudo , can you try `sudo -s` to get a root shell, or sudo su postgres – Doon Apr 01 '14 at 11:48
0

Your system user postgresql doesn't have a password (I state with no proof... but I think you'll find this to be true.)

Normally you should use commands like these:

# Test that YOU can use psql (as postgres) to run a query:
psql -U postgres -c 'select * from pg_catalog.pg_user;'

# Test an interactive session:
psql -U postgres my_database
my_database=# select 42 as the_answer;

# Create a new database
psql -U postgres my_database
my_database=# create database mydb;


Alternatively, it's probably possible to login like this (it usually is):

sudo su postgres

And you could probably use this to run createdb. But running psql as yourself is probably better.

Community
  • 1
  • 1
mdahlman
  • 9,204
  • 4
  • 44
  • 72
  • The only resolution I could find ATM is this last one: `sudo su postgres`. From there the psql commands work just fine. – Tal Apr 01 '14 at 09:01
  • It's great that this works as a solution. What was the barrier to using psql as a different user? (Or what was the error?) – mdahlman Apr 01 '14 at 16:31