Skip to main content

PostgreSQL Credentials

Description​

General​

  • Documentation: https://www.postgresql.org/docs/13/index.html
  • Summary: PostgreSQL is an open-source relational database management system. This detector catches PostgreSQL credentials in the form of a URI connection string, in a CLI command or stored in a pgpass file.
  • IPs allowlist: IPs allowlisting can be enforced on the server side.
  • Scopes: PostgreSQL supports a role-based access, and thus different level of access can be granted to different users.

Revoke the secret​

A variety of PostgreSQL commands are useful to change a user's role, password or even to drop the concerned user. This documentation page can be a good starting point to get more information on the topics.

Check for suspicious activity​

Auditing PostgreSQL logs can provide information about any suspicious activity on the database.

Details for Postgres assignment​

  • Family: identifiers

  • Category: data_storage

  • High recall: False

  • Validity check available: True

  • Analyzer available: True

  • On-premise instances exist: False

  • Only valid secrets raise an alert: False

  • Minimum number of matches: 4

  • Occurrences found for one million commits: 53.36

  • Prefixed: False

  • PreValidators:

- type: ContentWhitelistPreValidator
  patterns:
    - postgre
    - pgsql
    - psql

Examples​

- text: |
    docker run --name geonetwork -d -p 8080:8080 -e PG_HOST=google.com -e PG_PORT=5434 -e PG_USERNAME=postgres -e PG_PASSWORD=m42ploz2wd geonetwork
  host: google.com
  port: '5434'
  username: postgres
  password: m42ploz2wd

- text: |
    Assignment
    dbusername = doadmin
    dbpassword = vg498hwegw1udp6s
    dbhost = db-postgres-nyc1-76477-do-user-1391911-0.db.ondigitalocean.com
    dbport = 25060
    dbdatabase = defaultdb
    dbsslmode = require
  username: doadmin
  password: vg498hwegw1udp6s
  host: db-postgres-nyc1-76477-do-user-1391911-0.db.ondigitalocean.com
  port: '25060'

- text: |
    server.port=5433
    spring.datasource.postgres.hikari.jdbc-url=jdbc:postgresql://google.com/postgres
    spring.datasource.postgres.hikari.username=postgres
    spring.datasource.postgres.hikari.password=m42ploz2wd
  host: google.com
  port: '5433'
  username: postgres
  password: m42ploz2wd

Details for Postgres assignment attached port​

  • Family: identifiers

  • Category: data_storage

  • High recall: False

  • Validity check available: True

  • Analyzer available: True

  • On-premise instances exist: False

  • Only valid secrets raise an alert: False

  • Minimum number of matches: 4

  • Occurrences found for one million commits: 41.5

  • Prefixed: False

  • PreValidators:

- type: ContentWhitelistPreValidator
  patterns:
    - postgre

Examples​

- text: |
    postgres
    docker run
    --name geonetwork -d
    -p 8080:8080
    -e PG_HOST=google.com:5434
    -e PG_PORT=1212
    -e PG_USERNAME=root
    -e PG_PASSWORD=m42ploz2wd geonetwork
  host: google.com
  port: '5434'
  username: root
  password: m42ploz2wd

- text: |
    server.port=1212
    spring.datasource.url=jdbc:postgresql://google.com:9082/BLUDB
    spring.datasource.username=root
    spring.datasource.password=sup3rstr0ngpass
  host: google.com
  port: '9082'
  username: root
  password: sup3rstr0ngpass

Details for Postgres pgpass​

  • Family: identifiers

  • Category: data_storage

  • High recall: False

  • Validity check available: True

  • Analyzer available: True

  • On-premise instances exist: False

  • Only valid secrets raise an alert: False

  • Minimum number of matches: 5

  • Occurrences found for one million commits: 0.046

  • Prefixed: False

  • PreValidators:

- type: ContentWhitelistPreValidator
  patterns:
    - pgpass

Examples​

- text: secret.postgresql.host.com:5432:mydb:secret-us3r-oo:p@sswOrd
  host: secret.postgresql.host.com
  port: '5432'
  database: mydb
  username: secret-us3r-oo
  password: p@sswOrd
# Test with a password containing colon, should be escaped with a backslash
- text: secret.postgresql.host.com:5432:mydb:secret-us3r-oo:strongp@ss\:93
  host: secret.postgresql.host.com
  port: '5432'
  database: mydb
  username: secret-us3r-oo
  password: strongp@ss\:93

