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

documented as unsafe to pass user input identifiers are now quoted, but still not officially supported



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

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

  user = quote_ident(user)
  schema = quote_ident(schema)
  database = quote_ident(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

documented as unsafe to pass user input identifiers are now quoted, but still not officially supported



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

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

  user = quote_ident(user)
  schema = quote_ident(schema)
  database = quote_ident(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