How to create a new user and a new database in postgres, on ubuntu?

Question

linux

I am completely new to Ubuntu, before that I always created databases and tables in the open server on Windows. As I understand it, there is a default postgres superuser, which you can enter through the terminal by typing sudo -i -u postgres and inside it already work with the database. But now I have a need to create a new user, separate from postgres and create new databases inside it, BUT besides this, I would also like to connect this database to pgadmin, which in turn requires a login and password from the database, how can I create a new one base, in the new user and where to get the login and password from this base?

Answer 1

Password authentication is just one of many options available. Although the most simple and common. Which authentication method the DBMS will require depends on the list of rules in pg_hba.conf. Very rarely, you need something other than the peer service rule for postgres (it is because of it that psql after sudo -iu postgres does not ask for any passwords, but only from postgres) and md5 for everything else.
Basically, creating a new database with a separate owner user looks like this (from the superuser of the database):

CREATE USER "$NEWOWNER" PASSWORD '$NEWPASS';
CREATE DATABASE ${NEWDB} OWNER "$NEWOWNER";
REVOKE ALL ON DATABASE $NEWDB FROM public;

In the next answer, you are incorrectly prompted that "you can immediately specify which user is allowed access." create database will allow access to everyone (but it is the connection to the database, and not to everything inside the database - which is a common problem of misunderstanding the rights). And it makes sense, on the contrary, to take away access from all others, i.e. from public. Owner, of course, will save access.
A little closer to production, it makes sense to use this template:

CREATE USER "$NEWOWNER" PASSWORD '$NEWPASS';
CREATE DATABASE ${NEWDB} OWNER "$NEWOWNER";
CREATE ROLE ${NEWDB}_role;
CREATE ROLE ${NEWDB}_ro;
CREATE ROLE ${NEWDB}_rw;
GRANT ${NEWDB}_role TO ${NEWDB}_ro, ${NEWDB}_rw;
REVOKE ALL ON DATABASE $NEWDB FROM public;
GRANT CONNECT ON DATABASE $NEWDB TO ${NEWDB}_role;
GRANT ${NEWDB}_rw TO "$NEWOWNER";
\c $NEWDB
ALTER SCHEMA public OWNER TO "$NEWOWNER";
REVOKE ALL ON SCHEMA public FROM public;
GRANT USAGE ON SCHEMA public TO ${NEWDB}_ro, ${NEWDB}_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON SEQUENCES TO ${NEWDB}_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT,USAGE ON SEQUENCES TO ${NEWDB}_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON TABLES TO ${NEWDB}_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO ${NEWDB}_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE "$NEWOWNER" GRANT SELECT ON SEQUENCES TO ${NEWDB}_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE "$NEWOWNER" GRANT SELECT,USAGE ON SEQUENCES TO ${NEWDB}_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE "$NEWOWNER" GRANT SELECT ON TABLES TO ${NEWDB}_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE "$NEWOWNER" GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO ${NEWDB}_rw;

As a result, we get:
- the user-owner of the database, which is intended to perform all sorts of data schema migrations
- the role_base_name_role that can be given to other users to be able to connect to this database, but without access to application tables (for example, we use it for monitoring)
- the role_base_name_ro that will give select-only rights to all (including future) tables created from the user-owner of this database
- role base_name_rw - respectively to perform select,insert,update,delete