Can I create host-specific users in Postgres? (ex: postgres@localhost)

coolaj86

12/15/22, 2:14 AM

My Question

Can I set permissions on a user (ex: postgres) such that that user is only allowed to login from TCP localhost, but not the Internet?

Trusted Sockets vs Passwords for Remotes

I get that you can initialize postgres to allow local users to login without a password, and remote hosts to login with a password:

initdb \
            -D "$POSTGRES_DATA_DIR/" \
            --username postgres --pwfile "$PWFILE" \
            --auth-local=trust --auth-host=password

Intranet vs Internet

For any system that's connecting across the internet I want to use a user that has a very, very strong (non-memorable) random 128-bit string.

For local and intranet access, however, I'd prefer to be able to have a username and password that I can remember (and type).

Can I do this... or do I just have to set up one user per system that's allowed to connect, with a .pgpass on each?
(I don't want to share keys in plaintext files between computers)

112

1 + 0

postgresql

security

Score:1

Server

mforsetti

12/15/22, 5:47 AM

You haven't provide any info about your PostgreSQL version, I assume you're using PostgreSQL 12.

Can I set permissions on a user (ex: postgres) such that that user is only allowed to login from TCP localhost, but not the Internet?

Yes, use pg_hba.conf. You may want to load this first via hba_file runtime configuration.

For local and intranet access, however, I'd prefer to be able to have a username and password that I can remember (and type).

If you want to add a local-only user protected with a password, add

local <database_name> <local-only_user_name> scram-sha-256

in your pg_hba.conf.

You probably want to create the user first via CREATE ROLE:

CREATE ROLE username LOGIN ENCRYPTED PASSWORD 'insert-your-password-here';

You may also want to check host and hostssl record entry to add to your pg_hba.conf to configure your intranet and internet based authentication.

Check linked documentations for more info.

0 + 2

coolaj86

12/16/22, 9:06 PM

"local: This record matches connection attempts using Unix-domain sockets." (local is not localhost, which is part of what was tripping me up earlier)

coolaj86

12/16/22, 9:07 PM

The comments in my pg_hba.conf did not seem to suggest a username could be specified, but I see the official docs do. Thanks. I'll try this out later today and mark it as the solution if it works. :)

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: Can I create host-specific users in Postgres? (ex: postgres@localhost)

TH: ฉันสามารถสร้างผู้ใช้เฉพาะโฮสต์ใน Postgres ได้หรือไม่ (เช่น postgres@localhost)

RO: Pot crea utilizatori specifici gazdei în Postgres? (ex: postgres@localhost)

RU: Могу ли я создавать пользователей для конкретного хоста в Postgres? (например: postgres@localhost)

VI: Tôi có thể tạo người dùng dành riêng cho máy chủ lưu trữ trong Postgres không? (ví dụ: postgres@localhost)

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.