Postgres Pro Enterprise : Documentation: 16: F.47. pgpro_bfile — a composite type to access an external file or S3 storage : Postgres Professional

F.47. pgpro_bfile — a composite type to access an external file or S3 storage
Prev	Up	Appendix F. Additional Supplied Modules and Extensions	Home	Next

F.47. pgpro_bfile — a composite type to access an external file or S3 storage #

F.47.1. Data Structures
F.47.2. Functions
F.47.3. Example

The pgpro_bfile extension introduces a composite type bfile that implements an Oracle-like technique to access an external file or S3 (Simple Storage Service).

F.47.1. Data Structures #

The bfile type contains two fields:

dir_id — the directory identifier in the bfile_directories table
file_name — the name of the external file

Information on directories is stored in the bfile_directories table, which has the following columns:

dir_id — a unique directory identifier of the int32 type.
dir_alias — a unique directory alias.
dir_path — path to the directory.

Although having two unique identifiers dir_id and dir_alias is excessive, dir_id is best for storing in bfile because it requires less storage and ensures faster search, while working with dir_alias is more convenient for a user migrating from Oracle, especially to better understand command semantics.

Information on directory access rights is stored in the bfile_directory_roles table, which has the following columns:

dr_dir_id — a directory identifier of the int32 type, that is, an external reference to the dir_id column in the bfile_directories table.
dr_role_id — ID of a user/role that has access rights on the directory.
dr_rights — a bit array of access rights on the directory (1 means read access, 2 means write access). Choice of a bit array assumes that a need to grant new access rights can arise, such as a need in a separate delete right.

F.47.2. Functions #

Functions to work with directories and to manage directory access rights are implemented in PL/PgSQL, while functions to work with bfile values are implemented in C for better performance.

Table F.42. Functions for Working with Directories

Function Description
`bfile_directory_create (a_dir_alias text, a_dir_path text) returns (int)` Creates a directory for the given alias and path and returns the ID of the created directory. Only a superuser can call this function.
`bfile_directory_delete (a_dir_alias text) returns (void)` Deletes the directory with the given alias. Only a superuser can call this function.
`bfile_directory_rename (a_dir_alias text, a_new_dir_alias text) returns (void)` Changes the alias of the directory. Only a superuser can call this function.
`bfile_directory_set_path (a_dir_alias text, a_dir_path text) returns (void)` Changes the path to the directory with the given alias. Only a superuser can call this function.
`bfile_directory_get_path_by_alias (a_dir_alias text) returns (text)` Returns the path to the directory with the given alias.
`bfile_directory_get_path_by_id (a_dir_id int) returns (text)` Returns the path to the directory with the given ID.
`bfile_directory_get_alias_by_id (a_dir_id int) returns (text)` Returns the alias of the directory with the given ID.
`bfile_directory_get_id_by_alias (a_dir_alias text) returns (int)` Returns the ID of the directory with the given alias.

Function

Description

bfile_directory_create (a_dir_alias text, a_dir_path text) returns (int)

Creates a directory for the given alias and path and returns the ID of the created directory. Only a superuser can call this function.

bfile_directory_delete (a_dir_alias text) returns (void)

Deletes the directory with the given alias. Only a superuser can call this function.

bfile_directory_rename (a_dir_alias text, a_new_dir_alias text) returns (void)

Changes the alias of the directory. Only a superuser can call this function.

bfile_directory_set_path (a_dir_alias text, a_dir_path text) returns (void)

Changes the path to the directory with the given alias. Only a superuser can call this function.

bfile_directory_get_path_by_alias (a_dir_alias text) returns (text)

Returns the path to the directory with the given alias.

bfile_directory_get_path_by_id (a_dir_id int) returns (text)

Returns the path to the directory with the given ID.

bfile_directory_get_alias_by_id (a_dir_id int) returns (text)

Returns the alias of the directory with the given ID.

