7

PostgreSQL Database Security: Authentication

 3 years ago
source link: https://www.percona.com/blog/2021/02/01/postgresql-database-security-authentication/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
PostgreSQL Database Security: Authentication

Recently, I wrote an overview about what you need to know about PostgreSQL security. For this post, I want to write about PostgreSQL authentication. It is divided into three categories: 1. PostgreSQL Internal Authentication, 2. OS-based Authentication, and 3. External Server-Based Authentication.  In most cases, PostgreSQL is configured to be used with internal authentication, so here we will discuss each and every internal authentication method in detail. The next blog will cover the OS authentication methods such as PAM, Peer, and Ident.

Figure 1: PostgreSQL Authentication method.

The following is the list of PostgreSQL internal authentication supported methods.

  • Trust
  • Reject
  • md5
  • SCRAM
  • Cert

PostgreSQL has a configuration file to configure authentication called pg_hba.conf. All the authentication-related settings are part of this configuration file. Here is the sample pg_hba.conf file:

pg_hba.conf
Shell
host    database             user               address      auth-method    [auth-options]
------+--------------- +-----------------+----------------+--------------+---------------
Local |  all           |     all         |                |   trust 
host  |  all           |     all         | 127.0.0.1/32   |   trust
host  |  postgres      |     postgres    | 192.168.1.1/24 |   md5
host  |  replication   |     postgres    | 127.0.0.1/32   |   md5

The first column of the “pg_hbaa.conf” file is the “host”. It can be either local or host. The local is fixed for the Unix-Domain socket, and in the case of a host, you need to specify the host IP address in the address column. The second column is the database, which is used to specify the database name. You can set the authentication method based on databases, meaning your database can have its own authentication method. If these values are set to all, then all the databases will use the same authentication method. The third column of the file is the user, which means you can set separate authentication methods for different users and “all” means apply to all users. The fourth parameter is to specify the IP address, which means which IP address can use that authentication method. The next column is the auth-method which can be any of the authentication methods shown in Figure 1. The last column is auth-options, in case any authentication method has some options.

Trust and Reject

When you specify the authentication method Trust, then any user who fulfills the requirement will not require any password. Similarly, in the case of Reject, any user who fulfills the requirement will not be allowed to login into the system. Here is the example of Trust and Reject:

pg_hba.conf (Trust / Reject)
Shell
host        database          user    address        auth-method    [auth-options]
------+-----------------+-----------------+----------------+-------------------------+--------------------
host  |    all          |     all   | 127.0.0.1/32    |   trust
host  |    all          |     all   | 127.0.0.1/32    |   Reject

The pg_hba.conf file has two entries; the first one has authentication method trust and the second one has authentication method reject. A local host user no longer needs to have a password and will be granted permission to login into the system without the password. But any computer other than localhost will be rejected, because of the second line of the pg_hba.conf file.

Trust Authentication

Login From Localhost
Shell
[email protected]:~$ psql postgres -h 127.0.0.1 -U postgres
psql (12.4)
Type "help" for help.
postgres=>

Reject Authentication

Login From Remote Node
Shell
[email protected]:~$ psql postgres -h 10.0.2.1 -U postgres
psql: error: could not connect to server:
FATAL:  pg_hba.conf rejects connection for host "10.0.2.2", user "postgres", database "postgres"

In the case of md5 authentication, you need to provide the password. Let’s look at a simple example of that.

Shell
host        database          user    address            auth-method               [auth-options]
------+-----------------+-----------------+-----------+-------------------------+--------------------
host  |    all          |     all   | 10.0.2.2/32     |   md5
Shell
vagrant@vagrant:~$ psql postgres -h 10.0.2.1 -U postgres
Password for user postgres: 
psql (12.4)
Type "help" for help.
postgres=>

SCRAM

The SCRAM, or more specifically scram-sha-256, is a challenge-response scheme that prevents password sniffing on untrusted connections. It is one of the most secure authentication methods, using secure, cryptographically-hashed security, to store the passwords on the server.

Step 1: Change the password of the user

Shell
postgres=# SET password_encryption = 'scram-sha-256';
postgres=# ALTER USER postgres WITH PASSWORD 'test';
ALTER ROLE

Step 2: Change the pg_hba.conf file.

Shell
host        database          user    address        auth-method    [auth-options]
------+-----------------+-----------------+-----------+-------------------------+--------------------
host  |    all          |     all   | 10.0.2.2/32    |   <span class="s1">scram-sha-256</span>

Step 3: Test the connection

Shell
$ psql postgres -U postgres
Password for user postgres: 
psql (13.0)
Type "help" for help.

Percona Distribution for PostgreSQL is free to download and use. It is the best and most critical enterprise-level components from the open-source community, designed and tested to work together in one single source. 

Server Key and Certificate

Step 1: Generate Server keys

Generate Key in $PGDATA
Shell
$  openssl genrsa -des3 -out server.key 1024
Generating RSA private key, 1024 bit long modulus (2 primes)
.+++++
..................+++++
e is 65537 (0x010001)
Enter pass phrase for server.key:
Verifying - Enter pass phrase for server.key:
$ openssl rsa -in server.key -out server.key
Enter pass phrase for server.key:
writing RSA key
$ chmod og-rwx server.key

Step 2: Generate Server Certificate

Shell
$ openssl req -new -key server.key -days 3650 -out server.crt -x509
-----
Country Name (2 letter code) [AU]:PK
State or Province Name (full name) [Some-State]:ISB
Locality Name (eg, city) []:Islamabad
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Percona
Organizational Unit Name (eg, section) []:Dev
Common Name (e.g. server FQDN or YOUR name) []:localhost
Email Address []:[email protected]
$ cp server.crt root.crt

Client Keys and Certificate

Step 3: Generate a client certificate

Shell
$ openssl genrsa -des3 -out /tmp/postgresql.key 1024
Generating RSA private key, 1024 bit long modulus (2 primes)
..........................+++++
.....................................................+++++
e is 65537 (0x010001)
Enter pass phrase for /tmp/postgresql.key:
Verifying - Enter pass phrase for /tmp/postgresql.key:
$ openssl rsa -in /tmp/postgresql.key -out /tmp/postgresql.key
Enter pass phrase for /tmp/postgresql.key:
writing RSA key
$ openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr
-----
Country Name (2 letter code) [AU]:PK
State or Province Name (full name) [Some-State]:ISB
Locality Name (eg, city) []:Islamabad
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Percona
Organizational Unit Name (eg, section) []:Dev
Common Name (e.g. server FQDN or YOUR name) []:127.0.0.1
Email Address []:[email protected]
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:pakistan
An optional company name []:Percona

Step 4: Copy root.crt to the client.

Shell
$ cp $PGDATA/root.crt /tmp/

PostgreSQL Settings

Step 5:  In postgrsql.conf file set ssl = on

Shell
# - SSL -
ssl = on
#ssl_ca_file = ''
#ssl_cert_file = 'server.crt'

Step 6: Restart PostgreSQL

Shell
pg_ctl restart

Connection

Now, all set here, and you can test the connection using the psql command.

Shell
$ psql 'host=localhost port=5432 dbname=postgres user=vagrant sslmode=verify-full sslcert=/tmp/postgresql.crt sslkey=/tmp/postgresql.key sslrootcert=/tmp/root.crt'
psql (13.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# 

Conclusion

This is the second part of the security series blog, and in the first blog post, we see the main features of security we need to consider. In this blog, we started with authentication and only focused on the PostgreSQL authentication mechanism and still need to see how external authentication methods work in PostgreSQL. Stay tuned!

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.

Download Percona Distribution for PostgreSQL Today!


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK