Menu
In my PostgreSQL database I have 2 users: postgres and myuser.
The default user is postgres, but this user has no permission to query my foreign tables and myuser does. How can I check if I'm connected with the right user?
If I'm using the wrong user, how do I change to the right one?
464
For most systems, the default Postgres user is postgres and a password is not required for authentication. Thus, to add a password, we must first login and connect as the postgres user.
CREATE ROLE sa WITH LOGIN PASSWORD 'some-password. '; CREATE DATABASE master WITH OWNER sa; \c master; Now you are running this script using "psql" command line interface (CLI), so you get the message as below... CREATE ROLE CREATE DATABASE You are now connected to database "master" as user "postgres".
User Data in PostgreSQL All the user-related data is stored in the table named pg_user, which belongs to the schema named pg_catalog.
To get information about current connection from the psql command prompt:
\conninfo This displays more informations, though.
To change user:
\c - a_new_user ‘-’ substitutes for the current database.
To change database and user:
\c a_new_database a_new_user The SQL command to get this information:
SELECT current_user; Examples:
postgres=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432" postgres=# \c a_new_database a_new_user psql (12.1 (Ubuntu 12.1-1.pgdg16.04+1), server 9.5.20) You are now connected to database "a_new_database" as user "a_new_user". a_new_database=# SELECT current_user; current_user -------------- a_new_user (1 row) This page list few interesting functions and variables.
https://www.postgresql.org/docs/current/static/functions-info.html
90
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