bfile_directory_get_id_by_alias (a_dir_alias text) returns (int)

Returns the ID of the directory with the given alias.

Table F.43. Functions for Granting and Revoking Directory Access Rights

Function Description
`bfile_grant_directory (a_dir_alias text, a_role_name text, a_access_mask int) returns (void)` Grants access rights on the `a_dir_alias` directory to the `a_role_name` user/role. `a_access_mask` specifies the access to grant (1 — read, 2 — write, 3 — read and write). Only a superuser can call this function.
`bfile_revoke_directory (a_dir_alias text, a_role_name text, a_access_mask int) returns (void)` Revokes access rights on the `a_dir_alias` directory form the `a_role_name` user/role. `a_access_mask` specifies the access to revoke (1 — read, 2 — write, 3 — read and write). Only a superuser can call this function.
`bfile_cleanup_directory_roles () returns (void)` Removes information on directory access rights of deleted users/roles from the `bfile_directory_roles` table. Only a superuser can call this function.

Function

Description

bfile_grant_directory (a_dir_alias text, a_role_name text, a_access_mask int) returns (void)

Grants access rights on the a_dir_alias directory to the a_role_name user/role. a_access_mask specifies the access to grant (1 — read, 2 — write, 3 — read and write). Only a superuser can call this function.

bfile_revoke_directory (a_dir_alias text, a_role_name text, a_access_mask int) returns (void)

Revokes access rights on the a_dir_alias directory form the a_role_name user/role. a_access_mask specifies the access to revoke (1 — read, 2 — write, 3 — read and write). Only a superuser can call this function.

bfile_cleanup_directory_roles () returns (void)

Removes information on directory access rights of deleted users/roles from the bfile_directory_roles table. Only a superuser can call this function.

Table F.44. Functions for Working with bfile Values

Function Description
`bfile_make (a_dir_alias text, a_file_name text) returns (bfile)` Returns the `bfile` structure for the given directory alias and filename.
`bfile_make_dir_id (a_dir_id int, a_file_name text) returns (bfile)` Returns the `bfile` structure for the given directory ID and filename.
`bfile_open (a_bfile bfile[, a_access_mask int]) returns (int)` Opens the file for the given `a_bfile` and returns its descriptor. `a_access_mask` specifies the file access mode (1 — read, 2 — write, 3 — read and write), the default is 1.
`bfile_close (a_handler int) returns (void)` Closes the file for the given descriptor returned by the `bfile_open` function.
`bfile_close_all () returns (int)` Closes all the files that were opened earlier by the `bfile_open` function. Returns the number of closed files.
`bfile_length (a_handler int) returns (bigint)` Returns the size of the open file with the given descriptor.
`bfile_read (a_handler int[, a_offset bigint, a_length bigint]) returns (bytea)` From the open file with the descriptor `a_handler`, reads `a_length` bytes at the `a_offset` offset from the beginning of file and returns the bytes read. By default, `a_length` is -1, which specifies reading to the end of file, and `a_offset` is 0, which specifies reading from the beginning of file.
`bfile_write (a_handler int, a_buffer bytea[, a_offset bigint]) returns (void)` Writes the given buffer `a_buffer` to the open file having the given descriptor `a_handler` at the given `a_offset` offset from the beginning of file. By default, `a_offset` is -1, which means writing from the end of file.
`bfile_fileexists (a_bfile bfile) returns (boolean)` Returns `true` if the file corresponding to `a_bfile` can be used. The function checks that the file opens without errors.
`bfile_length_direct (a_bfile bfile) returns (bigint)` Returns the file size for the given `a_bfile`.
`bfile_read_direct (a_bfile bfile[, a_offset bigint, a_length bigint]) returns (bytea)` For the given `a_bfile`, reads `a_length` bytes at the `a_offset` offset from the beginning of file and returns the bytes read. By default, `a_length` is -1, which means reading to the end of file, and `a_offset` is 0, which means reading from the beginning of file.
`bfile_write_direct (a_bfile bfile, a_buffer bytea) returns (void)` For the given `a_bfile`, writes the given buffer `a_buffer` to a new file. Write access to the `a_bfile` directory is required.
`bfile_delete (a_bfile bfile) returns (void)` Deletes the file for the given `a_bfile`. Write access to the `a_bfile` directory is required.
`bfile_compare (a_bfile_1 bfile, a_bfile_2 bfile[, a_amount bigint, a_offset_1 bigint, a_offset_2 bigint]) returns (int)` When `a_amount`, `a_offset_1` and `a_offset_2` are not specified, if the sizes of `a_bfile_1` and `a_bfile_2` files are different, returns -1 if the size of `a_bfile_1` is less than the size of `a_bfile_2` and 1 otherwise. If the files have equal sizes, the function performs byte-wise comparison of the file data and returns 0 if the comparison shows equal data, -1 if the data in `a_bfile_1` is less than in `a_bfile_2` and 1 if the data in `a_bfile_1` is greater than in `a_bfile_2`. `a_amount` specifies the number of bytes in the given `bfile` values to compare, while `a_offset_1` and `a_offset_2` specify the offsets to start the comparison at.

