Postgres offers many client authentication methods : - Trust Authentication - Password Authentication - GSSAPI Authentication - SSPI Authentication - Ident Authentication - Peer Authentication - LDAP Authentication - RADIUS Authentication - Certificate Authentication - PAM Authentication - BSD Authentication The client authentication method flags on initdb : - -A or --auth - --auth-host - --auth-local …specify the default authentication method for users used in pg_hba.conf. I am making a Wizard tool for creating an initdb string, the string to be executed by someone setting up a new Postgres cluster. This led me to contemplate all the possible values for authentication with initdb. ➥ Of the list above, which are practical/sensible for use with initdb? Obviously trust is the simplest way to get started, though usually best to change soon after the cluster is established. Some are not practical. For example, PAM authentication requires that the user must already exist in the database. So PAM cannot be used for setting up a new cluster with initdb. I am not familiar with several of the other authentication methods. And I am no security expect. This leaves me wondering: Which of the client authentication methods could be used practically/sensibly with initdb?

I would like to use these two pages to enable DSE Unified Authentication and realize a secure environment. I have tried to configure it but it does not work. How is it as a symptom. When I type nodetool -u -pw status I get

"nodetool: Failed to connect to '127.0.0.1:7199' - FailedLoginException: 'Failed to login. Please re-try.'."

is returned. 2. nodetool status command from node2, which has not touched any files related to DSE Unified Authentication from the initial state, confirms that node1 is working as usual. Please let me know if you have any other suggestions on what else I should try or if I should change a file here or there. If I hit nodetool -h drain, etc. from another node, I get an error, but it is not an Error about Authentication, but a ConnectException. DOCUMENTS https://docs.datastax.com/en/dse68-security/docs/Auth/secEnableDseAuthenticator.html https://docs.datastax.com/en/dse68-security/docs/secJmxAuth.html Enabling JMX Native Authentication. procedure is not implemented.

I need to write a trigger which prevents certain users from logging into the production database. We use Active Data Guard. If I hardcode the Linux hostname in the trigger to verify the host is production, then in case of "Role Change", the trigger may not work. Correct? --- This is a three-tier system. The end users communicate via an application server, which will use a generic name. Here is a solution I had been contemplating implementing: 1. Have a table which will check for combination of user + server + database hostname (name of machine where database is hosted). 2. Have a DB_ROLE_CHANGE trigger which will get activated if the production now runs from standby 3. The DB_ROLE_TRIGGER will modify the table so that the database hostname now reflects new production server hostname 4. The APPSERVER and APPUSER will match OK. But since the hostname is now production - it will disallow login.

What is the most efficient approach of authentication, considering an user based authentication system combined with a token based authentication system? For this question please consider the following tables:

create table users (
  id int generated always as identity primary key,
  username text not null unique,
  password text not null
);

create table tokens (
  id int generated always as identity primary key,
  user_id int not null references users(id),
  access_token text unique default generate_random_token(),
  refresh_token text default generate_random_token(),
  updated_on timestamp with time zone default current_timestamp,
  created_on timestamp with time zone default current_timestamp
);

As far as I know to create an user and allow it to authenticate the following steps are mandatory (explained with query statements): 1. Optional: Create an user record.

insert into users (username, password) values ('foo', create_password_hash('bar'));

2. Check if the user can authenticate itself and remember the user's ID:

select * from users where username = 'foo' and password = validate_password_hash('bar', password);

3. Check if the user has an existing token and hand over the access_token:

select access_token from tokens where user_id = 123;

4. Optional: Create a token if the user doesn't have one yet:

insert into tokens (user_id) values (123) returning access_token;

If my assumption for the steps to be taken is right, then I wonder how to minimize these four statements into a single one or have at least some guidance how this could be done more efficient. Also I could imagine that my approach for this use case incorrect and that there is an other more efficient approach, please let me know. --- **BONUS** Perhaps your approach could also cover the process of refreshing a token. Considering the approach explained above the refresh should only occur when a token is found at the third step and according to the updated_on timestamp the token is "expired".

**TEST 1 - PostgreSQL with Active Directory authentication** 1. I have installed PotgreSQL Server v15.3 on Red Hat 8.8 and integrate PostgreSQL authentication using Active Directory. 2. In pg_hba.conf I have set only one settings (all other are commented out):

all all all gss

