Création d'utilisateurs et de rôles d'application dans Aurora PostgreSQL compatible - Recommandations AWS
RécapitulatifConditions préalables et limitationsArchitectureOutilsÉpopéesRessources connexesInformations supplémentaires

Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Création d'utilisateurs et de rôles d'application dans Aurora PostgreSQL compatible

Créée par Abhishek Verma (AWS)

Récapitulatif

Lorsque vous migrez vers l'édition compatible avec Amazon Aurora PostgreSQL, les utilisateurs et les rôles de base de données qui existent sur la base de données source doivent être créés dans la base de données compatible avec Aurora PostgreSQL. Vous pouvez créer les utilisateurs et les rôles dans Aurora PostgreSQL compatible en utilisant deux approches différentes :

  • Utilisez des utilisateurs et des rôles similaires dans la base de données cible et dans la base de données source. Dans cette approche, les langages de définition des données (DDLs) sont extraits de la base de données source pour les utilisateurs et les rôles. Ils sont ensuite transformés et appliqués à la base de données cible compatible Aurora PostgreSQL. Par exemple, le billet de blog Utiliser SQL pour mapper les utilisateurs, les rôles et les autorisations d'Oracle à PostgreSQL traite de l'utilisation de l'extraction à partir d'un moteur de base de données source Oracle.

  • Utilisez des utilisateurs et des rôles standardisés couramment utilisés lors du développement, de l'administration et pour effectuer d'autres opérations connexes dans la base de données. Cela inclut les opérations de lecture seule, de lecture/écriture, de développement, d'administration et de déploiement effectuées par les utilisateurs respectifs.

Ce modèle contient les autorisations requises pour la création d'utilisateurs et de rôles dans Aurora PostgreSQL compatible avec l'approche standardisée des utilisateurs et des rôles. Les étapes de création des utilisateurs et des rôles sont alignées sur la politique de sécurité qui consiste à accorder le moindre privilège aux utilisateurs de la base de données. Le tableau suivant répertorie les utilisateurs, leurs rôles correspondants et leurs informations sur la base de données.

Utilisateurs

Rôles

Objectif

APP_read

APP_RO

Utilisé pour l'accès en lecture seule au schéma APP

APP_WRITE

APP_RW

Utilisé pour les opérations d'écriture et de lecture sur le schéma APP

APP_dev_user

APP_DEV

Utilisé à des fins de développement sur le schémaAPP_DEV, avec accès en lecture seule au schéma APP

Admin_User

rds_superuser

Utilisé pour effectuer des opérations d'administration sur la base de données

APP

APP_DEP

Utilisé pour créer les objets sous le APP schéma et pour le déploiement d'objets dans le APP schéma

Conditions préalables et limitations

Prérequis

  • Un compte Amazon Web Services (AWS) actif

  • Une base de données PostgreSQL, une base de données Amazon Aurora PostgreSQL Edition compatible ou une base de données Amazon Relational Database Service (Amazon RDS) pour PostgreSQL

Versions du produit

  • Toutes les versions de PostgreSQL

Architecture

Pile technologique source

  • Toute base de données

Pile technologique cible

  • Compatible avec Amazon Aurora PostgreSQL

Architecture cible

Le schéma suivant montre les rôles des utilisateurs et l'architecture du schéma dans la base de données compatible Aurora PostgreSQL.

Rôles utilisateur et architecture de schéma pour la base de données compatible Aurora PostgreSQL.

                                                                                                                                    

Automatisation et mise à l'échelle

Ce modèle contient les utilisateurs, les rôles et le script de création de schéma, que vous pouvez exécuter plusieurs fois sans aucun impact sur les utilisateurs existants de la base de données source ou cible.

Outils

Services AWS

  • Amazon Aurora PostgreSQL Compatible Edition est un moteur de base de données relationnelle entièrement géré et compatible ACID qui vous aide à configurer, exploiter et dimensionner les déploiements PostgreSQL.