Function

Description

bfile_make (a_dir_alias text, a_file_name text) returns (bfile)

Returns the bfile structure for the given directory alias and filename.

bfile_make_dir_id (a_dir_id int, a_file_name text) returns (bfile)

Returns the bfile structure for the given directory ID and filename.

bfile_open (a_bfile bfile[, a_access_mask int]) returns (int)

Opens the file for the given a_bfile and returns its descriptor. a_access_mask specifies the file access mode (1 — read, 2 — write, 3 — read and write), the default is 1.

bfile_close (a_handler int) returns (void)

Closes the file for the given descriptor returned by the bfile_open function.

bfile_close_all () returns (int)

Closes all the files that were opened earlier by the bfile_open function. Returns the number of closed files.

bfile_length (a_handler int) returns (bigint)

Returns the size of the open file with the given descriptor.

bfile_read (a_handler int[, a_offset bigint, a_length bigint]) returns (bytea)

From the open file with the descriptor a_handler, reads a_length bytes at the a_offset offset from the beginning of file and returns the bytes read. By default, a_length is -1, which specifies reading to the end of file, and a_offset is 0, which specifies reading from the beginning of file.

bfile_write (a_handler int, a_buffer bytea[, a_offset bigint]) returns (void)

Writes the given buffer a_buffer to the open file having the given descriptor a_handler at the given a_offset offset from the beginning of file. By default, a_offset is -1, which means writing from the end of file.

bfile_fileexists (a_bfile bfile) returns (boolean)

Returns true if the file corresponding to a_bfile can be used. The function checks that the file opens without errors.

bfile_length_direct (a_bfile bfile) returns (bigint)

Returns the file size for the given a_bfile.

bfile_read_direct (a_bfile bfile[, a_offset bigint, a_length bigint]) returns (bytea)

For the given a_bfile, reads a_length bytes at the a_offset offset from the beginning of file and returns the bytes read. By default, a_length is -1, which means reading to the end of file, and a_offset is 0, which means reading from the beginning of file.

bfile_write_direct (a_bfile bfile, a_buffer bytea) returns (void)

For the given a_bfile, writes the given buffer a_buffer to a new file. Write access to the a_bfile directory is required.

bfile_delete (a_bfile bfile) returns (void)

Deletes the file for the given a_bfile. Write access to the a_bfile directory is required.

bfile_compare (a_bfile_1 bfile, a_bfile_2 bfile[, a_amount bigint, a_offset_1 bigint, a_offset_2 bigint]) returns (int)