Details for Postgres uri​

  • Family: identifiers

  • Category: data_storage

  • High recall: True

  • Validity check available: True

  • Analyzer available: True

  • On-premise instances exist: False

  • Only valid secrets raise an alert: False

  • Minimum number of matches: 5

  • Occurrences found for one million commits: 266.25

  • Prefixed: True

  • PreValidators:

- type: FilenameBanlistPreValidator
  banlist_extensions: []
  banlist_filenames: []
  check_binaries: false
  include_default_banlist_extensions: true
  ban_markup: false
- type: ContentWhitelistPreValidator
  patterns:
    - postg(res|is)

Examples​

- text: |
    CONNECTION_URI="postgres://postgres:m42ploz2wd@google.com:5434/thegift"
  host: google.com
  port: '5434'
  username: postgres
  password: m42ploz2wd
  scheme: postgres
  database: thegift
  connection_uri: postgres://postgres:m42ploz2wd@google.com:5434/thegift

- text: |
    Connection URI= postgresql://doadmin:vg498hwegw1udp6s@db-postgresql-nyc1-76477-do-user-1391911-0.db.ondigitalocean.com:25060/defaultdb?sslmode=require
  host: db-postgresql-nyc1-76477-do-user-1391911-0.db.ondigitalocean.com
  port: '25060'
  username: doadmin
  password: vg498hwegw1udp6s
  scheme: postgresql
  connection_uri: postgresql://doadmin:vg498hwegw1udp6s@db-postgresql-nyc1-76477-do-user-1391911-0.db.ondigitalocean.com:25060/defaultdb?sslmode=require
  query: 'sslmode=require'
  database: defaultdb

# Test special characters in password
- text: |
    CONNECTION_URI="postgres://postgres:m42p!o@2wd@google.com:5434/thegift"
  host: google.com
  port: '5434'
  username: postgres
  password: m42p!o@2wd
  scheme: postgres
  database: thegift
  connection_uri: postgres://postgres:m42p!o@2wd@google.com:5434/thegift

# Test postgis scheme
- text: |
    CONNECTION_URI="postgis://postgres:m42p!o@2wd@google.com:5434/thegift"
  host: google.com
  port: '5434'
  username: postgres
  password: m42p!o@2wd
  scheme: postgis
  database: thegift
  connection_uri: postgis://postgres:m42p!o@2wd@google.com:5434/thegift

# Test detection in md files
- text: |
    CONNECTION_URI="postgis://postgres:m42p!o@2wd@google.com:5434/thegift"
  host: google.com
  port: '5434'
  username: postgres
  password: m42p!o@2wd
  scheme: postgis
  database: thegift
  connection_uri: postgis://postgres:m42p!o@2wd@google.com:5434/thegift

# Single quotes are properly handled
- text: |
    create_engine('postgresql://postgres:m42ploz2wd@google.com:5432/mydb')
  connection_uri: postgresql://postgres:m42ploz2wd@google.com:5432/mydb
  host: google.com
  port: '5432'
  username: postgres
  scheme: postgresql
  password: m42ploz2wd
  database: mydb

Details for Postgres cli​

  • Family: identifiers

  • Category: data_storage

  • High recall: False

  • Validity check available: True

  • Analyzer available: True

  • On-premise instances exist: False

  • Only valid secrets raise an alert: False

  • Minimum number of matches: 3

  • Occurrences found for one million commits: 0.28

  • Prefixed: False

  • PreValidators:

- type: FilenameBanlistPreValidator
  banlist_extensions: []
  banlist_filenames: []
  check_binaries: false
  include_default_banlist_extensions: true
  ban_markup: true
- type: ContentWhitelistPreValidator
  patterns:
    - psql

Examples​

- text: |
    PGPASSWORD=strongp@ss psql -hdb-postgresql-ams3-58486-do-user-7772205-0.b.db.ondigitalocean.com -Udoadmin -p 25060
  host: db-postgresql-ams3-58486-do-user-7772205-0.b.db.ondigitalocean.com
  username: doadmin
  password: strongp@ss

- text: |
    PGPASSWORD=strongp@ss psql -h12.76.135.14 -Udoadmin -p 25060
  host: 12.76.135.14
  username: doadmin
  password: strongp@ss

