Postgresql – Postgres Aurora RDS password auth fails except with master user

aws-aurorapostgresqlpostgresql-10

I can successfully connect to a Postgres Aurora server using the master password thru the SSH tunnel on the bastion host I have setup.

However, when I create any other users, I can't connect with them even tho all other things are the same configuration wise. I get a DB level auth error.

password authentication failed for user "SomeUser"

I've added the users using these queries.

CREATE USER SomeUser LOGIN ENCRYPTED PASSWORD 'SoMePaSsWoRd';
GRANT CONNECT ON DATABASE postgres TO SomeUser;
ALTER USER SomeUser CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE postgres TO SomeUser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO SomeUser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO SomeUser;

What am I missing here?

Best Answer

Even tho PG is case sensitive in other cases, it requires lowercase usernames. Using a lower case name during login fixed the issue.

Related Solutions

PostgreSQL – Created User Can Access All Databases Without Grants

At the SQL level, every user can indeed connect to a newly created database, until the following SQL command is issued:

REVOKE connect ON DATABASE database_name FROM PUBLIC;

Once done, each user or role that should be able to connect has to be granted explicitly the connect privilege:

GRANT connect ON DATABASE database_name TO rolename;

Edit: In a multi-tenant scenario, more than just the connect privilege would be removed. For multi-tenancy tips and best practices, you may want to read on the postgresql public wiki: Shared Database Hosting and Managing rights in PostgreSQL.

Postgresql: create role, schema and change ownership but not as superuser

Per documentation,

You must own the schema to use ALTER SCHEMA. To rename a schema you must also have the CREATE privilege for the database. To alter the owner, you must also be a direct or indirect member of the new owning role, [...]

So in your case, you either need to be superuser or be a member of the role you are transferring the ownership to.

Ultimately, while I applaud your efforts, you're going to have a hard time managing this without some superuser use. So I'd focus on locking down the superuser use, maybe wrap the steps in a few security-definer functions.

Best Answer

Related Solutions

PostgreSQL – Created User Can Access All Databases Without Grants

Postgresql: create role, schema and change ownership but not as superuser

Related Question