This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!

If you think of a database as a room, today’s lesson is how to flexibly control the door of the room so that people who want to get in can get in.

Three files postgresql.conf, pg_hba.conf, and pg_ident.

Postgresql. Conf file

After the database cluster is installed and deployed, change the listening address of the database cluster. Otherwise, only the local address of the database server is listened on by default. In addition, ensure that the listening port number is normal and is not restricted by the firewall or other network security policies.

listen_addresses = '*'
port = 5432
Copy the code

Pg_hba.conf file

Access control files.

# TYPE       DATABASE      USER        ADDRESS      METHOD
 local            all       all                      ident
  host            all       all   127.0.0.1/32       trust
  host            all       all        ::1/128       trust
 local    replication       all                      trust
  host    replication       all   127.0.0.1/32       trust
  host    replication       all        ::1/128       trust
Copy the code

TYPE: database connection mode. There are four types

  • Local: matches a connection using a Unix domain socket. If there is no record of this type, a CONNECTION using a Unix domain socket is not allowed.
  • Host: matches TCP/IP connections. Host records match SSL or non-SSL connections. You need to configure listen_addresses.
  • Hostssl: matches TCP/IP connections. Only SSL encryption connections are used. You need to set SSL parameters.
  • Hostnossl: matches TCP/IP connections that do not use SSL.

DATABASE: Specifies which databases can be connected

  • The database name that matches, all specifies that it matches all databases.
  • Replication does not specify a database.
  • Multiple databases can be separated by commas.

USER: Specifies which users can be connected

  • The database user name that matches, all specifies that it matches all users.
  • You can provide multiple user names separated by commas.

ADDRESS: Specifies which IP addresses can be connected

  • Matched client computer address, all to match any IP address.
  • 0.0.0.0/0 indicates all IPv4 addresses.
  • :: 0/0 indicates all IPv6 addresses.
  • 192.168.100.101/32 This IP address is allowed for login.
  • 192.168.100.0/24 Network segments 19.168.100.0 to 192.168.100.255 are allowed to log in to the database

METHOD: Indicates the client authentication mode

  • Trust: You do not need a password or ident to log in as long as you know the database username. It is not recommended to use in production environments.
  • Md5: This is the common password authentication mode. If you are not using IDENT, use MD5. The password is transmitted to the database in MD5 format, which is more secure, and you do not need to create an operating system user with the same name.
  • Password: the password is sent to the database in plain text. Do not use it in the production environment.
  • Ident: Indicates the default local authentication mode for PostgreSQL on Linux. If a user can correctly log in to the operating system (note: not a database user), the user can use the mapped database user to log in to the database without a password. Operating system name, database user name, and database name must be the same.
  • Reject: Reject authentication, which is useful for “filtering out” certain hosts from a group.

After the pg_hba.conf file is modified, you need to reload the configuration without restarting the database.

select pg_reload_conf();
Copy the code

Pg_ident. Conf file

Database mapping file, an extension of ident authentication mode, and mapping between operating system users and database users. This file is used with pg_hba.conf.

Pg_ident. conf file # MAPNAME system-username pg-username ss aaa test ss postgres postgres ss Syd Syd pg_hba.conf file # TYPE DATABASE USER ADDRESS METHOD Local All All IDENT map= SS host all all 127.0.0.1/32 trust host all All ::1/128 trust Local replication all Trust host replication all 127.0.0.1/32 trust Host replication all ::1/128 trustCopy the code
  • MAPNAME: indicates the mapping name. The mapping name is configured in the pg_hba.conf file.
  • System-username: indicates the SYSTEM USERNAME.
  • Pg-username: indicates the USERNAME of the database.

After the configuration is complete, you can directly use the PSQL command to access the database without using the password. (PGUSER has been configured in the system environment variables of each user, and the corresponding user and database exist in the database cluster.)

-- AAA operating system user Logs in to the test database without password authentication. [root@dj ~]# su -aaa [aaa@dj ~]$PSQL PSQL (12.4) Type "help" for help.test => select user; User ------ test (1 row) --postgres Operating system user Log in to the Postgres database without password. [root@dj ~]# su - postgres [postgres@dj ~]$PSQL PSQL (12.4) Type "help" for help.postgres =# select user; User ---------- postgres (1 row) -- Syd Operating system user Log in to the Syd database without password. [root@dj ~]# su -syd [syd@dj ~]$PSQL PSQL (12.4) Type "help" for help.syd => select user; user ------ syd (1 row)Copy the code

The database user created in the last addition must have the database login permission. PostgreSql provides the concepts of users and roles. The only difference between the two is that a user has the login permission after being created, while a role can log in to a database only with additional login permission.

Create role CE1 and user ce2. postgres=# create role ce1 password 'ce1'; CREATE ROLE postgres=# create user ce2 password 'ce2'; CREATE ROLE -- Viewing permissions, you can find that ROLE CE1 has no login permission. postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- ce1 | Cannot login | {} ce2 | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} repmgr | Superuser | {} Syd | | {} the test | | {} - switch connected users also can see ce1 role not login permissions. postgres=# \c - ce1; FATAL: role "ce1" is not permitted to log in Previous connection kept postgres=# \c - ce2; You are now connected to database "postgres" as user "ce2". Postgres => \ c-postgres -- Switch if ce1 has granted the login permission. postgres=# alter role ce1 with login; ALTER ROLE postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- ce1 | | {} ce2 | | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} repmgr | Superuser | {} syd | | {} test | | {} postgres=# \c - ce2; You are now connected to database "postgres" as user "ce2".Copy the code