PostgreSqL — Create User, Create Database, Grant privileges in Windows

follow the steps

Connect with Postgres user
psql -U postgres

Create the database with springbootapplication (name can be different)
CREATE DATABASE springbootapplication;

Create user with password
CREATE USER springboot WITH PASSWORD ‘springboot’;

GRANT ALL PRIVILEGES

GRANT ALL PRIVILEGES ON DATABASE springbootapplication to springboot;

when you got this error :-io.r2dbc.postgresql.ExceptionFactory$PostgresqlPermissionDeniedException: permission denied for table product

run this command

GRANT ALL PRIVILEGES ON TABLE table_name To user_name

example

GRANT ALL PRIVILEGES ON TABLE product TO springboot;

Connect to the database where you want to create a table. We will create a table in database springbootapplication

\c springbootapplication

Use command \d to check the list of relations (tables)

CREATE TABLE command

CREATE TABLE product(
id serial PRIMARY KEY,
description VARCHAR (50) ,
price double precision);

INSERT INTO product(description,price)
VALUES (‘book’ ,2330.23);

CREATE DATABASE springbootapplication;
CREATE USER springboot WITH PASSWORD ‘springboot’;
GRANT ALL PRIVILEGES ON DATABASE springbootapplication to springboot;
\c springbootapplication
INSERT INTO product(description,price) VALUES(‘book’,2500);
GRANT ALL PRIVILEGES ON TABLE product TO springboot;
#DROP DATABASE
DROP DATABASE springbootapplication;
#DROP user
DROP USER springboot

ERROR: permission denied for sequence product_pid_seq using Postgres

GRANT USAGE, SELECT ON SEQUENCE product_pid_seq TO springboot;

CREATE TABLE Product(
pid serial PRIMARY KEY,
productDescription VARCHAR (150),
productPrice double precision);

CREATE TABLE product(
id serial PRIMARY KEY,
description VARCHAR (50) ,
price double precision);

GRANT ALL PRIVILEGES ON TABLE product TO springboot;
SELECT * from Product

GRANT USAGE, SELECT ON SEQUENCE product_pid_seq TO springboot;

CREATE DATABASE school_service;

CREATE USER e*dm* WITH PASSWORD ‘e*dm*’;

GRANT ALL PRIVILEGES ON DATABASE school_service to e*dm*;

If anyone is not able to find the postgresql.conf file location in your setup, you can always ask the Postgres itself.

SHOW config_file;