Common DBA tasks for PostgreSQL - Amazon Relational Database Service

Common DBA tasks for PostgreSQL

This section describes the Amazon RDS implementations of some common DBA tasks for DB instances running the PostgreSQL database engine. To deliver a managed service experience, Amazon RDS doesn't provide shell access to DB instances, and it restricts access to certain system procedures and tables that require advanced privileges.

For information about working with PostgreSQL log files on Amazon RDS, see PostgreSQL database log files.

Creating roles

When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is a predefined Amazon RDS role similar to the PostgreSQL superuser role (customarily named postgres in local instances), but with some restrictions. As with the PostgreSQL superuser role, the rds_superuser role has the most privileges for your DB instance. You should not assign this role to users unless they need the most access to the DB instance.

The rds_superuser role can do the following:

  • Add extensions that are available for use with Amazon RDS. For more information, see Some supported PostgreSQL features and the PostgreSQL documentation.

  • Manage tablespaces, including creating and deleting them. For more information, see Tablespaces for PostgreSQL on Amazon RDS and the Tablespaces section in the PostgreSQL documentation.

  • View all users not assigned the rds_superuser role using the pg_stat_activity command and stop their connections using the pg_terminate_backend and pg_cancel_backend commands.

  • Grant and revoke the rds_replication role for all roles that are not the rds_superuser role. For more information, see the GRANT section in the PostgreSQL documentation.

The following example shows how to create a user and then grant the user the rds_superuser role. User-defined roles, such as rds_superuser, have to be granted.

create role testuser with password 'testuser' login; grant rds_superuser to testuser;

Managing PostgreSQL database access

In Amazon RDS for PostgreSQL, you can manage which users have privileges to connect to which databases. In other PostgreSQL environments, you sometimes perform this kind of management by modifying the pg_hba.conf file. In Amazon RDS, you can use database grants instead.

New databases in PostgreSQL are always created with a default set of privileges. The default privileges allow PUBLIC (all users) to connect to the database and to create temporary tables while connected.

To control which users are allowed to connect to a given database in Amazon RDS, first revoke the default PUBLIC privileges. Then grant back the privileges on a more granular basis. The following example code shows how.

psql> revoke all on database <database-name> from public; psql> grant connect, temporary on database <database-name> to <user/role name>;

For more information about privileges in PostgreSQL databases, see the GRANT command in the PostgreSQL documentation.

Working with PostgreSQL parameters

PostgreSQL parameters that you set for a local PostgreSQL instance in the postgresql.conf file are maintained in the DB parameter group for your DB instance. If you create a DB instance using the default parameter group, the parameter settings are in the parameter group called default.postgres9.6.

When you create a DB instance, the parameters in the associated DB parameter group are loaded. You can modify parameter values by changing values in the parameter group. You can also change parameter values, if you have the security privileges to do so, by using the ALTER DATABASE, ALTER ROLE, and SET commands. You can't use the command line postgres command or the env PGOPTIONS command, because you have no access to the host.

Keeping track of PostgreSQL parameter settings can occasionally be difficult. Use the following command to list current parameter settings and the default value.

select name, setting, boot_val, reset_val, unit from pg_settings order by name;

For an explanation of the output values, see the pg_settings topic in the PostgreSQL documentation.

If you set the memory settings too large for max_connections or shared_buffers, you will prevent the PostgreSQL instance from starting up. Some parameters use units that you might not be familiar with; for example, shared_buffers sets the number of 8-KB shared memory buffers used by the server.

The following error is written to the postgres.log file when the instance is attempting to start up, but incorrect parameter settings are preventing it from starting.

2013-09-18 21:13:15 UTC::@:[8097]:FATAL: could not map anonymous shared memory: Cannot allocate memory 2013-09-18 21:13:15 UTC::@:[8097]:HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 3514134274048 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.

There are two types of PostgreSQL parameters, static and dynamic. Static parameters require that the DB instance be rebooted before they are applied. Dynamic parameters can be applied immediately. The following table shows parameters that you can modify for a PostgreSQL DB instance and each parameter's type.

Parameter name

Apply_Type

Description

application_name

Dynamic Sets the application name to be reported in statistics and logs.

array_nulls

Dynamic Enables input of NULL elements in arrays.

authentication_timeout

Dynamic Sets the maximum allowed time to complete client authentication.

autovacuum

Dynamic Starts the autovacuum subprocess.

autovacuum_analyze_scale_factor

Dynamic Number of tuple inserts, updates, or deletes before analyze as a fraction of reltuples.

autovacuum_analyze_threshold

Dynamic Minimum number of tuple inserts, updates, or deletes before analyze.

autovacuum_naptime

Dynamic Time to sleep between autovacuum runs.

autovacuum_vacuum_cost_delay

Dynamic Vacuum cost delay, in milliseconds, for autovacuum.

autovacuum_vacuum_cost_limit

Dynamic Vacuum cost amount available before napping, for autovacuum.

autovacuum_vacuum_scale_factor

Dynamic Number of tuple updates or deletes before vacuum as a fraction of reltuples.

autovacuum_vacuum_threshold

Dynamic Minimum number of tuple updates or deletes before vacuum.

backslash_quote

Dynamic Sets whether a backslash (\) is allowed in string literals.

bgwriter_delay

Dynamic Background writer sleep time between rounds.

bgwriter_lru_maxpages

Dynamic Background writer maximum number of LRU pages to flush per round.

bgwriter_lru_multiplier

Dynamic Multiple of the average buffer usage to free per round.

bytea_output

Dynamic Sets the output format for bytes.

check_function_bodies

Dynamic Checks function bodies during CREATE FUNCTION.

checkpoint_completion_target

Dynamic Time spent flushing dirty buffers during checkpoint, as a fraction of the checkpoint interval.

checkpoint_segments

Dynamic Sets the maximum distance in log segments between automatic write-ahead log (WAL) checkpoints.

checkpoint_timeout

Dynamic Sets the maximum time between automatic WAL checkpoints.

checkpoint_warning

Dynamic Enables warnings if checkpoint segments are filled more frequently than this.

client_encoding

Dynamic Sets the client's character set encoding.

client_min_messages

Dynamic Sets the message levels that are sent to the client.

commit_delay

Dynamic Sets the delay in microseconds between transaction commit and flushing WAL to disk.

commit_siblings

Dynamic Sets the minimum concurrent open transactions before performing commit_delay.

constraint_exclusion

Dynamic Enables the planner to use constraints to optimize queries.

cpu_index_tuple_cost

Dynamic Sets the planner's estimate of the cost of processing each index entry during an index scan.

cpu_operator_cost

Dynamic Sets the planner's estimate of the cost of processing each operator or function call.

cpu_tuple_cost

Dynamic Sets the planner's estimate of the cost of processing each tuple (row).

cursor_tuple_fraction

Dynamic Sets the planner's estimate of the fraction of a cursor's rows that will be retrieved.

datestyle

Dynamic Sets the display format for date and time values.

deadlock_timeout

Dynamic Sets the time to wait on a lock before checking for deadlock.

debug_pretty_print

Dynamic Indents parse and plan tree displays.

debug_print_parse

Dynamic Logs each query's parse tree.

debug_print_plan

Dynamic Logs each query's execution plan.

debug_print_rewritten

Dynamic Logs each query's rewritten parse tree.

default_statistics_target

Dynamic Sets the default statistics target.

default_tablespace

Dynamic Sets the default tablespace to create tables and indexes in.

default_transaction_deferrable

Dynamic Sets the default deferrable status of new transactions.

default_transaction_isolation

Dynamic Sets the transaction isolation level of each new transaction.

default_transaction_read_only

Dynamic Sets the default read-only status of new transactions.

default_with_oids

Dynamic Creates new tables with OIDs by default.

effective_cache_size

Dynamic Sets the planner's assumption about the size of the disk cache.

effective_io_concurrency

Dynamic Number of simultaneous requests that can be handled efficiently by the disk subsystem.

enable_bitmapscan

Dynamic Enables the planner's use of bitmap-scan plans.

enable_hashagg

Dynamic Enables the planner's use of hashed aggregation plans.

enable_hashjoin

Dynamic Enables the planner's use of hash join plans.

enable_indexscan

Dynamic Enables the planner's use of index-scan plans.

enable_material

Dynamic Enables the planner's use of materialization.

enable_mergejoin

Dynamic Enables the planner's use of merge join plans.

enable_nestloop

Dynamic Enables the planner's use of nested-loop join plans.

enable_seqscan

Dynamic Enables the planner's use of sequential-scan plans.

enable_sort

Dynamic Enables the planner's use of explicit sort steps.

enable_tidscan

Dynamic Enables the planner's use of TID scan plans.

escape_string_warning

Dynamic Warns about backslash (\) escapes in ordinary string literals.

extra_float_digits

Dynamic Sets the number of digits displayed for floating-point values.

from_collapse_limit

Dynamic Sets the FROM-list size beyond which subqueries are not collapsed.

fsync

Dynamic Forces synchronization of updates to disk.

full_page_writes

Dynamic Writes full pages to WAL when first modified after a checkpoint.

geqo

Dynamic Enables genetic query optimization.

geqo_effort

Dynamic GEQO: effort is used to set the default for other GEQO parameters.

geqo_generations

Dynamic GEQO: number of iterations of the algorithm.

geqo_pool_size

Dynamic GEQO: number of individuals in the population.

geqo_seed

Dynamic GEQO: seed for random path selection.

geqo_selection_bias

Dynamic GEQO: selective pressure within the population.

geqo_threshold

Dynamic Sets the threshold of FROM items beyond which GEQO is used.

gin_fuzzy_search_limit

Dynamic Sets the maximum allowed result for exact search by GIN.

hot_standby_feedback

Dynamic Determines whether a hot standby sends feedback messages to the primary or upstream standby.

intervalstyle

Dynamic Sets the display format for interval values.

join_collapse_limit

Dynamic Sets the FROM-list size beyond which JOIN constructs are not flattened.

lc_messages

Dynamic Sets the language in which messages are displayed.

lc_monetary

Dynamic Sets the locale for formatting monetary amounts.

lc_numeric

Dynamic Sets the locale for formatting numbers.

lc_time

Dynamic Sets the locale for formatting date and time values.

log_autovacuum_min_duration

Dynamic Sets the minimum running time above which autovacuum actions will be logged.

log_checkpoints

Dynamic Logs each checkpoint.

log_connections

Dynamic Logs each successful connection.

log_disconnections

Dynamic Logs end of a session, including duration.

log_duration

Dynamic Logs the duration of each completed SQL statement.

log_error_verbosity

Dynamic Sets the verbosity of logged messages.

log_executor_stats

Dynamic Writes executor performance statistics to the server log.

log_filename

Dynamic Sets the file name pattern for log files.

log_hostname

Dynamic Logs the host name in the connection logs.

log_lock_waits

Dynamic Logs long lock waits.

log_min_duration_statement

Dynamic Sets the minimum running time above which statements will be logged.

log_min_error_statement

Dynamic Causes all statements generating an error at or above this level to be logged.

log_min_messages

Dynamic Sets the message levels that are logged.

log_parser_stats

Dynamic Writes parser performance statistics to the server log.

log_planner_stats

Dynamic Writes planner performance statistics to the server log.

log_rotation_age

Dynamic Automatic log file rotation will occur after N minutes.

log_rotation_size

Dynamic Automatic log file rotation will occur after N kilobytes.

log_statement

Dynamic Sets the type of statements logged.

log_statement_stats

Dynamic Writes cumulative performance statistics to the server log.

log_temp_files

Dynamic Logs the use of temporary files larger than this number of kilobytes.

maintenance_work_mem

Dynamic Sets the maximum memory to be used for maintenance operations.

max_stack_depth

Dynamic Sets the maximum stack depth, in kilobytes.

max_standby_archive_delay

Dynamic Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data.

max_standby_streaming_delay

Dynamic Sets the maximum delay before canceling queries when a hot standby server is processing streamed WAL data.
max_wal_size Dynamic Sets the WAL size that triggers the checkpoint. For PostgreSQL version 9.6 and earlier, max_wal_size is in units of 16 MB. For PostgreSQL version 10 and later, max_wal_size is in units of 1 MB.
min_wal_size Dynamic Sets the minimum size to shrink the WAL to. For PostgreSQL version 9.6 and earlier, min_wal_size is in units of 16 MB. For PostgreSQL version 10 and later, min_wal_size is in units of 1 MB.

quote_all_identifiers