# Test with full option names
- text: |
    PGPASSWORD=strongp@ss psql --host=db-postgresql-ams3-58486-do-user-7772205-0.b.db.ondigitalocean.com --username doadmin -p 25060
  host: db-postgresql-ams3-58486-do-user-7772205-0.b.db.ondigitalocean.com
  username: doadmin
  password: strongp@ss

# Test with another order for options
- text: |
    PGPASSWORD=strongp@ss psql --usern=doadmin -p 25060 --hos db-postgresql-ams3-58486-do-user-7772205-0.b.db.ondigitalocean.com
  host: db-postgresql-ams3-58486-do-user-7772205-0.b.db.ondigitalocean.com
  username: doadmin
  password: strongp@ss

# Test with some extra options in the middle of it all
- text: |
    PGPASSWORD=strongp@ss psql --username doadmin -d mydatabase --host db-postgresql-ams3-58486-do-user-7772205-0.b.db.ondigitalocean.com
  host: db-postgresql-ams3-58486-do-user-7772205-0.b.db.ondigitalocean.com
  username: doadmin
  password: strongp@ss

# Multiple whitespaces
- text: |
    PGPASSWORD=strongp@ss psql    --username      doadmin -d mydatabase --host      db-postgresql-ams3-58486-do-user-7772205-0.b.db.ondigitalocean.com
  host: db-postgresql-ams3-58486-do-user-7772205-0.b.db.ondigitalocean.com
  username: doadmin
  password: strongp@ss

Secret Analyzer​

Analysis Method​

  • Provider allows scopes enumeration: False
  • Total network call count: 100
  • Total call count may vary: True

HTTP Calls​

Requests are designed to capture metadata and not to function effectively. No HTTP calls for this analyzer.

Other Calls​


WITH schema_privileges AS (
    SELECT
        quote_ident(pg_namespace.nspname) AS nspname,
        CASE WHEN HAS_SCHEMA_PRIVILEGE(pg_namespace.oid, p.privileges)
            THEN p.privileges
        END AS acls
    FROM
        pg_catalog.pg_namespace,
        (SELECT unnest(ARRAY['CREATE', 'USAGE']) AS privileges) AS p
    WHERE quote_ident(pg_namespace.nspname) NOT IN (
        'information_schema', 'pg_catalog', 'pg_toast'
    )
    UNION
    SELECT
        quote_ident(pg_namespace.nspname) AS nspname,
        'OWNER'
    FROM
        pg_catalog.pg_namespace
        JOIN pg_roles ON pg_roles.oid = nspowner
    WHERE quote_literal(pg_roles.rolname) = quote_literal(current_role)
      AND quote_literal(pg_namespace.nspname) NOT IN (
          'information_schema', 'pg_catalog', 'pg_toast'
      )
)
SELECT
    nspname,
    array_remove(ARRAY_AGG(acls ORDER BY acls), NULL)
FROM schema_privileges
GROUP BY nspname


WITH RECURSIVE role_membership AS (
    SELECT
        oid,
        quote_ident(rolname) AS rolname,
        array_remove(
            ARRAY[
                CASE WHEN rolsuper THEN 'SUPER' END,
                CASE WHEN rolcreaterole THEN 'CREATE ROLE' END,
                CASE WHEN rolcreatedb THEN 'CREATE DATABASE' END,
                CASE WHEN rolbypassrls THEN 'BYPASS RLS' END,
                CASE WHEN rolcanlogin THEN 'LOGIN' END,
                CASE WHEN rolreplication THEN 'REPLICATION ROLE' END
            ],
            null
        ) AS privileges,
        FALSE AS inherit_option
    FROM
        pg_catalog.pg_roles
    WHERE
        quote_literal(rolname) = quote_literal(current_role)
    UNION ALL
    SELECT
        pg_auth_members.roleid,
        quote_ident(member_of_roles.rolname) AS rolname,
        array_remove(
            ARRAY[
                CASE WHEN member_of_roles.rolsuper THEN 'SUPER' END,
                CASE WHEN member_of_roles.rolcreaterole THEN 'CREATE ROLE' END,
                CASE WHEN member_of_roles.rolcreatedb THEN 'CREATE DATABASE' END,
                CASE WHEN member_of_roles.rolbypassrls THEN 'BYPASS RLS' END,
                CASE WHEN member_of_roles.rolcanlogin THEN 'LOGIN' END,
                CASE WHEN member_of_roles.rolreplication THEN 'REPLICATION ROLE' END
            ],
            null
        ) AS privileges,
        pg_auth_members.inherit_option
    FROM
        role_membership
        JOIN pg_catalog.pg_auth_members ON role_membership.oid = pg_auth_members.member
        JOIN pg_catalog.pg_roles AS member_of_roles ON pg_auth_members.roleid = member_of_roles.oid
    WHERE
        role_membership.rolname not like 'pg_%'
),
all_privileges AS (
    SELECT array_agg(DISTINCT privileges) AS privileges
    FROM (
        SELECT UNNEST(privileges) AS privileges
        FROM role_membership
    ) t
    ORDER BY privileges
),
all_roles AS (
    SELECT array_agg(DISTINCT rolname ORDER BY rolname) AS roles
    FROM role_membership
),
all_noinherit_roles AS (
    SELECT array_agg(DISTINCT rolname) AS role_noinherit
    FROM (
        SELECT role_membership.rolname
        FROM role_membership
        WHERE NOT EXISTS (
            SELECT 1
            FROM role_membership AS role_to_test
            JOIN role_membership AS role_with_inherit USING (rolname)
            WHERE role_to_test.rolname = role_membership.rolname
              AND role_with_inherit.inherit_option
        )
    ) t
)
SELECT
    all_privileges.privileges,
    all_roles.roles,
    all_noinherit_roles.role_noinherit
FROM
    all_privileges, all_roles, all_noinherit_roles

SET ROLE %s
SELECT setting FROM pg_settings WHERE name LIKE 'server_version_num'

WITH table_privileges AS (
    SELECT
        quote_ident(t.schemaname) AS schemaname,
        quote_ident(t.tablename) AS tablename,
        n.oid AS schema_oid,
        c.oid AS table_oid,
        CASE WHEN HAS_TABLE_PRIVILEGE(c.oid, p.privileges)
            THEN p.privileges
        END AS acls
    FROM
        pg_catalog.pg_tables AS t
        JOIN pg_catalog.pg_namespace AS n ON t.schemaname = n.nspname
        JOIN pg_catalog.pg_class AS c ON t.tablename = c.relname AND n.oid = c.relnamespace,
        (SELECT unnest(
        ARRAY['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER']
        ) AS privileges) AS p
    WHERE quote_literal(schemaname) NOT IN ('pg_catalog', 'information_schema')
      AND HAS_SCHEMA_PRIVILEGE(n.oid, 'USAGE')
    UNION
    SELECT
        quote_ident(t.schemaname),
        quote_ident(t.tablename),
        n.oid AS schema_oid,
        c.oid AS table_oid,
        'OWNER'
    FROM
        pg_catalog.pg_tables AS t
        JOIN pg_catalog.pg_namespace AS n ON t.schemaname = n.nspname
        JOIN pg_catalog.pg_class AS c ON t.tablename = c.relname AND n.oid = c.relnamespace
    WHERE quote_literal(t.tableowner) = quote_literal(current_role)
)
SELECT
    schemaname,
    tablename,
    CASE WHEN HAS_SCHEMA_PRIVILEGE(schema_oid, 'USAGE')
          THEN pg_total_relation_size(table_oid)
            ELSE NULL
        END AS size,
    array_remove(ARRAY_AGG(acls ORDER BY acls), NULL) as privileges
FROM table_privileges
GROUP BY schemaname, tablename, schema_oid, table_oid


WITH db_privileges AS (
    SELECT
        quote_ident(datname) AS datname,
        CASE WHEN HAS_DATABASE_PRIVILEGE(pg_database.oid, p.privileges)
            THEN
                CASE WHEN (p.privileges = 'CONNECT' AND pg_database.datallowconn)
                THEN p.privileges
                END
            ELSE p.privileges
        END AS db_acls
    FROM
        pg_catalog.pg_database,
        (SELECT unnest(ARRAY['CREATE', 'CONNECT', 'TEMPORARY']) AS privileges) AS p
    UNION
    SELECT
        quote_ident(datname) AS datname,
        'OWNER' AS db_acls
    FROM
        pg_catalog.pg_database
        JOIN pg_roles ON pg_roles.oid = datdba
    WHERE pg_roles.rolname = current_role
)
SELECT
    datname,
    array_remove(ARRAY_AGG(db_acls ORDER BY db_acls), NULL)
FROM db_privileges
GROUP BY datname


