[PostgreSQL] CRUD

### データ挿入
insert into department (department_code, department_name) values (‘a’, ‘営業部’);
insert into department (department_code, department_name) values (‘b’, ‘総務部’);
insert into department values (‘c’, ‘製造部’);
insert into department (department_code, department_name) values (‘d’, ‘経理部’);
insert into department (department_code, department_name) values (‘e’, ‘人事部’);
insert into department (department_code, department_name) values (‘f’, ‘物流部’);

### データ取得
select * from department;
select department_code, department_name from department;
select department_code as “部署コード”, department_name as “部署名” from department;
select department_code, department_name from department where department_code = ‘b’;

### データ更新
update department set department_name = ‘hoge’ where department_code = ‘a’;

### データ削除
delete from department;
delete from department where department_code = ‘c’;
delete from department where department_code in(‘a’, ‘c’, ‘e’);

[PostgreSQL] 基礎編

PostgreSQLの他にも、Oracle Database, SQL Server, MySQLなどがある
ライセンスが無料、PostgreSQL Global Development Groupが開発を行なっている
参考になる情報がそこまで多くない

### PostgreSQL接続
$ psql –version
psql (PostgreSQL) 14.2 (Ubuntu 14.2-1.pgdg20.04+1)
$ sudo -u postgres psql

### データベース作成、移動
postgres=# create database testdb;
CREATE DATABASE

postgres=# \c testdb;
You are now connected to database “testdb” as user “postgres”.
testdb=#

### テーブル作成
CREATE TABLE department(
department_code character varying(10) NOT NULL,
department_name character varying(100),
CONSTRAINT pk_department PRIMARY KEY(department_code)
);

– テーブル一覧
testdb=# \dt;
List of relations
Schema | Name | Type | Owner
——–+————+——-+———-
public | department | table | postgres

– テーブルの中身
testdb=# \d department;
Table “public.department”
Column | Type | Collation | Nullable | Default
—————–+————————+———–+———-+———
department_code | character varying(10) | | not null |
department_name | character varying(100) | | |
Indexes:
“pk_department” PRIMARY KEY, btree (department_code)

コマンドは違うが、MySQLと基本的な考え方はある程度同じっぽいな

[PostgreSQL] スキーマとは

PostgreSQLのスキーマとは?
– 1つのデータベースの中に複数設定することができる名前空間のこと
– テーブルやビュー、インデックスなどはスキーマの下に配置される
Database -> schema -> table, view, sequence, index

CREATE DATABASEをすると、6つのスキーマが作成される
– information_schema: データベースのメタデータを取得するために利用されるビュー
– pg_catalog: PostgreSQLがシステム的に備えているオブジェクトを格納しているスキーマ
– pg_toast: PostgreSQLで可変長データ型のデータを扱えるようにToastの仕組みを実行するスキーマ
– pg_temp_${backendID}: 一時テーブル用のスキーマ
– public: 標準で使用されるスキーマ

CREATE SCHEMA test_schema;
CREATE SCHEMA test_schema CASCADE;

なるほどー

pgAdminとは?

pgAdminとは?
-> PostgreSQLの管理ツールの一つで、ネットワークを通じて遠隔のデータベースサーバをGUIで管理することができる

### Ubuntu20.04にpgadminのインストール
0. 既にpsql v14が入っている状態
$ psql -V
psql (PostgreSQL) 14.2 (Ubuntu 14.2-1.pgdg20.04+1)

1. リポジトリの設定
$ sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
$ sudo sh -c ‘echo “deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main” > /etc/apt/sources.list.d/pgadmin4.list && apt update’

2. pgAdminインストール
$ sudo apt install pgadmin4

3. メールアドレスとパスワードの登録
$ sudo /usr/pgadmin4/bin/setup-web.sh

4. プラウザから起動
http://192.168.56.10/pgadmin4

なるほどー、少し理解した。

ubuntuにold versionのpsqlをインストールして動かす

focalfossaにpsql8.4を入れて、laravel5.7から接続する

