PostgreSQL connections

DataMasque masks PostgreSQL tables in place.

Required privileges
- Database privileges
- Additional privileges
Connection parameters
- PostgreSQL Data Encodings
Encrypted connections
Supported data types
Unsupported key columns

Required privileges

Database privileges

A DataMasque connection for PostgreSQL requires the ability to modify table structure, but PostgreSQL doesn't provide direct ALTER TABLE permissions. Instead, table modification is controlled through ownership. For the connection user to have the necessary access, they either:

own the target table(s), or
belong to a role that owns the target tables, or
belong to a dedicated role created for table ownership.

The method you choose will depend on your existing database setup, security requirements, and whether you need to manage permissions across multiple tables. The following sections explain how to implement each of these permission methods, along with specific SQL commands where needed.

Connection user owns target tables

In this case, no extra GRANTs need to be applied.

Connection user belongs to a role that owns the target tables

If the table is already owned by a different role, grant the connection user access to this role:

GRANT <target table owner role> TO <connection user>;

Connection user belongs to a dedicated ownership role

Create a dedicated owner role for the target table(s). This approach is recommended when you need to manage permissions at scale or across multiple tables and users.

Create a role data_owner (or another username of your choice) without login access. This will be used solely for permission management.
Grant the created role to your connection user.
Set this role as the owner of your target tables.

CREATE ROLE data_owner;
GRANT data_owner TO <connection user>;
ALTER TABLE <target table> OWNER TO data_owner;

Additional privileges

CONNECT and TEMPORARY privileges to the target database.

GRANT CONNECT, TEMPORARY ON DATABASE <target database> to <connection user>;

SELECT privilege on ALL TABLES IN SCHEMA PG_CATALOG.

GRANT SELECT ON ALL TABLES IN SCHEMA PG_CATALOG TO <connection user>;

SELECT privilege on these INFORMATION_SCHEMA tables:

GRANT SELECT ON INFORMATION_SCHEMA.TABLES TO <connection user>;
GRANT SELECT ON INFORMATION_SCHEMA.COLUMNS TO <connection user>;
GRANT SELECT ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS TO <connection user>;
GRANT SELECT ON INFORMATION_SCHEMA.KEY_COLUMN_USAGE TO <connection user>;
GRANT SELECT ON INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE TO <connection user>;

USAGE and CREATE privileges to create tables in the target schema.

GRANT USAGE, CREATE ON SCHEMA <target schema> TO <connection user>;

Ensure the connection user has visibility (in their schema search path) of all tables that will be masked.

ALTER USER <connection user> IN DATABASE <target database> SET SEARCH_PATH TO <target schema>;

Connection parameters

For PostgreSQL databases, the following fields are optional:


*Schema*	The name of the schema that DataMasque will operate on. If not specified, the database user's default schema will be used.
*Data Encoding*	An encoding to be used when retrieving data containing different character sets from the database. Note: This should match the encoding of the data stored, not the character set of the database. The list of supported encodings can be found below.

PostgreSQL Data Encodings

The list of supported data encodings for PostgreSQL databases is as follows:

Character Set	Value (API)
Automatic	`null`
ASCII	`SQL_ASCII`
UTF-8	`UTF8`
ISO 8859-1, ECMA 94	`LATIN1`
ISO 8859-2, ECMA 94	`LATIN2`
ISO 8859-3, ECMA 94	`LATIN3`
ISO 8859-4, ECMA 94	`LATIN4`
ISO 8859-9, ECMA 128	`LATIN5`
ISO 8859-10, ECMA 144	`LATIN6`
ISO 8859-13	`LATIN7`
ISO 8859-14	`LATIN8`
ISO 8859-15	`LATIN9`
ISO 8859-16, ASRO SR 14111	`LATIN10`
Big Five	`BIG5`
Extended UNIX Code-CN	`EUC_CN`
Extended UNIX Code-JP	`EUC_JP`
Extended UNIX Code-JP, JIS X 0213	`EUC_JIS_2004`
Extended UNIX Code-KR	`EUC_KR`
Extended UNIX Code-TW	`EUC_TW`
National Standard	`GB18030`
Extended National Standard	`GBK`
ISO 8859-5, ECMA 113	`ISO_8859_5`
ISO 8859-6, ECMA 114	`ISO_8859_6`
ISO 8859-7, ECMA 118	`ISO_8859_7`
ISO 8859-8, ECMA 121	`ISO_8859_8`
JOHAB	`JOHAB`
KOI8-R	`KOI8R`
KOI8-U	`KOI8U`
Shift JIS	`SJIS`
Shift JIS, JIS X 0213	`SHIFT_JIS_2004`
Unified Hangul Code	`UHC`
Windows CP866	`WIN866`
Windows CP874	`WIN874`
Windows CP1250	`WIN1250`
Windows CP1251	`WIN1251`
Windows CP1252	`WIN1252`
Windows CP1253	`WIN1253`
Windows CP1254	`WIN1254`
Windows CP1255	`WIN1255`
Windows CP1256	`WIN1256`
Windows CP1257	`WIN1257`
Windows CP1258	`WIN1258`

Encrypted connections

DataMasque supports encrypted connections to PostgreSQL instances that have the 'SSL mode' option enabled. No additional configuration is required in DataMasque - DataMasque will use encrypted connections with SSL mode preferred.

Note: DataMasque currently doesn't support sslmode=verify-ca or sslmode=verify-full for PostgreSQL. This is on our roadmap and will be available in a future release.

Supported data types

The following PostgreSQL data types are supported.

Data type	Description
char(n)	Fixed-length character data of `n` size.
varchar(n)	Variable-length character data of `n` size.
text	32 bit string value.
decimal, numeric	Numerical value with decimal precision.
bigint, int8	64 bit integer value.
int, int4, integer	32 bit integer value.
int2, smallint	16 bit integer value.
float4, real	32 bit numerical value with 6 decimal digits of precision.
double	64 bit numerical value with 15 decimal digits of precision.
money	Currency value.
boolean	`true` or `false`.
date	Date value with no time.
timestamp	Timestamp including both a date and time.
timestamp with time zone	Timestamp including time zone.
json	For storing JSON (JavaScript Object Notation) data. Stores an exact copy of the input text.
jsonb	For storing JSON (JavaScript Object Notation) data. Stored in a decomposed binary format.
xml	XML data.

Unsupported key columns

Data Type	Unsupported Format
real	All
double precision	All