Help Center/
Relational Database Service_RDS for PostgreSQL/
Best Practices/
Creating a Read-Only User
Updated on 2025-09-09 GMT+08:00
Creating a Read-Only User
RDS for PostgreSQL does not support the syntax for creating read-only users. You can create read-only users using user permissions and roles. This topic describes how to create a read-only user.
Procedure
- Create a user whose username is readonly and password is readonlypasswd.
CREATE USER readonly WITH ENCRYPTED PASSWORD 'readonlypasswd';
- Run the following SQL statement as user root to set the default transaction for user readonly to read-only:
ALTER USER readonly SET default_transaction_read_only=on;
- Run the following SQL statement as user root to grant the USAGE permission on a schema (for example, public) to user readonly:
GRANT USAGE ON SCHEMA public to readonly;
- Run the following SQL statement as user root to grant the SELECT permission on all tables in public to user readonly:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
- Ensure that the SELECT permission on new tables in the public schema is granted to user readonly by default.
Whatever user is used to execute the following statement, the SELECT permission on the new tables created by the user is granted to readonly by default.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly ;
- To set a read-only user (for example, readonly) for a database, repeat 3 to 5 for all schemas in the database.
- To set a global read-only user (for example, readonly) for an instance, connect to each database and perform 6.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot