How to get psql to prompt for a password with the superuser postgres?
I'm using PostgreSQL 9.6.2 on Windows 7 and when I start psql from the command prompt with:
psql -U myusername
it asks for a password just fine and logs in.
However if I do:
psql -U postgres
it's gonna log in directly to the main database with superuser rights without asking for any kind of password. If I type in:
psql -U postgres -W
then it's gonna ask for the su password which is by the way the one that I set during the one-click installation.
I've checked the pg_hba.conf and it's set to md5 for all users, all databases. I've even changed the password from the db with: postgres=# ALTER USER postgres PASSWORD 'myPassword';, to no avail.
The question is why when I log as a normal user it asks for a password and doesn't when i log as superuser? It's not a major issue as I can access my databases in either case, but it just doesn't seem safe at all. Any workaround would be very much appreciated.
postgresql windows authentication postgresql-9.6
edited Feb 24 '17 at 10:01
dezso
22k116096
asked Feb 24 '17 at 1:42
pazeltovpazeltov
2413
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I'm using PostgreSQL 9.6.2 on Windows 7 and when I start psql from the command prompt with:
psql -U myusername
it asks for a password just fine and logs in.
However if I do:
psql -U postgres
it's gonna log in directly to the main database with superuser rights without asking for any kind of password. If I type in:
psql -U postgres -W
then it's gonna ask for the su password which is by the way the one that I set during the one-click installation.
I've checked the pg_hba.conf and it's set to md5 for all users, all databases. I've even changed the password from the db with: postgres=# ALTER USER postgres PASSWORD 'myPassword';, to no avail.
The question is why when I log as a normal user it asks for a password and doesn't when i log as superuser? It's not a major issue as I can access my databases in either case, but it just doesn't seem safe at all. Any workaround would be very much appreciated.
postgresql windows authentication postgresql-9.6
edited Feb 24 '17 at 10:01
dezso
22k116096
asked Feb 24 '17 at 1:42
pazeltovpazeltov
2413
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Do you have apgpass.conf? postgresql.org/docs/current/static/libpq-pgpass.html
– a_horse_with_no_name
Feb 24 '17 at 6:54
Can you please show the fullpg_hba.conf? I mean all lines that are not commented out.
– dezso
Feb 24 '17 at 7:35
I had a look at thepgpass.confand the postgres password was indeed stored there in plain text. I commented the lines out and now psql prompts for password when I login as postgres.
– pazeltov
Feb 24 '17 at 15:04
add a comment |
I'm using PostgreSQL 9.6.2 on Windows 7 and when I start psql from the command prompt with:
psql -U myusername
it asks for a password just fine and logs in.
However if I do:
psql -U postgres
it's gonna log in directly to the main database with superuser rights without asking for any kind of password. If I type in:
psql -U postgres -W
then it's gonna ask for the su password which is by the way the one that I set during the one-click installation.
I've checked the pg_hba.conf and it's set to md5 for all users, all databases. I've even changed the password from the db with: postgres=# ALTER USER postgres PASSWORD 'myPassword';, to no avail.
The question is why when I log as a normal user it asks for a password and doesn't when i log as superuser? It's not a major issue as I can access my databases in either case, but it just doesn't seem safe at all. Any workaround would be very much appreciated.
postgresql windows authentication postgresql-9.6
edited Feb 24 '17 at 10:01
dezso
22k116096
asked Feb 24 '17 at 1:42
pazeltovpazeltov
2413
I'm using PostgreSQL 9.6.2 on Windows 7 and when I start psql from the command prompt with:
psql -U myusername
it asks for a password just fine and logs in.
However if I do:
psql -U postgres
it's gonna log in directly to the main database with superuser rights without asking for any kind of password. If I type in:
psql -U postgres -W
then it's gonna ask for the su password which is by the way the one that I set during the one-click installation.
I've checked the pg_hba.conf and it's set to md5 for all users, all databases. I've even changed the password from the db with: postgres=# ALTER USER postgres PASSWORD 'myPassword';, to no avail.
The question is why when I log as a normal user it asks for a password and doesn't when i log as superuser? It's not a major issue as I can access my databases in either case, but it just doesn't seem safe at all. Any workaround would be very much appreciated.
postgresql windows authentication postgresql-9.6
postgresql windows authentication postgresql-9.6
edited Feb 24 '17 at 10:01
dezso
22k116096
asked Feb 24 '17 at 1:42
pazeltovpazeltov
2413
edited Feb 24 '17 at 10:01
dezso
22k116096
asked Feb 24 '17 at 1:42
pazeltovpazeltov
2413
edited Feb 24 '17 at 10:01
dezso
22k116096
edited Feb 24 '17 at 10:01
dezso
22k116096
edited Feb 24 '17 at 10:01
dezso
22k116096
22k116096
asked Feb 24 '17 at 1:42
pazeltovpazeltov
2413
asked Feb 24 '17 at 1:42
pazeltovpazeltov
2413
asked Feb 24 '17 at 1:42
pazeltovpazeltov
2413
2413
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 6 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
Do you have apgpass.conf? postgresql.org/docs/current/static/libpq-pgpass.html
– a_horse_with_no_name
Feb 24 '17 at 6:54
Can you please show the fullpg_hba.conf? I mean all lines that are not commented out.
– dezso
Feb 24 '17 at 7:35
I had a look at thepgpass.confand the postgres password was indeed stored there in plain text. I commented the lines out and now psql prompts for password when I login as postgres.
– pazeltov
Feb 24 '17 at 15:04
add a comment |
1
Do you have apgpass.conf? postgresql.org/docs/current/static/libpq-pgpass.html
– a_horse_with_no_name
Feb 24 '17 at 6:54
Can you please show the fullpg_hba.conf? I mean all lines that are not commented out.
– dezso
Feb 24 '17 at 7:35
I had a look at thepgpass.confand the postgres password was indeed stored there in plain text. I commented the lines out and now psql prompts for password when I login as postgres.
– pazeltov
Feb 24 '17 at 15:04
1
1
Do you have a
pgpass.conf? postgresql.org/docs/current/static/libpq-pgpass.html– a_horse_with_no_name
Feb 24 '17 at 6:54
Do you have a
pgpass.conf? postgresql.org/docs/current/static/libpq-pgpass.html– a_horse_with_no_name
Feb 24 '17 at 6:54
Can you please show the full
pg_hba.conf? I mean all lines that are not commented out.– dezso
Feb 24 '17 at 7:35
Can you please show the full
pg_hba.conf? I mean all lines that are not commented out.– dezso
Feb 24 '17 at 7:35
I had a look at the
pgpass.conf and the postgres password was indeed stored there in plain text. I commented the lines out and now psql prompts for password when I login as postgres.– pazeltov
Feb 24 '17 at 15:04
I had a look at the
pgpass.conf and the postgres password was indeed stored there in plain text. I commented the lines out and now psql prompts for password when I login as postgres.– pazeltov
Feb 24 '17 at 15:04
add a comment |
2 Answers
2
active
oldest
votes
As per @a_horse_with_no_name's comment, have a look in your pgpass.conf (.pgpass on Linux) file if you have one, and see if there's an entry for the postgres user:
The file .pgpass in a user's home directory or the file referenced by
PGPASSFILE can contain passwords to be used if the connection requires
a password (and no password has been specified otherwise). On
Microsoft Windows the file is named %APPDATA%postgresqlpgpass.conf
(where %APPDATA% refers to the Application Data subdirectory in the
user's profile).
answered Dec 30 '18 at 22:48
James DouglasJames Douglas
1034
add a comment |
why when I log as a normal user it asks for a password and doesn't when i log as superuser?
it just doesn't seem safe at all.
The small but important detail that you're missing is that nobody but you (or your DBA Team) should be allowed anywhere near the database server and absolutely should not have the postgres account credentials. Without these, "logging in as postgres, with or without a password" simply won't be a option for them.
Remember: As the DBA, you'll have to cleanup the "mess" that other people make, so always keep the biggest and best tools for yourself!
answered Dec 31 '18 at 11:52
Phill W.Phill W.
65131
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f165387%2fhow-to-get-psql-to-prompt-for-a-password-with-the-superuser-postgres%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
As per @a_horse_with_no_name's comment, have a look in your pgpass.conf (.pgpass on Linux) file if you have one, and see if there's an entry for the postgres user:
The file .pgpass in a user's home directory or the file referenced by
PGPASSFILE can contain passwords to be used if the connection requires
a password (and no password has been specified otherwise). On
Microsoft Windows the file is named %APPDATA%postgresqlpgpass.conf
(where %APPDATA% refers to the Application Data subdirectory in the
user's profile).
answered Dec 30 '18 at 22:48
James DouglasJames Douglas
1034
add a comment |
As per @a_horse_with_no_name's comment, have a look in your pgpass.conf (.pgpass on Linux) file if you have one, and see if there's an entry for the postgres user:
The file .pgpass in a user's home directory or the file referenced by
PGPASSFILE can contain passwords to be used if the connection requires
a password (and no password has been specified otherwise). On
Microsoft Windows the file is named %APPDATA%postgresqlpgpass.conf
(where %APPDATA% refers to the Application Data subdirectory in the
user's profile).
answered Dec 30 '18 at 22:48
James DouglasJames Douglas
1034
add a comment |
As per @a_horse_with_no_name's comment, have a look in your pgpass.conf (.pgpass on Linux) file if you have one, and see if there's an entry for the postgres user:
The file .pgpass in a user's home directory or the file referenced by
PGPASSFILE can contain passwords to be used if the connection requires
a password (and no password has been specified otherwise). On
Microsoft Windows the file is named %APPDATA%postgresqlpgpass.conf
(where %APPDATA% refers to the Application Data subdirectory in the
user's profile).
answered Dec 30 '18 at 22:48
James DouglasJames Douglas
1034
As per @a_horse_with_no_name's comment, have a look in your pgpass.conf (.pgpass on Linux) file if you have one, and see if there's an entry for the postgres user:
The file .pgpass in a user's home directory or the file referenced by
PGPASSFILE can contain passwords to be used if the connection requires
a password (and no password has been specified otherwise). On
Microsoft Windows the file is named %APPDATA%postgresqlpgpass.conf
(where %APPDATA% refers to the Application Data subdirectory in the
user's profile).
answered Dec 30 '18 at 22:48
James DouglasJames Douglas
1034
answered Dec 30 '18 at 22:48
James DouglasJames Douglas
1034
answered Dec 30 '18 at 22:48
James DouglasJames Douglas
1034
answered Dec 30 '18 at 22:48
James DouglasJames Douglas
1034
1034
add a comment |
add a comment |
why when I log as a normal user it asks for a password and doesn't when i log as superuser?
it just doesn't seem safe at all.
The small but important detail that you're missing is that nobody but you (or your DBA Team) should be allowed anywhere near the database server and absolutely should not have the postgres account credentials. Without these, "logging in as postgres, with or without a password" simply won't be a option for them.
Remember: As the DBA, you'll have to cleanup the "mess" that other people make, so always keep the biggest and best tools for yourself!
answered Dec 31 '18 at 11:52
Phill W.Phill W.
65131
add a comment |
why when I log as a normal user it asks for a password and doesn't when i log as superuser?
it just doesn't seem safe at all.
The small but important detail that you're missing is that nobody but you (or your DBA Team) should be allowed anywhere near the database server and absolutely should not have the postgres account credentials. Without these, "logging in as postgres, with or without a password" simply won't be a option for them.
Remember: As the DBA, you'll have to cleanup the "mess" that other people make, so always keep the biggest and best tools for yourself!
answered Dec 31 '18 at 11:52
Phill W.Phill W.
65131
add a comment |
why when I log as a normal user it asks for a password and doesn't when i log as superuser?
it just doesn't seem safe at all.
The small but important detail that you're missing is that nobody but you (or your DBA Team) should be allowed anywhere near the database server and absolutely should not have the postgres account credentials. Without these, "logging in as postgres, with or without a password" simply won't be a option for them.
Remember: As the DBA, you'll have to cleanup the "mess" that other people make, so always keep the biggest and best tools for yourself!
answered Dec 31 '18 at 11:52
Phill W.Phill W.
65131
why when I log as a normal user it asks for a password and doesn't when i log as superuser?
it just doesn't seem safe at all.
The small but important detail that you're missing is that nobody but you (or your DBA Team) should be allowed anywhere near the database server and absolutely should not have the postgres account credentials. Without these, "logging in as postgres, with or without a password" simply won't be a option for them.
Remember: As the DBA, you'll have to cleanup the "mess" that other people make, so always keep the biggest and best tools for yourself!
answered Dec 31 '18 at 11:52
Phill W.Phill W.
65131
answered Dec 31 '18 at 11:52
Phill W.Phill W.
65131
answered Dec 31 '18 at 11:52
Phill W.Phill W.
65131
answered Dec 31 '18 at 11:52
Phill W.Phill W.
65131
65131
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f165387%2fhow-to-get-psql-to-prompt-for-a-password-with-the-superuser-postgres%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Do you have a
pgpass.conf? postgresql.org/docs/current/static/libpq-pgpass.html– a_horse_with_no_name
Feb 24 '17 at 6:54
Can you please show the full
pg_hba.conf? I mean all lines that are not commented out.– dezso
Feb 24 '17 at 7:35
I had a look at the
pgpass.confand the postgres password was indeed stored there in plain text. I commented the lines out and now psql prompts for password when I login as postgres.– pazeltov
Feb 24 '17 at 15:04