Dynamic Adds quotes (") to all identifiers when generating SQL fragments.

random_page_cost

Dynamic Sets the planner's estimate of the cost of a non-sequentially fetched disk page.
rds.adaptive_autovacuum Dynamic Automatically tunes the autovacuum parameters whenever the transaction ID thresholds are exceeded.

rds.log_retention_period

Dynamic Sets log retention such that Amazon RDS deletes PostgreSQL logs that are older than N minutes.
rds.restrict_password_commands Static Restricts who can manage passwords to users with the rds_password role. Set this parameter to 1 to enable password restriction. The default is 0.

search_path

Dynamic Sets the schema search order for names that are not schema-qualified.

seq_page_cost

Dynamic Sets the planner's estimate of the cost of a sequentially fetched disk page.

session_replication_role

Dynamic Sets the sessions behavior for triggers and rewrite rules.

sql_inheritance

Dynamic Causes subtables to be included by default in various commands.

ssl_renegotiation_limit

Dynamic Sets the amount of traffic to send and receive before renegotiating the encryption keys.

standard_conforming_strings

Dynamic Causes ... strings to treat backslashes literally.

statement_timeout

Dynamic Sets the maximum allowed duration of any statement.

synchronize_seqscans

Dynamic Enables synchronized sequential scans.

synchronous_commit

Dynamic Sets the current transactions synchronization level.

tcp_keepalives_count

Dynamic Maximum number of TCP keepalive retransmits.

tcp_keepalives_idle

Dynamic Time between issuing TCP keepalives.

tcp_keepalives_interval

Dynamic Time between TCP keepalive retransmits.

temp_buffers

Dynamic Sets the maximum number of temporary buffers used by each session.

temp_tablespaces

Dynamic Sets the tablespaces to use for temporary tables and sort files.

timezone

Dynamic Sets the time zone for displaying and interpreting time stamps.

track_activities

Dynamic Collects information about running commands.

track_counts

Dynamic Collects statistics on database activity.

track_functions

Dynamic Collects function-level statistics on database activity.

track_io_timing

Dynamic Collects timing statistics on database I/O activity.

transaction_deferrable

Dynamic Indicates whether to defer a read-only serializable transaction until it can be started with no possible serialization failures.

transaction_isolation

Dynamic Sets the current transactions isolation level.

transaction_read_only

Dynamic Sets the current transactions read-only status.

transform_null_equals

Dynamic Treats expr=NULL as expr IS NULL.

update_process_title

Dynamic Updates the process title to show the active SQL command.

vacuum_cost_delay

Dynamic Vacuum cost delay in milliseconds.

vacuum_cost_limit

Dynamic Vacuum cost amount available before napping.

vacuum_cost_page_dirty

Dynamic Vacuum cost for a page dirtied by vacuum.

vacuum_cost_page_hit

Dynamic Vacuum cost for a page found in the buffer cache.

vacuum_cost_page_miss

Dynamic Vacuum cost for a page not found in the buffer cache.

vacuum_defer_cleanup_age

Dynamic Number of transactions by which vacuum and hot cleanup should be deferred, if any.

vacuum_freeze_min_age

Dynamic Minimum age at which vacuum should freeze a table row.

vacuum_freeze_table_age

Dynamic Age at which vacuum should scan a whole table to freeze tuples.

wal_writer_delay

Dynamic WAL writer sleep time between WAL flushes.

work_mem

Dynamic Sets the maximum memory to be used for query workspaces.

xmlbinary

Dynamic Sets how binary values are to be encoded in XML.

xmloption

Dynamic Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.

autovacuum_freeze_max_age

Static Age at which to autovacuum a table to prevent transaction ID wraparound.

autovacuum_max_workers

Static Sets the maximum number of simultaneously running autovacuum worker processes.

max_connections

Static Sets the maximum number of concurrent connections.

max_files_per_process

Static Sets the maximum number of simultaneously open files for each server process.

max_locks_per_transaction

Static Sets the maximum number of locks per transaction.

max_pred_locks_per_transaction

Static Sets the maximum number of predicate locks per transaction.

max_prepared_transactions

Static Sets the maximum number of simultaneously prepared transactions.

shared_buffers

Static Sets the number of shared memory buffers used by the server.

ssl

Static Enables SSL connections.
temp_file_limit Static Sets the maximum size in KB to which the temporary files can grow.

track_activity_query_size

Static Sets the size reserved for pg_stat_activity.current_query, in bytes.

wal_buffers

Static Sets the number of disk-page buffers in shared memory for WAL.

Amazon RDS uses the default PostgreSQL units for all parameters. The following table shows the PostgreSQL default unit and value for each parameter.

Parameter name

Unit

effective_cache_size

8 KB

segment_size

8 KB

shared_buffers

8 KB

temp_buffers

8 KB

wal_buffers

8 KB

wal_segment_size

8 KB

log_rotation_size

KB

log_temp_files

KB

maintenance_work_mem

KB

max_stack_depth

KB

ssl_renegotiation_limit

KB
temp_file_limit KB

work_mem

KB

log_rotation_age

minutes

autovacuum_vacuum_cost_delay

ms

bgwriter_delay

ms

deadlock_timeout

ms

lock_timeout

ms

log_autovacuum_min_duration

ms

log_min_duration_statement

ms

max_standby_archive_delay

ms

max_standby_streaming_delay

ms

statement_timeout

ms

vacuum_cost_delay

ms

wal_receiver_timeout

ms

wal_sender_timeout

ms

wal_writer_delay

ms

archive_timeout

s

authentication_timeout

s

autovacuum_naptime

s

checkpoint_timeout

s

checkpoint_warning

s

post_auth_delay

s

pre_auth_delay

s

tcp_keepalives_idle

s

tcp_keepalives_interval

s

wal_receiver_status_interval

s

Audit logging for a PostgreSQL DB instance

There are several parameters you can set to log activity that occurs on your PostgreSQL DB instance. These parameters include the following:

  • The log_statement parameter can be used to log user activity in your PostgreSQL database. For more information, see PostgreSQL database log files.

  • The rds.force_admin_logging_level parameter logs actions by the RDS internal user (rdsadmin) in the databases on the DB instance, and writes the output to the PostgreSQL error log. Allowed values are disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, and panic. The default value is disabled.

  • The rds.force_autovacuum_logging_level parameter logs autovacuum worker operations in all databases on the DB instance, and writes the output to the PostgreSQL error log. Allowed values are disabled, debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, and panic. The default value is disabled. The Amazon RDS recommended setting for rds.force_autovacuum_logging_level: is LOG. Set log_autovacuum_min_duration to a value from 1000 or 5000. Setting this value to 5,000 writes activity to the log that takes more than 5 seconds and shows "vacuum skipped" messages. For more information on this parameter, see Best practices for working with PostgreSQL.

Working with the pgaudit extension

The pgaudit extension provides detailed session and object audit logging for RDS for PostgreSQL. You can enable session auditing or object auditing using this extension. You can use the pgaudit extension with the following versions:

  • RDS for PostgreSQL version 13, all minor versions

  • RDS for PostgreSQL version 12, all minor versions

  • RDS for PostgreSQL version 11, all minor versions

  • RDS for PostgreSQL version 10, all minor versions

  • RDS for PostgreSQL version 9.6.3 and higher 9.6 versions

  • RDS for PostgreSQL version 9.5.7 and higher 9.5 versions

For more information on the pgaudit extension, see the GitHub project documentation. Choose the documentation for your PostgreSQL version.

To use the pgaudit extension, make sure a custom DB parameter group is associated with your DB instance and set parameters in this custom DB parameter group. For information about parameter groups, see Working with DB parameter groups.

With session auditing, you can log audit events from various sources and include the fully qualified command text when available. Modify the custom parameter group that is associated with your DB instance so that shared_preload_libraries contains pgaudit and then set the pgaudit.log parameter to log any of the following types of events:

  • READ – Audits SELECT and COPY when the source is a relation or a query.

  • WRITE – Audits INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.

  • FUNCTION – Audits function calls and DO blocks.

  • ROLE – Audits statements related to roles and privileges, such as GRANT, REVOKE, CREATE ROLE, ALTER ROLE, and DROP ROLE.

  • DDL – Audits all data definition language (DDL) statements that aren't included in the ROLE class.

  • MISC – Audits miscellaneous commands, such as DISCARD, FETCH, CHECKPOINT, VACUUM, and SET.

To log more than one of these types of events with session auditing, use a comma-separated list. To log all of these types of events, set pgaudit.log to ALL. You might need to reboot your DB instance to apply the changes.

With object auditing, you can refine the audit logging to work with specific relations. For example, you can specify that you want audit logging for READ operations on a specific number of tables.

To use object auditing with the pgaudit extension

  1. Create a database role called rds_pgaudit using the following command.

    CREATE ROLE rds_pgaudit;
  2. Modify the custom parameter group that is associated with your DB instance to do the following:

    • Set pgaudit.role to the role rds_pgaudit.

    • Use the shared preload libraries that contain pgaudit.

    The following commands modify a custom parameter group.

    aws rds modify-db-parameter-group \ --db-parameter-group-name my-parameter-group \ --parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \ --region us-west-2 aws rds modify-db-parameter-group \ --db-parameter-group-name my-parameter-group \ --parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \ --region us-west-2
  3. Reboot the instance so that the DB instance picks up the changes to the parameter group.

    aws rds reboot-db-instance \ --db-instance-identifier rds-test-instance \ --region us-west-2
  4. Run the following command to confirm that pgaudit has been initialized.

    SHOW shared_preload_libraries; shared_preload_libraries -------------------------- rdsutils,pgaudit (1 row)
  5. Run the following command to create the pgaudit extension.

    CREATE EXTENSION pgaudit;
  6. Run the following command to confirm pgaudit.role is set to rds_pgaudit.

    SHOW pgaudit.role; pgaudit.role ------------------ rds_pgaudit

To test the audit logging, run several commands that you have chosen to audit. For example, you might run the following commands.

CREATE TABLE t1 (id int); GRANT SELECT ON t1 TO rds_pgaudit; SELECT * FROM t1; id ---- (0 rows)

The database logs should contain an entry similar to the following.

... 2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1; ...

For information on viewing the logs, see Working with Amazon RDS database log files.

Working with the pg_repack extension

You can use the pg_repack extension to remove bloat from tables and indexes. This extension is supported on Amazon RDS for PostgreSQL versions 9.6.3 and later. For more information on the pg_repack extension, see the GitHub project documentation.

To use the pg_repack extension

  1. Install the pg_repack extension on your Amazon RDS for PostgreSQL DB instance by running the following command.

    CREATE EXTENSION pg_repack;
  2. Run the following commands to grant write access to repack temporary log tables created by pg_repack.

    ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC; ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;
  3. Use the pg_repack client utility to connect to a database. Use a database role that has rds_superuser privileges to connect to the database. In the following connection example, the rds_test role has rds_superuser privileges, and the database endpoint used is rds-test-instance.cw7jjfgdr4on8.us-west-2.rds.amazonaws.com.

    pg_repack -h rds-test-instance.cw7jjfgdr4on8.us-west-2.rds.amazonaws.com -U rds_test -k postgres

    Connect using the -k option. The -a option is not supported.

  4. The response from the pg_repack client provides information on the tables on the DB instance that are repacked.

    INFO: repacking table "pgbench_tellers" INFO: repacking table "pgbench_accounts" INFO: repacking table "pgbench_branches"

Using pgBadger for log analysis with PostgreSQL

You can use a log analyzer such as pgbadger to analyze PostgreSQL logs. The pgbadger documentation states that the %l pattern (log line for session/process) should be a part of the prefix. However, if you provide the current rds log_line_prefix as a parameter to pgbadger it should still produce a report.

For example, the following command correctly formats an Amazon RDS for PostgreSQL log file dated 2014-02-04 using pgbadger.

./pgbadger -f stderr -p '%t:%r:%u@%d:[%p]:' postgresql.log.2014-02-04-00

Viewing the contents of pg_config

In PostgreSQL version 9.6.1, you can see the compile-time configuration parameters of the currently installed version of PostgreSQL using the new view pg_config. You can use the view by calling the pg_config function as shown in the following sample.

select * from pg_config(); name | setting -------------------+--------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------- BINDIR | /rdsdbbin/postgres-9.6.1.R1/bin DOCDIR | /rdsdbbin/postgres-9.6.1.R1/share/doc HTMLDIR | /rdsdbbin/postgres-9.6.1.R1/share/doc INCLUDEDIR | /rdsdbbin/postgres-9.6.1.R1/include PKGINCLUDEDIR | /rdsdbbin/postgres-9.6.1.R1/include INCLUDEDIR-SERVER | /rdsdbbin/postgres-9.6.1.R1/include/server LIBDIR | /rdsdbbin/postgres-9.6.1.R1/lib PKGLIBDIR | /rdsdbbin/postgres-9.6.1.R1/lib LOCALEDIR | /rdsdbbin/postgres-9.6.1.R1/share/locale MANDIR | /rdsdbbin/postgres-9.6.1.R1/share/man SHAREDIR | /rdsdbbin/postgres-9.6.1.R1/share SYSCONFDIR | /rdsdbbin/postgres-9.6.1.R1/etc PGXS | /rdsdbbin/postgres-9.6.1.R1/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE | '--prefix=/rdsdbbin/postgres-9.6.1.R1' '--with-openssl' '--with-perl' '--with-tcl' '--with-ossp-uuid' '--with-libxml' '--with-libraries=/rdsdbbin /postgres-9.6.1.R1/lib' '--with-includes=/rdsdbbin/postgres-9.6.1.R1/include' '--enable-debug' CC | gcc CPPFLAGS | -D_GNU_SOURCE -I/usr/include/libxml2 -I/rdsdbbin/postgres-9.6.1.R1/include CFLAGS | -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict- aliasing -fwrapv -fexcess-precision=standard -g -O2 CFLAGS_SL | -fpic LDFLAGS | -L../../src/common -L/rdsdbbin/postgres-9.6.1.R1/lib -Wl,--as-needed -Wl, -rpath,'/rdsdbbin/postgres-9.6.1.R1/lib',--enable-new-dtags LDFLAGS_EX | LDFLAGS_SL | LIBS | -lpgcommon -lpgport -lxml2 -lssl -lcrypto -lz -lreadline -lrt -lcrypt -ldl -lm VERSION | PostgreSQL 9.6.1 (23 rows)

Working with the orafce extension

The orafce extension provides functions that are common in commercial databases, and can make it easier for you to port a commercial database to PostgreSQL. Amazon RDS for PostgreSQL versions 9.6.6 and later support this extension. For more information about orafce, see the orafce project on GitHub.

Note

Amazon RDS for PostgreSQL doesn't support the utl_file package that is part of the orafce extension. This is because the utl_file schema functions provide read and write operations on operating-system text files, which requires superuser access to the underlying host.

To use the orafce extension

  1. Connect to the DB instance with the master user name that you used to create the DB instance.

    Note

    If you want to enable orafce on a different database in the same instance, use the /c dbname psql command to change from the primary database after initiating the connection.

  2. Enable the orafce extension with the CREATE EXTENSION statement.

    CREATE EXTENSION orafce;
  3. Transfer ownership of the oracle schema to the rds_superuser role with the ALTER SCHEMA statement.

    ALTER SCHEMA oracle OWNER TO rds_superuser;
    Note

    If you want to see the list of owners for the oracle schema, use the \dn psql command.

Accessing external data with the postgres_fdw extension

You can access data in a table on a remote database server with the postgres_fdw extension. If you set up a remote connection from your PostgreSQL DB instance, access is also available to your read replica.

To use postgres_fdw to access a remote database server

  1. Install the postgres_fdw extension.

    CREATE EXTENSION postgres_fdw;
  2. Create a foreign data server using CREATE SERVER.

    CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
  3. Create a user mapping to identify the role to be used on the remote server.

    CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password');
  4. Create a table that maps to the table on the remote server.

    CREATE FOREIGN TABLE foreign_table ( id integer NOT NULL, data text) SERVER foreign_server OPTIONS (schema_name 'some_schema', table_name 'some_table');

Accessing external data with the oracle_fdw extension

You can use the PostgreSQL oracle_fdw extension to provide a foreign data wrapper for easy and efficient access to Oracle databases. For a complete description, see the oracle_fdw documentation.

The oracle_fdw extension is supported on Amazon RDS for PostgreSQL versions 12.7, 13.3, and higher.

Enabling the oracle_fdw extension

To enable the oracle_fdw extension

  • Run the following command using an account that has rds_superuser permissions.

    CREATE EXTENSION oracle_fdw;

Example using a foreign server linked to an Amazon RDS for Oracle database

To create a foreign server linked to an RDS for Oracle database

  1. Note the following on the RDS for Oracle DB instance:

    • Endpoint

    • Port

    • Database name

  2. Create a foreign server.

    test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name'); CREATE SERVER
  3. Grant usage to a user who doesn't have rds_superuser permissions, for example user1.

    test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1; GRANT
  4. Connect as user1 and create a mapping to an Oracle user.

    test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword'); CREATE USER MAPPING
  5. Create a foreign table linked to an Oracle table.

    test=> create foreign table mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE'); CREATE FOREIGN TABLE
  6. Query the foreign table.

    test=> select * from mytab; a --- 1 (1 row)

If the query reports the following error, check your security group and access control list (ACL) to make sure that both instances can communicate.

ERROR: connection for foreign table "mytab" cannot be established DETAIL: ORA-12170: TNS:Connect timeout occurred

Encryption in transit

PostgreSQL-to-Oracle encryption in transit is based on a combination of client and server configuration parameters. For an example using Oracle 21c, see About the Values for Negotiating Encryption and Integrity in the Oracle documentation. The client used for oracle_fdw on Amazon RDS is configured with ACCEPTED, meaning that the encryption depends on the Oracle database server configuration.

If your database is on RDS for Oracle, see Oracle native network encryption to configure the encryption.

pg_user_mapping and pg_user_mappings permissions

User mapping permissions are illustrated using the example roles in the following table. The rdssu1 and rdssu2 users have the rds_superuser role, while user1 doesn't.

Note

You can use the \du metacommand in psql to list existing roles.

test=> \du List of roles Role name | Attributes | Member of -----------------+------------------------------------------------------------+------------------------------------------------------------- rdssu1 | | {rds_superuser} rdssu2 | | {rds_superuser} user1 | | {}

Users with the rds_superuser role can't query the pg_user_mapping table. The following example uses rdssu1.

test=> SET SESSION AUTHORIZATION rdssu1; SET test=> select * from pg_user_mapping; ERROR: permission denied for table pg_user_mapping

On RDS for PostgreSQL, all users—even ones with the rds_superuser role—can see only their own user mappings (umoptions) in the pg_user_mappings table. Following is an example.

test=> SET SESSION AUTHORIZATION rdssu1; SET test=> select * from pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+---------------------------------- 16414 | 16411 | oradb | 16412 | user1 | 16423 | 16411 | oradb | 16421 | rdssu1 | {user=oracleuser,password=mypwd} 16424 | 16411 | oradb | 16422 | rdssu2 | (3 rows) test=> SET SESSION AUTHORIZATION rdssu2; SET test=> select * from pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+---------------------------------- 16414 | 16411 | oradb | 16412 | user1 | 16423 | 16411 | oradb | 16421 | rdssu1 | 16424 | 16411 | oradb | 16422 | rdssu2 | {user=oracleuser,password=mypwd} (3 rows) test=> SET SESSION AUTHORIZATION user1; SET test=> select * from pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+-------------------------------- 16414 | 16411 | oradb | 16412 | user1 | {user=oracleuser,password=mypwd} 16423 | 16411 | oradb | 16421 | rdssu1 | 16424 | 16411 | oradb | 16422 | rdssu2 | (3 rows)

Because of differences in implementation of information_schema._pg_user_mappings and pg_catalog.pg_user_mappings, a manually created rds_superuser requires additional permissions to view passwords in pg_catalog.pg_user_mappings.

No additional permissions are required for an rds_superuser to view passwords in information_schema._pg_user_mappings.

Users who don't have the rds_superuser role can view passwords in pg_user_mappings only under the following conditions:

  • The current user is the user being mapped and owns the server or holds the USAGE privilege on it.

  • The current user is the server owner and the mapping is for PUBLIC.

Restricting password management

You can restrict who can manage database user passwords to a special role. By doing this, you can have more control over password management on the client side.

You enable restricted password management with the static parameter rds.restrict_password_commands and use a role called rds_password. When the parameter rds.restrict_password_commands is set to 1, only users that are members of the rds_password role can run certain SQL commands. The restricted SQL commands are commands that modify database user passwords and password expiration time.

To use restricted password management, your DB instance must be running Amazon RDS for PostgreSQL 10.6 or higher. Because the rds.restrict_password_commands parameter is static, changing this parameter requires a database restart.

When a database has restricted password management enabled, if you try to run restricted SQL commands you get the following error: ERROR: must be a member of rds_password to alter passwords.

Following are some examples of SQL commands that are restricted when restricted password management is enabled.

postgres=> CREATE ROLE myrole WITH PASSWORD 'mypassword'; postgres=> CREATE ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2020-01-01'; postgres=> ALTER ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2020-01-01'; postgres=> ALTER ROLE myrole WITH PASSWORD 'mypassword'; postgres=> ALTER ROLE myrole VALID UNTIL '2020-01-01'; postgres=> ALTER ROLE myrole RENAME TO myrole2;

Some ALTER ROLE commands that include RENAME TO might also be restricted. They might be restricted because renaming a PostgreSQL role that has an MD5 password clears the password.

The rds_superuser role has membership for the rds_password role by default, and you can't change this. You can give other roles membership for the rds_password role by using the GRANT SQL command. We recommend that you give membership to rds_password to only a few roles that you use solely for password management. These roles require the CREATEROLE attribute to modify other roles.

Make sure that you verify password requirements such as expiration and needed complexity on the client side. We recommend that you restrict password-related changes by using your own client-side utility. This utility should have a role that is a member of rds_password and has the CREATEROLE role attribute.