Skip to content

Running under non‐privileged user

Jump to bottom
Alex Kasko edited this page Feb 14, 2024 · 1 revision

On this page:

When installer cannot be used

WiltonDB MSI installer creates Windows SCM service that runs PostgreSQL DB instance. This way client applications (like SSMS) can connect to DB immediately after the installation. This service creation step is mandatory - installation cannot be completed without it.

Windows SCM services configuration requires Administrator priviliges. This makes the installer unusable for users that do not have Administrator permissions on the destination machine.

In such cases WiltonDB ZIP bundle can be used instead of MSI installer.

Running WiltonDB from a ZIP bundle

ZIP bundle is provided (along with MSI installer) beginning from 3.3_4.6.1 release. It contains the same files as MSI installer including all the DB engine files and a wiltondb-setup.ps1 script that can be used to create a DB cluster.

Under a non-privileged user:

  1. Download wiltondb3.3_4.6.1.zip (or newer) ZIP bundle and unzip it (File Explorer "Extract All..." context menu option can be used). We assume that it is unzipped in user home directory on path C:\Users\user1\wiltondb3.3_4.6.1:
C:\Users\user1\wiltondb3.3_4.6.1>dir /b
bin
include
lib
share
  1. Run share\installer\wiltondb-setup.ps1 script to initialize a DB cluster. Path to cluster directory needs to be specified with DataDir flag:
C:\Users\user1\wiltondb3.3_4.6.1>powershell -NoProfile -ExecutionPolicy Bypass share\installer\wiltondb-setup.ps1 -DataDir data

Expected output:

C:\Users\user1\wiltondb3.3_4.6.1\bin\initdb.exe -D data -U postgres -E UTF8 --locale=C --no-instructions
The files belonging to this database system will be owned by user "user1". This user must also own the server process.  The database cluster will be initialized with locale "C". The default text search configuration will be set to "english".  Data page checksums are disabled.  fixing permissions on existing directory data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... windows selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Europe/London creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok  initdb: warning: enabling "trust" authentication for local connections initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
C:\Users\user1\wiltondb3.3_4.6.1\bin\pg_ctl.exe -o "-p 5432" start -D data
waiting for server to start....2024-02-13 14:37:55.771 GMT [3564] LOG:  starting PostgreSQL 15.4, compiled by Visual C++ build 1937, 64-bit
2024-02-13 14:37:55.790 GMT [3564] LOG:  listening on IPv6 address "::1", port 5432
2024-02-13 14:37:55.798 GMT [3564] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2024-02-13 14:37:55.908 GMT [1940] LOG:  database system was shut down at 2024-02-13 14:37:31 GMT
2024-02-13 14:37:55.938 GMT [3564] LOG:  database system is ready to accept connections
 done
server started
C:\Users\user1\wiltondb3.3_4.6.1\bin\psql.exe -p 5432 -U postgres -d postgres -a -f C:\Users\user1\wiltondb3.3_4.6.1\share\installer\wiltondb-setup-01.sql -v enable_logging_collector=ON -v postgres_port=5432 -v max_connections=256 -v enable_ssl=OFF -v username=wilton -v username_quoted='wilton' -v user_password='wilton' -v dbname=wilton -v dbname_quoted='wilton'
  ALTER SYSTEM SET logging_collector = :enable_logging_collector;  ALTER SYSTEM ALTER SYSTEM SET log_directory = 'log';  ALTER SYSTEM ALTER SYSTEM SET log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log';  ALTER SYSTEM ALTER SYSTEM SET log_rotation_age = '1d';  ALTER SYSTEM   ALTER SYSTEM SET port = :postgres_port;  ALTER SYSTEM ALTER SYSTEM SET max_connections = :max_connections;  ALTER SYSTEM ALTER SYSTEM SET ssl = :enable_ssl;  ALTER SYSTEM ALTER SYSTEM SET shared_preload_libraries = 'babelfishpg_tds';  ALTER SYSTEM   CREATE USER :username WITH SUPERUSER CREATEDB CREATEROLE PASSWORD :user_password INHERIT;  CREATE ROLE CREATE DATABASE :dbname OWNER :username;  CREATE DATABASE