WITH procedure_privileges AS (
    SELECT
        quote_ident(pg_namespace.nspname) AS schemaname,
        quote_ident(pg_proc.proname) AS proname,
        'EXECUTE' AS acls
    FROM
        pg_catalog.pg_proc
        JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = pg_proc.pronamespace)
    WHERE
        nspname NOT IN ('pg_catalog', 'information_schema')
        AND has_schema_privilege(pg_namespace.oid, 'USAGE')
        AND has_function_privilege(pg_proc.oid, 'EXECUTE')
    UNION
    SELECT
        quote_ident(pg_namespace.nspname) AS schemaname,
        quote_ident(pg_proc.proname) AS proname,
        'OWNER'
    FROM
        pg_catalog.pg_proc
        JOIN pg_roles ON pg_roles.oid = pg_proc.proowner
        JOIN pg_catalog.pg_namespace ON (pg_namespace.oid = pg_proc.pronamespace)
    WHERE
        nspname NOT IN ('pg_catalog', 'information_schema')
        AND has_schema_privilege(pg_namespace.oid, 'USAGE')
        AND pg_roles.rolname = current_role
)
SELECT
    schemaname,
    proname,
    array_remove(ARRAY_AGG(acls ORDER BY acls), NULL) as privileges
FROM procedure_privileges
GROUP BY schemaname, proname


WITH RECURSIVE role_membership AS (
    SELECT
        oid,
        quote_ident(rolname) AS rolname,
        array_remove(
            ARRAY[
                CASE WHEN rolsuper THEN 'SUPER' END,
                CASE WHEN rolcreaterole THEN 'CREATE ROLE' END,
                CASE WHEN rolcreatedb THEN 'CREATE DATABASE' END,
                CASE WHEN rolbypassrls THEN 'BYPASS RLS' END,
                CASE WHEN rolcanlogin THEN 'LOGIN' END,
                CASE WHEN rolreplication THEN 'REPLICATION ROLE' END
            ],
            null
        ) AS privileges,
        pg_roles.rolinherit,
        FALSE AS inherit_option
    FROM
        pg_catalog.pg_roles
    WHERE
        quote_literal(rolname) = quote_literal(current_role)
    UNION ALL
    SELECT
        pg_auth_members.roleid,
        quote_ident(member_of_roles.rolname) AS rolname,
        array_remove(
            ARRAY[
                CASE WHEN member_of_roles.rolsuper THEN 'SUPER' END,
                CASE WHEN member_of_roles.rolcreaterole THEN 'CREATE ROLE' END,
                CASE WHEN member_of_roles.rolcreatedb THEN 'CREATE DATABASE' END,
                CASE WHEN member_of_roles.rolbypassrls THEN 'BYPASS RLS' END,
                CASE WHEN member_of_roles.rolcanlogin THEN 'LOGIN' END,
                CASE WHEN member_of_roles.rolreplication THEN 'REPLICATION ROLE' END
            ],
            null
        ) AS privileges,
        member_of_roles.rolinherit,
        role_membership.rolinherit AS inherit_option
    FROM
        role_membership
        JOIN pg_catalog.pg_auth_members ON role_membership.oid = pg_auth_members.member
        JOIN pg_catalog.pg_roles AS member_of_roles ON pg_auth_members.roleid = member_of_roles.oid
    WHERE
        role_membership.rolname not like 'pg_%'
),
all_privileges AS (
    SELECT array_agg(DISTINCT privileges) AS privileges
    FROM (
        SELECT UNNEST(privileges) AS privileges
        FROM role_membership
    ) t
    ORDER BY privileges
),
all_roles AS (
    SELECT array_agg(DISTINCT rolname ORDER BY rolname) AS roles
    FROM role_membership
),
all_noinherit_roles AS (
    SELECT array_agg(DISTINCT rolname) AS role_noinherit
    FROM (
        SELECT role_membership.rolname
        FROM role_membership
        WHERE NOT EXISTS (
            SELECT 1
            FROM role_membership AS role_to_test
            JOIN role_membership AS role_with_inherit USING (rolname)
            WHERE role_to_test.rolname = role_membership.rolname
              AND role_with_inherit.inherit_option
        )
    ) t
)
SELECT
    all_privileges.privileges,
    all_roles.roles,
    all_noinherit_roles.role_noinherit
FROM
    all_privileges, all_roles, all_noinherit_roles

Last updated on

Something we didn’t cover?

See our Roadmap

Subscribe on GitHub

Submit a request

API status