Home / server / Questions / 1074592
Accepted
coolaj86
Asked: 2021-08-15 18:14:23 +0800 CST

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

  • 772

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)

security postgresql

1 Answers

  1. Best Answer

    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.

    • 1