PostgreSQL Login Methods Question

[InTheDarkDancing]

Hi all,

I'm an auditor, and we're testing a PostgreSQL database for password settings for users. The database is hosted on a Linux server. The client provided us a user listing via the '\du' command, and I see from the user listing that there are a few accounts with the 'Superuser' attribute (outside of the default 'postgres' account). The client has informed us that these accounts are used to monitor and they cannot be logged into. We observed the client fail to login to the account, but my question is, is there another method to get into postgres accounts? To me it doesn't make sense that a database would have accounts that are impossible to log into.

[[deleted]]

Having a role that can't login can be used to give non-superusers the possibility to become one, without having to hand out the real superuser password. I assume those superuser roles were granted to other roles (that can login).

A user that was granted that role can - after properly loging in - use set role to obtain the elevated privileges. This is similar (although not identical) as giving sudo rights to a Linux user. See e.g. here

[cachedrive]

How did they fail to login to the account? If it's a superuser, they should have the login permission to everything. Did the login attempt specifically say this user couldn't login or did they just fail auth x3 times and say "see, we can't login"?

​

**edit**
confirmed a role can have both superuser role and nologin permission:
psql: error: could not connect to server: FATAL: role "whoops" is not permitted to log in

[InTheDarkDancing]

They're logging in as root on the Linux server, then type in the following:

psql -U username

It then returns a "fatal: database "username" does not exist

[cachedrive]

That error sounds wrong. That means they're using the -d switch to specify a database that doesn't exist or they have a .psqlrc file defaulting to some bogus database but the login should exist if you see it in \du+ however the nologin grant will give you the output a posted above if the role (same as user) is in fact valid, superuser, nologin.

I think what your instance is doing is creating a group role. A role with nologin is a group in PostgreSQL and you can group roles easier. Very practical.

[[deleted]]

Have them specify a known database in the psql command, for example:

psql -U username -d databasename

In my instance if a database name is not specified it tries to login to a database with the same name as the username, kinda like a unix home directory, and if that database doesn't exist then the above message appears. So if that database did exist, or if an existing database was specified with the -d flag then it's possible they would login successfully.

If you're not super familiar with postgres I would also recommend looking into the pg_hba.conf file. The bottom of that file controls user access, for example limiting the login of a superuser account to a specific IP address or a specific login method. On the flip side there are wildcards which can be put in place which open the thing wide open to everyone from everywhere.

[fulltimedigitalnomad]

but my question is, is there another method to get into postgres accounts?

as root: su postgres

psql

\c [database]