I have installed yugabytedb in minikube on my laptop and created a database with owner 'Rodgers'. Then I run the ysqlsh to execute ysql commands from the terminal, one of which is 'CREATE DATABASE ...'.
Problem When I try connecting to the database using an external Go application by providing the application with user as 'Rodgers' and the set password, it fails to connect. I have found out that the tables created were attached to owner 'yugabyte', not 'Rodgers'. But the database to which I have connected and from where am running the CREATE DATABASE command belongs to Rodgers.
What's going on here?
It's best to rehearse all this using "ysqlsh". When everything works there, connecting from any client program (Python, go, ...) etc will work — as long as you have the right driver. The PostgresSQL drivers work with YugabyteDB.
The following is mainly commands for "ysqlsh" — both SQLs and so-called metacommands (the ones starting with backslash). But occasionally, there are commands that you do from the O/S prompt. So you must read the following carefully and then do what it says after each comment — mainly in "ysqlsh" but a couple of times at the O/S prompt. So you can't simply run the script "lights out".
Start with virgin YB single-node cluster (fresh from "yb-create).
$ ysqlsh -h localhost -p 5433 -d yugabyte -U yugabyte
Now follow the script.
-- Shows two "Superuser" users: "postgres" and "yugabyte" (nothing else).
\du
-- Shows two databases: "postgres" and "yugabyte" (nothing else except "system" databases).
-- Both "postgres" and "yugabyte" databases are owned by "postgres".
\l
-- Create a new "ordinary user and connect as that user.
create user rodgers login password 'p';
alter user rodgers createdb;
-- Now connect to database yugabyte as user rodgers
\c yugabyte rodgers
-- Create a new database and check it's there.
create database rog_db owner rodgers;
\l
-- Name | Owner | Encoding | Collate | Ctype | Access privileges
-- -----------------+----------+----------+---------+-------------+-----------------------
...
-- rog_db | rodgers | UTF8 | C | en_US.UTF-8 |
-- ...
-- Now connect to the new "rog_db" database. Works fine.
\c rog_db rodgers
-- Quit "ysqlsh.
\q
Connect again. Works fine.
$ ysqlsh -h localhost -p 5433 -d rog_db -U rodgers
Now carry on with the script.
-- Works fine.
create table t(k int primary key);
-- Inspect it. First "\d", then "\d t".
\d
-- List of relations
-- Schema | Name | Type | Owner
-- --------+------+-------+---------
-- public | t | table | rodgers
\d t
-- Table "public.t"
Column | Type | Collation | Nullable | Default
-- --------+---------+-----------+----------+---------
-- k | integer | | not null |
-- Indexes:
-- "t_pkey" PRIMARY KEY, lsm (k HASH)
-- This is OK for playing. But terrible for real work.
drop table t;
\c rog_db yugabyte
drop schema public;
\c rog_db rodgers
create schema rog_schema authorization rodgers;
-- For future connect commands.
alter user rodgers set search_path = 'rog_schema';
-- for here and now.
set schema 'rog_schema';
create table t(k int primary key);
\d
-- List of relations
-- Schema | Name | Type | Owner
-- ------------+------+-------+---------
-- rog_schema | t | table | rodgers
--------------------------------------------------------------------------------
I just stepped through all of this using "YB-2.2.0.0-b0" on my laptop (macOS Big Sur). It all worked fine.
Please try this in your minikube env and report back.
Regards, Bryn Llewellyn, Technical Product Manager at Yugabyte Inc.