$ sudo apt-get install wget ca-certificates
$ wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
$ sudo sh -c ‘echo “deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main” >> /etc/apt/sources.list.d/pgdg.list’
$ sudo apt-get update
$ sudo apt –fix-broken install -o Dpkg::Options::=”–force-overwrite”
$ sudo apt-get install postgresql-8.4

$ psql –version
psql (PostgreSQL) 8.4.22

$ sudo apt install -y php
$ sudo apt-get install php7.4-dom php-pgsql
$ curl -sS https://getcomposer.org/installer | php
$ php composer.phar create-project –prefer-dist laravel/laravel blog “5.7.*”

.env

DB_CONNECTION=psql
DB_HOST=127.0.0.1
DB_PORT=5432
DB_DATABASE=test
DB_USERNAME=root
DB_PASSWORD=password

postgres-# sudo -u postgres psql
postgres-# create role root with createdb createrole login SUPERUSER password ‘password’;
postgres-# create database test

\c test

$ psql –version
psql (PostgreSQL) 8.4.22
$ php artisan -V
Laravel Framework 5.7.29

test=# \d
List of relations
Schema | Name | Type | Owner
——–+——————-+———-+——-
public | migrations | table | root
public | migrations_id_seq | sequence | root
public | password_resets | table | root
public | users | table | root
public | users_id_seq | sequence | root
(5 rows)

イレギュラーですな

[AWS RDS] Postgresを作成し、pg_dumpでbackupを取得する

### 前準備
VPC, subnetを作って、EC2の作成

### DB用のネットワーク作成
– Inbound rouleでPostgresとして、セキュリティグループはec2のセキュリティグループにする
 L これにより、ec2しかアクセスできないようになる

– EC2のVPCでpostgres用のsubnetを作成
 L 192.168.x.0/28とする

### RDSでpostgresの作成
– create database, postgres 13.3-R1
– db2.t.micro
– 作成に2~3分かかる。出来たら、endpointをメモ(hoge.fuga.ap-northeast-1.rds.amazonaws.com)

### EC2でpostgresのインストール
$ sudo yum update
$ sudo yum install -y postgresql.x86_64
// 接続
$ psql -h hoge.fuga.ap-northeast-1.rds.amazonaws.com -U root -d postgres
postgres=>

CREATE TABLE playground (
equip_id serial PRIMARY KEY,
type varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar(25) check (location in (‘north’, ‘south’, ‘west’, ‘east’, ‘northeast’, ‘southeast’, ‘southwest’, ‘northwest’)),
install_date date
);

INSERT INTO playground (type, color, location, install_date) VALUES (‘slide’, ‘blue’, ‘south’, ‘2022-04-28’);
INSERT INTO playground (type, color, location, install_date) VALUES (‘swing’, ‘yellow’, ‘northwest’, ‘2022-08-16’);

postgres=> SELECT * FROM playground;
equip_id | type | color | location | install_date
———-+——-+——–+———–+————–
1 | slide | blue | south | 2022-04-28
2 | swing | yellow | northwest | 2022-08-16

### バックアップの取得
$ sudo pg_dump -U root -h hoge.fuga.ap-northeast-1.rds.amazonaws.com -p 5432 postgres -f /home/ec2-user/test.sql
パスワード:
pg_dump: サーババージョン: 13.3、pg_dump バージョン: 9.2.24
pg_dump: サーババージョンの不整合のため処理を中断しています

なんやと! EC2のpostgresのバージョンをRDSのバージョンと合わせないといけないらしい

$ sudo yum install gcc
$ sudo yum install readline-devel
$ sudo yum install zlib-devel
$ cd /usr/local/src
$ sudo wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz
$ sudo tar -xvzf postgresql-13.3.tar.gz
$ cd postgresql-13.3
$ ./configure –prefix=/usr/local/postgresql-13.3/ –with-pgport=5432
$ make
$ sudo make install

再度バックアップの取得
$ /usr/local/postgresql-13.3/bin/pg_dump -U root -h hoge.fuga.ap-northeast-1.rds.amazonaws.com -p 5432 postgres -f /home/ec2-user/test.sql
$ cd /home/ec2-user/
$ ls
test.sql

うおおおおおおおおおおおおおおおおおおお
すげえ感動した

Postgresのデータバックアップとリストア

