How to enable regular linux users to access postgres database without sudo access?

Question

We have a postgres database server(10.3) installed on Centos7, and created a database with the name of "db_name". We have database access. The setting in pg_hba.conf is as the following:

# "local" is for Unix domain socket connections only
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

When I have sudo access, I can access the database "db_name".

[root@localhost bin]# sudo -s       
[root@localhost bin]# psql -U db_name db_user
psql (10.3)
Type "help" for help.

db_name=>

When I tried to access database as a regular linux user, I got the following error:

[linuxuser@localhost bin]# psql -U db_name db_user
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The reason we want to limit some users sudo access is that our report specialists need an access to the database "db_name", but we don't want them to have the sudo privilege to do something else.

What kind of settings shall I do in order to make it work? Thanks!

Try a TCP connection: psql -h -U username dbname. Additionally: you did not mention you created a user, only a database. When you create a database, Postgres does not automatically create a user for you as well. — user330315
– user330315, Commented Apr 26, 2018 at 5:50

smeng · Accepted Answer · 2018-04-27 04:22:40Z

3

I found the solution. The cause of the problem is that there is no /var/run/postgresql/.s.PGSQL.5432 file existed. By default, unix_socket_directories is '/tmp' which is set in postgresql.conf.

For some reason, the regular linux user is not looking at /tmp/.s.PGSQL.5432, but instead looking at /var/run/postgresql/.s.PGSQL.5432 file.

So the fix is as the following:

cd /var/run
mkdir postgresql 
cd postgresql/ 
ln -s /tmp/.s.PGSQL.5432 .s.PGSQL.5432

The following command works as well.

psql -h /tmp -U db_user db_name

edited Apr 27, 2018 at 4:22

answered Apr 27, 2018 at 4:17

smeng

711 gold badge1 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kiruahxh Over a year ago

You saved my day

Kiruahxh Over a year ago

Short version : sudo mkdir -p /var/run/postgresql && sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

Julián Esteban Salomón Torres · Accepted Answer · 2018-04-26 06:01:17Z

1

In PostgreSQL you should create a role to access the DB.

For that you will have to do this:

Change to postgres account (Created during installation of postgresql)
```
$ sudo -i -u postgres
```

Create a new role

postgres@server createuser --interactive

Output
Enter name of role to add: DB_Name
Shall the new role be a superuser? (y/n) y

Create Database
```
postgres@server createdb DB_Name
```

Create user, change to user and access to database

$ sudo adduser DB_Name
$ sudo -i -u DB_Name
$ psql
DB_Name=# \conninfo

References:

How To Install and Use PostgreSQL on CentOS 7

answered Apr 26, 2018 at 6:01

Julián Esteban Salomón Torres

2782 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to enable regular linux users to access postgres database without sudo access?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related