Controlling user access to the PostgreSQL database
In PostgreSQL version 14 and earlier, new databases are created with a default set of
privileges in the database's public schema that allow all database users and
roles to create objects. These privileges allow database users to connect to the database, for
example, and create temporary tables while connected.
Starting with PostgreSQL version 15, the CREATE privilege on the
public schema is no longer granted to PUBLIC by default, and the
public schema is owned by the pg_database_owner role. If
you're using PostgreSQL 15 or later, database users can no longer create objects in the
public schema unless you explicitly grant them the CREATE
privilege. For more information, see the PostgreSQL 15 release notes.
To better control user access to the databases instances that you create on your Aurora PostgreSQL DB cluster primary node
, we recommend that
you revoke these default public privileges. After doing so, you then grant
specific privileges for database users on a more granular basis, as shown in the following
procedure.
To set up roles and privileges for a new database instance
Suppose you're setting up a database on a newly created Aurora PostgreSQL DB cluster for use by several researchers, all of whom need read-write access to the database.
-
Use
psql(or pgAdmin) to connect to the primary DB instance on your Aurora PostgreSQL DB cluster:psql --host=your-cluster-instance-1.666666666666.aws-region.rds.amazonaws.com --port=5432 --username=postgres --passwordWhen prompted, enter your password. The
psqlclient connects and displays the default administrative connection database,postgres=>, as the prompt. -
To prevent database users from creating objects in the
publicschema, do the following:postgres=>REVOKE CREATE ON SCHEMA public FROM PUBLIC;REVOKE -
Next, you create a new database instance:
postgres=>CREATE DATABASElab_db;CREATE DATABASE -
Revoke all privileges from the
PUBLICschema on this new database.postgres=>REVOKE ALL ON DATABASElab_dbFROM public;REVOKE -
Create a role for database users.
postgres=>CREATE ROLElab_tech;CREATE ROLE -
Give database users that have this role the ability to connect to the database.
postgres=>GRANT CONNECT ON DATABASElab_dbTOlab_tech;GRANT -
Grant all users with the
lab_techrole all privileges on this database.postgres=>GRANT ALL PRIVILEGES ON DATABASElab_dbTOlab_tech;GRANT -
Create database users, as follows:
postgres=>CREATE ROLE lab_user1 LOGIN PASSWORD 'change_me';CREATE ROLEpostgres=>CREATE ROLE lab_user2 LOGIN PASSWORD 'change_me';CREATE ROLE -
Grant these two users the privileges associated with the lab_tech role:
postgres=>GRANT lab_tech TO lab_user1;GRANT ROLEpostgres=>GRANT lab_tech TO lab_user2;GRANT ROLE
At this point, lab_user1 and lab_user2 can connect to the
lab_db database. This example doesn't follow best practices for enterprise
usage, which might include creating multiple database instances, different schemas, and
granting limited permissions. For more complete information and additional scenarios, see
Managing PostgreSQL
Users and Roles
For more information about privileges in PostgreSQL databases, see the GRANT