### tmpフォルダにバックアップ
pg_dump -U ユーザー名 –format=出力形式 –file=出力先 バックアップを取るDB名 と書く

$ sudo -i -u postgres
$ pg_dump –format=p –file=/tmp/test.sql postgres
$ exit
$ cd /tmp
$ ls

### restore
$ psql -f test.sql
$ psql
postgres=# \dt;

OK これをrdsから取得したい

RDSの場合は
$ pg_dump -U USER_NAME -h HOST_NAME -p port DATABASE_NAME -f FILE_NAME

なるほど、ほぼ一緒か。
RDSでpostgresを作成するところからかな。

Ubuntu20.04にPostgreSQLをinstall

### postgresql, pgadminのinstall
$ sudo apt-get install curl ca-certificates gnupg
$ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
$ sudo sh -c ‘echo “deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main” > /etc/apt/sources.list.d/pgdg.list’
$ sudo apt update

$ sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
$ sudo sh -c ‘echo “deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main” > /etc/apt/sources.list.d/pgadmin4.list && apt update’

$ sudo apt install postgresql-11 pgadmin4
$ psql -V
psql (PostgreSQL) 14.0 (Ubuntu 14.0-1.pgdg20.04+1)

### postアカウント
$ sudo -i -u postgres
$ psql
// 操作終了
postgres=# \q

// アカウントの切り替えなし
$ sudo -u postgres psql
// パスワード変更
$ ALTER USER postgres PASSWORD ‘hoge’;

### user作成
$ sudo -i -u postgres
$ createuser –interactive

### create table

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

postgres=# \d
List of relations
Schema | Name | Type | Owner
——–+————————-+———-+———-
public | playground | table | postgres
public | playground_equip_id_seq | sequence | postgres
(2 rows)

シーケンスなしの場合
postgres=# \dt

### insert
$ INSERT INTO playground (type, color, location, install_date) VALUES (‘slide’, ‘blue’, ‘south’, ‘2022-04-28’);
$ INSERT INTO playground (type, color, location, install_date) VALUES (‘swing’, ‘yellow’, ‘northwest’, ‘2022-08-16’);

$ SELECT * FROM playground;

### update
$ UPDATE playground SET color = ‘red’ WHERE type = ‘swing’;
$ DELETE FROM playground WHERE type = ‘slide’;

次はpostgresのデータバックアップとリストアの方法

[SpringBoot2.4.3] DB認証によるログイン処理を実装したい

WebSecurityConfig.java

package com.example.demo;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.config.annotation.authentication.builders.AuthenticationManagerBuilder;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.builders.WebSecurity;
import org.springframework.security.config.annotation.web.configuration.EnableWebSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.crypto.password.NoOpPasswordEncoder;
import org.springframework.security.web.util.matcher.AntPathRequestMatcher;

import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.security.crypto.password.PasswordEncoder;

@Configuration
@EnableWebSecurity
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {
	
	@Bean
	PasswordEncoder passwordEncoder() {
		return NoOpPasswordEncoder.getInstance();
	}
	
	@Override
	public void configure(WebSecurity web) throws Exception {
		web.ignoring().antMatchers("/css/**", "/resources/**");
	}
	
	@Override
	protected void configure(HttpSecurity http) throws Exception {
		http
			.authorizeRequests()
				.antMatchers("/", "/home").permitAll()
				.anyRequest().authenticated()
				.and()
			.formLogin()
				.loginPage("/login")
				.loginProcessingUrl("/login")
				.usernameParameter("username")
				.passwordParameter("password")
				.successForwardUrl("/home")
				.failureUrl("/login?error")
				.permitAll()
				.and()
			.logout()
				.logoutUrl("/logout")
				.permitAll()
				.logoutRequestMatcher(new AntPathRequestMatcher("/logout"));
	}
}

DemoController.java
L UserModelは後から作ります

package com.example.demo;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.security.core.Authentication;

@Controller
public class DemoController {
	@RequestMapping(value = {"/", "/home"})
	public String home() {
		return "home";
	}
	
	@RequestMapping(value = "/hello")
	public String hello(Authentication authentication, Model model) {
		UserModel userModel = (UserModel)authentication.getPrincipal();
		model.addAttribute("name", userModel.getUsername());
		
		return "hello";
	}
}