3. On separate Red Hat 8.8 machine I have installed PostgreSQL Client v15.3 and I have successfully connected to PostgreSQL server using Kerberos authentication and Active Directory using psql tool as _user01_. 4. On separate Windows 2022 Server I have set static IP and DNS address and added computer to Active Directory domain and I can successfully login to Windows with user _user01_. On this PC I have installed PostgreSQL Client v15.3 and I have tried to connect to PostgreSQL server using psql tool with command: psql -h server.adexample.com -p 5432 -d postgres but error is returned: psql: error: connection to server at "server.adexample.com" (192.168.100.36), port 5432 failed: FATAL: no pg_hba.conf entry for host "192.168.100.41", user "user01", database "postgres", no encryption **This is the PROBLEM I am trying to solve.** 5. On Windows where PostgreSQL client v15.3 is installed I monitored network traffic with Wireshark and I see psql client first sends "SSL/TLS" request and server refuses, then sends "clear text" request and server returns error (from step 4). From network packets it looks like psql client does not even tries to connect to server using Kerberos. **TEST 2 - IBM Db2 with Active Directory authentication** 1. Separate Red Hat 8.8 I have installed IBM Db2 database server v11.5 and configure it to authenticate with Active Directory using Kerberos. 2. On separate Red Hat 8.8 I have installed IBM Db2 database client v11.5 and I can successfully connect to Db2 server using Kerberos authentication and Active Directory. 3. On the same Windows 2022 Server (that I have already installed PostgreSQL client and Kerberos authentication fails) I have installed IBM Db2 database client v11.5 and I can successfully connect to IBM Db2 database server using Kerberos Active Directory with Windows user 'user01'. I conclude Windows Kerberos is correctly working. **Questions:** 1. What should I do on PostgreSQL Client on Windows to successfully connect to PostgreSQL Server on Red Hat using Active Directory? 2. Is there some setting I additionally have to add to pg_hba.conf on PostgreSQL Server site? 3. Does PostgreSQL Client support native Windows Kerberos implementation or do I need to additionally install some software? If yes, which one?

I have been reading up on the .pgpass file, but I can’t get it working for me. My .pgpass file looks something like this: 127.0.0.1:5432:accounts:fred:p@55w0rd I set the privilege to 0600 (this is on CentOS Linux) and try:

psql

Where I get the message: >psql: error: FATAL: database "…" does not exist where … is my user name. I can successfully connect if I use:

psql -u … -d accounts

so I don’t think my .pgpass file is doing its job. How can I get it to work?

I have a cluster that consists of: 1) pgpool 3) master 3) slave Using pgpool-II version 4.3.2 and Postgres 14.4 Several users have been created, all are authenticated through pgpool. There was a need to create new users. In pool_passwd I create entries in this way: pg_enc -m -f /etc/pgpool2/pgpool.conf -i users.txt (in users.txt there are user logins and passwords) When connecting with Dbeaver > ERROR: backend authentication failed > Details: backend response with > kind 'E' when expecting 'R'

pg_md5 -m --config-file="/etc/pgpool2/pgpool.conf" -u "user" "pass"

> ERROR: failed to authenticate with backend using SCRAM > Details: valid password not found pool_hba.conf:

host    all         all         10.99.0.0/32          scram-sha-256
host    all         postgres    0.0.0.0/0             scram-sha-256

local   all         all                               md5
local   all         postgres                          scram-sha-256

host    all         all         127.0.0.1/32          md5
host    all         all         ::1/128               md5
host    all         all         0.0.0.0/0             md5

local all all    trust
host  all all    10.99.0.0/32 trust

What could be the problem?

I'm using SQL Server 2016 (SP1) Standard Edition. I have a linked server that uses Windows Authentication and needs to because it connects to a non-SQL Server database. It uses an OLE database provider. I have a third-party application that needs to use this linked server, but its application connects using SQL Server Authentication. Is there a way to allow a SQL Server login to use this linked server?

I want to configure my PostgreSQL server to enable authentication with TLS certificate and password at the same time (for the same connection, not just enable the two authentication mechanisms). Then for a client to authenticate, it must : - provide a valid certificate with the username set in the common name and signed by a trusted root ca - provide a password for this specific user I want to do this to improve safety. This way, even if my client certificate or my root CA are leaked, you still need a password to authenticate. I can't find out how to do that. In this page of the PostgreSQL documentation , it is said that when you use TLS certificate : > No password prompt will be sent to the client Then is it even possible to do this and if yes, how ?

On IBM Db2 v11.5.8.0 on Linux x86_64 I have two local databases DB01 and DB02. Both databases individually are configured to use LDAP authentication. Now I am required to setup a federated access from DB01 to DB02. Looking at CREATE SERVER and CREATE USER MAPPING there is always definition of userid and password. I don't know the userid and password for our users (at least I don't know passwords). Is it possible to define federated access without specifying userid/password at CREATE SERVER and/or CREATE USER MAPPING? What I would like is that database: - passes authentication (userid/password) from DB01 to DB02 or - database does not perform authentication for federated users, because authentication was already made when user connected to DB01 or - something similar, but without me required to define userid/password for every user in CREATE SERVER or CREATE USER MAPPING command. Regards