When a_amount, a_offset_1 and a_offset_2 are not specified, if the sizes of a_bfile_1 and a_bfile_2 files are different, returns -1 if the size of a_bfile_1 is less than the size of a_bfile_2 and 1 otherwise. If the files have equal sizes, the function performs byte-wise comparison of the file data and returns 0 if the comparison shows equal data, -1 if the data in a_bfile_1 is less than in a_bfile_2 and 1 if the data in a_bfile_1 is greater than in a_bfile_2. a_amount specifies the number of bytes in the given bfile values to compare, while a_offset_1 and a_offset_2 specify the offsets to start the comparison at.

F.47.3. Example #

The following example illustrates usage of the bfile type.

We will run a script on the server file system. But first, let's create a directory to store bfile data:

mkdir "/tmp/bfiles"

Now we will use psql to run the script below:

-- Create the pgpro_bfile extension:
CREATE EXTENSION pgpro_bfile;

-- Create a directory in the database to store bfile:
SELECT bfile_directory_create('BFILE_DATA', '/tmp/bfiles');

-- Create the file bfile.data on the file system and add there the value of '0123456789':
SELECT bfile_write_direct(bfile_make('BFILE_DATA', 'bfile.data'), '0123456789');

-- Create the table bfile and add there one record referencing that file:
CREATE TABLE bfile_table(id int, bf bfile);
INSERT INTO bfile_table VALUES (1, bfile_make('BFILE_DATA', 'bfile.data'));

-- Create a user to run the script:
CREATE USER bf_test_user;

-- Grant bf_test_user with read-write access to BFILE_DATA:
SELECT bfile_grant_directory('BFILE_DATA', 'bf_test_user', 3);

-- Grant bf_test_user with rights on bfile_table:
GRANT ALL ON bfile_table TO bf_test_user;

-- Register as bf_test_user:
SET SESSION AUTHORIZATION bf_test_user;

DO $$
DECLARE
  v_bfile bfile;
  v_buffer bytea;
  v_length bigint;
  v_handler int;
BEGIN
  -- Open the file for reading and writing:
  SELECT bfile_open(bf, 3) INTO v_handler FROM bfile_table WHERE id = 1;

  -- This character string will be written to end of file:
  PERFORM bfile_write(v_handler, '_suffix');
  -- This character string will be written from the position 0 and replace '0123456':
  PERFORM bfile_write(v_handler, 'prefix_', 0);

  -- Read the character string from file to a buffer and print out its length and contents:
  v_buffer = bfile_read(v_handler);
  RAISE NOTICE 'Buffer length: %', length(v_buffer);
  RAISE NOTICE 'Buffer content: %', encode(v_buffer, 'escape');

  -- Get and print out the file size:
  v_length = bfile_length(v_handler);
  RAISE NOTICE 'BFILE length: %', v_length;

  -- Close bfile:
  PERFORM bfile_close(v_handler);
END $$;

-- Check the contents of the table:
SELECT encode(b, 'escape'), length(b) from (SELECT bfile_read_direct(bf) b FROM bfile_table) x;

-- Delete the table, user and pgpro_bfile extension:
RESET SESSION AUTHORIZATION;
DROP TABLE bfile_table;
DROP USER bf_test_user;
DROP EXTENSION pgpro_bfile;

The script produces the following output:

 CREATE EXTENSION
 bfile_directory_create
------------------------
                      1
(1 row)

 bfile_write_direct
--------------------

(1 row)

CREATE TABLE
INSERT 0 1
CREATE ROLE
 bfile_grant_directory
-----------------------

(1 row)

GRANT
SET
NOTICE:  Buffer length: 17
NOTICE:  Buffer content: prefix_789_suffix
NOTICE:  BFILE length: 17
DO
      encode       | length
-------------------+--------
 prefix_789_suffix |     17
(1 row)

RESET
DROP TABLE
DROP ROLE
DROP EXTENSION

Prev	Up	Next
F.46. pgpro_application_info — port applications using `DBMS_APPLICATION_INFO` package	Home	F.48. pg_proaudit — enables detailed logging of various security events

epub pdf