PostgreSQL and how to shield your databases well

The recipe:

CREATE USER myuser with password 'secret_passwd' role postgres;
CREATE DATABASE mydatabase with owner myuser;
\connect mydatabase
REVOKE ALL ON DATABASE mydatabase FROM public;
REVOKE ALL ON SCHEMA public FROM public;    
ALTER SCHEMA public owner to myuser;
REVOKE myuser FROM postgres;

The deep dive

First things first

There are a few things you need to know to understand a bit about how PostgreSQL works.

1. CREATE USER and CREATE GROUP are aliases for the CREATE ROLE statement. A user is just a role with LOGIN privileges.

    CREATE USER = CREATE ROLE + LOGIN privilege
   

2. PostgreSQL has a public backend role, and every new user and role inherits permissions from it.

3. When a new database is created, PostgreSQL creates a schema called public and grants access on this schema to the public backend role. Mmm... too many things called public here, don't you think?

4. Because of this default behavior, any user can connect to new databases and create objects in each database's public schema.

Create the user

CREATE USER myuser with password 'secret_passwd' role postgres;

This statement will create a new user, and it'll add postgres role/user into myuser role. I know, syntax may look confusing.

Create the database

CREATE DATABASE mydatabase with owner myuser;

It creates a new database named mydatabase and makes myuser role the owner.

Securizes the database

\connect mydatabase
REVOKE ALL ON DATABASE mydatabase FROM public;
REVOKE ALL ON SCHEMA public FROM public;

Revokes all privileges on the new database and on the public schema to the public role. I mean, this revokes public access to the database.

Delegates ownership of public schema

ALTER SCHEMA public owner to myuser;

Now the user is the real owner.

The final part

Execute this query if you want to revoke access to this database even to postgres user. It's up to you.

REVOKE myuser FROM postgres;

Remember. After this, even though postgres user, which is the user with more RDS privileges, won't have access to that database. It'll only be accessible for myuser user.

In case you have to operate on it with postgres user, you'll need to grant access for it.

GRANT myuser TO postgres;

If not, you'll get a permission denied error.

If you have any question, doubt, or any improvement I could do, feel free to let me know in this Twitter thread :)

More info at

Managing PostgreSQL users and roles
Managing PostgreSQL Database Access
PostgreSQL GRANT Documentation
PostgreSQL REVOKE Documentation