Why can't I `su postgres`?

Question

1. I wonder why when I provide root's password, the following command reports failure?

   $ su postgres
   Password: 
   su: Authentication failure
   

   Is it correct that su asks for the password of root, not of postgres?

   If it is the password of postgres, when I installed postgreSQL, I didn't set up a login name to connect to postgresql server, and I didn't explicitly create the user postgres on my Ubuntu, so what is its password?

   in /etc/passwd

   postgres:x:124:133:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
   

   in /etc/shadow:

   postgres:*:17478:0:99999:7:::
   

2. If I indeed can't su postgres, then generally speaking, what target users can su switch to?

   Is it correct that they are also the users whose ids setuid() can take as argument?

   From APUE, I learned that login names without valid login shell command can't be used for login. Are they also can be sued to? But postgres has a valid login shell command /bin/bash, so why can't I su postgres?

Thanks.

Answer 1

Look at the second field of /etc/shadow:

postgres:*:17478:0:99999:7:::

Normally it would have the encrypted password, but here it has just a single asterisk. That means the account is locked - no password will be acceptable for it. This is the state any new account will have until a password is assigned to it.

To transition into a user account that is currently locked, you would need a transition method that does not ask for the password of the target account. For su, that would mean you would have to fully become root first.

It would be possible to configure sudo to allow you access to the postgres account even though it is locked for password authentication. The /etc/sudoers line would be something like this:

Tim     ALL=(postgres) ALL

The sudo command line equivalent to su postgres would be sudo -u postgres -s. Note: in this method, some environment settings from your original account may be still in use in your session as user postgres. You may or may not want that: it could be actually useful if you have two or more database administrators with different personal preferences for their shell/environment both sudoing to the postgres account.

If you want the environment to be exactly as if it would be if user postgres would have when logged in directly, you could also use sudo -u postgres -i (the equivalent of su - postgres).

But if you want to have su postgres work, you would just need to have a password set for the user postgres. That can be achieved by running passwd postgres as root.

Answer 2

You should not give the postgres user a password. This is considered a security flaw as someone can now login as that user and do whatever they want. Instead, you should give yourself sudo permission on this user and use constructs like the following.

sudo -u postgres psql ...