aws-rds-data-masking-data-anonymization/sql/03_fake_data_generators.sql at main · sendtoshailesh/aws-rds-data-masking-data-anonymization · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
-- PostgreSQL Data Anonymization in AWS RDS
-- Fake data generator functions

-- Create a function to generate fake first names
CREATE OR REPLACE FUNCTION anon.fake_first_name()
RETURNS text AS
$$
DECLARE
  names text[] := ARRAY['John', 'Jane', 'Robert', 'Mary', 'William', 'Patricia', 'James', 'Jennifer', 'Michael', 'Linda',
                        'David', 'Elizabeth', 'Richard', 'Barbara', 'Joseph', 'Susan', 'Thomas', 'Jessica', 'Charles', 'Sarah',
                        'Daniel', 'Margaret', 'Christopher', 'Nancy', 'Matthew', 'Lisa', 'Anthony', 'Karen', 'Mark', 'Betty',
                        'Donald', 'Dorothy', 'Steven', 'Sandra', 'Paul', 'Ashley', 'Andrew', 'Kimberly', 'Joshua', 'Donna'];
BEGIN
  RETURN names[1 + (random() * (array_length(names, 1) - 1))::integer];
END;
$$
LANGUAGE plpgsql VOLATILE;

-- Create a function to generate fake last names
CREATE OR REPLACE FUNCTION anon.fake_last_name()
RETURNS text AS
$$
DECLARE
  names text[] := ARRAY['Smith', 'Johnson', 'Williams', 'Jones', 'Brown', 'Davis', 'Miller', 'Wilson', 'Moore', 'Taylor',
                        'Anderson', 'Thomas', 'Jackson', 'White', 'Harris', 'Martin', 'Thompson', 'Garcia', 'Martinez', 'Robinson',
                        'Clark', 'Rodriguez', 'Lewis', 'Lee', 'Walker', 'Hall', 'Allen', 'Young', 'Hernandez', 'King',
                        'Wright', 'Lopez', 'Hill', 'Scott', 'Green', 'Adams', 'Baker', 'Gonzalez', 'Nelson', 'Carter'];
BEGIN
  RETURN names[1 + (random() * (array_length(names, 1) - 1))::integer];
END;
$$
LANGUAGE plpgsql VOLATILE;

-- Create a function to generate a fake full name
CREATE OR REPLACE FUNCTION anon.fake_name()
RETURNS text AS
$$
BEGIN
  RETURN anon.fake_first_name() || ' ' || anon.fake_last_name();
END;
$$
LANGUAGE plpgsql VOLATILE;

-- Create a function to generate fake addresses
CREATE OR REPLACE FUNCTION anon.fake_address()
RETURNS text AS
$$
DECLARE
  street_numbers int[] := ARRAY[123, 456, 789, 101, 202, 303, 404, 505, 606, 707, 808, 909, 1010, 1111, 1212];
  street_names text[] := ARRAY['Main', 'Oak', 'Pine', 'Maple', 'Cedar', 'Elm', 'Washington', 'Lake', 'Hill', 'Park',
                              'Spring', 'River', 'Church', 'North', 'South'];
  street_types text[] := ARRAY['St', 'Ave', 'Blvd', 'Rd', 'Ln', 'Dr', 'Way', 'Pl', 'Ct', 'Terrace'];
  cities text[] := ARRAY['Springfield', 'Franklin', 'Greenville', 'Bristol', 'Clinton', 'Kingston', 'Marion', 'Salem',
                         'Georgetown', 'Fairview', 'Riverside', 'Madison', 'Oxford', 'Arlington', 'Burlington'];
  states text[] := ARRAY['AL', 'AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA'];
  zips text[] := ARRAY['12345', '23456', '34567', '45678', '56789', '67890', '78901', '89012', '90123', '01234'];

  street_number int;
  street_name text;
  street_type text;
  city text;
  state text;
  zip text;
BEGIN
  street_number := street_numbers[1 + (random() * (array_length(street_numbers, 1) - 1))::integer];
  street_name := street_names[1 + (random() * (array_length(street_names, 1) - 1))::integer];
  street_type := street_types[1 + (random() * (array_length(street_types, 1) - 1))::integer];
  city := cities[1 + (random() * (array_length(cities, 1) - 1))::integer];
  state := states[1 + (random() * (array_length(states, 1) - 1))::integer];
  zip := zips[1 + (random() * (array_length(zips, 1) - 1))::integer];

  RETURN street_number || ' ' || street_name || ' ' || street_type || ', ' || city || ', ' || state || ' ' || zip;
END;
$$
LANGUAGE plpgsql VOLATILE;

-- Create a function to generate fake phone numbers
CREATE OR REPLACE FUNCTION anon.fake_phone()
RETURNS text AS
$$
BEGIN
  RETURN '(' || (anon.random_int(200, 999))::text || ') ' ||
         (anon.random_int(200, 999))::text || '-' ||
         (anon.random_int(1000, 9999))::text;
END;
$$
LANGUAGE plpgsql VOLATILE;

-- Create a function to generate fake email addresses
CREATE OR REPLACE FUNCTION anon.fake_email()
RETURNS text AS
$$
DECLARE
  domains text[] := ARRAY['example.com', 'sample.org', 'test.net', 'demo.io', 'fake.co'];
  first_name text;
  last_name text;
  domain text;
BEGIN
  first_name := lower(anon.fake_first_name());
  last_name := lower(anon.fake_last_name());
  domain := domains[1 + (random() * (array_length(domains, 1) - 1))::integer];

  RETURN first_name || '.' || last_name || '@' || domain;
END;
$$
LANGUAGE plpgsql VOLATILE;