Managing PostgreSQL users and roles

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:

CREATE USER myuser WITH PASSWORD 'secret_passwd';

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

CREATE ROLE myuser WITH LOGIN PASSWORD 'secret_passwd';

 
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:

postgres=# show search_path;
   search_path  
-----------------
 "$user", public
(1 row)

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:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

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:

REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

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:

CREATE ROLE readonly;

 
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”:

GRANT CONNECT ON DATABASE mydatabase TO readonly;

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