Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 4 · Lesson 28 · 5 min read

SQL Server to PostgreSQL: Week 4 Admin Lab

On this page
  1. The administration lab
  2. Step 1: The sample database
  3. Step 2: Roles with least privilege
  4. Step 3: Prove it
  5. Step 4: Back up, restore, verify
  6. Step 5: Watch the sessions
  7. What a strong answer includes
  8. Check yourself

The administration lab

Before a migrated database goes live, someone has to prove the basics work. Do this drill on a test server. Try it yourself first, then compare with the walkthrough.

  1. Create a trainingdb database with an hr.employees table and three rows.
  2. Create two login roles. hr_admin can read and change hr data. hr_reader can only read it, including tables added later.
  3. Prove both roles behave as intended.
  4. Back up the database and the roles, restore them into a new database, and prove the restore is complete.
  5. While that runs, show which sessions are connected to each database.

Step 1: The sample database

SQL
CREATE SCHEMA hr;
CREATE TABLE hr.employees (
  employee_id  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name    text NOT NULL,
  department   text,
  hire_date    date NOT NULL DEFAULT current_date
);
INSERT INTO hr.employees (full_name, department) VALUES
  ('Maria Gomez', 'Engineering'), ('John Kim', 'Finance'), ('Aisha Rahman', 'Engineering');

Run this in a new database: CREATE DATABASE trainingdb;, then connect to it in DataGrip or with \c trainingdb in psql.

Step 2: Roles with least privilege

Following Day 22, privileges go to group roles and people or applications join them:

SQL
CREATE ROLE hr_read  NOLOGIN;
CREATE ROLE hr_write NOLOGIN;
GRANT hr_read TO hr_write;

GRANT USAGE ON SCHEMA hr TO hr_read;
GRANT SELECT ON ALL TABLES IN SCHEMA hr TO hr_read;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA hr TO hr_write;

ALTER DEFAULT PRIVILEGES IN SCHEMA hr GRANT SELECT ON TABLES TO hr_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA hr GRANT INSERT, UPDATE, DELETE ON TABLES TO hr_write;

CREATE ROLE hr_admin  LOGIN PASSWORD 'change-me';
CREATE ROLE hr_reader LOGIN PASSWORD 'change-me';
GRANT hr_write TO hr_admin;
GRANT hr_read  TO hr_reader;

Two things to notice. The default privileges here apply to tables created later by the role running these statements, so run your migrations as that same role, or add FOR ROLE. And hr_admin can change data but can't drop tables or alter the schema. Schema changes belong to a separate migration role.

Step 3: Prove it

Test as each role, because a superuser bypasses every check:

SQL
SET ROLE hr_reader;
SELECT count(*) FROM hr.employees;                                  -- 3
UPDATE hr.employees SET department = 'Sales' WHERE employee_id = 1;
-- ERROR: permission denied for table employees
RESET ROLE;

CREATE TABLE hr.departments (department_id int PRIMARY KEY, name text);   -- created later
SET ROLE hr_reader;
SELECT count(*) FROM hr.departments;                                -- 0: default privileges worked
RESET ROLE;

SET ROLE hr_admin;
UPDATE hr.employees SET department = 'Sales' WHERE employee_id = 2;  -- works
RESET ROLE;

Step 4: Back up, restore, verify

Following Day 23. The roles live outside the database, so back them up separately:

Shell
pg_dumpall --globals-only -f globals.sql
pg_dump -d trainingdb -Fc -f trainingdb.dump

createdb trainingdb_restored
pg_restore -d trainingdb_restored trainingdb.dump
psql -d trainingdb_restored -c "SELECT count(*) FROM hr.employees;"

On this same server the roles already exist, so the restore's GRANT statements succeed. On a new server, run psql -f globals.sql first. Otherwise every GRANT fails and no application can log in. That's the most common surprise in a first restore drill.

Verification is more than "it ran without errors":

  • Row counts match in every table.
  • Ownership and grants survived: \dp hr.* in psql, or information_schema.role_table_grants.
  • The identity sequence continues correctly: an insert into the restored table gets id 4, not 1.

Step 5: Watch the sessions

Following Day 27, while the restore runs, open a second console:

SQL
SELECT pid, usename, datname, application_name, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE datname IN ('trainingdb', 'trainingdb_restored');

You'll see pg_restore's session, listed with application name pg_restore, next to your own. During a real cutover, this is how you confirm nothing is still writing to the old database.

What a strong answer includes

  • Group roles holding privileges, and login roles only holding memberships.
  • USAGE on the schema, not just table grants.
  • Default privileges, tested with a table created after the grants.
  • Every role tested with SET ROLE, including a failed write as the reader.
  • pg_dumpall --globals-only alongside pg_dump, restored first on a new server.
  • A restore verified by row counts, grants and a test insert.

Check yourself

The global objects were never restored. Roles are cluster-wide, so they aren't in a single database's dump. Run psql -f globals.sql, produced by pg_dumpall --globals-only, on the new server before pg_restore, then rerun the restore so ownership and grants apply.

Default privileges apply only to tables created by the role that defined them. The developer's role created the table. Use ALTER DEFAULT PRIVILEGES FOR ROLE <developer or migration role>, or have all schema changes run as one migration role.

Grants interact through schema USAGE, memberships and default privileges, and a superuser bypasses all of them. Trying the actual operations as the role is the only reliable proof.

Connect as the real application roles, run representative queries and a few writes, compare row counts and checksums with the source, and check that sequences are past the maximum ids. A restore that completes but leaves the app unable to log in or insert isn't a success.

Retrofitting permissions on a running application means discovering every missing grant through production errors. Designing group roles first, and testing with SET ROLE, makes the application's real needs explicit from day one.