Community Contributing Handbook PostgreSQL

PostgreSQL

Setting up PostgreSQL for local development

NOTE: Work in progress.

Install PostgreSQL and pgAdmin4

Follow the instructions on https://www.postgresql.org/download/

Set up database

Open pgAdmin 4. We will be working on the PostgreSQL 13 server.

Right click on Databases and select Create.
Create new database
Fill inn database name eventsdb and click Save.
Configure new database
You should now be able to see a new database in the list. If you don’t - right click the server and select REFRESH.
Confirm new database

Set up admin user

Right click on Login/Group Roles , select Create and Login/Group Roles.
Step 1
In the General tab fill out the username platform_events_admin
Step 2
Navigate to the Definition tab and insert password: Password
Step 3
Navigate to the Priviliges tab and enable all options and click Save.
Step 4
Confirm that the user has been created in the side bar. If you cannot see the user, try refreshing the Login/Group Roles section.
Step 5

Set up app user

Right click on Login/Group Roles , select Create and Login/Group Roles.
Step 1
In the General tab fill out the username platform_events
Step 2
Navigate to the Definition tab and insert password: Password
Step 3
Navigate to the Priviliges tab and enable Can log in and click Save.
Step 4
Confirm that the user has been created in the side bar. If you cannot see the user, try refreshing the Login/Group Roles section.
Step 5
Create a schema called events. [A more detailed description to come..]
Right click the database in the side menu and select Query tool…
Step 6
Run script grating user privilliges on schema resources.

GRANT  USAGE  ON SCHEMA events TO platform_events;
GRANT SELECT,INSERT,UPDATE,REFERENCES,DELETE,TRUNCATE,REFERENCES,TRIGGER ON ALL TABLES IN SCHEMA events TO platform_events;
GRANT ALL ON ALL SEQUENCES IN SCHEMA events TO platform_events;

The result should be as shown in the picture below.