Disable / bypass authentication in PostgreSQL

Question

I need to run benchmarks (pgbench) in different environments on different versions of PostgreSQL. Authentication works inconsistently and is a generally huge headache / adds a lot of fragile workarounds around the code that has no reason to care about access to the database.

Is there any way I could do something similar to MySQL:

update user set authentication_string=password(''),
 plugin='mysql_native_password'
 where user='root';

and be done with it forever?

I would also be interested in being able to run the database and utilities as super-user (root). Again, I have no interest in all the security dance, there's nothing to protect, but the price for authentication is too high (weeks of work in order to make scripts work reliably, while they aren't even the main focus of my work).

Answer 1

Normally, the entries in the pg_hba.conf file demand a password from the client.

But if you change the authentication method (in the last column) from the usual password/md5/scram-sha-256 to trust, then

PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names).

... and does not even ask for a password.

Of course, restrictions made in the database and user columns still apply. This method should only be used when there is adequate operating-system-level protection on connections to the server.
[...]
trust authentication is appropriate and very convenient for local connections on a single-user workstation. [...] It is seldom reasonable to use trust for any TCP/IP connections other than those from localhost (127.0.0.1).

• wvxvw: Thanks, I'm going to try this as soon as I get back to working on this. Sounds promising.

Answer 2

The default Linux distribution of PostgreSQL (https://www.postgresql.org/download/) creates the system user "postgres", simply by installing the package from there. It also has the default behaviour, that this user may access the database without password.

This is set up via the config file pg_hba.conf (https://www.postgresql.org/docs/9.2/auth-pg-hba-conf.html):

# Rule Name: local access as postgres user
local all postgres ident 

So, on Linux you simply can change to this user with

su - postgres (for root users)

or

sudo -u postgres -i (for sudo users)

Windows has a similar command "runas" since XP/2003.

When connecting to Postgresql server via a command like

psql

the server asks the operating system for the executing user and that's it. You won't even need to set a password. Because pg_hba.conf defines local superuser access for this user.

createdb test 
pgbench -i test 
pgbench test

• wvxvw: What do you mean by "default" distribution? (Who is the distributor?). Windows is of the least concern to me. But using su is out of question really. Not in my setup. And not in this way (I could use sudo -Hiu postgres ... but this is the headache I specifically want to avoid). I don't want to manage system users for PostgreSQL, this is why I asked the question.
• wvxvw: To rephrase this: It would probably be easier for me to build PostgreSQL from sources, if in this way I could avoid dealing with managing system users and trying to prevent gazilion of ways authentication may fail.