Edit

Configure security for your Azure Arc-enabled PostgreSQL server

  • Article

This document describes various aspects related to security of your server group:

  • Encryption at rest
  • Postgres roles and users management
    • General perspectives
    • Change the password of the postgres administrative user
  • Audit

Note

As a preview feature, the technology presented in this article is subject to Supplemental Terms of Use for Microsoft Azure Previews.

The latest updates are available in the release notes.

Encryption at rest

You can implement encryption at rest either by encrypting the disks on which you store your databases and/or by using database functions to encrypt the data you insert or update.

Hardware: Linux host volume encryption

Implement system data encryption to secure any data that resides on the disks used by your Azure Arc-enabled Data Services setup. You can read more about this topic:

Software: Use the PostgreSQL pgcrypto extension in your server group

In addition of encrypting the disks used to host your Azure Arc setup, you can configure your Azure Arc-enabled PostgreSQL server to expose mechanisms that your applications can use to encrypt data in your database(s). The pgcrypto extension is part of the contrib extensions of Postgres and is available in your Azure Arc-enabled PostgreSQL server. You find details about the pgcrypto extension here. In summary, with the following commands, you enable the extension, you create it and you use it:

Create the pgcrypto extension

Connect to your server group with the client tool of your choice and run the standard PostgreSQL query:

CREATE EXTENSION pgcrypto;

Find details here about how to connect.

Verify the list the extensions ready to use in your server group

You can verify that the pgcrypto extension is ready to use by listing the extensions available in your server group. Connect to your server group with the client tool of your choice and run the standard PostgreSQL query:

select * from pg_extension;

You should see pgcrypto if you enabled and created it with the commands indicated above.

Use the pgcrypto extension

Now you can adjust the code your applications so that they use any of the functions offered by pgcrypto:

  • General hashing functions
  • Password hashing functions
  • PGP encryption functions
  • Raw encryption functions
  • Random-data functions

For example, to generate hash values. Run the command:

select crypt('Les sanglots longs des violons de l_automne', gen_salt('md5'));

Returns the following hash:

              crypt
------------------------------------
 $1$/9ACBYOV$z52PAGjQ5WTU9xvEECBNv/

Or, for example:

select hmac('Les sanglots longs des violons de l_automne', 'md5', 'sha256');

Returns the following hash:

                                hmac
--------------------------------------------------------------------
 \xd4e4790b69d2cc8dbce3385ee63272bc7760f1603640bb211a7b864e695570c5

Or, for example, to store encrypted data like a password:

  • An application stores secrets in the following table:

    create table mysecrets(USERid int, USERname char(255), USERpassword char(512));

  • Encrypt their password when creating a user:

    insert into mysecrets values (1, 'Me', crypt('MySecretPasswrod', gen_salt('md5')));

  • Notice that the password is encrypted:

    select * from mysecrets;

Output:

- USERid: 1
- USERname: Me
- USERpassword: $1$Uc7jzZOp$NTfcGo7F10zGOkXOwjHy31

When you connect with the application and pass a password, it looks up in the mysecrets table and returns the name of the user if there is a match between the password that is provided to the application and the passwords stored in the table. For example:

  • Pass the wrong password:

    select USERname from mysecrets where (USERpassword = crypt('WrongPassword', USERpassword));

    Output

      USERname
---------
(0 rows)

  • Pass the correct password:

    select USERname from mysecrets where (USERpassword = crypt('MySecretPasswrod', USERpassword));

    Output:

      USERname
---------
Me
(1 row)

This small example demonstrates that you can encrypt data at rest (store encrypted data) in Azure Arc-enabled PostgreSQL server using the Postgres pgcrypto extension and your applications can use functions offered by pgcrypto to manipulate this encrypted data.

Postgres roles and users management

General perspectives

To configure roles and users in your Azure Arc-enabled PostgreSQL server, use the standard Postgres way to manage roles and users. For more details, read here.

Audit

For audit scenarios please configure your server group to use the pgaudit extensions of Postgres. For more details about pgaudit see pgAudit GitHub project. To enable the pgaudit extension in your server group read Use PostgreSQL extensions.

Use SSL connection

SSL is required for client connections. In connection string, the SSL mode parameter should not be disabled. Form connection strings.

Related content