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.
CREATE GROUPare aliases for the
CREATE ROLEstatement. A user is just a role with
CREATE USER = CREATE ROLE + LOGIN privilege
PostgreSQL has a
publicbackend role, and every new user and role inherits permissions from it.
When a new database is created, PostgreSQL creates a schema called
publicand grants access on this schema to the
publicbackend role. Mmm... too many things called public here, don't you think?
Because of this default behavior, any user can connect to new databases and create objects in each database's
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
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
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 :)If you found it useful, coffee is always appreciated. Thanks in advance!