Autres services

  • psql est un outil frontal basé sur un terminal qui est installé avec chaque installation de base de données PostgreSQL. Il possède une interface de ligne de commande pour exécuter des commandes SQL, PL-PGSQL et du système d'exploitation.

  • pgAdmin est un outil de gestion open source pour PostgreSQL. Il fournit une interface graphique qui vous permet de créer, de gérer et d'utiliser des objets de base de données.

Épopées

TâcheDescriptionCompétences requises

Créez l'utilisateur de déploiement.

L'utilisateur de déploiement APP sera utilisé pour créer et modifier les objets de base de données lors des déploiements. Utilisez les scripts suivants pour créer le rôle d'utilisateur de déploiement APP_DEP dans le schémaAPP. Validez les droits d'accès pour vous assurer que cet utilisateur a uniquement le privilège de créer des objets dans le schéma requisAPP.

  1. Connectez-vous à l'utilisateur administrateur et créez le schéma.

    CREATE SCHEMA APP;

  2. Créez l'utilisateur.

    CREATE USER APP WITH PASSWORD  <password> ;

  3. Créez le rôle.

    CREATE ROLE APP_DEP ;
GRANT all on  schema APP to APP_DEP ;
GRANT USAGE ON SCHEMA APP to APP_DEP ;
GRANT connect on database <db_name>  to APP_DEP ;
GRANT APP_DEP to APP;

  4. Pour tester les privilèges, connectez-vous aux tables APP et créez-les.

    set search_path to APP;
SET
CREATE TABLE test(id integer ) ;
CREATE TABLE

  5. Vérifiez les privilèges.

    select schemaname  , tablename , tableowner    from pg_tables where tablename like 'test' ;
schemaname   | tablename |  tableowner 
APP                  | test            | APP
DBA

Créez l'utilisateur en lecture seule.

L'utilisateur en lecture seule APP_read sera utilisé pour effectuer l'opération en lecture seule dans le schéma. APP Utilisez les scripts suivants pour créer l'utilisateur en lecture seule. Validez les droits d'accès pour vous assurer que cet utilisateur a le privilège de lire uniquement les objets du schéma APP et d'accorder automatiquement un accès en lecture à tout nouvel objet créé dans le schémaAPP.

  1. Créez l'utilisateurAPP_read.

    create user APP_read ;
alter user APP_read with password 'your_password' ;

  2. Créez le rôle.

    CREATE ROLE APP_ro ;
GRANT SELECT ON ALL TABLES IN SCHEMA  APP  TO  APP_RO   ;
GRANT USAGE ON SCHEMA  APP   TO APP_RO
GRANT CONNECT ON DATABASE  testdb  TO APP_RO ;
GRANT APP_RO TO APP_read;

  3. Pour tester les privilèges, connectez-vous en utilisant l'APP_readutilisateur.

    set search_path to APP ;
create table test1( id integer) ;
ERROR:  permission denied for schema APP
LINE 1: create table test1( id integer) ;
insert into test values (34) ;
ERROR: permission denied for table test SQL state: 42501
select from test
no rows selected
DBA

Créez l'utilisateur de lecture/écriture.

L'utilisateur de lecture/écriture APP_WRITE sera utilisé pour effectuer des opérations de lecture et d'écriture sur le schéma. APP Utilisez les scripts suivants pour créer l'utilisateur de lecture/écriture et lui attribuer le APP_RW rôle. Validez les droits d'accès pour vous assurer que cet utilisateur dispose de privilèges de lecture et d'écriture uniquement sur les objets du schéma APP et pour accorder automatiquement un accès en lecture et en écriture à tout nouvel objet créé dans le schémaAPP.

  1. Créez l'utilisateur.

    CREATE USER APP_WRITE ;
alter user APP_WRITE  with password 'your_password' ;

  2. Créez le rôle.

    CREATE ROLE APP_RW;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA APP   TO APP_RW ;
GRANT CONNECT ON DATABASE postgres to APP_RW ;
GRANT USAGE ON SCHEMA APP to APP_RW ;
ALTER DEFAULT PRIVILEGES IN SCHEMA APP
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO APP_RW ;
GRANT APP_RW to APP_WRITE

  3. Pour tester les privilèges, connectez-vous en utilisant l'APP_WRITEutilisateur.

    SET SEARCH_PATH to APP;
