Tag: security

How to Extract and Replicate PostgreSQL Permissions When Migrating to a New Instance

Migrating a PostgreSQL database isn’t just about moving data—getting the right roles and permissions in place is critical for security and proper application function. This post demonstrates how to extract roles and permissions from your source instance and apply them to a new PostgreSQL environment.

Understanding Permissions in PostgreSQL

PostgreSQL controls access via roles. A role can represent either a database user or a group, and roles are granted permissions (privileges) on objects (tables, databases, schemas, etc.) using GRANT and REVOKE commands. These permissions can be viewed and managed at various levels:

  • Database: Control who can connect
  • Schema: Control access to groups of tables, functions, etc.
  • Objects: Control what actions (SELECT, INSERT, UPDATE, etc.) users can perform on tables, functions, or sequences.

You can view permissions using PostgreSQL’s psql meta-commands:

  • \l+ — Show database privileges
  • \dn+ — Show schema privileges
  • \dp+ — Show table and other object privileges

Step 1: Extract Roles from the Source Instance

To extract roles (users and groups) from your current PostgreSQL server:

pg_dumpall --roles-only > roles.sql

Note:

  • This command will export all roles (but not their passwords in managed services like Azure Database for PostgreSQL).
  • In cloud managed systems, you might not have the ability to extract passwords; you’ll need to set them manually on the target instance.

Step 2: Extract Role & Object Permissions

Extracting object-level permissions (like all GRANT and REVOKE statements) can be done while dumping database schema:

pg_dump -h <source_server> -U <username> -d <dbname> -s > db_schema.sql

Next, filter permission statements:

If you’re working in PowerShell (Windows), run:

pg_dump -h "psql-prod-01.postgres.database.azure.com" -U pgadmin -d "prod_db" -s | Select-String -Pattern "^(GRANT|REVOKE|ALTER DEFAULT PRIVILEGES)" -Path "C:\Path\to\db_schema.sql" | ForEach-Object { $_.Line } > C:\Path\to\permissions.sql

If you’re working on Mac/Linux, run:

pg_dump -h "psql-prod-01.postgres.database.azure.com" -U pgadmin -d "prod_db" -s | grep -E '^(GRANT|REVOKE|ALTER DEFAULT PRIVILEGES)' > perms.sql

This extracts all lines related to granting or revoking privileges and puts them in perms.sql.

Step 3: Prepare and Edit Scripts

  • Review the extracted roles.sql and `permissions.sql:
    • Remove any references to unsupported roles (like postgres superuser in cloud environments).
    • Plan to set user passwords manually if they weren’t included.

Step 4: Copy Roles and Permissions to the New Instance

  1. Recreate roles:textpsql -h <target_server> -U <admin_user> -f roles.sql
    • Remember to set or update passwords for each user after creation.
  2. Apply object-level permissions:textpsql -h <target_server> -U <admin_user> -d <target_db> -f perms.sql

Step 5: Validate Permissions

Connect as each role or user to ensure operations work as expected:

  • Use \dp tablename in psql to check table permissions.
  • Use the information_schema views (e.g., role_table_grants) to query permissions programmatically:SELECT grantee, privilege_type, table_name FROM information_schema.role_table_grants;

Query Azure MySQL Audit Logs

The common practice for user management in Azure AD is to create Azure AD Security Groups for users and assign permissions to groups rather than individual users. For example, the common practice for users who need access to Azure MySQL instance would to be add them to an Azure AD Group (say MySqlDb_Users) and then setup this Azure AD Group for access on Azure MySQL Instance.

Pre-requisite

  • Ensure that Audit Logs are enabled on all the production instances for Azure MySql as documented here — https://docs.microsoft.com/en-us/azure/mysql/howto-configure-audit-logs-portal
  • Make sure to enable Auditing on Server Parameters section (at least Connection audit log event type).
  • The logs can be saved to Storage Account, Event Hubs or Azure Log Analytics Workspace. We used Log Analytics Workspace for the runbook because it provides rich querying capabilities.

The following Log Analytics Query and the corresponding screenshot shows that individual user activity is tracked in the Audit Logs in spite of them using the Azure AD Group as username to connect Azure MySQL — external_user_s field logs the exact individual user, connection_id_d and thread_id_d carry over the Connection/ThreadId which can be used to tie the user across all log records.

AzureDiagnostics
| where Category == "MySqlAuditLogs"
| order by TimeGenerated desc
| project TimeGenerated, event_class_s, event_subclass_s, user_s, external_user_s, connection_id_d, thread_id_d, event_time_t, sql_text_s, db_s, table_name_s, error_code_d

Shell script to create a new user and add their SSH public key to an Ubuntu VM

Below is a shell script that creates a new user and adds their SSH public key to an Ubuntu VM:

#!/bin/bash

# Function to create a new user and add SSH public key
create_user_and_add_ssh_key() {
    username=$1
    ssh_key_path=$2

    # Create user
    sudo adduser --disabled-password --gecos "" $username

    # Create .ssh directory if not exists
    sudo -u $username mkdir -p /home/$username/.ssh

    # Copy SSH public key to authorized_keys file
    cat $ssh_key_path >> /home/$username/.ssh/authorized_keys

    # Set proper permissions
    sudo chown -R $username:$username /home/$username/.ssh
    sudo chmod 700 /home/$username/.ssh
    sudo chmod 600 /home/$username/.ssh/authorized_keys
}

# Example usage
new_username="newuser"
ssh_key_path="/path/to/public_key.pub"

create_user_and_add_ssh_key $new_username $ssh_key_path

Make sure to replace newuser with the desired username and /path/to/public_key.pub with the path to the SSH public key file you want to add.

Save this script to a file (e.g., create_user_and_add_ssh_key.sh), make it executable (chmod +x create_user_and_add_ssh_key.sh), and then run it (./create_user_and_add_ssh_key.sh).

This script will create a new user without a password prompt and add their SSH public key to the authorized_keys file.