Module: PgHero::Methods::Users

Included in:
Database
Defined in:
lib/pghero/methods/users.rb

Instance Method Summary collapse

Instance Method Details

#create_user(user, password: nil, schema: "public", database: nil, readonly: false, tables: nil) ⇒ Object 



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
 
# File 'lib/pghero/methods/users.rb', line 4

def create_user(user, password: nil, schema: "public", database: nil, readonly: false, tables: nil)
  password ||= random_password
  database ||= connection_model.connection_config[:database]

  commands =
    [
      "CREATE ROLE #{user} LOGIN PASSWORD #{quote(password)}",
      "GRANT CONNECT ON DATABASE #{database} TO #{user}",
      "GRANT USAGE ON SCHEMA #{schema} TO #{user}"
    ]
  if readonly
    if tables
      commands.concat table_grant_commands("SELECT", tables, user)
    else
      commands << "GRANT SELECT ON ALL TABLES IN SCHEMA #{schema} TO #{user}"
      commands << "ALTER DEFAULT PRIVILEGES IN SCHEMA #{schema} GRANT SELECT ON TABLES TO #{user}"
    end
  else
    if tables
      commands.concat table_grant_commands("ALL PRIVILEGES", tables, user)
    else
      commands << "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA #{schema} TO #{user}"
      commands << "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA #{schema} TO #{user}"
      commands << "ALTER DEFAULT PRIVILEGES IN SCHEMA #{schema} GRANT ALL PRIVILEGES ON TABLES TO #{user}"
      commands << "ALTER DEFAULT PRIVILEGES IN SCHEMA #{schema} GRANT ALL PRIVILEGES ON SEQUENCES TO #{user}"
    end
  end

  # run commands
  connection_model.transaction do
    commands.each do |command|
      execute command
    end
  end

  {password: password}
end

#drop_user(user, schema: "public", database: nil) ⇒ Object 



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
 
# File 'lib/pghero/methods/users.rb', line 42

def drop_user(user, schema: "public", database: nil)
  database ||= connection_model.connection_config[:database]

  # thanks shiftb
  commands =
    [
      "REVOKE CONNECT ON DATABASE #{database} FROM #{user}",
      "REVOKE USAGE ON SCHEMA #{schema} FROM #{user}",
      "REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA #{schema} FROM #{user}",
      "REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA #{schema} FROM #{user}",
      "ALTER DEFAULT PRIVILEGES IN SCHEMA #{schema} REVOKE SELECT ON TABLES FROM #{user}",
      "ALTER DEFAULT PRIVILEGES IN SCHEMA #{schema} REVOKE SELECT ON SEQUENCES FROM #{user}",
      "ALTER DEFAULT PRIVILEGES IN SCHEMA #{schema} REVOKE ALL ON SEQUENCES FROM #{user}",
      "ALTER DEFAULT PRIVILEGES IN SCHEMA #{schema} REVOKE ALL ON TABLES FROM #{user}",
      "DROP ROLE #{user}"
    ]

  # run commands
  connection_model.transaction do
    commands.each do |command|
      execute command
    end
  end

  true
end