Class: Detector::Addons::Postgres

Inherits:
Base
  • Object
show all
Defined in:
lib/detector/addons/postgres.rb

Instance Attribute Summary

Attributes inherited from Base

#keys, #uri

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from Base

#asn, #connection?, #connection_usage_percentage, #databases?, detect, #geo, #geography, #host, #infrastructure, #initialize, #ip, #kind, #ping, #port, #region, register_addon, #sql?, #summary, #tables?, #tcp_test, #transport?, #udp_test, #valid?, #with_connection

Constructor Details

This class inherits a constructor from Detector::Base

Class Method Details

.capabilities_for(url) ⇒ Object 



10
11
12
 
# File 'lib/detector/addons/postgres.rb', line 10

def self.capabilities_for(url)
  { sql: true, kv: true, url: url, kind: :postgres, databases: true, tables: true }
end

.handles_uri?(uri) ⇒ Boolean

Returns:

  • (Boolean) 


6
7
8
 
# File 'lib/detector/addons/postgres.rb', line 6

def self.handles_uri?(uri)
  uri.scheme.downcase =~ /postgres/
end

Instance Method Details

#cli_name ⇒ Object 



172
173
174
 
# File 'lib/detector/addons/postgres.rb', line 172

def cli_name
  "psql"
end

#close ⇒ Object 



264
265
266
267
268
269
 
# File 'lib/detector/addons/postgres.rb', line 264

def close
  if @conn
    @conn.close rescue nil
    @conn = nil
  end
end

#connection ⇒ Object 



14
15
16
17
 
# File 'lib/detector/addons/postgres.rb', line 14

def connection
  # Create a new connection each time without caching
  PG::Connection.new(uri) rescue nil
end

#connection_count ⇒ Object 



140
141
142
143
 
# File 'lib/detector/addons/postgres.rb', line 140

def connection_count
  return nil unless connection
  connection.exec("SELECT count(*) FROM pg_stat_activity").first['count'].to_i
end

#connection_info ⇒ Object 



150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
 
# File 'lib/detector/addons/postgres.rb', line 150

def connection_info
  return nil unless connection
  begin
    global_limit = connection.exec("SELECT current_setting('max_connections')").first['current_setting'].to_i
    global_count = connection.exec("SELECT count(*) FROM pg_stat_activity").first['count'].to_i
    
    # For PostgreSQL user connections - depends on per-user limits if set
    user_limit_result = connection.exec("SELECT rolconnlimit FROM pg_roles WHERE rolname = current_user").first
    user_limit = user_limit_result['rolconnlimit'].to_i
    user_limit = global_limit if user_limit <= 0 # If unlimited, use global limit
    
    user_count = connection.exec("SELECT count(*) FROM pg_stat_activity WHERE usename = current_user").first['count'].to_i
    
    {
      connection_count: { user: user_count, global: global_count },
      connection_limits: { user: user_limit, global: global_limit }
    }
  rescue => e
    nil
  end
end

#connection_limit ⇒ Object 



145
146
147
148
 
# File 'lib/detector/addons/postgres.rb', line 145

def connection_limit
  return nil unless connection
  connection.exec("SELECT current_setting('max_connections')").first['current_setting'].to_i
end

#current_database ⇒ Object 



48
49
50
 
# File 'lib/detector/addons/postgres.rb', line 48

def current_database
  @current_db ||= connection.exec("SELECT current_database()").first['current_database']
end

#database_count ⇒ Object 



89
90
91
92
 
# File 'lib/detector/addons/postgres.rb', line 89

def database_count
  return nil unless connection
  @database_count ||= connection.exec("SELECT count(*) FROM pg_database WHERE datistemplate = false").first['count'].to_i
end

#databases ⇒ Object 



94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
 
# File 'lib/detector/addons/postgres.rb', line 94

def databases
  return [] unless connection
  result = []
  
  # Get the list of databases and their sizes
  db_list = connection.exec("SELECT datname, pg_size_pretty(pg_database_size(datname)) as size, 
                             pg_database_size(datname) as raw_size 
                             FROM pg_database 
                             WHERE datistemplate = false 
                             ORDER BY raw_size DESC")
  
  # For each database, get table count
  db_list.each do |row|
    db_name = row['datname']
    
    # Skip system databases or databases we can't connect to
    next if ['postgres', 'template0', 'template1'].include?(db_name)
    
    # Get table count for this database
    table_count = 0
    
    begin
      # Create a temporary connection to count tables
      temp_conn = PG::Connection.new(host: host, port: port, user: uri.user, 
                                    password: uri.password, dbname: db_name) rescue nil
                                    
      if temp_conn
        table_count = temp_conn.exec("SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public'").first['count'].to_i
        temp_conn.close
      end
    rescue
      # Skip if we can't connect
      next
    end
    
    result << { 
      name: db_name, 
      size: row['size'], 
      raw_size: row['raw_size'].to_i,
      table_count: table_count
    }
  end
  
  @databases = result
end

#estimated_row_count(table:, database: nil) ⇒ Object 



231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
 
# File 'lib/detector/addons/postgres.rb', line 231

def estimated_row_count(table:, database: nil)
  return nil unless connection
  
  # Use the current database if none is specified
  db_name = database || current_database
  
  begin
    # If we need to query a different database, temporarily connect to it
    if db_name != current_database
      # Create a temporary connection to the specified database
      temp_conn = PG::Connection.new(host: host, port: port, user: uri.user, 
                                   password: uri.password, dbname: db_name) rescue nil
      return nil unless temp_conn
      
      # Use pg_class.reltuples for a fast, statistics-based row estimate
      count = temp_conn.exec("SELECT reltuples::bigint AS estimate 
                            FROM pg_class 
                            WHERE relname = '#{table}'").first
      temp_conn.close
      return count ? count['estimate'].to_i : nil
    end
    
    # Query the current database using pg_class.reltuples
    count = connection.exec("SELECT reltuples::bigint AS estimate 
                          FROM pg_class 
                          WHERE relname = '#{table}'").first
    
    count ? count['estimate'].to_i : nil
  rescue => e
    nil
  end
end

#protocol_type ⇒ Object 



176
177
178
 
# File 'lib/detector/addons/postgres.rb', line 176

def protocol_type
  :tcp
end

#replication_available? ⇒ Boolean

Returns:

  • (Boolean) 


220
221
222
223
224
225
226
227
228
229
 
# File 'lib/detector/addons/postgres.rb', line 220

def replication_available?
  return nil unless connection
  
  begin
    replication_roles = connection.exec("SELECT rolname, rolreplication FROM pg_roles WHERE rolreplication = true;")
    !replication_roles.values.empty?
  rescue => e
    nil
  end
end

#table_count(database_name) ⇒ Object 



29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
# File 'lib/detector/addons/postgres.rb', line 29

def table_count(database_name)
  return nil unless connection
  
  # If we need to query a different database, temporarily connect to it
  if database_name != current_database
    # Create a temporary connection to the specified database
    temp_conn = PG::Connection.new(host: host, port: port, user: uri.user, 
                                   password: uri.password, dbname: database_name) rescue nil
    return nil unless temp_conn
    
    count = temp_conn.exec("SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public'").first['count'].to_i
    temp_conn.close
    return count
  end
  
  # Query the current database
  @table_count ||= connection.exec("SELECT count(*) FROM information_schema.tables WHERE table_schema = 'public'").first['count'].to_i
end

#tables(database_name) ⇒ Object 



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
 
# File 'lib/detector/addons/postgres.rb', line 52

def tables(database_name)
  return [] unless connection
  
  # If we need to query a different database, temporarily connect to it
  if database_name != current_database
    # Create a temporary connection to the specified database
    temp_conn = PG::Connection.new(host: host, port: port, user: uri.user, 
                                   password: uri.password, dbname: database_name) rescue nil
    return [] unless temp_conn
    
    result = temp_conn.exec("SELECT table_name, 
                          pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) as size,
                          pg_total_relation_size(quote_ident(table_name)) as raw_size,
                          (SELECT reltuples::bigint FROM pg_class WHERE relname = table_name) as row_count
                          FROM information_schema.tables 
                          WHERE table_schema = 'public'
                          ORDER BY raw_size DESC").map do |row|
      { name: row['table_name'], size: row['size'], raw_size: row['raw_size'].to_i, row_count: row['row_count'].to_i }
    end
    
    temp_conn.close
    return result
  end
  
  # Query the current database
  @tables ||= {}
  @tables[database_name] ||= connection.exec("SELECT table_name, 
                                           pg_size_pretty(pg_total_relation_size(quote_ident(table_name))) as size,
                                           pg_total_relation_size(quote_ident(table_name)) as raw_size,
                                           (SELECT reltuples::bigint FROM pg_class WHERE relname = table_name) as row_count
                                           FROM information_schema.tables 
                                           WHERE table_schema = 'public'
                                           ORDER BY raw_size DESC").map do |row|
    { name: row['table_name'], size: row['size'], raw_size: row['raw_size'].to_i, row_count: row['row_count'].to_i }
  end
end

#usage ⇒ Object 



24
25
26
27
 
# File 'lib/detector/addons/postgres.rb', line 24

def usage
  return nil unless connection
  connection.exec("SELECT pg_size_pretty(pg_database_size(current_database())) AS size").first['size']
end

#user_access_level ⇒ Object 



180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
 
# File 'lib/detector/addons/postgres.rb', line 180

def user_access_level
  return nil unless connection
  
  is_superuser = connection.exec("SELECT usesuper FROM pg_user WHERE usename = current_user").first["usesuper"] == "t" rescue false
  is_replication = connection.exec("SELECT rolreplication FROM pg_roles WHERE rolname = current_user").first["rolreplication"] == "t" rescue false
  roles = connection.exec("SELECT r.rolname FROM pg_roles r JOIN pg_auth_members m ON r.oid = m.roleid JOIN pg_roles u ON m.member = u.oid WHERE u.rolname = current_user").map { |row| row["rolname"] } rescue []
  
  create_db = connection.exec("SELECT usecreatedb FROM pg_user WHERE usename = current_user").first["usecreatedb"] == "t" rescue false
  
  if is_superuser
    "Superuser (full access)"
  elsif is_replication
    "Replication user (system-level replication access)"
  elsif create_db
    "Database creator (can create new databases)"
  elsif roles.include?("rds_superuser")
    "RDS Superuser (limited admin privileges)"
  else
    # Check if can access system catalogs (higher than regular user)
    begin
      connection.exec("SELECT count(*) FROM pg_shadow")
      "Power user (access to system catalogs)"
    rescue => e
      # Check if can create tables in current database
      begin
        connection.exec("CREATE TABLE __temp_access_check (id int); DROP TABLE __temp_access_check;")
        "Regular user (table management)"
      rescue => e
        # Check for readonly access
        begin
          connection.exec("SELECT current_database()")
          "Read-only user"
        rescue => e
          "Limited access"
        end
      end
    end
  end
end

#version ⇒ Object 



19
20
21
22
 
# File 'lib/detector/addons/postgres.rb', line 19

def version
  return nil unless connection
  @version ||= connection.exec("SELECT version()").first['version']
end