Creating a Read-Only User in AWS PostgreSQL

by

Amazon Web Services (AWS) offers a robust managed database service known as Amazon RDS (Relational Database Service) that supports various database engines, including PostgreSQL. One common requirement for managing databases is the need to create users with specific roles and permissions. In this article, we’ll walk through the steps to create a read-only user in an AWS PostgreSQL database.

Why Create a Read-Only User?

Creating a read-only user can help enhance your database security and manageability. This type of user can:

  • Limit Data Exposure: Provide access to users who need to retrieve data but should not alter it.
  • Prevent Data Modifications: Reduce the risk of accidental deletions or updates that could compromise data integrity.
  • Segregate Duties: Facilitate data analysis and reporting tasks without overexposing sensitive write operations.

Prerequisites

  1. AWS Account: An active AWS account.
  2. Amazon RDS PostgreSQL Instance: You should have an RDS PostgreSQL instance running.
  3. PostgreSQL Client: Install a PostgreSQL client such as psql, PgAdmin, or use the AWS RDS Query Editor.
  4. Admin Access: Access to a user account with sufficient privileges to create new users and assign roles.

Step-by-Step Guide to Creating a Read-Only User

Step 1: Connect to Your RDS PostgreSQL Instance

You can connect to your RDS PostgreSQL instance using any PostgreSQL client. Here’s an example using the command line interface with psql:

psql --host=your-rds-endpoint.amazonaws.com --port=5432 --username=your-admin-username --dbname=your-database-name

You will be prompted for your password. Once you enter it, you will be connected to your PostgreSQL database.

Step 2: Create the Read-Only User

To create a new user, you need to execute the CREATE USER command. Here’s how to create a user named readonly_user with the password password123:

CREATE USER readonly_user WITH PASSWORD 'password123';

Step 3: Grant Read-Only Permissions

After creating the user, you need to grant it permission to access the database without the ability to modify data. You can do this by using the GRANT command. For instance, to grant readonly_user SELECT permissions on all tables in a specific schema (commonly public), use the following commands:

GRANT CONNECT ON DATABASE your_database_name TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Step 4: Set Default Privileges for Future Tables

If you want the readonly_user to automatically have access to any tables created in the future, you can set default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

Step 5: Confirm Permissions

It’s always good practice to verify the permissions granted to the new user. You can check the privileges assigned to readonly_user with the following query:

\dp

This command lists the access privileges of all tables in the current database. Ensure that readonly_user has SELECT permissions.

Step 6: Test the Read-Only User

Finally, log out of the current session and reconnect using the readonly_user credentials to confirm everything works as expected. Use the command:

psql --host=your-rds-endpoint.amazonaws.com --port=5432 --username=readonly_user --dbname=your-database-name

Attempt to run a SELECT query:

SELECT * FROM your_table_name;

Then try to perform an INSERT or UPDATE operation to confirm that the user does not have write permissions:

INSERT INTO your_table_name (column1) VALUES ('test');

This operation should return an error indicating insufficient privileges.

Conclusion

Creating a read-only user in an Amazon RDS PostgreSQL instance is a straightforward process that enhances database security and management. By following the steps outlined in this article, you can set up a user who has the ability to query data without risking changes to your database, making it easier to manage who can access sensitive information.

By implementing this practice, you streamline your database operations, maintain data integrity, and establish a clearer separation of duties among users. Always remember to tailor user permissions based on the specific requirements and security policies of your organization.

Leave a Comment