Create a new user and database in PostgreSQL
I have an AWS PostgreSQL instance serving a small web app. When building a new application, I wanted to reuse the existing instance to reduce costs. While I could share the same credentials across both applications, I decided to isolate them for security by creating a dedicated user and database. Here are the 2 methods I used:
1. Using Terminal
- Connect to database
template1:
# psql -d template1 -U postgres
- Add a user called
tom:
template1=# CREATE USER tom WITH PASSWORD 'myPassword';
Tip: Generate a random password with Node.js in the terminal:
node -e "console.log(crypto.randomBytes(16).toString('base64').replace(/\W/g, ''))"
- Add a database called
jerry_db:
template1=# CREATE DATABASE jerry_db;
- Now grant all privileges on the database:
template1=# GRANT ALL PRIVILEGES ON DATABASE jerry_db to tom;
- To prevent other roles from creating objects in the
publicschema:
REVOKE ALL ON schema public FROM public;
- In practice, you will need to grant all privileges on the
publicschema to the new user:
GRANT ALL ON schema public TO tom;
- Type
\qto quit:
template1=# \q
2. Using DBeaver GUI
- Create a new role with
Is User,Inherit, andCan Login:
CREATE ROLE "duke2" WITH
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
INHERIT
LOGIN
NOREPLICATION
NOBYPASSRLS
CONNECTION LIMIT -1;
- Create a new database and set its owner to the new user.
