Creating PostgreSQL DB for Python Or Other Backend Apps

I’ve been trying to catch onto the “use PostgreSQL for everything” trend. So here’s a personal reference note on how to create a new database along with a user, assign the necessary persmissions, and link it up to your app.

Step 1: Run psql as the postgresuser

$ sudo -u postgres psql

Step 2: Create Database

postgres=# CREATE DATABASE yourDB;

Step 3: Create A User With Password

postgres=# CREATE USER yourUserName WITH ENCRYPTED PASSWORD 'yourPassword';

Step 4: Give User Privileges On Database

postgres=# GRANT ALL PRIVILEGES ON DATABASE yourDB TO yourUserName;

Step 5: Give User Schema Privileges

One extra step to let your app create tables, columns, etc.

postgres=# \c yourDB;
yourDB=# GRANT ALL ON SCHEMA public TO yourUserName;

Step 6: Use the URL:

postgresql://yourUserName:yourPassword@localhost/yourDB