I'm comparing the password hashes of SQL logins on AG nodes. Of the twenty SQL logins 15 have the same hashes over the 2 nodes and 5 have different hashes. Is a different hash an indicator of a different password, or could a salt have been added to some passwords and not others. Ideally we wouldn't be using SQL logins at all, but our hand is forced in some instances

I am trying to create a simple user with the rights permission to access to any database and can do any actions. When I trying to execute the createUser command I got this error: db.createUser({ user: "mongoadmin" , pwd: "mongoadmin", roles: ["userAdminAnyDatabase", "dbAdminAnyDatabase", "readWriteAnyDatabase"]}) 2015-08-20T17:09:42.300+0000 E QUERY Error: couldn't add user: No role named userAdminAnyDatabase@new_vehicles_catalog The problem above only happen when I enable the auth configuration and I need it. So, How do I create an user with admin permission for any database. I want it because I configured my mongo services to uses authentication connection. If I want to execute a dump of my data I have to use this authentication parameters. Please any help? Using **mongo version 3.0.5**. the service is on **Amazon Linux AMI 2015.03 (HVM), SSD Volume Type - ami-1ecae776**

Test Cube, Test User, connect to it(Azure Analysis Service) in SSMS, right click the cube, browse, click on "Impersonate": And you will see a series of prompts that are specific to a on-premise implementation of AD, so local users, local groups(In this case it's just my local system): How do I select a user from MS Entra through these prompts? If that can't been done is there another easy UI driven way to impersonate a user? Did Microsoft just not extend support for this authentication type to SSMS(at least for Azure Analysis Services)?

I've got a C# app where I need to validate credentials for a DB2 database (V7R3). The way historically we've done this is to append the credentials to a connection string and actually attempt to connect to the database and just see whether the connection succeeds or fails. But is there a better way to do that, like an API for testing credentials or something?

When someone tries to connect to a SQL Server instance, the error shows up: > It's not possible do generate SSPI context. Yesterday we had a blackout (don't know how to say this expression in English) and I had to shut down our servers. Looking for answers, I found this: SQL Server 2008 connectivity issue : cannot generate SSPI context But it doesn't help me, because they're working fine until yesterday. I don't want to change anything. But if a have to, I will change it. Obs: I can't restart the server now. --- Edit: Since I my answer, we haven't had any errors.

I have installed Percona PAm plugin on my Percona server as shown below: mysql> show plugins; ... | auth_pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL | | auth_pam_compat | ACTIVE | AUTHENTICATION | auth_pam_compat.so | GPL | +-------------------------------+----------+--------------------+--------------------+---------+ And also have this configured: cat /etc/pam.d/mysqld auth required pam_sss.so account required pam_sss.so I have a group on the AD server called "dba", and added an AD user 'john.d' in this group. So I would like to log into MySQL using AD users e.g., john.d, who should also inherit all privileges granted to the "dba" group. Below is how this AD group, "dba", is a setup to allow its users access the Percona server: CREATE USER ''@'' IDENTIFIED WITH auth_pam AS 'mysqld,dba=dbarole'; CREATE USER 'dbarole'@'%' IDENTIFIED BY 'dbapass'; GRANT ALL PRIVILEGES ON *.* TO 'dbarole'@'%'; GRANT PROXY ON 'dbarole'@'%' TO ''@''; When I log into MySQL as dbarole, everything works well with all granted privileges. But when I log in as john.d, one of the AD users included into the "dba" AD group, this user does not inherit the privileges (ALL) granted to its group, but only has the USAGE privilege as shown below: mysql> show grants; +-----------------------------------+ | Grants for @ | +-----------------------------------+ | GRANT USAGE ON *.* TO ''@'' | | GRANT PROXY ON 'dba'@'%' TO ''@'' | +-----------------------------------+ 2 rows in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.01 sec) My Question is, how can I get an AD user to inherit privileges granted to their group in MySQL?

