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.

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.

Details for PostgreSQL Credentials

• 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

Details for PostgreSQL Credentials

• 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

Details for PostgreSQL Pgpass Credentials

• 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

Details for PostgreSQL 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

Details for PostgreSQL CLI Credentials

• 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

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

Non-HTTP queries or HTTP calls made through a third-party app (e.g., Python package).

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

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

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 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 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

SELECT setting FROM pg_settings WHERE name LIKE 'server_version_num'

SET ROLE %s