CREATE TABLE test1( id integer) ;
ERROR:   permission denied for schema APP
LINE 1: create table test1( id integer) ;
SELECT * FROM test ;
id 
----
12
INSERT INTO test values (31) ;
INSERT 0 1

Créez l'utilisateur administrateur.

L'utilisateur admin Admin_User sera utilisé pour effectuer des opérations d'administration sur la base de données. Des exemples de ces opérations sont CREATE ROLE etCREATE DATABASE. Admin_Userutilise le rôle intégré rds_superuser pour effectuer des opérations d'administration sur la base de données. Utilisez les scripts suivants pour créer et tester le privilège de l'utilisateur administrateur Admin_User dans la base de données.

  1. Créez l'utilisateur et accordez le rôle.

    create user Admin_User WITH PASSWORD   ‘Your password’
ALTER user Admin_user CREATEDB;
ALTER user Admin_user CREATEROLE;

  2. Pour tester le privilège, connectez-vous depuis l'Admin_Userutilisateur.

    SELECT * FROM APP.test ;
  id
  ----
   31
CREATE ROLE TEST ;
CREATE DATABASE test123 ;
DBA

Créez l'utilisateur de développement.

L'utilisateur de développement APP_dev_user aura le droit de créer les objets dans son schéma local APP_DEV et d'accéder en lecture dans le schémaAPP. Utilisez les scripts suivants pour créer et tester les privilèges de l'utilisateur APP_dev_user dans la base de données.

  1. Créez l'utilisateur.

    CREATE USER APP1_dev_user with password ‘your password’;

  2. Créez le APP_DEV schéma pourApp_dev_user.

    CREATE SCHEMA APP1_DEV ;

  3. Créez le rôle APP_DEV

    CREATE ROLE APP1_DEV ;
GRANT   APP1_RO to APP1_DEV ;
GRANT SELECT ON ALL TABLES IN SCHEMA APP1_DEV to APP1_dev_user
GRANT USAGE, CREATE ON SCHEMA APP1_DEV to APP1_DEV_USER
GRANT APP1_DEV  to APP1_DEV_USER ;

  4. Pour tester les privilèges, connectez-vous depuisAPP_dev_user.

    CREATE TABLE APP1_dev.test1( id integer ) ;
CREATE TABLE
INSERT into APP1_dev.test1 ( select * from APP1.test );
INSERT 0 1
CREATE TABLE APP1.test4 ( id int) ;
ERROR:   permission denied for schema APP1
LINE 1: create table APP1.test4 ( id int) ;
DBA

Ressources connexes

Documentation de PostgreSQL

 

Informations supplémentaires

Amélioration de PostgreSQL 14

PostgreSQL 14 fournit un ensemble de rôles prédéfinis qui donnent accès à certaines fonctionnalités et informations privilégiées couramment nécessaires. Les administrateurs (y compris les rôles dotés de CREATE ROLE privilèges) peuvent accorder ces rôles ou d'autres rôles dans leur environnement aux utilisateurs, en leur donnant accès aux fonctionnalités et informations spécifiées.

Les administrateurs peuvent autoriser les utilisateurs à accéder à ces rôles à l'aide de la GRANT commande. Par exemple, pour attribuer le pg_signal_backend rôle àAdmin_User, vous pouvez exécuter la commande suivante.

GRANT pg_signal_backend TO Admin_User;

Le pg_signal_backend rôle est destiné à permettre aux administrateurs d'activer des rôles fiables, non superutilisateurs, pour envoyer des signaux à d'autres backends. Pour plus d'informations, consultez la section Amélioration de PostgreSQL 14.

Ajustement précis de l'accès

Dans certains cas, il peut être nécessaire de fournir un accès plus granulaire aux utilisateurs (par exemple, un accès basé sur des tables ou un accès basé sur des colonnes). Dans de tels cas, des rôles supplémentaires peuvent être créés pour accorder ces privilèges aux utilisateurs. Pour plus d'informations, consultez la section Subventions PostgreSQL.