I have MySQL 8.0.33 on Ubuntu. In the error log, I see the following warning: > [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead. In my mysql.cnf file, I changed this line: default_authentication_plugin= mysql_native_password to this line: authentication_policy= mysql_native_password but it broke my application: when the application tries to connect to the database, it gets an error Could not connect: The server requested authentication method unknown to the client. What is the correct way to solve this issue and remove the warning, without making too many changes to the application?

I have two Docker containers with a Postgres db in each. I am exposing the Postgres socket of each container to the other one, using Docker volumes, like so:

docker run --rm -itd --name=containerone -p 127.0.0.1:3003:3000 -v containeronepgsocket:/var/run/postgresql -v containertwopgsocket:/var/run/postgresql_containertwo registry.gitlab.com/mycont/mycont

The above makes it so that in the container I have the regular socket /var/run/postgresql, as well as the socket /var/run/postgresql_containertwo My objective is to be able to query each container from within the other one, by using the Postgres peer authentication method (i.e. no need for a password). I have managed to accomplish this when querying through javascript. Here is my code, using the Sequelize package from within container one to query container two:

const sequelize_containertwo = new Sequelize('containertwo', 'myuser', undefined, {
    host: '/var/run/postgresql_containertwo',
    dialect: 'postgres'
});

As you can see, the password is left as undefined. As long as the socket is in the OS, and I am using the right OS user, the peer auth method works and I am able to query the other container. But I also have PL/pgSQL functions and I need to query the other container from within those functions. And this is where I am hitting a problem. From within the functions, I am querying the same container but I also need to query data from the other container. To accomplish this, my first thought was to use dblink. I tried to make something like this:

SELECT * 
  FROM dblink('postgresql:///containertwo_db?host=/var/run/postgresql_containertwo',
    SELECT mycol
    FROM mytable
    )
    AS t1(mycol BIGINT);

This provides the host as a parameter in the connection string. I have already had success connecting to the socket using exactly this connection string but from the docker_entrypoint file. When I try from within dblink, I get the error that a password is required. How can I query the other container from within a PL/pgSQL function and using peer authentication? Can I do it through dblink, and if so, how? Or is there some other way?

I'm using **psql (9.2.23)** on my Dell Latitude 3540 laptop running **Linux CentOS 7**. I want to do a simple thing: create a database username and log in as that username. My operating system username is "davide", and the username I'd like to create and use in psql is "davide3". My laptop name is "laptop". Here are the steps I followed, after having installed PostgreSQL: > [davide@laptop ~]$ sudo su - postgres > > [sudo] password for davide: > > Last login: XXX > > -bash-4.2$ psql > > psql (9.2.23) > > Type "help" for help. > > `postgres=# CREATE USER davide3 WITH PASSWORD 'davide3' CREATEDB > CREATEUSER;` > > CREATE ROLE > > postgres=# \q > > -bash-4.2$ exit > > logout Then I try to login to the database by using the username "davide3": > [davide@laptop ~]$ psql -d postgres -U davide3 > > psql: FATAL: Peer authentication failed for user "davide3" Here's the trouble: I was expecting to get a password-insertion message, and to insert the 'davide3' password, but I did not get anything instead. **What did I do wrong? How to solve this issue?** Thanks

I'm trying to connect to a remote MySQL database for replication. The database is on a machine that's part of a secured network, so after opening the security enough to allow my replication destination access to the 3306 port I wanted to connect through the CLI tool to check the connection. However, no matter how I try to connect, I get the following response: ERROR 1045 (28000): Access denied for user '{user}'@'{IP}' (using password: NO) Despite what this message says, the user that I've been trying to connect with does have a password set and I do provide it for the CLI. I've tried passing the password interactively, inline, and through a .cnf file I've made sure the password is correct, and made sure that the user is expected to come from the IP address of the machine that hosts the replication destination. I've changed the permissions for the user to ALL in case there was a permission that was denying access. I've made sure that the replication destination server does not have any users that can log in with a password in case that was affecting default behavior. None of those have changed the behavior, and unfortunately the only resources I can find online are how to log in if one has forgotten to root password, or how to set the root password after one done a fresh install. EDIT: These are the commands I've used to try to pass the password to the CLI:

Interactively:

mysql --host={destination IP} --protocol=tcp -u remote_user -p

Inline:

mysql --host={destination IP} --protocol=tcp -u remote_user -p{password}

mysql --host={destination IP} --protocol=tcp --user=remote_user --password={password}

Configuration file:

(Config file {user}@{host}.cnf)
[client]
host={host}
user={user}
password={password}
protocol=tcp

(Command)
mysql --defaults-extra-file=/{user}@{host}.cnf

EDIT 2: The user table of the database I'm looking to connect to contains this relevant row:

| Host                  | User             | Password          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
| {-IP address-}        | azure_user       | {-password_hash-} | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | N          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            |          |            |             |              |             0 |           0 |               0 |                    0 |

Unfortunately, this database is running MySQL v.5.1 and so does not contain all of the fields requested. The identifying information has been removed, but the IP is correct for the machine that I am trying to access the database from and so is the username. The password hash was set using ALTER USER 'azure_user'@'{-IP address-}' IDENTIFIED BY '{password}'.