Create Read-Only Access for AWS RDS Postgres

Hello, today I am going to write about one of the operations I’ve found to be a little more different on AWS than on a normal self-installed/hosted Postgres database. Apparently, AWS RDS by default gives some default privileges to the public schemas inside the database which stops us from creating the read-only access for custom users the usual way.

When you create a new database inside AWS RDS, the default schema named public will also be created which will grant access to a backend role that is also named public. This causes all new users and roles to have this public role granted to them by default. So if you create a new user named person he will have access to all databases since that’s his default privilege.

So as you would assume, person would inherit all the permissions and it wouldn’t be a read-only user even if we would restrict all privileges as he would still be able to create objects in the public schema. Luckily, this can be fixed by revoking and altering all the default settings. So let’s go step by step connected as a postgres user.

Revoke the default CREATE permission on the public schema from the public role:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Next is to revoke ALL privileges from the public role which will stop it from being able to connect to the database.

REVOKE ALL ON DATABASE "databasename" FROM PUBLIC;

Read-Only Role

So now that we changed some of the default behavior that is there by default, we can proceed to the next step which is creating a read-only role which we will grant to all the new users that will need read-only access in the future.

So let’s begin with the creation of the role:

CREATE ROLE readonly;

It’s important to mention that when you create a new role, it has no permissions by default and it can not be used to log in to the database. So we need to give it the power to do that:

GRANT CONNECT ON DATABASE databasename TO readonly;

Next, we need to give USAGE privilege to this readonly role we just made. Since our schema is usually called public by default we would run this command:

GRANT USAGE ON SCHEMA public TO readonly;

Now, ideally, this new role must have SELECT privileges which are in our case, for all the tables inside the public schema.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

In case you want to do it just for same tables and not all:

GRANT SELECT ON TABLE tablenameone, tablename2, tablename3 TO readonly;

The very important thing to mention is that all the new tables that get created in the future will still not be accessible by the readonly role. That’s why we need to alter those default privileges. We can do it like this:

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

After this logically you would just grant readonly role to your user or if the user doesn’t exist you could create the user first and then do the grant:

CREATE USER johndoe WITH PASSWORD 'unhackablepass123';

GRANT readonly TO johndoe;

Possible Additional Tweaks and Debugging

As you already know there is a default postges database every time you create the RDS instance. It’s recommended to forbid all users except postgres superuser to connect to postgres database. This can be done with this query:

REVOKE CONNECT ON DATABASE "postgres" FROM PUBLIC;

In case your postgres user won’t connect to this database later just do:

GRANT CONNECT ON DATABASE "postgres" TO postgres;

Additionally if your read only role and users can still create tables, do these commands as postgres user while connected to the database you are willing to change permissions for:

1. REVOKE ALL ON SCHEMA public FROM PUBLIC;

2. GRANT USAGE TO SCHEMA public TO readonly/username;

3. GRANT CREATE TO SCHEMA public TO readonly/username;

4. GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly/username;

IMPORTANT NOTICE: If you are doing all this on a running production, all these steps might cause downtime due to revoking privileges, especially if there are multiple databases connected to each other so make sure to announce short downtime during these queries executions.

Conclusion

While these tasks don’t seem to be that hard, they can cause serious issues in production. Make sure you are not clearing your history in the terminal so that you can have a quick look at what have you done.

Also, make sure to know which users are inside the database with which privileges and ownership of databases. You could simply take a screenshot to make sure that everything is looking great at the end.

For all additional questions, feel free to contact me.