postgres is a root user of postgresql database engine by default. If we want to create another user, first we need to login as postgres root user. The second step is to create any new user we want. In Postgresql each individual database user must has corresponding database that its name must match with the user. Example: if the database username is jack, database named jack must be created and then to be linked to user jack. The last step is to grant several access to that user.
What happens if the postgres root user want to let the other users to access and do CRUD operations on his database and tables ? The following steps below will explain to you the process.
On the pi user prompt, type sudo su postgres followed by ENTER. It means we switch from pi user to postgres database user.
Your prompt changes from pi@kiwisoft to postgres@kiwisoft currently on pi user home folder /home/pi$. To run the postgresql, type psql at the prompt followed by ENTER.
postgres=# user database prompt appears.
Type \l followed by ENTER, to show the available databases.
You can see kiwisoft database from our previous lesson INSTALLING POSTGRESQL ON RASPBERRY OS BOOKWORM still there. We are going to use this database to run our demo.
Type \c kiwisoft followed by ENTER, to connect postgres user to this database.
The system will inform your connection is succesful.
Copy and paste the following below sql script to create settings table on the postgres database prompt.
CREATE TABLE settings (
name VARCHAR(50) PRIMARY KEY,
value VARCHAR(50)
);hhh
hh
s
GRANT ALL PRIVILEGES ON DATABASE kiwisoft TO pi;
GRANT ALL ON SCHEMA public TO pi;
GRANT SELECT, UPDATE, INSERT, DELETE ON settings TO pi;
hh
hh
hh
hh
hh
hh
John Hadikusumo 