C:\Users\user1\wiltondb3.3_4.6.1\bin\pg_ctl.exe restart -D data
wa2024-02-13 14:37:57.466 GMT [3564] LOG:  received fast shutdown request
iting for server to shut down...2024-02-13 14:37:57.478 GMT [3564] LOG:  aborting any active transactions
.2024-02-13 14:37:57.497 GMT [3564] LOG:  background worker "logical replication launcher" (PID 4436) exited with exit code 1
2024-02-13 14:37:57.510 GMT [1216] LOG:  shutting down
2024-02-13 14:37:57.514 GMT [1216] LOG:  checkpoint starting: shutdown immediate
..2024-02-13 14:38:00.265 GMT [1216] LOG:  checkpoint complete: wrote 927 buffers (5.7%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.230 s, sync=2.424 s, total=2.755 s; sync files=308, longest=0.075 s, average=0.008 s; distance=4226 kB, estimate=4226 kB
2024-02-13 14:38:00.481 GMT [3564] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2024-02-13 14:38:00.730 GMT [3348] LOG:  redirecting log output to logging collector process
2024-02-13 14:38:00.730 GMT [3348] HINT:  Future log output will appear in directory "log".
 done
server started
C:\Users\user1\wiltondb3.3_4.6.1\bin\psql.exe -p 5432 -U postgres -d wilton -a -f C:\Users\user1\wiltondb3.3_4.6.1\share\installer\wiltondb-setup-02.sql -v username=wilton -v username_quoted='wilton' -v tds_port=1433 -v dbname=wilton -v dbname_quoted='wilton' -v migration_mode='multi-db'
  CREATE EXTENSION IF NOT EXISTS babelfishpg_tds CASCADE;  psql:C:/Users/user1/wiltondb3.3_4.6.1/share/installer/wiltondb-setup-02.sql:2: NOTICE:  installing required extension "babelfishpg_tsql" psql:C:/Users/user1/wiltondb3.3_4.6.1/share/installer/wiltondb-setup-02.sql:2: NOTICE:  installing required extension "uuid-ossp" psql:C:/Users/user1/wiltondb3.3_4.6.1/share/installer/wiltondb-setup-02.sql:2: NOTICE:  installing required extension "babelfishpg_common" CREATE EXTENSION GRANT ALL ON SCHEMA sys to :username;  GRANT   ALTER SYSTEM SET babelfishpg_tds.port = :tds_port;  ALTER SYSTEM ALTER SYSTEM SET babelfishpg_tsql.database_name = :dbname_quoted;  ALTER SYSTEM   ALTER DATABASE :dbname SET babelfishpg_tsql.migration_mode = :migration_mode;  ALTER DATABASE SELECT pg_reload_conf();   pg_reload_conf  ----------------  t (1 row)  CALL sys.initialize_babelfish(:username_quoted);  CALL
C:\Users\user1\wiltondb3.3_4.6.1\bin\pg_ctl.exe stop -D data
waiting for server to shut down..... done
server stopped
Updated data\pg_hba.conf
Setup complete, use '"C:\Users\user1\wiltondb3.3_4.6.1\bin\pg_ctl.exe" start -D "C:\Users\user1\wiltondb3.3_4.6.1\data"' to start the server.
  1. DB cluster is created in C:\Users\user1\wiltondb3.3_4.6.1\data directory and we can use pg_ctl command to run the DB instance:
C:\Users\user1\wiltondb3.3_4.6.1>"C:\Users\user1\wiltondb3.3_4.6.1\bin\pg_ctl.exe" start -D "C:\Users\user1\wiltondb3.3_4.6.1\data"
waiting for server to start....2024-02-13 14:41:20.255 GMT [6744] LOG:  redirecting log output to logging collector process
2024-02-13 14:41:20.255 GMT [6744] HINT:  Future log output will appear in directory "log".
 done
server started
  1. With DB instance up and running we can connect to it with sqlcmd on TDS port 1433:
C:\Users\user1\wiltondb3.3_4.6.1>sqlcmd -S "tcp:127.0.0.1,1433" -U wilton -P wilton
1> select @@version
2> go
version                                                                                                                                                                                                                                         
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Babelfish for PostgreSQL with SQL Server Compatibility - 12.0.2000.8
Feb 11 2024 23:01:05
Copyright (c) Amazon Web Services
PostgreSQL 15.4 (Babelfish 3.3.0)                                                                                       

(1 rows affected)

And with psql on Postgres port 5432:

C:\Users\user1\wiltondb3.3_4.6.1>set PGPASSWORD=wilton

C:\Users\user1\wiltondb3.3_4.6.1>bin\psql -U wilton
psql (15.4)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

wilton=# select version();
                          version
------------------------------------------------------------
 PostgreSQL 15.4, compiled by Visual C++ build 1937, 64-bit
(1 row)
  1. pg_ctl can also be used to stop the DB instance:
"C:\Users\user1\wiltondb3.3_4.6.1\bin\pg_ctl.exe" stop -D "C:\Users\user1\wiltondb3.3_4.6.1\data"
waiting for server to shut down.... done
server stopped

Configuring DB cluster init options

DB cluster creation in previous section assumes that both 5432 and 1433 TCP ports are available on the DB machine.

Though, when setting up DB under non-privileged user, it may be possible that DB machine already has both MSSQL and Postgres installed system-wide and running so both 5432 and 1433 ports are already taken:

> netstat -nta

Active Connections

  Proto  Local Address          Foreign Address        State           Offload State

  TCP    0.0.0.0:1433           0.0.0.0:0              LISTENING       InHost
  TCP    127.0.0.1:5432         0.0.0.0:0              LISTENING       InHost

In this case custom ports need to be used when initializing DB cluster.

wiltondb-setup.ps1 script supports a number of command line options (with default values listed below):

  • -DataDir path\to\dbcluster\dir - path to DB cluster directory to create (mandatory)
  • -InstallDir "<script_dir>\..\.. - path to WiltonDB unzipped bundle
  • -Locale C - DB cluster locale
  • -EnableLoggingCollector:$True - whether logging_collector DB option is enabled
  • -PostgresPort 5432 - TCP port to use for Postgres connections
  • -TdsPort 1433 - TCP port to use for TDS connections
  • -MaxConnections 256 - value to set for max_connections DB option
  • -EnableSSL:$False - whether to generate SSL certificate and to enable ssl DB option
  • -UserName wilton - DB superuser name, used for both Postgres and TDS connections
  • -UserPassword wilton - password for DB superuser
  • -DatabaseName wilton - Babelfish "physical" DB name, cannot be changed after the initialization
  • -MigrationMode multi-db - support for multiple "logical" DBs, affects Postgres schemas naming
  • -UpdateHbaConf:$True - allows remote TCP connections in pg_hba.conf, additional DB config is still required to effectively enable remote access
  • -GrantLocalService:$False - changes DB cluster files ACLs to allow to run DB instance under LocalService user
  • -EnableEventLog:$False - logs script commands to Windows Event log

Example of creating a custom DB cluster with custom TCP ports, single-db migration mode, with SSL enabled and logging collector disabled:

C:\Users\user1\wiltondb3.3_4.6.1>powershell -NoProfile -ExecutionPolicy Bypass share\installer\wiltondb-setup.ps1 ^
    -DataDir data_custom ^
    -PostgresPort 7432 ^
    -TdsPort 7433 ^
    -MigrationMode single-db ^
    -EnableSSL ^
    -EnableLoggingCollector:$False

Expected output (abridged):

C:\Users\user1\wiltondb3.3_4.6.1\bin\initdb.exe -D data_custom -U postgres -E UTF8 --locale=C --no-instructions
[...]
C:\Users\user1\wiltondb3.3_4.6.1\bin\openssl.exe req -config C:\Users\user1\wiltondb3.3_4.6.1\share\openssl.cnf -new -x509 -days 3650 -noenc -text -batch -out data_custom\server.crt -keyout data_custom\server.key -subj /CN=localhost
.+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*.....+.......+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*.....+......+....+............+..+............+...+.......+......+.....+...+....+...+........+..........+.........+...+..+............+....+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ .......+.+.....+....+........+...+......+.+...+......+.....+.............+.....+.......+.........+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*..+...............+....+........+.......+...........+.+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*...........+....+......+........+...............+......+......+.+..............+.+...+..+.......+..+.+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -----
C:\Users\user1\wiltondb3.3_4.6.1\bin\pg_ctl.exe -o "-p 7432" start -D data_custom
waiting for server to start....2024-02-14 18:30:11.675 GMT [10100] LOG:  starting PostgreSQL 15.4, compiled by Visual C++ build 1937, 64-bit
2024-02-14 18:30:11.696 GMT [10100] LOG:  listening on IPv6 address "::1", port 7432
2024-02-14 18:30:11.708 GMT [10100] LOG:  listening on IPv4 address "127.0.0.1", port 7432
2024-02-14 18:30:11.838 GMT [10244] LOG:  database system was shut down at 2024-02-14 18:29:49 GMT
2024-02-14 18:30:11.870 GMT [10100] LOG:  database system is ready to accept connections
 done
server started
[...]
Setup complete, use '"C:\Users\user1\wiltondb3.3_4.6.1\bin\pg_ctl.exe" start -D "C:\Users\user1\wiltondb3.3_4.6.1\data_custom"' to start the server.

See an error or have a question: open an issue or ask in Discord chat.

Home
Getting Started on Windows
Getting Started on Linux
Import data from MSSQL
Backup and restore
Using WiltonDB
Clone this wiki locally