module PgHero::Methods::Users

Public Instance Methods

create_user(user, password: nil, schema: "public", database: nil, readonly: false, tables: nil) click to toggle source

documented as unsafe to pass user input TODO quote in 3.0, but still not officially supported

# 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]

  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) click to toggle source

documented as unsafe to pass user input TODO quote in 3.0, but still not officially supported

# File lib/pghero/methods/users.rb, line 46
def drop_user(user, schema: "public", database: nil)
  database ||= PgHero.connection_config(connection_model)[: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

Private Instance Methods

random_password() click to toggle source
# File lib/pghero/methods/users.rb, line 75
def random_password
  require "securerandom"
  SecureRandom.base64(40).delete("+/=")[0...24]
end
table_grant_commands(privilege, tables, user) click to toggle source
# File lib/pghero/methods/users.rb, line 80
def table_grant_commands(privilege, tables, user)
  tables.map do |table|
    "GRANT #{privilege} ON TABLE #{table} TO #{user}"
  end
end