pom.xml
L jdbcとpostgres追加

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jdbc</artifactId>
		</dependency>
		
		<dependency>
			<groupId>org.postgresql</groupId>
			<artifactId>postgresql</artifactId>
			<scope>runtime</scope>
		</dependency>

application.properties
L jdbc, postgres追加

spring.jpa.database=POSTGRESQL
spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=root
spring.datasource.password=

$ psql -U root test
test=> \d

create table t_user (
	id serial primary key,
	name varchar(255),
	password varchar(255),
	enabled boolean DEFAULT true
);

insert into t_user (name, password) values
('user1','password'),
('user2','password');

select * from t_user;

UserModel.java

package com.example.demo;

import java.util.Collection;

import lombok.NoArgsConstructor;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.GrantedAuthority;

@NoArgsConstructor
@AllArgsConstructor
@Data
public class UserModel implements UserDetails{

	private String id;
	private String name;
	private String password;
	private boolean enabled;
	
	@Override
	public Collection<? extends GrantedAuthority> getAuthorities(){
		return null;
	}
	
	@Override
	public String getPassword() {
		return this.password;
	}
	
	@Override
	public String getUsername() {
		return this.name;
	}
	
	@Override
	public boolean isAccountNonExpired() {
		return true;
	}
	
	@Override
	public boolean isAccountNonLocked() {
		return true;
	}
	
	@Override
	public boolean isCredentialsNonExpired() {
		return true;
	}
	
	@Override
	public boolean isEnabled() {
		return this.enabled;
	}
}

UserRepository.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.UserRepository">
	<select id="selectByUser" parameterType="com.example.demo.UserRepository" resultType="com.example.demo.UserModel">
		SELECT id, name, password, enabled FROM t_user where id = #{id};
	</select>
</mapper>

UserRepository.java

package com.example.demo;

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserRepository {
	public UserModel selectByUser(String username);
}

UserDetailsServiceImpl.java

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UsernameNotFoundException;

@Component
public class UserDetailsServiceImpl implements UserDetailsService {
	
	@Autowired
	private UserRepository userRepository;
	
	@Override
	public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
		if(StringUtils.isEmpty(username)) throw new UsernameNotFoundException("");
		
		UserModel userModel = userRepository.selectByUser(username);
		
		if(userModel == null) throw new UsernameNotFoundException("");
		if(!userModel.isAccountNonExpired() || !userModel.isAccountNonLocked() || 
				!userModel.isCredentialsNonExpired() || !userModel.isEnabled())
			throw new UsernameNotFoundException("");
		return userModel;
	}

}

Description:

Field userRepository in com.example.demo.UserDetailsServiceImpl required a bean of type ‘com.example.demo.UserRepository’ that could not be found.

なんでや。。。なんでや。。。。
もう一回やるか、次ラストで。

centos8でpostgresをインストールする

# systemctl restart firewalld
# sudo firewall-cmd –permanent –add-port=8756/tcp
success
# systemctl restart firewalld
# ls
practice-0.0.1-SNAPSHOT.jar
# java -jar practice-0.0.1-SNAPSHOT.jar

-> postgresが入ってないのでエラーになる

# yum module list postgresql
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:27:06 ago on Sat Feb 20 12:56:42 2021.
CentOS Linux 8 – AppStream
Name Stream Profiles Summary
postgresql 9.6 client, server [d] PostgreSQL server and client module
postgresql 10 [d] client, server [d] PostgreSQL server and client module
postgresql 12 client, server [d] PostgreSQL server and client module
# yum install -y @postgresql:12/server
# /usr/bin/postgresql-setup –initdb
# systemctl start postgresql
# systemctl enable postgresql
# psql –version
psql (PostgreSQL) 12.5

なんや
vi /var/lib/pgsql/data/pg_hba.conf
psql -h localhost -U postgres
alter role root with superuser login password ”;
ALTER USER root WITH PASSWORD ‘password’;

# psql -U root test
Password for user root:
psql (12.5)
Type “help” for help.

test=#

なんか色々触ってたらできたな