如何使普通的Linux用户无需sudo访问即可访问postgres数据库？ [英] How to enable regular linux users to access postgres database without sudo access?

问题描述

我们在Centos7上安装了一个postgres数据库服务器（10.3），并创建了一个名为 db_name的数据库。我们具有数据库访问权限。 pg_hba.conf中的设置如下：

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

当我具有sudo访问权限时，我可以访问数据库 db_name。

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=>

当我尝试以常规Linux用户身份访问数据库时，出现以下错误：

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"?

我们想要限制某些用户sudo访问的原因是我们的报表专家需要访问数据库 db_name，但我们不希望他们拥有sudo特权来执行其他操作。

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!

推荐答案

我找到了解决方案。
问题的原因是不存在/var/run/postgresql/.s.PGSQL.5432文件。
默认情况下，unix_socket_directories是在postgresql.conf中设置的'/ tmp'。

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.

由于某种原因，常规的Linux用户不会看/ tmp /.s.PGSQL.5432，而是查看/var/run/postgresql/.s.PGSQL.5432文件。

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 

这篇关于如何使普通的Linux用户无需sudo访问即可访问postgres数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！