Postgres Create Database Without Public Schema

PostgreSQL is one of the most popular open-source relational database systems. With more than 30 years of development work, PostgreSQL has proven to be a highly reliable and robust database that can handle a large number of complicated data workloads. PostgreSQL is considered to be the primary open-source database choice when migrating from commercial databases such as Oracle. Amazon Web Services (AWS) provides two managed PostgreSQL options: Amazon Relational Database Service (Amazon RDS) for PostgreSQL and Amazon Aurora PostgreSQL. In this post, I talk about some of the best practices for managing users and roles in PostgreSQL.

With PostgreSQL, you can create users and roles with granular access permissions. The new user or role must be selectively granted the required permissions for each database object. This gives a lot of power to the end user, but at the same time, it makes the process of creating users and roles with the correct permissions potentially complicated.

PostgreSQL lets you grant permissions directly to the database users. However, as a good practice, it is recommended that you create multiple roles with specific sets of permissions based on application and access requirements. Then assign the appropriate role to each user. The roles should be used to enforce a least privilege model for accessing database objects. The master user that is created during Amazon RDS and Aurora PostgreSQL instance creation should be used only for database administration tasks like creating other users, roles, and databases. The master user should never be used by the application.

The recommended approach for setting up fine-grained access control in PostgreSQL is as follows:

Use the master user to create roles per application or use case, like readonly and readwrite.
Add permissions to allow these roles to access various database objects. For example, the readonly role can only run SELECT queries.
Grant the roles the least possible permissions required for the functionality.
Create new users for each application or distinct functionality, like app_user and reporting_user.
Assign the applicable roles to these users to quickly grant them the same permissions as the role. For example, grant the readwrite role to app_user and grant the readonly role to reporting_user.
At any time, you can remove the role from the user in order to revoke the permissions.

The following diagram summarizes these recommendations:

The following sections discuss these steps in detail. You can connect to the RDS endpoint for your PostgreSQL database using a client such as psql and run the SQL statements.

Users, groups, and roles

Users, groups, and roles are the same thing in PostgreSQL, with the only difference being that users have permission to log in by default. The CREATE USER and CREATE GROUP statements are actually aliases for the CREATE ROLE statement.

In other relational database management systems (RDBMS) like Oracle, users and roles are two different entities. In Oracle, a role cannot be used to log in to the database. The roles are used only to group grants and other roles. This role can then be assigned to one or more users to grant them all the permissions. For more details with a focus on how to migrate users, roles, and grants from Oracle to PostgreSQL, see the AWS blog post Use SQL to map users, roles, and grants from Oracle to PostgreSQL.

To create a PostgreSQL user, use the following SQL statement:

You can also create a user with the following SQL statement:

Both of these statements create the exact same user. This new user does not have any permissions other than the default permissions available to the public role. All new users and roles inherit permissions from the public role. The following section provides more details about the public role.

Public schema and public role

When a new database is created, PostgreSQL by default creates a schema named public and grants access on this schema to a backend role named public. All new users and roles are by default granted this public role, and therefore can create objects in the public schema.

PostgreSQL uses a concept of a search path. The search path is a list of schema names that PostgreSQL checks when you don't use a qualified name of the database object. For example, when you select from a table named "mytable", PostgreSQL looks for this table in the schemas listed in the search path. It chooses the first match it finds. By default, the search path contains the following schemas:

The first name "$user" resolves to the name of the currently logged in user. By default, no schema with the same name as the user name exists. So the public schema becomes the default schema whenever an unqualified object name is used. Because of this, when a user tries to create a new table without specifying the schema name, the table gets created in the public schema. As mentioned earlier, by default, all users have access to create objects in the public schema, and therefore the table is created successfully.

This becomes a problem if you are trying to create a read-only user. Even if you restrict all privileges, the permissions inherited via the public role allow the user to create objects in the public schema.

To fix this, you should revoke the default create permission on the public schema from the public role using the following SQL statement:

Make sure that you are the owner of the public schema or are part of a role that allows you to run this SQL statement.

The following statement revokes the public role's ability to connect to the database:

This makes sure that users can't connect to the database by default unless this permission is explicitly granted.

Revoking permissions from the public role impacts all existing users and roles. Any users and roles that should be able to connect to the database or create objects in the public schema should be granted the permissions explicitly before revoking any permissions from the public role in the production environment.

Creating database roles

The following sections document the process of creating new roles and granting them permissions to access various database objects. Permissions must be granted at the database, schema, and schema object level. For example, if you need to grant access to a table, you must also make sure that the role has access to the database and schema in which the table exists. If any of the permissions are missing, the role cannot access the table.

Read-only role

The first step is to create a new role named readonly using the following SQL statement:

This is a base role with no permissions and no password. It cannot be used to log in to the database.

Grant this role permission to connect to your target database named "mydatabase":

The next step is to grant this role usage access to your schema. Let's assume the schema is named myschema:

This step grants the readonly role permission to perform some activity inside the schema. Without this step, the readonly role cannot perform any action on the objects in this schema, even if the permissions were granted for those objects.

The next step is to grant the readonly role access to run select on the required tables.

If the requirement is to grant access on all the tables and views in the schema, then you can use the following SQL:

The preceding SQL statement grants SELECT access to the readonly role on all the existing tables and views in the schema myschema. Note that any new tables that get added in the future will not be accessible by the readonly user. To help ensure that new tables and views are also accessible, run the following statement to grant permissions automatically:

Read/write role

The process of adding a read/write role is very similar to the read-only role process covered previously. The first step is creating a role:

Grant this role permission to connect to your target database:

Grant schema usage privilege:

If you want to allow this role to create new objects like tables in this schema, then use the following SQL instead of the one preceding:

The next step is to grant access to the tables. As mentioned in the previous section, the grant can be on individual tables or all tables in the schema. For individual tables, use the following SQL:

For all the tables and views in the schema, use the following SQL:

To automatically grant permissions on tables and views added in the future:

For read/write roles, there is normally a requirement to use sequences also. You can give selective access as follows:

You can also grant permission to all sequences using the following SQL statement:

To automatically grant permissions to sequences added in the future:

You can grant more or fewer permissions based on the requirements. The PostgreSQL GRANT command documentation provides more details about the objects on which permissions can be granted and the required SQL statements.

Creating database users

With the roles in place, the process of creating users is simplified. Just create the user and grant it one of the existing roles. Here are the SQL statements for this process:

This grants myuser1 the same permissions as the readonly role.

Similarly, you can grant read and write access to a user by granting the readwrite role. The PostgreSQL CREATE USER documentation contains more details about the parameters you can set while creating a user. For example, you can specify an expiry time for the user or allow the user to create databases.

Managing user passwords

After creating a user, you must provide these credentials to the application so that it can access the database. It is essential to make sure that these credentials are not hardcoded in the source code or placed in some shared configuration files as clear text. AWS provides a solution for this with AWS Secrets Manager. Using Secrets Manager, you can store the credentials and then use AWS Identity and Access Management (IAM) to allow only certain IAM users and roles to read the credentials. For the steps involved in this, see Creating and Managing Secrets with AWS Secrets Manager in the AWS Secrets Manager User Guide.

In addition to storing the credentials, a very useful feature that Secrets Manager provides is database user password rotation. You can use this feature to set up a policy to automatically change the password at a certain frequency. For details about how to set this up so that there is no downtime for the applications, see Rotating Your AWS Secrets Manager Secrets.

Amazon RDS and Amazon Aurora PostgreSQL provide a new restricted password management feature that is supported with PostgreSQL 10.6 and higher. Using a new parameter and a special role, you can limit database user password changes to members of this special role. By doing this, you enable greater control over password management on the client side (for example, expiry requirements and complexity requirements).

IAM database authentication

Amazon RDS and Aurora PostgreSQL have integrated with IAM so that you can authenticate to your DB instance using IAM database authentication. This feature is available for Amazon RDS PostgreSQL versions 9.5.14, 9.6.9 or higher, and version 10.4 or higher. For Aurora PostgreSQL, this feature is available for versions 9.6.9 or higher, and version 10.4 or higher. The key benefit of this feature is that you can use IAM to centrally manage access to your database resources instead of managing access individually on each DB instance. Using this method, the administrator can easily grant or revoke database access via an IAM policy.

After the IAM grants have been added, the user can request a temporary password using the AWS CLI and then connect to the DB using this temporary password. The following diagram shows this workflow.

For more information about this feature, see IAM Database Authentication for MySQL and PostgreSQL. The documentation also contains detailed steps to configure IAM DB authentication.

This method deals only with the authentication part. Granting permissions on various database objects is done within the database as explained in this post. For example, to grant this user readwrite access, run the following SQL statement:

Revoking or changing user permissions

Using the method documented previously, it becomes very easy to revoke privileges from a user. For example, you can remove the readwrite permission from myuser1 using the following SQL statement:

Similarly, you can grant a new role as follows:

Monitoring usage

You can monitor user activity by setting PostgreSQL logging parameters available in the RDS parameter groups. For example, you can set the log_connections and log_disconnections parameters to capture all new connections and disconnections. After setting these parameters in the parameter group, you will see the following messages in the log files:

If you require more detailed session or object-level custom auditing information, then you can use the pgAudit extension. The steps to configure pgAudit with Amazon RDS and Aurora PostgreSQL are available in Working with the pgaudit Extension in the Amazon RDS User Guide.

Increasing database logging does impact storage size, I/O use, and CPU use. Because of this, it is important that you test these changes before deploying them in production.

Checking the granted roles

You can use the following query to get a list of all the database users and roles along with a list of roles that have been granted to them:

Here is sample output from a test RDS instance:

Note that a user can be member of multiple roles with distinct or overlapping permissions. In this case, the user gets a sum of all the permissions.

You can also use the catalog table pg_roles to check attributes like password expiry date or number of parallel connections allowed.

Summary

In this post, I shared some best practices for managing users and roles in PostgreSQL. This post provides a basic framework that you can modify based on your application requirements and the principles of least privilege.

To summarize the concepts, I have provided the following reference SQL statements for implementing the users and roles using an example scenario:

A PostgreSQL database has been created with primary database named mydatabase.
A new schema has been created named myschema with multiple tables.
Two reporting users must be created with the permissions to read all tables in the schema myschema.
Two app users must be created with permissions to read and write to all tables in the schema myschema and also to create new tables.
The users should automatically get permissions on any new tables that are added in the future.

To implement this scenario, you must connect to the database mydatabase using the master user, and then run the following SQL statements using any of the PostgreSQL clients like psql or pgAdmin:

You can find more information about PostgreSQL users and roles on the PostgreSQL documentation website.

If you have any questions or comments about this blog post, feel free to use the comments section here to post your thoughts.

About the Author

Yaser Raja is a Senior Consultant with Professional Services team at Amazon Web Services. He works with customers to build scalable, highly available and secure solutions in AWS cloud. His focus area is homogenous and heterogeneous migrations of on-premise databases to AWS RDS and Aurora PostgreSQL.

Postgres Create Database Without Public Schema

Source: https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/