SQL Server to PostgreSQL: Week 4 Admin Lab
On this page
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.
- Create a
trainingdbdatabase with anhr.employeestable and three rows. - Create two login roles.
hr_admincan read and changehrdata.hr_readercan only read it, including tables added later. - Prove both roles behave as intended.
- Back up the database and the roles, restore them into a new database, and prove the restore is complete.
- While that runs, show which sessions are connected to each database.
Step 1: The sample database
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:
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:
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:
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, orinformation_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:
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-onlyalongsidepg_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.