# Migrating security features to Aurora PostgreSQL Security This topic provides conceptual content comparing the security and encryption features of Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL. You can gain a comprehensive understanding of how encryption, user permissions, access control, and authentication mechanisms differ between these two database systems. The content explores encryption functions, data protection at rest, user management, and role-based access control, highlighting similarities and key differences in implementation. By understanding these concepts, you can make informed decisions about securing your data and implementing robust security measures when migrating from SQL Server to Aurora PostgreSQL. This knowledge is crucial for database administrators and developers to ensure a smooth transition while maintaining data integrity and compliance in the new database environment. **Topics** + [ # Column encryption for Aurora PostgreSQL ](chap-sql-server-aurora-pg.security.columnencryption.md) + [ # Data control language for Aurora PostgreSQL ](chap-sql-server-aurora-pg.security.datacontrollanguage.md) + [ # Transparent data encryption Aurora PostgreSQL ](chap-sql-server-aurora-pg.security.transparentdataencryption.md) + [ # Users and roles for Aurora PostgreSQL ](chap-sql-server-aurora-pg.security.usersroles.md) # Column encryption for Aurora PostgreSQL This topic provides reference information comparing encryption and decryption capabilities between Microsoft SQL Server 2019 and Amazon Aurora PostgreSQL. You can understand the encryption functions available in SQL Server and their counterparts in Aurora PostgreSQL. The topic highlights the similarities in functionality while noting the differences in syntax and options. It introduces the encryption hierarchy in SQL Server and the various encryption algorithms supported by Aurora PostgreSQL. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | N/A | N/A | Syntax and option differences, similar functionality. | ## SQL Server Usage SQL Server provides encryption and decryption functions to secure the content of individual columns. The following list identifies common encryption functions: + `EncryptByKey` and `DecryptByKey`. + `EncryptByCert` and `DecryptByCert`. + `EncryptByPassPhrase` and `DecryptByPassPhrase`. + `EncryptByAsymKey` and `DecryptByAsymKey`. You can use these functions anywhere in your code; they aren’t limited to encrypting table columns. A common use case is to increase run time security by encrypting of application user security tokens passed as parameters. These functions follow the general SQL Server encryption hierarchy, which in turn use the Windows Server Data Protection API. Symmetric encryption and decryption consume minimal resources. You can use them for large data sets. **Note** This section doesn’t cover Transparent Data Encryption (TDE) or Always Encrypted end-to-end encryption. ### Syntax General syntax for `EncryptByKey` and `DecryptByKey`: ``` EncryptByKey ( , { 'text to be encrypted' }, { }, { } ); ``` ``` DecryptByKey ( 'Encrypted Text' , , { ) ``` ### Examples The following examples demonstrate how to encrypt an employee Social Security Number. Create a database master key. ``` USE MyDatabase; CREATE MASTER KEY ENCRYPTION BY PASSWORD = ''; ``` Create a certificate and a key. ``` CREATE CERTIFICATE Cert01 WITH SUBJECT = 'SSN'; ``` ``` CREATE SYMMETRIC KEY SSN_Key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Cert01; ``` Create an `Employees` table. ``` CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, SSN_encrypted VARBINARY(128) NOT NULL ); ``` Open the symmetric key for encryption. ``` OPEN SYMMETRIC KEY SSN_Key DECRYPTION BY CERTIFICATE Cert01; ``` Insert the encrypted data. ``` INSERT INTO Employees (EmployeeID, SSN_encrypted) VALUES (1, EncryptByKey(Key_GUID('SSN_Key') , '1112223333', 1, HashBytes('SHA1', CONVERT(VARBINARY, 1))); ``` ``` SELECT EmployeeID, CONVERT(CHAR(10), DecryptByKey(SSN, 1 , HashBytes('SHA1', CONVERT(VARBINARY, EmployeeID)))) AS SSN FROM Employees; EmployeeID SSN_Encrypted SSN 1 0x00F983FF436E32418132... 1112223333 ``` For more information, see [Encrypt a Column of Data](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver15) and [Encryption Hierarchy](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encryption-hierarchy?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) provides encryption and decryption functions similar to SQL Server using the `pgcrypto` extension. To use this feature, you must first install the `pgcrypto` extension. ``` CREATE EXTENSION pgcrypto; ``` Aurora PostgreSQL supports many encryption algorithms: + MD5 + SHA1 + SHA224/256/384/512 + Blowfish + AES + Raw encryption + PGP Symmetric encryption + PGP Public-Key encryption This section describes the use of `PGP_SYM_ENCRYPT` and `PGP_SYM_DECRYPT`, but there are many more options available. For more information, see the link and the end of this section. ### Syntax Encrypt columns using `PGP_SYM_ENCRYPT`. ``` pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text ``` ### Examples The following examples demonstrate how to encrypt an employee’s Social Security Number. Create the `users` table. ``` CREATE TABLE users (id SERIAL, name VARCHAR(60), pass TEXT); ``` Insert the encrypted data. ``` INSERT INTO users (name, pass) VALUES ('John',PGP_SYM_ENCRYPT('123456', 'AES_KEY')); ``` Verify the data is encrypted. ``` SELECT * FROM users; id name pass 2 John \xc30d04070302c30d07ff8b3b12f26ad233015a72bab4d3bb73f5a80d5187b1b043149dd961da58e76440ca9eb4a5f7483cc8ce957b47e39b143cf4b1192bb39 ``` Query using the encryption key. ``` SELECT name, PGP_SYM_DECRYPT(pass::bytea, 'AES_KEY') as pass FROM users WHERE (name LIKE '%John%'); name pass John 123456 ``` Update the data. ``` UPDATE users SET (name, pass) = ('John',PGP_SYM_ENCRYPT('0000', 'AES_KEY')) WHERE id='2'; SELECT name, PGP_SYM_DECRYPT(pass::bytea, 'AES_KEY') as pass FROM users WHERE (name LIKE '%John%'); name pass John 0000 ``` For more information, see [pgcrypto](https://www.postgresql.org/docs/13/pgcrypto.html) in the *PostgreSQL documentation*. # Data control language for Aurora PostgreSQL This topic provides reference information about user permissions and access control in Amazon Aurora PostgreSQL, comparing it to Microsoft SQL Server. You can understand how Aurora PostgreSQL implements the ANSI standard for data control language commands, including GRANT and REVOKE. The topic explains the various permission levels available in Aurora PostgreSQL, from individual object permissions to schema-wide access. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Five star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-5.png) | N/A | N/A | Similar syntax and similar functionality. | ## SQL Server Usage The ANSI standard specifies, and most Relational Database Management Systems (RDBMS) use, `GRANT` and `REVOKE` commands to control permissions. However, SQL Server also provides a `DENY` command to explicitly restrict access to a resource. `DENY` takes precedence over GRANT and is needed to avoid potentially conflicting permissions for users having multiple logins. For example, if a user has DENY for a resource through group membership but GRANT access for a personal login, the user is denied access to that resource. In SQL Server, you can grant permissions at multiple levels from lower-level objects such as columns to higher-level objects such as servers. Permissions are categorized for specific services and features such as the service broker. You can use permissions in conjunction with database users and roles. For more information, see [Users and Roles](chap-sql-server-aurora-pg.security.usersroles.md). ### Syntax Simplified syntax for SQL Server DCL commands: ``` GRANT { ALL [ PRIVILEGES ] } | [ ON ] TO DENY { ALL [ PRIVILEGES ] } | [ ON ] TO REVOKE [ GRANT OPTION FOR ] {[ ALL [ PRIVILEGES ] ]|} [ ON ] { TO | FROM } ``` For more information, see [Permissions Hierarchy (Database Engine)](https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-hierarchy-database-engine?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) supports the ANSI Data Control Language (DCL) commands `GRANT` and `REVOKE`. Administrators can grant or revoke permissions for individual objects such as a column, a stored function, or a table. You can grant permissions to multiple objects using `ALL % IN SCHEMA`. In the example preceding, `%` can be `TABLES`, `SEQUENCES`, or `FUNCTIONS`. Use the following command to grant select on all tables in schema to a specific user. ``` GRANT SELECT ON ALL TABLES IN SCHEMA TO ; ``` Aurora PostgreSQL provides a `GRANT` permission option that is similar to SQL Server `WITH GRANT OPTION` clause. This permission grants a user permission to further grant the same permission to other users. ``` GRANT EXECUTE ON FUNCTION demo.Procedure1 TO UserY WITH GRANT OPTION; ``` The following table identifies Aurora PostgreSQL privileges. | Permissions | Use to | | --- | --- | | `SELECT` | Use to query rows from table. | | `INSERT` | Use to insert rows into a table. | | `UPDATE` | Use to update rows in table. | | `DELETE` | Use to delete rows from table. | | `TRUNCATE` | Use to truncate a table. | | `REFERENCES` | Use to create a foreign key constraint. | | `TRIGGER` | Use to create a trigger on the specified table. | | `CREATE` | The purpose of this permission depends on the target object. For more information, see [GRANT](https://www.postgresql.org/docs/13/sql-grant.html) in the *PostgreSQL documentation*. | | `CONNECT` | Use to connect to the specified database. | | `TEMPORARY` or `TEMP` | Use to create temporary tables. | | `EXECUTE` | Use to run a function. | | `USAGE` | The purpose of this permission depends on the target object. For more information, see [GRANT](https://www.postgresql.org/docs/13/sql-grant.html) in the *PostgreSQL documentation*. | | `ALL` or `ALL PRIVILEGES` | Grant all available privileges. | ### Syntax ``` GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] ) [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) } ON [ TABLE ] table_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { { USAGE | SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } ON { SEQUENCE sequence_name [, ...] | ALL SEQUENCES IN SCHEMA schema_name [, ...] } TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } ON DATABASE database_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON DOMAIN domain_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN DATA WRAPPER fdw_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON FOREIGN SERVER server_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [,...] | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE lang_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] } ON LARGE OBJECT loid [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] } ON SCHEMA schema_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespace_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] GRANT { USAGE | ALL [ PRIVILEGES ] } ON TYPE type_name [, ...] TO role_specification [, ...] [ WITH GRANT OPTION ] where role_specification can be: [ GROUP ] role_name | PUBLIC | CURRENT_USER | SESSION_USER GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ] ``` ### Examples Grant `SELECT` permission to a user on all tables in the demo database. ``` GRANT SELECT ON ALL TABLES IN SCHEMA emps TO John; ``` Revoke `EXECUTE` permissions from a user on the `EmployeeReport` stored procedure. ``` REVOKE EXECUTE ON FUNCTION EmployeeReport FROM John; ``` For more information, see [GRANT](https://www.postgresql.org/docs/13/sql-grant.html) in the *PostgreSQL documentation*. # Transparent data encryption Aurora PostgreSQL This topic provides reference information about data encryption capabilities in Microsoft SQL Server and Amazon Aurora PostgreSQL. You can understand how Transparent Data Encryption (TDE) works in SQL Server to protect data at rest, and how Aurora PostgreSQL offers similar functionality through Amazon RDS encryption. The topic explains the encryption mechanisms, key management, and limitations associated with these features. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Four star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-4.png) | N/A | N/A | Storage level encryption managed by Amazon RDS. | ## SQL Server Usage Transparent data encryption (TDE) is an SQL Server feature designed to protect data at rest in the event an attacker obtains the physical media containing database files. TDE doesn’t require application changes and is completely transparent to users. The storage engine encrypts and decrypts data on-the-fly. Data isn’t encrypted while in memory or on the network. You can turn TDE on or off individually for each database. TDE encryption uses a Database Encryption Key (DEK) stored in the database boot record, making it available during database recovery. The DEK is a symmetric key signed with a server certificate from the master system database. In many instances, security compliance laws require TDE for data at rest. ### Examples The following example demonstrates how to enable TDE for a database: Create a master key and certificate. ``` USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword'; CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate'; ``` Create a database encryption key. ``` USE MyDatabase; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE TDECert; ``` Enable TDE. ``` ALTER DATABASE MyDatabase SET ENCRYPTION ON; ``` For more information, see [Transparent data encryption (TDE)](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) provides the ability to encrypt data at rest (data stored in persistent storage) for new database instances. When data encryption is enabled, Amazon Relational Database Service (RDS) automatically encrypts the database server storage, automated backups, read replicas, and snapshots using the AES-256 encryption algorithm. You can manage the keys used for Amazon Relational Database Service (Amazon RDS) encrypted instances from the Identity and Access Management (IAM) console using the AWS Key Management Service (AWS KMS). If you require full control of a key, you must manage it yourself. You can’t delete, revoke, or rotate default keys provisioned by AWS KMS. The following limitations exist for Amazon RDS encrypted instances: + You can only enable encryption for an Amazon RDS database instance when you create it, not afterward. It is possible to encrypt an existing database by creating a snapshot of the database instance and then creating an encrypted copy of the snapshot. You can restore the database from the encrypted snapshot. For more information, see [Copying a snapshot](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CopySnapshot.html) in the *Amazon Relational Database Service User Guide*. + Encrypted database instances can’t be modified to disable encryption. + Encrypted Read Replicas must be encrypted with the same key as the source database instance. + An unencrypted backup or snapshot can’t be restored to an encrypted database instance. + KMS encryption keys are specific to the region where they are created. Copying an encrypted snapshot from one region to another requires the KMS key identifier of the destination region. **Note** Disabling the key for an encrypted database instance prevents reading from, or writing to, that instance. When Amazon RDS encounters a database instance encrypted by a key to which Amazon RDS doesn’t have access, it puts the database instance into a terminal state. In this state, the database instance is no longer available and the current state of the database can’t be recovered. To restore the database instance, you must re-enable access to the encryption key for Amazon RDS and then restore the database instance from a backup. ### Examples The following walkthrough demonstrates how to enable TDE. **Enable encryption** In the database settings, enable encryption and choose a master key. You can choose the default key provided for the account or define a specific key based on an IAM KMS ARN from your account or a different account. ![\[Enable encryption\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-enable-encryption.png) **Create an encryption key** To create your own key, browse to the Key Management Service (KMS), choose **Customer managed keys**, and create a new key. Choose the key type and the key material origin, and then choose **Next**. Create alias and description, and then choose **Next**. ![\[Create alias and description\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-alias-description.png) For **Define Key Administrative Permissions**, leave the default values and choose **Next**. Make sure that you assigned the key to the relevant users who will need to interact with Amazon Aurora. ![\[Key usage permissions\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-key-permissions.png) Review and edit the key policy, and then choose **Finish**. ![\[Key policy\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-key-policy.png) Now, you can set the master encryption key by using the ARN of the key that you have created or picking it from the list. ![\[Set master encryption key\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-sql-server-aurora-pg-set-master-encryption-key.png) Proceed to the finish and launch the instance. For more information, see [Specifying Amazon S3 encryption](https://docs.aws.amazon.com/AmazonS3/latest/userguide/specifying-s3-encryption.html) in the *Amazon Simple Storage Service User Guide* and [s3](https://docs.aws.amazon.com/cli/latest/reference/s3/) in the *Command Line Interface Command Reference*. # Users and roles for Aurora PostgreSQL This topic provides reference information about the security and authentication differences between Microsoft SQL Server and Amazon Aurora PostgreSQL. You can understand how user management, role-based access control, and authentication mechanisms differ between these two database systems. The topic explains the fundamental concepts of users, roles, and permissions in both SQL Server and PostgreSQL, highlighting the key differences in terminology and implementation. | Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences | | --- | --- | --- | --- | | ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/images/pb-compatibility-3.png) | N/A | N/A | Syntax and option differences, similar functionality. There are no users in PostgreSQL, only roles. | ## SQL Server Usage SQL Server provides two layers of security principals: logins at the server level and users at the database level. Logins are mapped to users in one or more databases. Administrators can grant logins server-level permissions that aren’t mapped to particular databases such as database creator, system administrator, and security administrator. SQL Server also supports roles for both the server and the database levels. At the database level, administrators can create custom roles in addition to the general purpose built-in roles. For each database, administrators can create users and associate them with logins. At the database level, the built-in roles include `db_owner`, `db_datareader`, `db_securityadmin`, and others. A database user can belong to one or more roles (users are assigned to the public role by default and can’t be removed). Administrators can grant permissions to roles and then assign individual users to the roles to simplify security management. Logins are authenticated using either Windows Authentication, which uses the Windows Server Active Directory framework for integrated single sign-on, or SQL authentication, which is managed by the SQL Server service and requires a password, certificate, or asymmetric key for identification. You can create logins that use Windows Authentication for individual users and domain groups. In previous versions of SQL server, the concepts of user and schema were interchangeable. For backward compatibility, each database has several existing schemas, including a default schema named dbo which is owned by the `db_owner` role. Logins with system administrator privileges are automatically mapped to the dbo user in each database. Typically, you don’t need to migrate these schemas. ### Examples Create a login. ``` CREATE LOGIN MyLogin WITH PASSWORD = 'MyPassword' ``` Create a database user for `MyLogin`. ``` USE MyDatabase; CREATE USER MyUser FOR LOGIN MyLogin; ``` Assign `MyLogin` to a server role. ``` ALTER SERVER ROLE dbcreator ADD MEMBER 'MyLogin' ``` Assign `MyUser` to the `db_datareader` role. ``` ALTER ROLE db_datareader ADD MEMBER 'MyUser'; ``` For more information, see [Database-level roles](https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15) in the *SQL Server documentation*. ## PostgreSQL Usage PostgreSQL supports only roles; there are no users. However, there is a `CREATE USER` command, which is an alias for `CREATE ROLE` that automatically includes the `LOGIN` permission. Roles are defined at the database cluster level and are valid in all databases in the PostgreSQL cluster. ### Syntax The following example shows a simplified syntax for `CREATE ROLE` in Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL). ``` CREATE ROLE name [ [ WITH ] option [ ... ] ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid ``` The `UNENCRYPTED PASSWORD` option was dropped in PostgreSQL 10, the password must be kept encrypted. ### Example Create a new database role called `hr_role`. Users can use this role to create new databases in the PostgreSQL cluster. Note that this role isn’t able to login to the database and act as a database user. In addition, grant `SELECT`, `INSERT`, and `DELETE` privileges on the `hr.employees` table to the role. ``` CREATE ROLE hr_role; GRANT SELECT, INSERT,DELETE on hr.employees to hr_role; ``` ## Summary The following table summarizes common security tasks and the differences between SQL Server and Aurora PostgreSQL. | Task | SQL Server | Aurora PostgreSQL | | --- | --- | --- | | View database users | `SELECT Name FROM sys.sysusers` | `SELECT * FROM pg_roles where rolcanlogin = true;` | | Create a user and password | `CREATE USER WITH PASSWORD = ;` | `CREATE USER WITH PASSWORD '';` | | Create a role | `CREATE ROLE ` | `CREATE ROLE ` | | Change a user’s password | `ALTER LOGIN WITH PASSWORD = ;` | `ALTER USER WITH PASSWORD '';` | | External authentication | Windows Authentication | N/A | | Add a user to a role | `ALTER ROLE ADD MEMBER ` | `ALTER ROLE SET ` | | Lock a user | `ALTER LOGIN DISABLE` | `REVOKE CONNECT ON DATABASE from ;` | | Grant `SELECT` on a schema | `GRANT SELECT ON SCHEMA:: to ` | `GRANT SELECT ON ALL TABLES IN SCHEMA TO ;` | For more information, see [CREATE ROLE](https://www.postgresql.org/docs/13/sql-createrole.html) in the *PostgreSQL documentation*.