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

PostgreSQL Roles vs SQL Server Logins and Users

On this page
  1. A SQL Server security script to translate
  2. Logins and users become roles
  3. Group roles instead of db_datareader and db_datawriter
  4. Default privileges for future tables
  5. Testing permissions with SET ROLE
  6. Windows authentication and passwords
  7. Check yourself

A SQL Server security script to translate

Here's a typical SQL Server setup: a reporting login that can read the Sales database, and an application login that can read and write it.

SQL
-- T-SQL
CREATE LOGIN ReportUser WITH PASSWORD = 'change-me';
CREATE LOGIN AppUser    WITH PASSWORD = 'change-me';
USE Sales;
CREATE USER ReportUser FOR LOGIN ReportUser;
CREATE USER AppUser    FOR LOGIN AppUser;
ALTER ROLE db_datareader ADD MEMBER ReportUser;
ALTER ROLE db_datareader ADD MEMBER AppUser;
ALTER ROLE db_datawriter ADD MEMBER AppUser;

Before translating, you need one idea: PostgreSQL has no separate logins and users.

Logins and users become roles

SQL ServerPostgreSQL
Login, instance-wideRole with LOGIN
User, per database, mapped to a loginThe same role. Nothing to map.
Database role (db_datareader)Role without LOGIN, used as a group
sysadminRole with SUPERUSER
ALTER ROLE r ADD MEMBER uGRANT r TO u
EXECUTE AS USERSET ROLE r

Roles belong to the whole cluster, not to one database. You create a role once, and then grant it access to whichever databases and objects it needs. CREATE USER still exists, but it's just CREATE ROLE ... LOGIN.

There are also no built-in db_datareader and db_datawriter roles per database. You build your own group roles, which is a few more lines but much more precise.

Group roles instead of db_datareader and db_datawriter

SQL
CREATE SCHEMA sales;
CREATE TABLE sales.orders (order_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, total numeric);

-- Group roles replace db_datareader and db_datawriter
CREATE ROLE sales_read  NOLOGIN;
CREATE ROLE sales_write NOLOGIN;
GRANT sales_read TO sales_write;              -- writers can read

-- Login roles replace logins and users
CREATE ROLE report_user LOGIN PASSWORD 'change-me';
CREATE ROLE app_user    LOGIN PASSWORD 'change-me' CONNECTION LIMIT 50;
GRANT sales_read  TO report_user;
GRANT sales_write TO app_user;

-- Privileges, layer by layer
GRANT CONNECT ON DATABASE postgres TO sales_read;          -- use your database name
GRANT USAGE ON SCHEMA sales TO sales_read;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO sales_read;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO sales_write;

Notice the three layers. A role needs CONNECT on the database, USAGE on the schema, and a privilege on the table. Missing USAGE is the classic mistake: the error says "permission denied for schema" even though the table grant is there.

Members get their group's privileges automatically, the INHERIT behavior, which is the default. For a break-glass admin role you'd rather people activate on purpose, PostgreSQL 16+ lets you grant membership with WITH INHERIT FALSE, and the member then runs SET ROLE to use it.

Default privileges for future tables

db_datareader covers tables created later. GRANT ... ON ALL TABLES IN SCHEMA doesn't. It covers only tables that exist when you run it. Next week's migration adds sales.refunds, and nobody can read it.

Default privileges fill the gap. They apply to objects created later by a specific role, usually the one your migrations run as:

SQL
CREATE ROLE migrator LOGIN PASSWORD 'change-me';
GRANT USAGE, CREATE ON SCHEMA sales TO migrator;

ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA sales
  GRANT SELECT ON TABLES TO sales_read;
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA sales
  GRANT INSERT, UPDATE, DELETE ON TABLES TO sales_write;

If tables are created by a different role than the one named in FOR ROLE, the rule never fires. That's the usual reason for "the new table has no grants".

For tools that really should read everything, such as backup or monitoring agents, PostgreSQL has predefined roles: pg_read_all_data, pg_write_all_data and pg_monitor. They're the nearest equivalent to server-wide read roles.

Testing permissions with SET ROLE

Superusers bypass every check, so testing as postgres proves nothing. Switch to the role:

SQL
SET ROLE report_user;
SELECT count(*) FROM sales.orders;                 -- works
INSERT INTO sales.orders (total) VALUES (10);      -- ERROR: permission denied for table orders
RESET ROLE;

Windows authentication and passwords

There's no direct equivalent of Windows integrated authentication. PostgreSQL authenticates through pg_hba.conf (Day 2), and the options include:

  • scram-sha-256 passwords, the default choice.
  • gss (Kerberos) or sspi on Windows, for Active Directory single sign-on. This needs real setup work.
  • ldap, to check passwords against Active Directory.
  • Cloud identity, such as Microsoft Entra ID on Azure Database for PostgreSQL or IAM authentication on Amazon RDS.

Decide this early. It affects every connection string. SQL Server logins' password hashes can't be migrated either, so plan to set new passwords or move to directory-based authentication.

In DataGrip, roles appear at the data source level, not inside a database, because they belong to the whole cluster.

Check yourself

GRANT USAGE ON SCHEMA sales to the role, or to a group it belongs to. Table privileges only work once the role can reach the schema.

ALTER DEFAULT PRIVILEGES FOR ROLE <creating role> IN SCHEMA ... GRANT SELECT ON TABLES TO <group>, set up for the role that runs your migrations.

One login role for the person, a member of whichever group roles carry the privileges. Roles are cluster-wide, so there's no per-database user to create.

On a domain, PostgreSQL can use GSSAPI with Kerberos so users sign in with their Windows identity. On managed services, the usual equivalent is the platform's identity integration, such as Microsoft Entra ID authentication on Azure or IAM authentication on AWS, with database roles mapped to those identities.

Both are roles. A role with LOGIN can connect and behaves like a user, while a role without it acts as a group that holds privileges. Granting privileges to group roles and adding login roles as members reproduces SQL Server's database roles cleanly.